1 Reply Latest reply on Feb 27, 2013 4:47 AM by Mike_Mitchell

    Multi-table conditional value list

    timmcmanus

      Hello brilliant FileMaker Gurus!

       

      I have an interesting problem I hope someone can give me some guidance with. I have three tables with boolens in them in addition to one table having a field that contains more than one value. Ultimately I would like to present the user with a drop-down that has information from all of these tables to ensure consistent data entry. That's the executive summary, now for the details.

       

      I have a table named "Rider" with a boolen in it to determine if the record is "Active" or "Inactive" (I use "Yes" and "No" as the boolean on the named field "Active"). So the value list needs to contain the field "Name" from the table "Rider" where the record is "Active"="Yes".

       

      Then (we're not done yet) I need to do the same for two additional tables and append them in the same manner to the value list. So the "Rider Name" value list will contain the "Name" field from three different tables where "Active"="Yes".

       

      That's the first challenge I can't figure out how to solve / approach.

       

      The second one is equally as interesting. The two other tables "Staff" and "Volunteer" contain a field that has check boxes named "Roles". Any of these "Staff" or "Volunteer" records can have multiple roles. And I need to create a value list with this information. So, in the case above for "Riders" one of the "Roles" is also 'rider', so "Staff" and "Volunteer" records that are "Active"="Yes" and "Role"="rider" will also appear in the "Rider" value list.

       

      Is this possible? And if so, whay would be the best way to approach it?

       

      I have a separate database to hold value lists. They are tables with one field and that field has the values in the list. Then that database has value lists created based on the tables/fields in the same database. Then, other databased use value lists from that database. It allows me also to create new tables / value lists. What I had thought was that I could create a table in there that is populated with a script that performs all of these conditions to populate it. However, that calculation would need to be performed every time a database record or corresponding field was updates. I'm not sure this can be done from the server in the backend as a stored procedure of sorts. I think if it were done in front of the user it would kill the performance and detract from the UI experience.

       

      Suggestions are welcome, and criticism is okay too because I know the approach is somewhat insane.

       

      Thanks in advance!

        • 1. Re: Multi-table conditional value list
          Mike_Mitchell

          Tim -

           

          In the first case, you can do this a couple of different ways:

           

          1) Define a field called "Yes", a global calculation that always evaluates to "Yes" (text string). Create a join from any table (maybe a globals table, or a convenient spot from which the value list will be used). The join will have the "Yes" field on the "left" and the "Active" field on the right. Define a value list (related records only) from the context of the calculation field, showing the contents of the "Name" field.

           

          Now here's where it gets interesting. You'll need to do this three times, once for each table where you're gathering the names for the value list. But now I have three value lists - what do I do to merge them? One good way is to use a custom function. Here's one such example, from Brian Dunning's site:

           

               http://www.briandunning.com/cf/102

           

          So, the calculation would look something like this:

           

               MergeValues ( ValueListItems ( [name of file] ; [name of valuelist1 ] ) ; ValueListItems ( [name of file] ; [name of valuelist1 ] ))

           

          then do it again with that result and ValueListItems ( [name of file] ; [name of valuelist3 ] ).

           

          2) The second method requires the use of FileMaker 12, but you can use ExecuteSQL to pull the values considerably easier than doing all that (and without adding the extra table occurrences to your graph). I'm not a SQL genius (so you may have to play with it), but this query should be close:

           

          SELECT DISTINCT Name FROM table1 WHERE Active = "Yes"

           

          UNION

           

          SELECT DISTINCT Name FROM table2 WHERE Active = "Yes"

           

          UNION

           

          SELECT DISTINCT Name FROM table3 WHERE Active = "Yes"

           

          (Of course, you substitute your appropriate table names for "table1", "table2", "table3".)

           

          The results of that will give you a return-delimited list of names that you can stick in a field and use as a value list.

           

          On your other request ... I'm a bit confused. You say you have a checkbox set, but need a value list. What are you using for the checkbox set (which, by definition, requires a value list) now? Maybe I'm just not reading it right, but I'm not quite understanding what you're needing.

           

          Mike

          1 of 1 people found this helpful