I Understand I need to relate the two tables
Assuming you dont have a relationship., can you screenshot the relationship graph as a starting point?
You may be able to get away with value lists where you create a list and reference these settings.
1) Create value list
2) in layout mode for Dispatch table adjust field settings to reference that value list
I'm kinda new to relating tables, portals and all the possibilities there, this is all very useful. thank you.
sorry i was just updating my first comment. My value list only applies if the inventory:price fields are static for all customers
Not sure what your needs are, but the data model and relationship seems a bit atypical.
I think a restructuring will allow more possibilities and better control over what appears to be an "invoice"
1 customer can have multiple orders; 1 order can have multiple items
the Customer table contains only the customer information
Order contains only the Customer ID, Item IDs, and Summary calculations
Items contains all relevant item information such as price
I will create a generic one real fast and attach an example
Thank you all,
Side Note: I wouldn't be surprised if my database is ENTIRELY atypical. I'm self taught, and running a very busy business which is currently using the database and it has evolved into something I barely recognize. Dispatch records, Inventory, chat live with techs in the field via iPad and using it as a POS at the front of our brick and mortar shop. I'm surprised I managed to get this far.
The relationship that you show in reply #3 isn't going to work. In order to link a record in Main Base to a record in Key Crew Inventory, you would need the same exact value in all 7 amount fields.
You need a separate record for each item just as brianb has recommended.
sorry for the delay I had to pick up the kids. I was going to create a sample solution, but noticed they have a starter solution already made. I recommend taking a look at it and see if this something that is close to your workflow and process.
Invoices.fmp12.zip 600.8 K
Thanks for all your help fellas. As I work through this, I'll let you know what I did.
yeah good luck. the main idea is to keep the data separate then use ONE common data point as a way to connect the tables. in the example below
-the customer table has a single record for each customer and only contains customer related informatio
-The customer is assigned an ID
-That CustomerID is then added to an invoice table when a new invoice is made, The invoice only identifies the customer by that ID and then references that ID in the Customer Table to pull all related info
This allows one customer to have many invoices/orders or a 1-to-many relationship designated by the ---<
you can then pull up how many times customer "1" has ordered, paid, frequency, etc..
the same idea applies to the invoice---<order & order>-----Items; 1 invoice has many order entries & 1 item can be ordered many times
Customer---<Order/invoice----<Order Details > -------Items
CustomerID Last name First Name Contact etc.....
InvoiceID customerID OrderDate Total Payment #of items etc....
table3 Order details
OrderID InvoiceID Item ID QTY Discount etc....
a1 A 213
a2 B 522
ItemID Manufacturer Vendor Cost Price etc....
213 abc xyz 5:00 10:00