Timestamp on Payment or PaySplit
Timestamp on Payment or PaySplit
I can't find anything that stores the time when a payment was made. I only see dates. Is there another table besides Payment and Paysplit that might have a timestamp? If not, can you add a timestamp to one of these tables?
We have 9 offices using OD now and 35+ providers. I'd like to create some notifications during the day about collections (payments) but need a timestamp to read when the payment was created.
Thanks.
We have 9 offices using OD now and 35+ providers. I'd like to create some notifications during the day about collections (payments) but need a timestamp to read when the payment was created.
Thanks.
Re: Timestamp on Payment or PaySplit
I can definitely ask but I wouldn't plan on this change happening any time soon mainly because this type of a change does not directly affect users but instead only improves the code "under the hood".
You might be able to get a rough idea on the time based on the PaymentCreate audit log entries. However, the logs are not associated to the payments in particular so if multiple payments are made on the same day (sounds like your issue) you will not be able to tell for certainty which time goes to which payment.
You might be able to get a rough idea on the time based on the PaymentCreate audit log entries. However, the logs are not associated to the payments in particular so if multiple payments are made on the same day (sounds like your issue) you will not be able to tell for certainty which time goes to which payment.
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: Timestamp on Payment or PaySplit
Hello,
I believe you are correct in that the Payment only has a date of when it is entered and has no hours/minutes information associated with it. Generally we do not keep track of that level of granularity and after researching this issue for you there does not seem to be a way to associate payments with any sort of hour/minute information directly. As for adding this information to the tables, updating or modifying the database is very dangerous and complicated due to its complexity and the way Open Dental interacts with it, and as Jason has said already it may take a while to get changed (If indeed it ever gets changed).
I believe you are correct in that the Payment only has a date of when it is entered and has no hours/minutes information associated with it. Generally we do not keep track of that level of granularity and after researching this issue for you there does not seem to be a way to associate payments with any sort of hour/minute information directly. As for adding this information to the tables, updating or modifying the database is very dangerous and complicated due to its complexity and the way Open Dental interacts with it, and as Jason has said already it may take a while to get changed (If indeed it ever gets changed).
"To understand what recursion is, you must first understand recursion."
David Graffeo
Open Dental Software
http://www.opendental.com
David Graffeo
Open Dental Software
http://www.opendental.com
Re: Timestamp on Payment or PaySplit
Agreed. I would imagine, if anything, we would simply add a new date time column called something along the lines of DateTStamp thus leaving the old ones alone but adding this extra functionality.dgraffeo wrote:As for adding this information to the tables, updating or modifying the database is very dangerous and complicated...
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
-
- Posts: 293
- Joined: Mon Apr 22, 2013 8:49 am
- Location: Dallas, TX
- Contact:
Re: Timestamp on Payment or PaySplit
Here's a basic concept that might be along the lines of what you're looking for. If you're running it in the User Query in Open Dental it will show the names and payment type names, but if you're running it as a MySQL query outside the program it will need a few more tweaks. It's not quite there, but it's a start.dporter wrote:I can't find anything that stores the time when a payment was made. I only see dates. Is there another table besides Payment and Paysplit that might have a timestamp? If not, can you add a timestamp to one of these tables?
We have 9 offices using OD now and 35+ providers. I'd like to create some notifications during the day about collections (payments) but need a timestamp to read when the payment was created.
Thanks.
Code: Select all
SET @SetDate='2014-11-13';
SELECT pm.PatNum, pm.PayType, pm.PayAmt, TIME_FORMAT(sl.LogDateTime, '%h:%i %p') AS TIME
FROM payment pm
INNER JOIN securitylog sl ON pm.PatNum=sl.PatNum
WHERE pm.PayDate=@SetDate AND sl.PermType=15
GROUP BY pm.PayNum;
Re: Timestamp on Payment or PaySplit
That query sort of works. But as Jason mentioned if there are two payments on the same date for that patient, that query would give incorrect results. This is because it just joined in two audit trail entries into one payment, and you just grouped by payment. So mysql is just picking one datetime at random from those two audit trail entries. Using audit trail entries gets even more complicated if any payments are deleted, or if the payment was edited to have a different paydate then it was initially created with.KevinRossen wrote:
Here's a basic concept that might be along the lines of what you're looking for. If you're running it in the User Query in Open Dental it will show the names and payment type names, but if you're running it as a MySQL query outside the program it will need a few more tweaks. It's not quite there, but it's a start.
Code: Select all
SET @SetDate='2014-11-13'; SELECT pm.PatNum, pm.PayType, pm.PayAmt, TIME_FORMAT(sl.LogDateTime, '%h:%i %p') AS TIME FROM payment pm INNER JOIN securitylog sl ON pm.PatNum=sl.PatNum WHERE pm.PayDate=@SetDate AND sl.PermType=15 GROUP BY pm.PayNum;
I wouldn't recommend trying to link audit trail entries like these to the database entries that created them unless the securitylog entry makes use of the FKey column, which payments don't currently do. Without the direct linkage there are too many factors that can cause you to get an unreliable/incorrect result.
-
- Posts: 293
- Joined: Mon Apr 22, 2013 8:49 am
- Location: Dallas, TX
- Contact:
Re: Timestamp on Payment or PaySplit
This one is a little better. It shows the first and last time a payment was created. It's not perfect, but imperfect is better than nothing.tgriswold wrote: That query sort of works. But as Jason mentioned if there are two payments on the same date for that patient, that query would give incorrect results. This is because it just joined in two audit trail entries into one payment, and you just grouped by payment. So mysql is just picking one datetime at random from those two audit trail entries. Using audit trail entries gets even more complicated if any payments are deleted, or if the payment was edited to have a different paydate then it was initially created with.
I wouldn't recommend trying to link audit trail entries like these to the database entries that created them unless the securitylog entry makes use of the FKey column, which payments don't currently do. Without the direct linkage there are too many factors that can cause you to get an unreliable/incorrect result.
Code: Select all
SET @SetDate='2014-11-14';
SELECT pm.PatNum, pm.PayType, pm.PayAmt, TIME_FORMAT(MIN(sl.LogDateTime), '%h:%i %p') AS FirstEntry, TIME_FORMAT(MAX(sl.LogDateTime), '%h:%i %p') AS LastEntry
FROM payment pm
INNER JOIN securitylog sl ON pm.PatNum=sl.PatNum
WHERE pm.PayDate=@SetDate AND sl.PermType=15 AND DATE(sl.LogDateTime)=@SetDate
GROUP BY pm.PayNum;
Re: Timestamp on Payment or PaySplit
I would also recommend using payment.DateEntry instead of payment.PayDate since payment.PayDate is user editable and you could technically enter a payment for a past/future date. Payment.DateEntry should always match the audit trail PaymentCreate permissions's date.