AnsweredAssumed Answered

Multi-table conditional value list

Question asked by timmcmanus on Feb 26, 2013
Latest reply on Feb 27, 2013 by Mike_Mitchell

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!