I have a fairly straightforward solution that records Invoices and Payments. I'm needing to modify the Payments data entry screen so that after selecting the Contact making the Payment I can then choose from a list of outstanding invoices (i.e any invoice where the balance owing is greater than 0).
The challenge I'm facing at the moment is that the Invoices::AmountOwing field is an unstored calculation as it's referencing the related Invoice Items and Payment Items to generate the Invoice Total and the Total Payments, so I can't create a relationship to Invoices from Payments based on the unstored calculation field.
Here's my structure at a high level:
Contacts > Invoices > Invoice Items
Contacts > Payments > Payment Items
Invoice Items is related to Payment Items by the InvoiceID number. What I'm trying to do is, when on a Payment record and having selected the Contact ID, I would then like to have a valuelist that users can simply select from that shows all Invoices with an outstanding amount owing.
Any one been down this road before?