Syncing Patients Using Simple Patients API - DateTStamp Sorting and Error Handling

[Ummehani]

I'm working with the Simple Patients API to sync patient data, and I have a couple of questions regarding the handling of `DateTStamp` and record fetching.

1. Fetching in DateTStamp Order:
Is there a search parameter or any other way to fetch patient records in an ordered manner by `DateTStamp`? Currently, I am fetching patient data using the following API:

Code: Select all

https://api.opendental.com/api/v1/patients/Simple?Limit=100&Offset=0

But there doesn't seem to be a way to ensure the results are returned in the order of `DateTStamp`. This presents an issue when trying to sync patients. If one of the records in the middle fails to fetch, I’m unsure how to proceed with syncing the rest of the records, as they are not in the order of `DateTStamp`. Is there a recommended way to handle this?

2. DateTStamp Filter for Inserted/Updated Records:
When using the `DateTStamp` parameter to fetch records updated or inserted after a certain date (e.g., for syncing records from a specific point in time), will this ensure that all records are returned in chronological order of `DateTStamp`? For example:

Code: Select all

https://api.opendental.com/api/v1/patients/Simple?DateTStamp=2024-09-19%2005%3A30%3A00&Limit=100&Offset=0

In this case, does the API guarantee that the results are sorted by `DateTStamp`, or do we need to handle the sorting on our end? It seems challenging to sync records without having them returned in this order.

Any guidance or best practices on syncing large datasets with `DateTStamp` filtering would be greatly appreciated!

[SLeon]

Good morning,

The Patients GET Simple returns results sorted by PatNum (ascending). You are quite correct that if the API were to return unsorted results it would be difficult to get datasets complete/in sync. We call this "drift" in our documentation. Nearly all of our API GET endpoints sort by the Primary Key of their table. A notable exception is Appointments GET, which sorts by AptDateTime then AptNum, due to how appointments are used.

1. Fetching in DateTStamp Order:
Is there a search parameter or any other way to fetch patient records in an ordered manner by `DateTStamp`?

No. All API methods will return sorted by default and do not allow for custom sorting.

2. ... When using the `DateTStamp` parameter to fetch records updated or inserted ... will this ensure that all records are returned in chronological order of `DateTStamp`

No. Using an optional parameter does not change the default sorting in any way, including to sort by the parameter(s).

From your description, it sounds as though you are leveraging the DateTStamp parameter correctly. By using this parameter, you can only return records that have changed since the last time your application polled for the data. These results will be sorted by Primary Key, so while they they aren't ordered by date, they are ordered so that you can page/collect all the data in the set.

That process will work great for keeping your application's cache up-to-date, but the initial data grab would be a lengthy process. If this is something you are sure your application needs to do (eg Open Dental does not cache the patient table) then I would recommend using our Queries POST endpoint. This would allow you to query for the specific data you want, ORDER BY whichever parameters you want, and eliminate paging altogether.

Another strategy to keep an up-to-date list of patient data is to leverage Api Events. These are essentially identical to your application running using Patients GET Simple with the DateTStamp parameter, but Open Dental itself performs this logic automatically, and sends it to a webhook site you host. More information on ApiEvents can be found in our API Guide – Subscriptions and Events.

Happy to help if you have further questions.

[Ummehani]

Thank you for the helpful response to my last questions about syncing patient data with the Simple Patients API. Based on your guidance, I've developed a plan to initially fetch data using the Queries POST API and subsequently use the API calls for each module with the DateTStamp parameter. Is this approach effective, or do you have any additional suggestions?

Additionally, I've noticed that some modules, like Operatories and Referrals, do not include the DateTStamp parameter. Could you provide suggestions for handling these modules? Also, if you could share a list of modules that lack the DateTStamp parameter, that would be very helpful for my verification.

Lastly, I've encountered issues with some APIs returning the response "API xyz is not a valid method." Specifically, I've had trouble with:

procedurecodes (PUT)
procnotes (GET)
FamilyModules (GET Insurance)

Could you please provide insight into these issues?

Thank you for your assistance!

[SLeon]

Good morning,

Using Queries POST for the requisite large data grabs and keeping them up-to-date with the table's GET method and timestamp filter is a great strategy. I think you will do well with this approach.

DateTStamps
Here is the link to our Database Schema. Descriptions of fields you find in our API Documentation are meant to supplement this page. You will notice that for some tables, the "timestamp" column is not named the same. For example, in the adjustment table it is called SecDateTEdit. Look for "timestamp" in the Type column.

I believe every API GET method for a table that contains the timestamp column also returns that field in the results. Most of the GET (multiple) endpoints also have the ability to filter by the timestamp column. If there is an API method that has this column in the schema but does not have it as a filter that you need, please let us know and we will add it to our development list. Our current pattern is to always return and filter by timestamps, but some GET methods are older and might need this added.

We cannot change the Open Dental schema, so tables without a timestamp column must remain that way. Fortunately these tables are those where it wouldn't make much sense to have them (like the Preference table, linker tables, etc.). For these PatNum or other filters are more appropriate to obtain specific data (and not keep an up-to-date list).

Error Message

There are two common reasons for seeing this type of error message.

1. Typographical error in URL - Besides spelling, make sure the resource name is lowercase and any "non-crud" modifier is cased to match our documentation. E.g. "procedurecodes" and "familymodules/Insurance".
2. Version - The Open Dental API is versioned, and so the dental office/your test database must be high enough to use each method. You can find the requisite version at the top of each API method in our documentation.

If you continue to receive this error message after addressing the above points, please reply with your version and we can help troubleshoot.