5 Replies Latest reply on Mar 29, 2017 4:48 AM by vm1

    Creating a Customer Purchase History, How To

    kwatch

      Title

      Creating a Customer Purchase History, How To

      Post

      Hi!

      I am using the Invoice Starter Solution in FileMaker Pro 11. Essentially what I would like to do is add another 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'd like information such as the item information, sale date, etc. Is there a way I can do this?

      Thanks!!

        • 1. Re: Creating a Customer Purchase History, How To
          philmodjunk

          What do you want to see in this Customer History in terms of data and format? A list of invoices and dates? A list of all items previously purchased or something else?

          • 2. Re: Creating a Customer Purchase History, How To
            kwatch

            My goal is that when I look up a customer, I can quickly see their purchase history (meaning a list of all items previously purchased) in this new tab that I have created. I figure that I need to have the data automatically fed from the invoices as I make them, if this is possible. 

            I have made new entry boxes in Products and Invoices (so that when I input an item number the rest of the item information is automatically fed, as this is how it is set-up in the Starter Solution). They are just simple data boxes of the details of the item. I would like to have automatically taken from an invoice and put into the Purchase History in the Customer Section as I create it:

            Sale Date (automatically created when creating a New Record in Invoices), Ship Date, Purchase Method (check-box format), Model Number, Serial Number, Price, and various other data boxes like Dial, Band, etc.

            Is this asking too much?

            • 3. Re: Creating a Customer Purchase History, How To
              philmodjunk

              There's no need to create any new fields, you just need to establish some new relationship links to see the same data already entered in the customer's invoices.

              Do you know how to create a portal? This is documented in filemaker help.

              Given that you want to add a tab on the customer layout, you'll need to define new relationships in Manage | Database | Relationships so you can match the current customer record to the line items records that are listed in a portal on every invoice. This will enable you to place a portal to the line items records that will list every item the curent customer has purchased.

              In the relationship graph, click on invoices to select it, then click the button with two plus signs to make a copy of it. Each of these boxes in the relationship graph are called table occurrences. Drag the copy over near to customers and drag from Customers::Customer ID to Invoices 2::Bill to Customer ID to link them. Do the same to make a copy of Line Items but link Invoices 2 to line items 2 so that you get Invoices 2::Invoice ID = Line Items 2::Invoice ID

              Now you can click OK to Dismiss Manage | Database, go to your layout and add a portal to Line Items 2 on your tab. You can place fields from both Line Items 2 and Invoices 2 in rows of this portal to display your customer's buying history.

              • 4. Re: Creating a Customer Purchase History, How To
                kwatch

                Ah! That was absolutely perfect, just what I wanted. Thank you so  much for the clear directions and taking your time to explain this to  me!

                Thanks again!

                • 5. Re: Creating a Customer Purchase History, How To
                  vm1

                  Hi PMJ,

                  I too am trying to accomplish the same thing. I'm also working from an invoice starter solution (FM 15) and understand the basics on creating these particular relationships between the customer table, line items and invoice.

                   

                  I have tried to replicate your instructions in my own database but am having difficulty substituting the field "Bill to Customer ID" (In the Invoices 2 table) and "Invoice ID" (In the Line Items 2 table).

                   

                  Drag the copy over near to customers and drag from Customers::Customer ID to Invoices 2::Bill to Customer ID to link them. Do the same to make a copy of Line Items but link Invoices 2 to line items 2 so that you get Invoices 2::Invoice ID = Line Items 2::Invoice ID

                   

                  I've attached a screenshot of my relationships and have made visible all the fields in my Customer, Products and Invoice tables. Do I need to create additional fields in the tables to link it all properly for the portal in my "purchase history' tab?

                   

                  Thanks in advanceScreen Shot 2017-03-29 at 12.24.16.png