Page 1 of 1
16.2 -Bad debt -Adjustment Categorization Query
Posted: Thu Jul 14, 2016 12:28 am
by drmkpillai
Hi all,
Using OD 16.2.30- Great New Feature of being able to categorize Certain Adjustment Types as Bad Debts.
Would like to know, the table structure to generate specific queries,Such Total adjustment in a period that are Bad debts
Thanking all
Re: 16.2 -Bad debt -Adjustment Categorization Query
Posted: Thu Jul 14, 2016 6:03 pm
by cmcgehee
To determine which adjustment types are considered Bad Debt, you would look in the
preference table. There will be a row that has a
PrefName of
BadDebtAdjustmentTypes. The
ValueString for that row will be a comma-delimited list of numbers. Each of those values will be a pointer to the
DefNum on the
definition table. These definitions are your bad debt adjustment types. Here's a simple query that will sum up bad debt adjustments:
Code: Select all
SELECT SUM(adjustment.AdjAmt)
FROM adjustment
INNER JOIN definition ON definition.DefNum=adjustment.AdjType
AND FIND_IN_SET(definition.DefNum,(SELECT ValueString FROM preference WHERE PrefName='BadDebtAdjustmentTypes'))>0
Happy querying!
Re: 16.2 -Bad debt -Adjustment Categorization Query
Posted: Fri Jul 15, 2016 10:52 am
by JoeMontano
In addition to Chris, here is a link to our database documentation/structure that we provide on our website.
http://opendental.com/manual/programmingresources.html
Re: 16.2 -Bad debt -Adjustment Categorization Query
Posted: Thu Jul 21, 2016 6:19 pm
by drmkpillai
Thanks for all your support