Open Dental Forum

Hi All,

Does anyone know how or where I can change, so that the Daily Production and Income Report is sorted base on the patient's appointment completion, rather then by Alphabetical order of Patient Name?

Thanks

FormRpProdInc.RunDaily() is where you would want to start.

Since the query doesn't make use of appointments you would need to JOIN appointments in the query and then change the ORDER BY at the end to use the appointment date instead of the proc date.

This line: +") ORDER BY "+DbHelper.UnionOrderBy("procdate",1)+","+DbHelper.UnionOrderBy("namelf",2);
would need to change to something like: +") ORDER BY "+DbHelper.UnionOrderBy("AptDateTime",1)+","+DbHelper.UnionOrderBy("namelf",2);

Hi Allen,

The AptNum attribute is in the procedurelog table, but not in other tables, eg. claimproc, paysplit, payment, etc.., that forms the hug UNION ALL query, which uses PayDate, ClaimDate, etc..

Do you know of anyone has done this before?

Thanks

Programmatically it's very unlikely. Even if someone did in fact do something like this, we would have no way of knowing it.

If someone paid us to do something like this for them in a custom query it will be found here:
http://opendentalsoft.com:1942/ODQueryL ... yList.aspx

So I was able to make the change to create production report to display in chronological order rather than by lname.

in RunDaily()

-- at the begining of the query
//-----------------
653: //+DbHelper.DateColumn("procedurelog.ProcDate")+" procdate,"
654: +"procedurelog.DateTStamp procdate,"
//-----------------
+"CONCAT(CONCAT(CONCAT(CONCAT(patient.LName,', '),patient.FName),' '),patient.MiddleI) namelf,"
...
...
687: // +"GROUP BY procedurelog.ProcNum "
688: + "GROUP BY procedurelog.DateTStamp,patient.LName,procedurelog.CodeNum "
...
...
at the end of the query
...
...
951: // + ") ORDER BY " + DbHelper.UnionOrderBy("procdate", 1)+"," + DbHelper.UnionOrderBy("namelf", 2);
952: + ")";

btw,

This will produce 3 sections in the report, all production income in the beginning of the report, then 2nd section is for writeoff and adjustment, then 3rd is for payment.

tomle wrote:...sorted base on the patient's appointment completion, rather then...

Well, I guess you got kind of got close to what you were asking for. You actually sorted it by the last time the procedure row in the database was / is manipulated. So your results will change as soon as someone goes in and changes (updates a note, etc.) about the procedure... Not sure if that's really what you are wanting.

Make sure that you read our database documentation if you are ever unsure about what a column does.

DateTStamp timestamp Automatically updated by MySQL every time a row is added or changed.

http://opendental.com/OpenDentalDocumen ... ocedurelog