I have what seems to be an interesting challenge; how to calculate commissions based on paid project based invoices. This differs from many other commission based systems based on accrued sales. Commissions at this company are based on payments received on project issued or related invoices.
I can see payments coming into table ARPayments based on a calendar date or fiscal period. But when I try to connect those payments to the project that initiated the invoice and payment, that is where my challenge begins. If I am not on the right path, please put forward some alternative suggestions.
Some constraints:
- All payments are initiated by invoices
- either directly issued through AR (so the project is referenced in the details tab) or
- through a project generated invoice.
- So, at least theoretically, a payment should not received unless an invoice, that is in AR, has been issued.
If I start a GI or report from the ARPayments table, to capture all payments based on a calendar period, and then connect to:
- If I look at the applications tab on the payment to get the invoice (with the intent connecting to ARInvoice to connect to the Project and the Project Manager (field=OwnerID), that is table ARTranPostBal, to which there is no connection to any other table. I have tried in GI and Report Designer, I can not connect to that table.
- Since I can not connect to ARTranPostBal, I tried to connect to variants of ARTran* but all either fail to connect or create duplicate values for each payment.
- The connection I have been fighting with is: ARPayment Β» ARTran* (there are a few tables, ARTranPostBal has no connections even though it is referenced on the applications tab of the payments and applications application) Β» ARInvoice Β» PMProject (to get the project ID and Project Manager [OwnerID]).
- It fails between ARTran* and ARInvoice.
So here is my question for the community, can you suggest a connection between an actual payment in ARPayment table and relate it to a source Project (in PMProject) that should have come from ARInvoice table in 1 report? By extension, a payment taken/posted in ARPayment should fail to find a connection to a payment if it is unapplied to an invoice. It should be listed as a payment received in the reporting period, but fail to find a connection to an invoice.
My current solution is to connect ARPayment to ARTranPost to get the invoice number related to a payment and then a separate GI to extract ARInvoice to PMProject. Then export both to Excel and via a Vlookup to connect Project IDβs and Project Managers to payments for reporting purposes. While it seems to work so far, it feels like a labour intensive solution.
I would like to think Acumtica can do better. Can someone redirect my thinking and solve my challenge⦠please⦠:-)