Please help with a unique relational database
First, I will say that I am learning the database creation slowly in filemaker 11. I have no programing background.
I am working on the a database to track orders. I have been running in circles the last few weeks with line Items. My order needs up to 8 line items per order with 12 seperate fields. Each Line Item is unique to the customer (no 2 customers order the exact same item). 7 of the 12 fields will be the same each time that Items is sold, however Weight, Pieces, Price, Due date change every order. There are times that the same Item is on one sales order 5 times with different due dates. There are also times that we have the same line Item on several open orders. Information from each sales order will eventually be linked to inventory, process orders, packing lists, and shipping reports.
I started with repeating fields and quickly discovered the limitations with this. I then created a portal with with a drop down list to select each item. After finally figuring how to make this work I discover that each time I change the weight I either create a new Line Item, or change old orders. I also tried creating fields in the sales order table to "lookup" the value from the line Items Table. This just doesnt seem efficient. Can someone please tell me how you would set this up, and I can spend my time moving in the correct direction instead of running in circles? Thank you in advance for your assistance.