Failed to retrieve all Payment Plan charges including future ones.

[joergzastrau]

Dear all,

the following SQL Query to count all Charges for a Payment Plan visible in the Open Dental GUI failed on me:

API: GET /payplancharges?PayPlanNum=123

SQL:

Code: Select all

-- Get the total number of charges
LEFT JOIN (
	SELECT 
		PayPlanNum, 
	COUNT(ChargeDate) AS TotalChargesCount
	FROM payplancharge
	GROUP BY PayPlanNum
) charges ON pp.PayPlanNum = charges.PayPlanNum

Both return the same number of charges including some future Charges (but not all).

I am using Open Dental Version 25.4.80.

What am I missing? I would appreciate any suggestions.

Joerg

[joergzastrau]

Dear all,

Follow-up to myself with a theory: It appears that only those Payment Plan Charges can be retrieved if SecDateTEdit is set, e.g. by manually editing a future charge in the GUI. I assume that the other charges I see in the GUI are automatically re-created when opening the dialog and actually not stored until edited. Correct?

Example (see Attachments): After editing Charge #10 the API will return 10 table entries (0-9). Charge #11 has an creation date, but no edit date. As soon as I open the charge and saving it, an edit date will be added and the charge will be retrievable using the API.

I just realised that a future parameter for the API request "GET payplancharges" called "getExpected" is not yet implemented. Is there an ETA for this?

The use case is for orthodontic treatment to estimate if the current payment plan needs to be adjusted according to treatment progress. For now we do this manually. E.g. if the estimated remaining treatment time is 4 months and we have another 6 months expected charges then obviously we have to address this issue with the patient.

Best regards

Joerg

[justine]

Dear all,

the following SQL Query to count all Charges for a Payment Plan visible in the Open Dental GUI failed on me:

API: GET /payplancharges?PayPlanNum=123

SQL:

Code: Select all

-- Get the total number of charges
LEFT JOIN (
	SELECT 
		PayPlanNum, 
	COUNT(ChargeDate) AS TotalChargesCount
	FROM payplancharge
	GROUP BY PayPlanNum
) charges ON pp.PayPlanNum = charges.PayPlanNum

Both return the same number of charges including some future Charges (but not all).

I am using Open Dental Version 25.4.80.

What am I missing? I would appreciate any suggestions.

Joerg

Dear all,

Follow-up to myself with a theory: It appears that only those Payment Plan Charges can be retrieved if SecDateTEdit is set, e.g. by manually editing a future charge in the GUI. I assume that the other charges I see in the GUI are automatically re-created when opening the dialog and actually not stored until edited. Correct?

Example (see Attachments): After editing Charge #10 the API will return 10 table entries (0-9). Charge #11 has an creation date, but no edit date. As soon as I open the charge and saving it, an edit date will be added and the charge will be retrievable using the API.

I just realised that a future parameter for the API request "GET payplancharges" called "getExpected" is not yet implemented. Is there an ETA for this?

The use case is for orthodontic treatment to estimate if the current payment plan needs to be adjusted according to treatment progress. For now we do this manually. E.g. if the estimated remaining treatment time is 4 months and we have another 6 months expected charges then obviously we have to address this issue with the patient.

Best regards

Joerg

Hello Joerg,

Thanks for the detailed explanation and screenshots. Your understanding is correct, the "getExpected" functionality for GET /payplancharges has not been implemented yet.

The feature is currently in our API development queue. We don't have an ETA at this time, but we will reply to this thread once complete.

Thanks!