How can I include date of last payment on our statements? There is currently a “last statement date” but I would rather change this to “last payment date”.
I think I need to add a payment table to the schema (likely ARPayment) but I haven’t been able to figure out how to then configure it on the Relationships tab. I believe from there I could just use =MAX([ARPayment.DocDate]) to display the most recent payment date.
Best answer by valentynbeznosiuk
Hello
I don’t think that you need to join the ARPayment table at all. The main table that is used on the Customer Statements report is ARStatementDetailInfo, which has DocType and DocDate fields. Having this you can use this expresion
=Max(IIf([ARStatementDetailInfo.DocType]='PMT',[ARStatementDetailInfo.DocDate],null))
In the example below, I did the same, but with [ARStatementDetailInfo.DocType]='INV' since I don’t have a lot of data to test with Payments, but I also tried it with Prepayments, and it worked as well, so it should work with Payments as well.
+11Hi
We also created a report similar to yours. I also thought of doing it like you said "use =MAX([ARPayment.DocDate]) to display the most recent payment date." However it was difficult to use MAX([ARPayment.DocDate] on Report Designer. So we had to create a new DAC by creating a SQL View to represent the MAX([ARPayment.DocDate] field.
Best Regards,
NNT
Hello
I don’t think that you need to join the ARPayment table at all. The main table that is used on the Customer Statements report is ARStatementDetailInfo, which has DocType and DocDate fields. Having this you can use this expresion
=Max(IIf([ARStatementDetailInfo.DocType]='PMT',[ARStatementDetailInfo.DocDate],null))
In the example below, I did the same, but with [ARStatementDetailInfo.DocType]='INV' since I don’t have a lot of data to test with Payments, but I also tried it with Prepayments, and it worked as well, so it should work with Payments as well.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
