3 Replies Latest reply on Aug 21, 2014 8:07 AM by philmodjunk

    Variable lookup



      Variable lookup


            I am very new to Filemaker.    I have a table with a field for medication class and one for medication.    In another table, I can look up the classes and choose from a drop down, but in that same table in the medication field I would like to show only the medications from the class chosen in the first field.   I haven't  been able to get this to work correctly.   Thanks!

        • 1. Re: Variable lookup

                    but in that same table in the medication field I would like to show only the medications from the class chosen in the first field.

               If by that you mean that you want a value list in a drop down or other format that is only medications from the class chosen in the first field, this is called a conditional value list.

               Here are my current resources for conditional value lists:

               There's a lot of overlap between the first two links so you can read one and skim the other. The demo file gives you several examples of different conditional value lists so you can examine them to see how they are set up. If you are using FileMaker 12 or later, you can open this demo file from your File menu to get a converted copy of the demo file that you can examine in your version of FileMaker.

               The next to last link discusses how to set up a chain of conditional value lists where the value selected in each conditional value list controls the values listed in the next value list and the very last link is a fairly new addition that describes how to use ExecuteSQL to produce conditional value lists that aren't easily possible with the other methods described here.

               Forum Tutorial: Custom Value List?

               Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list

               Demo File: https://www.dropbox.com/s/j6qf0z9fnem3uxd/ConditionalValueListDemo.fp7

               Hierarchical Conditional Value lists: Conditional Value List Question

               Using ExecuteSQL to produce a conditional value list: Using ExecuteSQL to Produce a Conditional Value List

               Feel free to post follow up questions here if those links aren't enough to get your value lists working correctly.

               And here's a brief "plug": I have a new .fmp12 tutorial file in development that will cover all of the above concepts plus new tricks all in one file that includes working examples of each method discussed in it. Stay tuned as I plan on posting a link on it once I put the final "polish" on it.

          • 2. Re: Variable lookup

                 Thanks for the response!   I totally get the concept and it works perfectly when I start from scratch, but I'm having a problem getting it to work with my existing database.   

                 I have a table called Formulary with (among others) fields called Class and Medication.  Class is being pulled from a custom value list with the classes of meds we have.

                 I have a table called Add/Usage with fields called Class and Medication.   

                 There is a relationship between Class on Custom Value List and Formulary Table.   There is also a relationship between Class on the Formulary Table and the Add/Usage table.

                 I know I need two value lists: one for class and one for medication from the formulary table, but this is where I can't get it to work correctly.  I think it may because of the custom value list I have and that I'm not pulling from the correct fields or don't have the relationships right.    How do I fix this?



            • 3. Re: Variable lookup

                   I don't get this part of your description:


                        I have a table called Formulary with (among others) fields called Class and Medication.

                   You can't establish a relationship between a value list and a table, you can only match fields between to tables and either match field might or might not be formatted with a value list.

                   To get your conditional value list in Add/Usage to selectively display drugs from Formulary, you need this relationship:


                   Add/Usage::Class = Formulary::Class

                   You would set up your value list to "show values using" Formulary and then select the "Include only related values starting from Add/Usage" option to make it a conditional value list.

                   But it is likely that you need two separate relationships between these two tables, one for the conditional value list and one so that once you have selected a drug from the Formulary, you can then show additional info from Formulary on your Add/Usage layout. You can do this by using two Tutorial: What are Table Occurrences? of the Formulary table, one for your value list and one to access data in Formulary once a drug has been selected. That will produce a set of relationships that look like this:


                   Formulary::__pkDrugID = Add/Usage::_fkDrugID
                   Formulary|ValueList::Class = Add/Usage::Class

                   For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

                   Formulary and Formulary|ValueList are two occurrences of the same data source table named Formulary. (You can double click a box in Manage | Database | Relationships to open a dialog where you can rename a table occurrence.)

                   Using this example, your conditional value list would be set up to display values from Formulary|ValueList instead of Formulary as described in the first example.

                   Note: Add/Usage is a less than optimum table or table occurrence name. When you refer to fields from this table in calculations, you'll get a more complicated expression as FileMaker has to enclose the name in brackets preceded by a dollar sign to keep FileMaker from evaluating that as the expression "Add divided by Usage". I recommend renaming this table and table occurrence to omit the / character.