4 Replies Latest reply on Mar 23, 2016 9:36 AM by jackmac

    Find invoices for each customer and total?


      Hi all,


      In my system I have say 10 customers, each customer rents products from us. If a customer has say three products, they currently receive an invoice for each product based on the day of the month they took it out.  I want to change that.  I would like my system to look through, find how many products that customer has, and create a summary invoice with a total due and the products listed. That way they get 1 invoice rather than 3.


      Any ideas?


      FM14 and Mac Mavericks

        • 1. Re: Find invoices for each customer and total?


          I would use a portal on an "invoice" layout to show products which the customer has rented out. Beneath the portal you can add a summary field for the amount due field from each product. I would then set the portal and summary field to "slide up" in the Inspector window during set up of the layout.



          • 2. Re: Find invoices for each customer and total?

            Jesse –


            a portal is a great UI device, but it is less well suited for printing. Conventional wisdom suggests to use a list layout based on an instance of the table with the greatest granularity – which in this case (as usual in a sales system) is the line items table (as in Customers --< LineItems >-- Products).


            Jack –

            the basic principle goes like this: write a script to …


            • search for all customers that have line items that haven't been charged yet

            • inside a loop, do this:

              • create a new invoice record for the current customer

              • go to a line items print layout and find all records for this customer that haven't been charged

              • give them all the new invoice ID and print them

              • return to customers and go to the next customer

            •  exit the loop when all customers in the found set have been processed


            This assumes that you have a separate Invoices table, and that each line item receives the foreign key (ID) of the invoice it appears on. Not being charged for a line item then simply means not having that invoice ID.

            • 3. Re: Find invoices for each customer and total?


              I would suggest trying the solution Erolst has explained. Besides being more conventional, it gives you better record keeping of your invoices for future reference.



              • 4. Re: Find invoices for each customer and total?

                Thanks Erolst, excellent answer and will do the job perfectly.  It's a longer way round than I had assumed but matches in with the system I use for creating other types of invoice and it will ensure I have a better record of invoices.