4 Replies Latest reply on Jul 14, 2014 3:08 PM by JackAragon

    Having a drop down menu select between multiple tables.

    JackAragon

      Title

      Having a drop down menu select between multiple tables.

      Post

           I apologize if this has been covered; I've been Googling for days and can't find exactly what I'm looking for.

           I'm assuming I'm not using the proper terminology in my searches, so if this has been covered you can post a link and I'll take it from there.

           I have multiple price lists from different manufacturers in different tables. I can't make one table since each manufacturer structures pricing differently.

           I also have a table of the manufacturers. They are linked to their price lists via a common ID (ManuID).

           Here's the scenario (using FMP12):

           1- Drop down menu of Manufacturers

           2- Drop down menu of price list SKU's

           Can I make menu 1 change which table I'm using for menu 2?

           I tried a calculation using Case, but to no avail.

            

           Thank you in advance.

        • 1. Re: Having a drop down menu select between multiple tables.
          philmodjunk
               

                    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.

          • 2. Re: Having a drop down menu select between multiple tables.
            JackAragon

                 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

                 etc...

                 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?

                  

                 Thanks!

            • 3. Re: Having a drop down menu select between multiple tables.
              philmodjunk

                   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.

              • 4. Re: Having a drop down menu select between multiple tables.
                JackAragon

                     I'm giving this a go. I think it's going to do the job nicely.

                     Thank you for your help.

                     -Ian