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?
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?
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.
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!
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?