1 2 Previous Next 22 Replies Latest reply on Feb 24, 2012 7:57 AM by elaraine

    Creating Conditional Value Lists- Beginner's Question

    elaraine

      Title

      Creating Conditional Value Lists- Beginner's Question

      Post

      I'm brand new to FileMaker Pro so the learning curve has been a bit steep (my apologies).

      I have an equipment inventory that I am trying to manage. I have the inventory as a database already in FileMaker with all the records. I would like to have a layout with several drop-down menus, in which the first drop-down menu is the Manufacturer and the second drop-down menu is the Model, and the second drop-down menu displays only the models from the selected manufacturer. I would like to add a couple more drop-down menus in this manner, to "narrow down" the records available, and finally, at the end, I would like to be able to view that single record, possibly even in the record detail format.

      I've read the post on "Creating Conditional Value Lists" and this is exactly what I need to do to have each drop-down menu only display the results that match the previous selection. However, I'm unclear on whether I need to create additional tables that get populated as a result of making a selection on the drop-down menus, or if I can do this entirely using the one table/database. Can someone give me a few pointers for getting started on creating relationships and the accompanying conditional value lists? I tried repeating the exercise from the aforementioned post but need a more basic roadmap and probably some help with the syntax. Thanks.

        • 1. Re: Creating Conditional Value Lists- Beginner's Question
          philmodjunk

          Here are 3 links on info on conditional value lists:

          Forum Tutorial: Custom Value List?

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

          Demo File: http://www.4shared.com/file/f8NsU2DJ/ConditionalValueListDemo.html

          The last one may be particularly helpful to you as it is the download link to a demo file you can examine to further your understanding.

          Can you get the basic first step to work?

          In other words, can you set up a conditional value list filtered by the specified manufacturer?

          The only table that should be modified by selecting data from your chain of conditional value lists is the single table where the record with the fields in which you are selecting data is defined.

          My best guess is that you will need no more than two tables and if this is just to find and view a record in the inventory table, you may only need one table, but you will need a number of occurrences of this table in order to define the needed relationships as you'll need a different relationship for each conditional value list in your chain of conditional value lists.

          So let me know if you if you can get the first conditional value list to work as this is the first "link" in your chain of value lists.

          • 2. Re: Creating Conditional Value Lists- Beginner's Question
            elaraine

             Thanks for the quick response, Phil. Unfortunately, I can't get the first step to work. I've tried this both using a portal and also with just drop-down menus and neither has worked for me. so I must be missing something pretty fundamental. I did read the posts, though, and this is exactly what I want to accomplish.

            If I have a series of two drop-down menus, I cannot get the second menu to display only the results matching the selected manufacturer. I can get the second menu to display all possible choices (not just the models by the selected manufacturer), or alternatively, if I link this second list to another table that has all the manufacturers and models, it displays nothing at all, even though I have defined the relationships between one table and another in the Manage Database tabs.

            From my main database, a) do I need to establish a second table?, b) how does the second table differ from the main table, and c) what is the difference in the Inspector between "Display Data from" and "Values from"? Thanks so much!

            • 3. Re: Creating Conditional Value Lists- Beginner's Question
              philmodjunk

              a) that depends on what should happen when you select a value from the conditional value list and what data is stored in your database. Do you wnat to find records or enter data?

              Example:

              You select a category, then select an item from the conditional value list and the system finds the desired inventory record.

              You select a category, then select an item and this enters data into the field of a new record or a record that you are editing.

              b) you may or may not have a second table. Relationships can be set up to link a table to itself by using two table occurrences of the same table in Manage | Database | Relationships.

              c) Display data identifies the table and field where the data shown in this layout object is shown. "values from" identifies the value list used to display the values you see when the field is formatted as a drop down list, pop up menu, radio buttons or checkboxes.

              Do you have a relationship defined in manage | Database | relationships to use for you conditional value list? If so what does it look like?

              You should see something like this:

              Table1::ManufacturerID = Table2::ManufacturerID

              Your layout would be based on Table1 and your value lists's values should come from Table2. (And the same table can appear on both sides of this relationship.)

              In manage | valuelists, create a new value lists and choose the "Use values from field" option.

              Select a field from Table2 in the Use values from First field list.

              Then select "include only related values starting from" Table1.

              Now, when you select or enter a manufacturerID in Table1::ManufacturerID, Your value list, set up on a second field will list value from only those records in table2 that have the manufacturerID matching the value selected in Table1::ManufacturerID.

              • 4. Re: Creating Conditional Value Lists- Beginner's Question
                elaraine

                 Hi Phil- thanks again for all your help.

                a) I want to find and display records; I don't need to create any new ones.

                b) Yes, I have a second table. My tables are set up this way. I have my original table/database, called Inventory. This was set up using a Starter Solution. Then I have a second table, called simply Table1, which includes the same records as Inventory. I have a relationship set up between Inventory and Table1, where the Manufacturer fields are set to be equal and the Description fields are set to be equal. Here's a screenshot of the relationships.

                • 5. Re: Creating Conditional Value Lists- Beginner's Question
                  elaraine

                   c) For my second drop-down menu (the one where I need the conditional value list), I have it set to display data from Inventory::Description, and values from a conditional value list called table1desc. This conditional value list uses values from the field: Table1::Description and displays only related records from Inventory. However, once I save and exit the layout, the second drop-down menu doesn't display anything, only white space. The first drop-down menu still displays the manufacturer just fine.

                  Based on your advice and other tutorials I've come across, I think this is close but obviously I'm still missing something. Thanks so much for your patience!

                  • 6. Re: Creating Conditional Value Lists- Beginner's Question
                    philmodjunk

                    Is Table1 a separate table or an added "occurrence" of Inventory? Either can work here for the value list, but if it's a separate table--you'll have extra challenges keeping this data up to date. For the purpose of your value list, it should be an added occurrence created by clicking the duplicate button (button with two green plus signs) and this will avoid any update issues.

                    You now have two issues for this process. Setting up the conditional value list and then using the selected manufacturer and Description to find and display the selected record.

                    Add two fields to Inventory: gSelectedManufacturer and gSelectedID_Record. For both of these, click the options button and specify "Use global storage" on the storage tab to make these global fields.

                    The relationship needs to be changed so that you only match by manufacturer not manufacturer and description:

                    Inventory::gSelectedManufacturer = Table1::Manufacturer

                    Place both fields on your layout and format gSelectedManufacturer as a value list of Manufacturer values. You'll get the same list of values whether you display this data from Inventory or Table1. Format gSelectedID_Record as your conditional value list. You need global fields for this so that selecting values in them does not modify an inventory record's data and global field data can easily be used to perform a find to locate and display the selected record.

                    Specify that field 1 take it's values from Table 1::K_ID_Record. Specify Table 1::Description provide values for field 2. At the bottom of this same dialog box, specify "Include only related values, starting from Inventory"

                    Now your script to find a record can be:

                    Enter FInd mode []
                    Set field [Inventory::K_ID_Record ; Inventory::gSelectedID_Record]
                    Perform Find

                    This allows you to select a manufacturer, then select a record by chossing it's description and then you perform the above script to find and display the record. (You can use a script trigger on the gSelectedID_Record field to perform this script automatically when you select a record.)

                    • 7. Re: Creating Conditional Value Lists- Beginner's Question
                      elaraine

                       Thanks, Phil! The sequential drop-down menus now work perfectly: you select a manufacturer, and then you select the corresponding piece of equipment.

                      I assume that if I wanted to create more drop-down menus to further narrow the field, I would proceed in the same way?

                      The script, however, is getting hung up on something- it says "The provided find criteria is not valid. Enter a valid request before proceeding." The script itself is posted here. Will the script work if there is more than one record to display, or will it only display unique records? Do I need to have a space created in which to view the records, like a portal or a tabbed panel? Thanks!

                      • 8. Re: Creating Conditional Value Lists- Beginner's Question
                        philmodjunk

                        Did you specify global storage for the gSelectedID_Record field?

                        If you do not specify global storage for that field, no criteria is entered by the script step and that would produce the error message you are seeing.

                        • 9. Re: Creating Conditional Value Lists- Beginner's Question
                          elaraine

                           Yes, both gSelectedID_Record and gSelectedManufacturer are set to Global.

                          • 10. Re: Creating Conditional Value Lists- Beginner's Question
                            philmodjunk

                            In that case, better check to make sure that you have set up gSelectedID_Record with your conditional value list, not some other field. The error message indicates that no data is being entered as criteria for the find. That indicates that either the field is not set up with global storage or that it is not getting a value in the first place.

                            • 11. Re: Creating Conditional Value Lists- Beginner's Question
                              elaraine

                               Ah, yes, you're right- the field is not getting a value in the first place. Although the correct values are displayed in the drop-down, when you select one it is not entered into the field. I'm not sure why the field isn't getting the value, but I'm working on it. Thanks!

                              • 12. Re: Creating Conditional Value Lists- Beginner's Question
                                philmodjunk

                                The value entered will be the "field 1" value from your value list. In the example given, field 1 should be the ID.

                                • 13. Re: Creating Conditional Value Lists- Beginner's Question
                                  elaraine

                                   It looks like there is a default value "stuck" in the field- interestingly, it's from an unrelated field (i.e. not any of the ones I am working with but one that is in the inventory table). As soon as I choose from the first drop-down menu, this value appears in the second, even though you can scroll through the correct list in the second drop-down menu. The default value does not appear as one of the choices, it's just always there. Where might I be referencing/getting this unwanted value? Sorry for all the questions.

                                  • 14. Re: Creating Conditional Value Lists- Beginner's Question
                                    philmodjunk

                                    It would appear you are working with the wrong fields and/or have the wrong relationship set up. It sounds like selecting a value in the first field is establishing a link to a related table and then a field from the related table automatically displays a value from the related table. Try switching to layout mode, capturing a screen shot as a jpg, gif or png file and using the controls below Post A Answer to upload them. I'll take a look and then ask some very specific questions of you based on what I see in the screen shot.

                                    1 2 Previous Next