1 2 3 Previous Next 42 Replies Latest reply on Nov 29, 2011 11:08 AM by philmodjunk

    Change value list based on another field value

    Mitch

      Title

      Change value list based on another field value

      Post

      Hi,

      I have a drop down menu field that opens Value List A.  Is it possible to have the drop down field open Value List B based on on the value of another field...?

      Mitch

        • 1. Re: Change value list based on another field value
          philmodjunk

          If you mean that Selecting a value in list A determines what values are listed in list B, then this is called a conditional value list.

          Here are some links on the subject you may find useful. (The first two have a lot of overlap.)

          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

          • 2. Re: Change value list based on another field value
            Mitch

            Hi Phil

            More like if Field A has Value C, then List B will display some pre determined values.  If Field A has Value D, then List B will display a different set of values.

            Mitch

            • 3. Re: Change value list based on another field value
              philmodjunk

              That's exactly what I meant by this.

              • 4. Re: Change value list based on another field value
                Mitch

                I have been playing with this and the sample that has been provided and I can not seem to get it to function.   Most probably becuase my first value list is fixed and not generated from a table.  I think...

                I have two drop down list fields on one layout, "Select" and "Link_To".

                I have three value lists,

                LIST A (Values "Documents" and "Articles", these are fixed, and not editable)

                LIST B (Value List of Document Names generated from the Document_Names fields, from the Documents_Table.

                LIST C (Value List of Article Names generated from the Article_Names fields, taken from the Articles_Table.

                If the value selected in the "Select" field was "Documents", then the drop down fields in the "Link_To" fields would display all values from LIST B: Document_Names, from the Documents_Table

                If the value selected in the "Select" field was "Articles", then the drop down fields in the "Link_To" fields would display all values from LIST C: Article_Names, from the Article_Table.

                • 5. Re: Change value list based on another field value
                  philmodjunk

                  Using custom values should not keep your conditional value list from working as long as you have a valid relationship that uses that selected value to control what values appear in the conditional value list. Since those values have to exist in a table in order for you to set up a relationship, it just ends up a bit redundant to use the custom values and will be more work to maintain as you'll need to manage the values in two places now.

                  The problem you have is that you do not have a single table from which to draw both article and document names--which is a table structure you need for your relationship here. (Can't define a single relationship from the table where you have your field from List A that links to two tables...)

                  • 6. Re: Change value list based on another field value
                    Mitch

                    Phil,

                    Would it work if I estalbished another Table (Table_Databases) with two fields: ID and Database_Type.  It has two records: "Articles" and "Documents".  Some as the drop down list.

                    In the Documents Table, a global field gDatabase_Type set as "Documents", likewise with the Article Table having a global field gDatabase_Type set as "Articles"

                    The Drop Down list LIST A takes its values from Table_Databases, instead of a fixed value list.

                    If this would work, what relationships have to be created, between which Tables and which Fields?

                    • 7. Re: Change value list based on another field value
                      philmodjunk

                      I suggest a different approach: Create a new table: Titles, with at least four fields, possibly five:

                      Title (text)
                      DocumentID
                      ArticleID
                      cDocType: (If (DocumentID ; "Document" ; "Article" ) )
                      cDocIDArtID: (Calculation DocumentID & ArticleID ) (May not need this one).

                      Import all your document titles and all your article titles into this table with their respective ID number primary keys in the designated field.

                      Set up these relationships:

                      Documents::DocumentID = Titles::DocumentID (Enable "allow creation..." for Titles)
                      Articles::ArticleID = Titles::ArticleID (Enable "Allow creation..." for Titles)

                      After importing the data into Titles, discard the individual title fields in Documents and Articles. On their respective layouts, replace these fields with the Titles::Title field. (When you create a new document or article record and type the title into this field, you'll automatically create the needed record in Titles.)

                      Now you can set up your conditional value list to draw values from this unified table of titles with a relationship between your first value list field and cDocType to control which set of titles appear in the value list.

                      • 8. Re: Change value list based on another field value
                        Mitch
                        Phil, Thanks for you time on this one. But its a bit out of my league, so I may have to find an alternative.... Mitch
                        • 9. Re: Change value list based on another field value
                          Mitch
                           Phil, I decided to give this a try on a copy of the database. I have so far: - Created the new Titles Table - Established the relationships - Imported the records into the Titles Table from the Document Table. The Articles Table is new, so nothing to import, however I have added some data to test - Replaced the Title and ID Fields on both layouts with the new fields from the Titles Table - New records all work fine along with the ID numbers, as expected. However, I am having trouble with the drop down fields/lists.. The first drop down field works fine, it gets it's values from the cDocType Field in the Titles Table, set to "all records" and displays "Documents" and "Articles". I can not get the next drop down field to dislay the list determined by the the previous field... Obviously it's in the value list settings.
                          • 10. Re: Change value list based on another field value
                            philmodjunk

                            Replaced the Title and ID Fields on both layouts...

                            Don't see why your replaced the ID fields as well as the Title fields here, but that shouldn't keep the value list from working.

                            What relationship did you set up from the layout's table occurrence to this new Titles table occurrence?

                            Exactly what options, fields, table occurrences did you select when setting up the conditional value list?

                            • 11. Re: Change value list based on another field value
                              Mitch
                               I have set up the following relationships:   The first is for the new table and creation of records as recomended - Documents::DocumentID = Titles::DocumentID (Enable "allow creation..." for Titles) and Articles::ArticleID = Titles::ArticleID (Enable "Allow creation..." for Titles).   The second occurrence is again the titles Table: Title::cDocType = DocumentID and Titles::cDocType = Articles::ArticleID.   In the Value list: First Field is Titles::cDocType, second Field is Titles::Title.   Only records relating to current Table, display second field.   No doubt these are wrong otherwise it would be working...!
                              • 12. Re: Change value list based on another field value
                                philmodjunk

                                What table occurence is your layout based on? (What do you see in Show Records From in Layout setup...)

                                • 13. Re: Change value list based on another field value
                                  Mitch
                                   Layout is based on the Table::Documents. I now expect it should be Table::Titles...?
                                  • 14. Re: Change value list based on another field value
                                    philmodjunk

                                    Quoting from what you just posted, the layout is based on either Table, or Documents, but not Table::Documents. Show Records from will only list the table occurrence name here, not a field. (A table occurrence is one of the "boxes" in Manage | Database | relationships.)

                                    1 2 3 Previous Next