There's two ways to do this. Is it important that historical data is preserved? For example if a customer's address changes, do you need to see the old address on old invoices and the new address on new invoices? Or is it fine if the invoice always shows the current address?
If it's the latter, then you need a relationship between an Invoice table and a Customer table based on Customer ID. The invoice should have address fields from the Customer table. The salesperson enters the Customer ID, and the address will display.
1 of 1 people found this helpful
It looks ok, but I think you have unnecessary join tables. Unless you assign an invoice to more than one customer, you don't need rel_customers_invoices. And probably not rel_invoices_items or rel_sales_customers. Putting a customer_idfk field in the invoices table directly is all you need. You can skip the join.
Wait: my sales person has to enter the customer id?
I had assumed that the customer ID was a serial number I wouldn't want my sales person messing with or really seeing.
Is it something they would assign? How would they remember the ID?
Should I create a value list portal?
Each customer has their own id number but you should include a redord number as well that way each customer can have more than one contact.
You than create a table occurance of the customer table to display the list of all customers for a salesmen and he can just select the customer from that list.
When the contact on the left is selected the information is displaied to the right.
Hope this helps
I have an issue with using customer_id exclusively as the key field. How am I supposed to remember the customer_id for Bob Jones? Is it normal practice to have the customer's name as a key field to another TO of the customers table to allow us choose from a list of available customer_ids?
You create a value list of your unique customer IDs (auto-enter serial or UUID) and specify use values from field and all values. In the next screen, select CustomerID in the left pane and your customer's full name (usually a concatenated calculation) in the right pane.
Below in same dialog, specify 'all' and 'only show values from second field.
Now attach this value list as POP-UP with CustomerIDs to your Invoices::CustomerID or your Quotes::CustomerID (the foreign keys where you will need to select your customers). It will display the customer name for selection and also after you you have selected, but it will insert the CustomerID into the field.
ADDED: If you have a lot of customer IDs, a portal for selection which can be filtered works well (usually provided in a modal or dialog window). And you can also filter the relationship using a global so if you type a 'c' only customers with names beginning less than or equal to c will display etc.
Thanks. I'll give that a go.