Goal:
We are trying to create a report that shows the percentage of Sales Orders that have been paid on time vs Sales orders that were paid after the payment term due date.
Challenge:
We are unable to find the “Date Paid” on custom reporting pages. We checked both the Sales Order & the Payments & refunds on orders report page.
In both cases, we did not see a “Date Paid” field:
@luke was able to help us figure out the syntax for creating reports that identify Sales Orders that are PAST DUE, but the challenge now is to try and identify orders that are currently paid, but were late.
Please let me know if you need any additional info, thanks!
Sorry for the delay in getting back to you, and great to hear that you got the syntax for the other report sorted.
From the Payments & refunds on orders report, you can use the Date field for the date of the payment (or refund). You can also use the Is payment field to check you’re looking at a payment and not a refund as a single order can have multiple transactions against it.
Because an order can have multiple payments and refunds against it, this is why there is no “date paid” field on an order/invoice (what date would we use if it was partly paid last week and the remainder today?). I guess we could have a “date fully paid” field which would be helpful for you here and unambiguous. If this is something you’d like to see, could you add a feature request for this and we’ll look to get this added.
If you know that you only have one payment per order, then you can use the Payments & refunds on orders report as your starting point and “join” through to the invoice information from there. I hope this makes sense, but please let us know if you need anything else.
Yes, this is just like joining in SQL and in fact that’s how it works under the hood. If you choose a field with a dot in it with BrewwQL, or an arrow on the field selections in the Raw Data Explorer, these are joins to another table.
For example, in this screenshot (which is based on the Payments & refunds report), you can see the “Invoice” fields and these are from joining through from the payments/refunds data (table) through to the invoice data (table).
