2 Replies Latest reply on Mar 8, 2014 1:42 PM by kennyferg

    Calculated Value Lists



      Calculated Value Lists


           Apologies if this has already been answered - I've gone through the posts but I can't find something that fits exactly what I have - at least doesn't seem to fit.

           I have a database with several tables but the following tables are the ones I'm interested in: "Publications" and "Adverts" which are linked with a relationship.

           The publication table has a text field named "PublicationID"  which is the name of the publication and a "Status" field which contains either "Active" or "Complete". In the Adverts table, I am using a drop down list which I need to display ONLY the PublicationID's which have a status of "Active". At the moment, the dropdown lists ALL PublicationID's irrespective of publication status and it's causing me a real headache.

           Any help at all would be hugely appreciated.


        • 1. Re: Calculated Value Lists

               This is called a conditional value list. While I am going to post a large list of links on the subject, Option 1 in the forum tutorial link describes using a calculation field to produce a conditional value list that would seem to be the simplest way to get the value list that you want here.

               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.

          • 2. Re: Calculated Value Lists

                 Hi Phil

                 Sorted! Thanks so much for your help. Just working through the simple Tutorial helped me massively to understand how things were working which allowed me to apply it to my own database.