3 Replies Latest reply on Jan 3, 2010 1:35 PM by djs_1

    Dynamic lookup of related table values to filter the list

    djs_1

      Title

      Dynamic lookup of related table values to filter the list

      Post

      I think this is simple, maybe I'm just the approach to do this. I want to dynamically filter which records to show in a dropdown box lookup.

       

      I have the following TABLE/field:

       

      STORIES

      StoryID (number)

      Name (text)

      Retired (number as boolean)

       

      SUBMISSIONS

      StoryID (number)

      StoryLookup (calculation ==> from Submissions, =Lookup( Stories::Name )

       

      The above works great for being able to specify a Story to associate with a Submission. However, some of the stories become obsolete and Retired is set to true. I would like to set a global variable called $$HideRetired to True and have the lookup list be filtered to show only the unretired Stories in the dropdown on the Submission layout. The reason for the global is that for some users, they want to see all stories, while other users will want to hide the retired stories. I can easily capture that in a global.

       

      I did not see how I could dynamically build a list via script. Ultimately, I would like this to work in a runtime solution.

       

      I am use FMP 10.0v3 on Mac OS 10.6.2 (Snow Leopard).

       

      Thank you.

       

      -djs. 

        • 1. Re: Dynamic lookup of related table values to filter the list
          djs_1
             I've come close, but I'm seeing behavior I don't understand. Instead of basing the lookup dropdown on StoryID and Name, I will use the two fields I added to the STORIES table. These are calculation fields are called StoryIDDynamic and NameDynamic. If Retired is false, StoryIDDynamic and NameDynamic are the same as their counterparts of StoryID and Name. If Retired is true, then I set StoryID to 0 and NameDynamic to "--". I would like the dynamic ones to be blank, but I need to return a value. I figure at least all the Retired ones will get reduced to "--". The odd thing is, of the 11 story records, it now shows only 4 of them. 
          • 2. Re: Dynamic lookup of related table values to filter the list
            comment_1
              

            I think I would do it this way:

             

             

            In the SUBMISSIONS table, define a global text field gRetired. Define another relationship between SUBMISSIONS and STORIES (using a new occurrence of the STORIES table) as:

             

            SUBMISSIONS::gRetired = STORIES 2::Retired

             

            When gRetired contains 1, only retired stories will be related. When it contains 0, only active stories will be related. When it contains both (separated by a carriage return), all stories will be related. Note that this assumes the Retired field in STORIES is never empty.

             

            Define your value list to show values from STORIES 2::StoryID.

            • 3. Re: Dynamic lookup of related table values to filter the list
              djs_1
                 Thank you for the input. Ultimately, I used the shadow field that was calculated but stored as an actual value. I simplified my original problem description, perhaps too much. I had a need to always display the records regardless of the setting. I was only hiding certain values from the dropdown based upon a user customization setting. Again, thank you.