Clarification on DateTStamp Filtering and API Behavior Across Multiple Open Dental Modules

[Ummehani]

I'm working with various Open Dental API modules to sync data, and I have several questions regarding the handling of specific parameters like `DateTStamp` and the behavior of certain endpoints. Below are my questions for different modules:

FamilyModule

1. Tables used in GET Insurance response:
What are the names of the tables that generate the GET Insurance response in the FamilyModule? I'm trying to understand which tables are involved in fetching insurance data through the API.

OperatoriesModule

1. GET operatories/1 behavior:
When I call the `GET operatories/1` API with a specific request parameter, it returns the entire list of operatories instead of the requested single record. Is this the expected behavior?

2. DateTStamp missing in response:
The response from the `GET operatories` endpoint does not include the `DateTStamp` field, even though this column exists in the `Operatory` table. Is there a reason why this field is not being returned?

3. DateTStamp filter not working:
When I call `GET operatories?DateTStamp=2024-10-09` with a timestamp filter, it still returns the entire list of records instead of the ones matching the `DateTStamp`. Could you clarify how this parameter should work?

RefAttaches Module

1. DateTStamp filter not working:
I called `GET refattaches?DateTStamp=2024-10-08 03:12:16` to filter records by the `DateTStamp`, but the API returned the entire list instead of filtered records. Is this a bug, or am I using the parameter incorrectly?

2. Empty response on POST:
When using the `POST /refattaches` endpoint to insert data, the response model shown in the documentation is not returned in Postman. Instead, I receive an empty result with a `201 Created` status. Is this the expected behavior for successful insertions?

Recalls Module

1. DateTStamp filter not working:
When calling `GET recalls?DateTStamp=2024-10-08 03:12:16`, I expect to see only records updated after that timestamp, but the entire list is returned. Can you provide guidance on how to use the `DateTStamp` filter effectively?

ProcedureLogs Module

1. GET procedurelogs/20 behavior:
I called `GET procedurelogs/20` expecting a single record, but the API returned the entire list of procedure logs instead. Is this normal behavior, or should I be getting only the record with the specified ID?

PatientNote Module

There is no `DateTStamp` column in the `PatientNote` table. Can data sync be achieved using the `SecDateTEdit` column instead?

ProcedureCodes Module

1. GET procedurecodes/81 behavior:
When I call `GET /procedurecodes/81`, the API returns multiple records instead of the single one specified. Is this expected?

2. Missing fields in response:
The response from the API in Postman contains fewer fields than what is listed in the documentation. Could you explain the discrepancy between the documented response model and what is returned via the API?

3. DateTStamp filter not working:
I used `GET procedurecodes?DateTStamp=2024-10-08 03:12:16` to filter records by timestamp, but the API returned the entire list instead. How can I ensure that only records after the specified timestamp are fetched?

ProcNote Module

1. GET method not valid:
When attempting to call `GET /procnotes`, the API returns the message "procnotes GET is not a valid method." Is this endpoint supported for fetching records?

2. No DateTStamp in table:
There is no `DateTStamp` column in the `ProcNote` table, and there is no update API available for this resource. Can I use the `EntryDateTime` column for data sync?

TreatPlan Module

There is no `DateTStamp` column in the `TreatPlan` table. Can the `SecDateTEdit` column be used for data sync in place of `DateTStamp`?

RecallType Module

When accessing the `RecallTypes` resource, the API returns "Recalltypes is not a valid resource." Is this endpoint supported, or is it currently unavailable?

Referrals Module

1. DateTStamp missing in response:
In the Referrals module, the `DateTStamp` field is present in the `Referral` table, but it is not included in the API response model. Is there a reason for this?

2. DateTStamp filter not working:
I called `GET referrals?DateTStamp=2024-10-08 03:12:16` expecting a filtered list of records, but the API returned the entire list instead. How should I properly use the `DateTStamp` filter for this endpoint?

Any guidance or best practices on resolving these issues and syncing data efficiently using `DateTStamp` filtering would be greatly appreciated! Thank you.

[justine]

I'm working with various Open Dental API modules to sync data, and I have several questions regarding the handling of specific parameters like `DateTStamp` and the behavior of certain endpoints. Below are my questions for different modules:

FamilyModule

1. Tables used in GET Insurance response:
What are the names of the tables that generate the GET Insurance response in the FamilyModule? I'm trying to understand which tables are involved in fetching insurance data through the API.

