4 Replies Latest reply on Mar 30, 2017 9:34 AM by vm1

    Customer Purchase History



      I am using the Invoice Starter Solution in FileMaker Pro 15.  I have created a tab "Purchase History" in the Customer Section, where information would automatically feed into it when a customer makes a new invoice/purchase is made. Therefore, a history of their purchases that I can view in the customer section.

      I have created two table occurrences - one for Invoices and one for Products.

      I am struggling to work out which fields to link Customers to Invoices 2 and Invoices 2 to Products 2 so I can add a portal to Products 2 to the "purchase History."

      Any help is much appreciated.Screen Shot 2017-03-30 at 16.04.01.png


      philmfdjunk I found a previous discussion on the same issue where you solved the problem. If you have any suggestions that would be great

        • 1. Re: Customer Purchase History

          In that "history" portal, do you want to see a list of all invoices--one line to an invoice or a list of all products purchased--each item on it's own line? Or do you want to see each product listed only once in that portal.


          The first option does not require any new table occurrences. You can just put a portal to invoices on a layout based on customers. I doubt that's what you want.


          If you want an "item list", you need   Customers---<Invoices---<Invoice Data>-----Products   and you'd put a portal to invoice data on your layout. Again, no new table occurrences need.


          If you want to list each product 1, make it a portal to Products.


          If you then want some kind of total quantity listed with each product, We'll need to do some creative relationship work or use ExecuteSQL to get the needed totals for each product for the current customer.

          • 2. Re: Customer Purchase History

            Hi PMJ,

            Thanks for the advice.


            Ok, great. The second option of a list of all products purchased (specific to that customer) is what I am trying to achieve. However the same record needs to be accessed in the Products Layout as well as the portal in the "purchase histories" tab. When I create a portal to invoice data would I use the fields from my Products Table?

            • 3. Re: Customer Purchase History

              Both the second and third options list all products purchased specific to customer.


              option 2)

              Product | Qty

              Widget 1 5

              Widget 2 3

              Widget 1 2


              Option 3


              Widget 1

              Widget 2


              If the same item is purchased more than once, option 2 lists each as a different row. Option 3 lists each product only once. Including a Qty of 7 for widget 1 and 3 for Widget 2 could be done, but it would take more to do than I've described thus far.


              And if you do this from products, this would list all purchases of that item for all customers. Is that what you would want? In this case, you would again use a portal to Invoice Data. But check your relationship graph as you might need to add an occurrence to invoice data depending one which occurrence of Products is the basis for your layout. Many developers use an approach called "anchor buoy" where each layout's table occurrence is the "anchor" to a set of table occurrences linked to it for just the use of that layout's context.

              • 4. Re: Customer Purchase History

                Each item is a unique item so each product would only need to be listed once so the purchase of any item will be unique to one customer. But in answer to your question, I would not like to list all purchases of an item for all customers. I will play around with a portal to Invoice Data. Screen Shot 2017-03-30 at 16.51.27.png