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
Daily Producion and Income Report Sort order
Re: Daily Producion and Income Report Sort order
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);
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);
Re: Daily Producion and Income Report Sort order
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
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
Re: Daily Producion and Income Report Sort order
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
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
The best thing about a boolean is even if you are wrong, you are only off by a bit.
Jason Salmon
Open Dental Software
http://www.opendental.com
Jason Salmon
Open Dental Software
http://www.opendental.com
Re: Daily Producion and Income Report Sort order
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: + ")";
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: + ")";
Re: Daily Producion and Income Report Sort order
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.
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.
Re: Daily Producion and Income Report Sort order
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.tomle wrote:...sorted base on the patient's appointment completion, rather then...
Make sure that you read our database documentation if you are ever unsure about what a column does.
http://opendental.com/OpenDentalDocumen ... ocedurelogDateTStamp timestamp Automatically updated by MySQL every time a row is added or changed.
The best thing about a boolean is even if you are wrong, you are only off by a bit.
Jason Salmon
Open Dental Software
http://www.opendental.com
Jason Salmon
Open Dental Software
http://www.opendental.com