1. FamilyModules GET Insurance states what tables are referenced when calling this endpoint.

OperatoriesModule

1. GET operatories/1 behavior:
When I call the `GET operatories/1` API with a specific request parameter, it returns the entire list of operatories instead of the requested single record. Is this the expected behavior?

2. DateTStamp missing in response:
The response from the `GET operatories` endpoint does not include the `DateTStamp` field, even though this column exists in the `Operatory` table. Is there a reason why this field is not being returned?

3. DateTStamp filter not working:
When I call `GET operatories?DateTStamp=2024-10-09` with a timestamp filter, it still returns the entire list of records instead of the ones matching the `DateTStamp`. Could you clarify how this parameter should work?

1. What version are you on? Operatories GET (single) was added in version 24.1.10.

2. The reason that operatory.DateTStamp is not being returned, is because it is not listed as a returnable field in the API documentation. Not all tables have DateTStamp fields.

3. The DateTStamp field is not working because it is not a supported parameter. Returning the field, and supporting it as a filter are two different things.

RefAttaches Module

1. DateTStamp filter not working:
I called `GET refattaches?DateTStamp=2024-10-08 03:12:16` to filter records by the `DateTStamp`, but the API returned the entire list instead of filtered records. Is this a bug, or am I using the parameter incorrectly?

2. Empty response on POST:
When using the `POST /refattaches` endpoint to insert data, the response model shown in the documentation is not returned in Postman. Instead, I receive an empty result with a `201 Created` status. Is this the expected behavior for successful insertions?

1. DateTStamp filtering will not function if it is not a supported refattaches GET parameter.

2. What version are you on? Refattaches POST was in enhanced in version 23.3.26. This enhancement added additional fields along with returning the object.

Recalls Module

1. DateTStamp filter not working:
When calling `GET recalls?DateTStamp=2024-10-08 03:12:16`, I expect to see only records updated after that timestamp, but the entire list is returned. Can you provide guidance on how to use the `DateTStamp` filter effectively?

1. Recalls GET does not list DateTStamp as a parameter. However, you are free to filter your results anyway you'd like, after you receive them.

ProcedureLogs Module

1. GET procedurelogs/20 behavior:
I called `GET procedurelogs/20` expecting a single record, but the API returned the entire list of procedure logs instead. Is this normal behavior, or should I be getting only the record with the specified ID?

1. What version are you on? Procedurelogs GET (single) was added in version 23.3.13.

PatientNote Module

There is no `DateTStamp` column in the `PatientNote` table. Can data sync be achieved using the `SecDateTEdit` column instead?

PatientNotes does not contain a DateTStamp field and patientnotes GET does not return SecDateTEdit or SecDateTEntry fields. I do not understand the value of constantly syncing entire tables. Are you planning on calling every one of these listed endpoints repeatedly every minute of the day?

ProcedureCodes Module

1. GET procedurecodes/81 behavior:
When I call `GET /procedurecodes/81`, the API returns multiple records instead of the single one specified. Is this expected?

2. Missing fields in response:
The response from the API in Postman contains fewer fields than what is listed in the documentation. Could you explain the discrepancy between the documented response model and what is returned via the API?

3. DateTStamp filter not working:
I used `GET procedurecodes?DateTStamp=2024-10-08 03:12:16` to filter records by timestamp, but the API returned the entire list instead. How can I ensure that only records after the specified timestamp are fetched?

1. What version are you on? ProcedureCodes GET (single) was added in version 23.3.25.

2. What version are you on? Procedurecodes was enhanced in version 23.3.25.

3. What version are you on? Procedurecodes DateTStamp was added in version 22.4.8.

ProcNote Module

1. GET method not valid:
When attempting to call `GET /procnotes`, the API returns the message "procnotes GET is not a valid method." Is this endpoint supported for fetching records?

2. No DateTStamp in table:
There is no `DateTStamp` column in the `ProcNote` table, and there is no update API available for this resource. Can I use the `EntryDateTime` column for data sync?

1. What version are you on? ProcNotes GET was added in version 24.2.29.

2. The API can only filter by parameters listed for the specific endpoint. EntryDateTime is not a filterable parameter for the procnotes endpoint. EntryDateTime the server time when the row is inserted. It is never updated again.

TreatPlan Module

