3 Replies Latest reply on Jun 11, 2014 9:13 AM by philmodjunk

    Updating changed field values across all tables?



      Updating changed field values across all tables?


           I have a table of people information (First and last names, age, gender, etc.), and wish to create a dynamic value list for a second table that will contain all first and last names of the males in the "people information" table. I've read about conditional value lists, but cannot seem to get it to work. Does anyone have specific steps that will do this for this specific data set/type?

        • 1. Re: Updating changed field values across all tables?

               If you need to "update changed field values across all tables", something is wrong with your basic database design. Except for match field values this  data shouldn't be in other tables to begin with.

               But I don't see an obvious connection between that title and the function of a conditional value list. A conditional value list uses one of several different methods to limit what values actually appear in a value list. You select "fruit" in field 1 and the value list in field 2 shows "apple, orange, pear...", if you select vegetable in field 1, the value list shows "carrot, lettuce, radish..." And sometimes there is no "field 1" and you simply want to list values from a table, but only those values in a single category such as a list of all currently employed employees from a table of both past and present employee data.

               So I am suggesting that you describe what you are trying to do in much more detail.

          • 2. Re: Updating changed field values across all tables?

                 Perhaps the title wasnt the most accurate one.

                 What I am trying to do is have a value list that is updated to only show the first and last names of the males in a table, so for example, if the table contains the following details:

                                1                     Johnny                     Male
                                2                     Bob                     Male
                                3                     Mary                     Female
                                4                     Chris                     Male

                 ...then I would like the value list for males in this table to update and be "Johnny" "Bob" and "Chris" (of course, in reality it would also include last names, but that's likely easy to tack onto this paradigm).

                 If the table is then updated to add another male with a name of "Joe" then I would like the value list to be updated to include "Joe."

                 Since the value list is males, I would not want the name Mary to be included in it at any time.

                 Following this, and perhaps this is where the title is more relevant, a second question would be how to get the values assigned to a table from this list, to be dynamically updated. For instance, if Johnny in the table above changes his name to "Chuck" for whatever reason, then I would like the secondary tables where he was assigned to be updated accordingly. I know I can do this through table lookups, but cannot seem to combine lookups with some sort of value list when initially setting a field. In this case the field being a person's name, I can set up a relationship to the person number (ie, the field on the far left) and then have this lookup the appropriate name so if it's changed, then it is updated accordingly; however, the value list for this would have to be a series of numbers and not the current name of the person that the number represents. I would love to have this as some sort of "mask" for the number, so the value list can be "human readable" per se, and then be updated if the initial table the value list represents is changed.

                 I hope this makes better sense.

            • 3. Re: Updating changed field values across all tables?

                   There are two ways to do this with a conditional value list. Since you always want males in the list Option 1 in the forum tutorial link, is a simpler option that does not require setting up a relationship to support the function of the value list as you would need to using the more typically described method for this.

                   Here are my links on conditional value lists. I'm including them all, but I don't think you'll need to look at more than the first and then possibly download the demo file if you want to look at actual working examples of some 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.