5 Replies Latest reply on Mar 27, 2016 10:35 PM by fmdataweb

    Create List of Outstanding Invoices


      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?

        • 1. Re: Create List of Outstanding Invoices

          Just giving this a bump to see if anyone has any suggestions in case this was missed - no one chimed in the first time around but I'm hoping many of you out there have had to tackle this before in similar Invoicing solutions.

          • 2. Re: Create List of Outstanding Invoices

            I'll stab at this.


            This is, I think, I familiar challenge for many.


            I think I've probably tried most, if not all, of the ways around this over the years. My recommendation would be to set an "Open" flag that gets set by script trigger when entries are made in invoice line items or payment line items. Make sure you save the entire transaction in one go, though (see Todd Geist's writings on this), so you don't end up committing a quantity or price without committing the change to the open flag, or vice-versa.


            Alternate approaches:


            1. With a calculated "open" flag: You could relate from Payments to Invoices where customer=customer (all the customer's invoices), then from there a self-relationship fom PAY_INV to PAY_INV_INV_open where inv::id = inv::id and calcOpenFlag = 1. It's a workaround for when you can't get the unstored calc on the "left", but be aware that this can be a performance killer if the one customer has a heck of a lot of invoices.


            2. With a calculated "open" flag: You could script searching for open invoices, grabbing their keys, then use the "magic value lists" technique to create a value list based on the global variable holding your invoice keys. http://www.modularfilemaker.org/module/virtual-value-list/ Again, not great performance-wise because the search on the unstored open flag is going to take a while.


            3. With a set "open" flag, but no script triggers: You could run a server-side script periodically (somewhat frequently) to find any modified payment or invoice line items and reset the flag for the related invoices. You might want to do this anyway even if you go the script trigger route, just to "bat cleanup" in case someone manages to edit a line item and get around your script triggers.


            Options 1 and 2 tend to work for smaller solutions, but bog down for larger ones. Option 3 leaves you dependent on the timing of a server-side script. I've seen, and built, solutions that use each of these methods and combinations thereof, but I'd recommend in 20/20 hindsight that the script triggers are the way to go. It "feels" harder and less dynamic, but in the end I think you'll find it's actually less hassle, especially as the solution grows.


            So, that's my two cents, for what they're worth. I hope this helps. Better yet, I hope someone posts a better and easier way for you. I'll be watching this thread.



            Chris Cain


            • 3. Re: Create List of Outstanding Invoices

              Could you create a status field in the invoice that gets flagged as being paid when the invoice is paid in full.  Then used that field for your relationship. 

              • 4. Re: Create List of Outstanding Invoices

                Thanks Chris,


                I had another "status" field that I was using to indicate the status of the Invoice (Paid, Open etc) and was using that to establish a relationship to the Payments table. I was hoping to do it without an additional field but it looks like it's not possible.


                We can't rely on a server side script to run so it looks like we'll have to go with the trigger based approach.


                Appreciate you chiming in.

                • 5. Re: Create List of Outstanding Invoices

                  Thanks - it looks like this is the way to go with a trigger that updates a secondary field to to track the "status" of the Invoice.