There is no `DateTStamp` column in the `TreatPlan` table. Can the `SecDateTEdit` column be used for data sync in place of `DateTStamp`?

Please see TreatPlans GET.

RecallType Module

When accessing the `RecallTypes` resource, the API returns "Recalltypes is not a valid resource." Is this endpoint supported, or is it currently unavailable?

What version are you on? RecallTypes GET (multiple) was added in version 24.1.44.

Referrals Module

1. DateTStamp missing in response:
In the Referrals module, the `DateTStamp` field is present in the `Referral` table, but it is not included in the API response model. Is there a reason for this?

2. DateTStamp filter not working:
I called `GET referrals?DateTStamp=2024-10-08 03:12:16` expecting a filtered list of records, but the API returned the entire list instead. How should I properly use the `DateTStamp` filter for this endpoint?

Any guidance or best practices on resolving these issues and syncing data efficiently using `DateTStamp` filtering would be greatly appreciated! Thank you.

1. DateTStamp is not returned in the API endpoint. 99% of API users do not use the DateTStamp field.

2. The API can only filter by parameters listed for the specific endpoint. Referrals GET does not return the DateTStamp field and thus, does not support filtering by DateTStamp.


I question the need to call all these endpoints constantly to keep some 1:1 copy of database records. Most API developers do not do this. If you do need a massive amount of data, it is much wiser to use the queries POST endpoint and gather data in bulk, formatted however you want it.

[Ummehani]

Thank you for your detailed response regarding my questions about the Open Dental API modules. I appreciate the clarifications provided. I have a few follow-up questions based on your insights:

FamilyModule
1. GET Insurance Response Tables: Could you specify the names of the tables that generate the GET Insurance response in the FamilyModule? I would like to obtain a select query for this response.
2. DateTStamp Parameter: I noticed that there is no `DateTStamp` parameter in the FamilyModule response. Could you clarify why this is the case?

ProcedureLogs Module
1. DateTStamp Filtering: When I called `GET procedurelogs?DateTStamp=2024-10-08 03:12:16`, it returned the entire list of records instead of filtering by the specified timestamp. Is there a reason for this behavior?

Additional Clarifications
1. For the ProcNote endpoint, I understand there’s no available parameter for syncing data. What would be the recommended approach for data synchronization in this case?
2. Regarding the Referrals endpoint, I noticed that the `DateTStamp` field is present, but it does not support filtering. Could you provide guidance on how I might effectively sync data from this endpoint?

Appointments Module
I encountered an issue with the Appointments module when trying to fetch slots. Specifically, I received a "date is invalid" response when hitting the following URL in Postman:
- URL: `https://api.opendental.com/api/v1/appoi ... 2024-10-08`
Could you please help clarify what might be causing this issue?

[justine]

Thank you for your detailed response regarding my questions about the Open Dental API modules. I appreciate the clarifications provided. I have a few follow-up questions based on your insights:

FamilyModule
1. GET Insurance Response Tables: Could you specify the names of the tables that generate the GET Insurance response in the FamilyModule? I would like to obtain a select query for this response.
2. DateTStamp Parameter: I noticed that there is no `DateTStamp` parameter in the FamilyModule response. Could you clarify why this is the case?

1. Open Dental calls this method when opening the Family Module. FamilyModules GET Insurance states what tables are referenced when calling this endpoint.

API_familymodulehighlight.jpg (228.11 KiB) Viewed 4620 times

2. There can be no DateTStamp parameter in the familymodules response because it is derived from multiple tables.

ProcedureLogs Module
1. DateTStamp Filtering: When I called `GET procedurelogs?DateTStamp=2024-10-08 03:12:16`, it returned the entire list of records instead of filtering by the specified timestamp. Is there a reason for this behavior?

1. I tested `GET procedurelogs?DateTStamp=2024-10-08 03:12:16` on version 24.2.28, and the return was filtered as expected. What version are you on? DateTStamp was added in version 21.3.34.

Additional Clarifications
1. For the ProcNote endpoint, I understand there’s no available parameter for syncing data. What would be the recommended approach for data synchronization in this case?
2. Regarding the Referrals endpoint, I noticed that the `DateTStamp` field is present, but it does not support filtering. Could you provide guidance on how I might effectively sync data from this endpoint?

1. I question the need to keep a constant, up to the minute, dataset. I would only call procnotes GET with patNum and procNum parameters, as needed, for the specific patient and procedure.

