I have multiple price lists from different manufacturers in different tables. I can't make one table since each manufacturer structures pricing differently.
While "structures pricing differently" is too vague to be absolutely sure, that does not sound like something that would require that your list draw data from more than one table.
Even with the differences, it should be possible to set up a single table of products and prices with a conditional value list used to to narrow the list to just those items from a single manufacturer. If there are "differences" between one set of records and another set, different related tables linked to the common table of items and prices can generally be set up to handle those differences.
First things first: 6 minutes to respond? I was hoping for something by the end of the day! That's awesome.
The pricing is really different by manufacturer:
A uses one price for everybody
B has 2 tiers depending on how many pieces are ordered
C has 4 tiers of pricing depending on how fast you pay and how much you spent
The price lists change a lot as well, though, which is another reason I keep them separate; that makes updates easier to manage.
However, I see your point. For the sake of this particular table, I just need a SKU, description, MSRP and MAP- the only things each table has in common.
If I were to generate a new table that used the information I need from the other tables (along with that handy ManuID) then that would work pretty cleanly.
I think I would want to generate that new compiled table whenever I opened the parent table though. Or would that be silly?
You would define a related table for each different pricing method, using the same related table for as many manufacturers as possible. I will note that it is sometimes possible to store a Filemaker Calculation expression as text in a field and then use the Evaluate function to compute a value. Thus even very different caculations are possible for different records in the same table. This is thus a method that might reduce the number of such tables needed in your database.
You would not create a new table time that you add a new record to the unified table of SKUs and prices. But you would select which pre-defined table to use for documenting needed details and use a portal to that table to create new records linked to your new product record in order to document the needed information.
I'm giving this a go. I think it's going to do the job nicely.
Thank you for your help.