Multi Comun Key Alternative
I am new to Filemaker; I used flavors of SQL in the past. Using these environments I could create indexes and keys based on multiple fields. This was the logical way to build and worked great for millions of records for decades without any problems. From what I have read in this forum that is frowned upon in Filemaker. Now that I am using FM I want to use best practices for the program but my experience has my brain locked into one way of thinking and I can't seem to come up with the best way to proceed. Here is what I am trying to accomplish-
- ID (text - unique and set by our web interface)
- SaleID (Unique)
- SaleHeaderID (From SaleHeaderDB)
- ItemID (From ItemsDB)
We have ~40,000 items and weekly sales (sometimes twice per week) that normally contain about 10% of our items (sometimes all our items). After a year an item may be included in 30 different sales with different sale prices. I want to be able to create a new sale in the SaleHeaderDB and then add items to SaleDetailDB from our ItemsDB. The combination of SaleHeaderID and ItemID will be unique in the SaleDetailDB. Previously this is what I would have used as the index and would have done something like: INSERT INTO SaleDetailDB (SaleHeaderID, ItemID) VALUES (#, ItemsDB:ID). With no additional tables and a single line of code records can easily be created, modified, queried, etc. The simplicity and low overhead is something I really really like.
From what I have read I am thinking I have to create a new Join table between SalesDetailDB and ItemsDB. Then when I want to add an item to a sale I need to script a layout change (two layout changes with the join?) to add the records and then reverse the layout change back to my original screen? My items layout already has a lot of filters (deleted product, pending items, non-item items...) so would these filters have to be refreshed every time I switch to layouts and came back? This seems bulky and cumbersome to setup, maintain and use so I am hoping I am missing something (I often am).
Any advice on on how to structure my tables and achieve what I need while still following FM's best practises would be greatly appreciated. Thanks in advance.
P.S. My fall back plan is to concatenate to two fields into an index but this make relationships tough to manage in FM.
P.P.S. The 2 Sale Databases are new at this point so if there is a better way these can be rebuilt or scrapped.