2. I wouldn't keep a constant sync of data, I'd call referrals GET (single) as needed, or I'd call referrals GET (multiple) and use the available parameters and pagination. API results are returned 100 at at time. After filtering, most offices will return 100 referrals or fewer. Alternatively, you can use a ShortQuery or Query to get data formatted however you'd like.

Appointments Module
I encountered an issue with the Appointments module when trying to fetch slots. Specifically, I received a "date is invalid" response when hitting the following URL in Postman:
- URL: `https://api.opendental.com/api/v1/appoi ... 2024-10-08`
Could you please help clarify what might be causing this issue?

1. You are trying to fetch appointment slots that are in the past. The date parameter must be today or a future date.

Thanks!

[Ummehani]

Thank you for your insights and clarifications regarding my previous questions. I have some additional inquiries based on the responses I received:

ProcedureLogs Module
1. GET /procedurelogs Response: In the response from the `GET /procedurelogs` endpoint, I noticed the fields "Priority" and "priority." Could you clarify which database tables are used to populate these fields?

FamilyModule
1. DateTStamp and Main Table: In the FamilyModule, which field is considered the `DateTStamp`, and which table is regarded as the main table for generating the response?
2. Query Confirmation: I have generated the following SQL query. Will it provide me with an identical response to what we receive from the Family Module GET API? Since there is no option to get multiple records, I plan to use this query for API queries and want to confirm its validity.

Code: Select all

SELECT
       pp.PatNum,
       insub.InsSubNum,
       insub.Subscriber,
       CONCAT(pat.FName, ' ', pat.LName) AS subscriber,
       insub.SubscriberID,
       insub.SubscNote AS SubscNote,
       pp.PatPlanNum,
       pp.Ordinal,
       CASE 
           WHEN pp.Ordinal = 1 THEN 'Primary'
           WHEN pp.Ordinal = 2 THEN 'Secondary'
           WHEN pp.Ordinal = 3 THEN 'Tertiary'
           ELSE 'Other'
       END AS ordinal,
       CASE 
           WHEN pp.IsPending = 1 THEN 'true'
           ELSE 'false'
       END AS IsPending,
       pp.Relationship,
       pp.PatID AS PatID,
       car.CarrierNum,
       car.CarrierName,
       plan.PlanNum,
       plan.GroupName AS GroupName,
       plan.GroupNum AS GroupNum,
       plan.PlanNote AS PlanNote,
       plan.FeeSched,
       fs.Description AS feeSchedule,
       plan.PlanType AS PlanType,
       CASE 
           WHEN plan.PlanType = '' THEN 'Category Percentage'
           WHEN plan.PlanType = 'p' THEN 'PPO Percentage'
           WHEN plan.PlanType = 'f' THEN 'Flat Copay'
           WHEN plan.PlanType = 'c' THEN 'Capitation'
           ELSE 'Other'
       END AS planType,
       plan.CopayFeeSched,
       plan.EmployerNum AS PlanEmployerNum,
       empPlan.EmpName AS PlanEmployerName,
       pat.EmployerNum AS PatientEmployerNum,
       empPat.EmpName AS PatientEmployerName,
       CASE 
           WHEN plan.IsMedical = 1 THEN 'true'
           ELSE 'false'
       END AS IsMedical
   FROM
       patplan pp
   INNER JOIN
       inssub insub ON pp.InsSubNum = insub.InsSubNum
   INNER JOIN
       patient pat ON pp.PatNum = pat.PatNum
   INNER JOIN
       insplan plan ON insub.PlanNum = plan.PlanNum
   INNER JOIN
       carrier car ON plan.CarrierNum = car.CarrierNum
   LEFT JOIN
       feesched fs ON plan.FeeSched = fs.FeeSchedNum
   LEFT JOIN
       employer empPlan ON plan.EmployerNum = empPlan.EmployerNum
   LEFT JOIN
       employer empPat ON pat.EmployerNum = empPat.EmployerNum;

Additional Modules
1. AppointmentTypes, ApptFields, and ApptFieldDefs: For these modules, there are no columns available to sync Open Dental data to our database. Could you suggest how I can sync this data effectively?
2. RecallTypes Table: In the RecallTypes table, there is no column for syncing Open Dental data. Do you have any suggestions on how we can address this issue?
3. Software Update: If we update the Open Dental software version, will the data remain intact, or will it be cleared during the update process?

