6 Replies Latest reply on Oct 30, 2012 3:23 PM by ClayReed

    Conditional Value List

    ClayReed

      Title

      Conditional Value List

      Post

           I’m creating a filemaker application for our photo studio that involves creating a Project and then adding Assets to that project. I have three tables; “Projects”, “Assets” and a join table “Project Assets”. On my Projects Layout I have a portal that displays records form the “Project Assets” table. I have a drop down menu (value list that pulls from the assets table) to select an asset to add to the project. When added, the asset ID and project ID become a new record in the “Project Assets” table. Everything works fine. Now the issue. Once an asset (record) has been added to the Portal (Project Assets table) I would like the dropdown (value) list to omit records that already exist in the “project assets” table thus avoiding duplicates. I know this must be some calculation but I have no clue how to make this work. I hope this makes since.

      Screen_Shot_2012-10-24_at_5.33.01_PM.png

        • 1. Re: Conditional Value List
          philmodjunk

               This type of conditional value list is sometimes called a diminishing value list.

               One method is illustrated in this demo file: https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7

               If you have FileMaker 12, there are ways to use the Execute SQL function to support a diminishing value list: Diminishing Value Lists and ExecuteSQL

          • 2. Re: Conditional Value List
            ClayReed

                 Thanks Phil it worked perfectly! I have another question with the same project. I am using the diminishing value list on my "Asset" layout as well.  This is just like the situation above except I will be adding projects to an asset instead of an asset to a project). The dropdown value list is listing all projects and as I add a project to the portal it disappears from the list - perfect. Now, I have a field in the projects table named "Status" where a project can be "active" or "complete". I would also like this value list to omit all projects with a status of "Complete" as well as the diminishing list. Any suggestions?

                  

            • 3. Re: Conditional Value List
              philmodjunk

                   A diminishing value list has this basic relationship:

                   ParentTable::ExclusionList ≠ ChildTable::IDField

                   If you define a calculation field, constActive as returning "Active" with Text as the return type, you can modify this relationship to be:

                   ParentTable::ExclusionList ≠ ChildTable::IDField AND
                   ParentTable::constActive = ChildTable::Status

              • 4. Re: Conditional Value List
                ClayReed

                     I'm new to calculations- what would that calculation look like? Also, what is a good source (book, online) for me  to gain an understanding on how calculations are constructed? 

                • 5. Re: Conditional Value List
                  philmodjunk

                       This is as simple as calculations get.

                       Open Manage|Database|Fields

                       Select the Parent Table table from the tables drop down. (Use your table and field names in place of mine.)

                       Enter constActive as the new field name.

                       Select "calculation" in the drop down of field types on the right.

                       Click Create

                       The specify calculation dialog will appear.

                       Enter "Active" in quotes. (I am assuming that records that are "active" have this exact text in the ChildTable::Status field.)

                       Select Text from the return type drop down in the lower left corner of the specify calculation dialog.

                       Click OK and you are done.

                       Now click over to the Relationshps tab and update your relationship to include constActive and Status as additional match fields in the relationship.

                       I don't know of a good resource on calculations to recommend though there are a number of books on FileMaker that you can research to see if any are a good match for what you need.

                  • 6. Re: Conditional Value List
                    ClayReed

                         Again, thanks so much Phil! I found a book that may be helpful and comes with exercise files. It's called "Filemaker Pro 12 the missing manual" seems to be pretty good.