3 Replies Latest reply on Dec 28, 2014 7:58 PM by philmodjunk

    Conditional Value List to Different Tables



      Conditional Value List to Different Tables


      I have a field where I will input a record, 1, 2, 3, .... 10 (the numbers signify tables). Depending on which number I select I would like another field do a value list from a field in said table.

      Is there a way I could do a calculation to look in the specified table in order to create the list?

        • 1. Re: Conditional Value List to Different Tables

          Why would data for the same field be stored in 10 different tables? While you may have a reason for this, at first glance this seems needlessly complex.

          I don't know of a way for a calculation to do what you want, but there is a way to use a script to create a conditional value list and the script can interact with different tables to generate that list of values.

          I suspect that the data in your ten tables should really be ten categories of records in the same table, but if you want to try the scripted method, you can check out the working examples in "Adventures in FileMaking #1 - Conditional Value Lists" The original release did not contain these script based examples, but it has recently been updated to expand the original 8 examples of different conditional value lists to include two more methods that rely on a script performing a find and using the resulting found set to build a value list.

          • 2. Re: Conditional Value List to Different Tables

            Thanks, I'll look into that.

            There are ten tables because each table takes in a wide variety of different and un-related data that is unique to its own. If it was a "category" there would be a wide variety of fields left blank.

            Either way each table has a global variable set to it, 1-10. I use an "overview" table that looks into the 10 based on the input of the global variable and a secondary field. So it knows what table and then what record to go to. I would just like to, value list from field, the records according to the table that is specified. I have a value list (1-10) set for the first, but have been putting in manually the second field, so if it would be a list I think it would be easier. Its just the records have been becoming quite long.

            Thanks again 


            • 3. Re: Conditional Value List to Different Tables

              Yet if it's "a wide variety of different and un-related data" how can they all serve to supply data to the same field? That seems a contradiction and suggests that your data model might need rethinking. (Please note the key work in that sentence is "suggests". wink) It's possible that you need a unified table of just the fields that are common to all 10 tables and that serves as the value source for your table and then additional detail tables store the data that varies from one set of records to another. (And there's no great sin committed in having fields in your table that only hold data for a small subset of all the records in the table either....)

              If you insist on this approach, please take a look at the script generated conditional value list examples that I already recommended. The examples only search one table an bring back a set of values from that one table, but the method could be modified so that the script checks the value of your global variable in order to determine which table to search for data with which to build the conditional value list.