Thank you for your continued assistance. I look forward to your insights!

[justine]

Thank you for your insights and clarifications regarding my previous questions. I have some additional inquiries based on the responses I received:

ProcedureLogs Module
1. GET /procedurelogs Response: In the response from the `GET /procedurelogs` endpoint, I noticed the fields "Priority" and "priority." Could you clarify which database tables are used to populate these fields?

1. Priority: Definition.DefNum where definition.Category=20. priority: String version of Priority.

FamilyModule
1. DateTStamp and Main Table: In the FamilyModule, which field is considered the `DateTStamp`, and which table is regarded as the main table for generating the response?
2. Query Confirmation: I have generated the following SQL query. Will it provide me with an identical response to what we receive from the Family Module GET API? Since there is no option to get multiple records, I plan to use this query for API queries and want to confirm its validity.

Code: Select all

SELECT
       pp.PatNum,
       insub.InsSubNum,
       insub.Subscriber,
       CONCAT(pat.FName, ' ', pat.LName) AS subscriber,
       insub.SubscriberID,
       insub.SubscNote AS SubscNote,
       pp.PatPlanNum,
       pp.Ordinal,
       CASE 
           WHEN pp.Ordinal = 1 THEN 'Primary'
           WHEN pp.Ordinal = 2 THEN 'Secondary'
           WHEN pp.Ordinal = 3 THEN 'Tertiary'
           ELSE 'Other'
       END AS ordinal,
       CASE 
           WHEN pp.IsPending = 1 THEN 'true'
           ELSE 'false'
       END AS IsPending,
       pp.Relationship,
       pp.PatID AS PatID,
       car.CarrierNum,
       car.CarrierName,
       plan.PlanNum,
       plan.GroupName AS GroupName,
       plan.GroupNum AS GroupNum,
       plan.PlanNote AS PlanNote,
       plan.FeeSched,
       fs.Description AS feeSchedule,
       plan.PlanType AS PlanType,
       CASE 
           WHEN plan.PlanType = '' THEN 'Category Percentage'
           WHEN plan.PlanType = 'p' THEN 'PPO Percentage'
           WHEN plan.PlanType = 'f' THEN 'Flat Copay'
           WHEN plan.PlanType = 'c' THEN 'Capitation'
           ELSE 'Other'
       END AS planType,
       plan.CopayFeeSched,
       plan.EmployerNum AS PlanEmployerNum,
       empPlan.EmpName AS PlanEmployerName,
       pat.EmployerNum AS PatientEmployerNum,
       empPat.EmpName AS PatientEmployerName,
       CASE 
           WHEN plan.IsMedical = 1 THEN 'true'
           ELSE 'false'
       END AS IsMedical
   FROM
       patplan pp
   INNER JOIN
       inssub insub ON pp.InsSubNum = insub.InsSubNum
   INNER JOIN
       patient pat ON pp.PatNum = pat.PatNum
   INNER JOIN
       insplan plan ON insub.PlanNum = plan.PlanNum
   INNER JOIN
       carrier car ON plan.CarrierNum = car.CarrierNum
   LEFT JOIN
       feesched fs ON plan.FeeSched = fs.FeeSchedNum
   LEFT JOIN
       employer empPlan ON plan.EmployerNum = empPlan.EmployerNum
   LEFT JOIN
       employer empPat ON pat.EmployerNum = empPat.EmployerNum;

1. As previously stated, there is no DateTStamp field for the FamilyModules GET Insurance endpoint. There cannot be one since this particular endpoint is derived from many tables. None of the tables are considered the 'main table'.

2. I do not review queries for validity. Queries written by third parties are outside the scope of my responsibility.

Additional Modules
1. AppointmentTypes, ApptFields, and ApptFieldDefs: For these modules, there are no columns available to sync Open Dental data to our database. Could you suggest how I can sync this data effectively?
2. RecallTypes Table: In the RecallTypes table, there is no column for syncing Open Dental data. Do you have any suggestions on how we can address this issue?
3. Software Update: If we update the Open Dental software version, will the data remain intact, or will it be cleared during the update process?

Thank you for your continued assistance. I look forward to your insights!

1. As previously stated, I would not sync data via the API. Instead, I recommend calling individual endpoints, as needed.

2. I do not recommend using the API to sync data.

3. The data will remain intact.