Create a filtered value list based on a condition in main file

[Conartist]

I run a tradeshow company and have written a couple of separate databases to track our business. Currently I have a main database that tracks all of our events and the exhibitors who will be attending those events. I have a separate freight database that we use in the warehouse to track their inbound shipments to us. To expedite the data entry for the freight database, I use a filtered value list setup so that they can pick the name of the show and then have access to a second drop down that shows the list of exhibitors filtered by the show name. Currently I export the customer list from our upcoming shows and manually add it to the freight database and then delete that show information as the show ends. I would like this to be done automatically by linking the main database to the freight database in some way. I understand how to link the databases together and have been playing with value lists across the files. My problem is that my main database has thousands of shows. The drop down list on the freight desk can't show all of the show names, it needs to just list upcoming shows and not the past events. To accomplish this "filter" in the main database I have a status field that calculates the current date and the show dates to give an "active" or "inactive" status to the event. This has added one more level of filtering, sorting or whatever it is to the situation and now I'm stuck on what to do next. How do I get only the "active" shows to show up on a value list in the freight desk?

[mr_vodka]

See if this file helps you.

http://www.fmforums.com/forum/showtopic.php?tid/199355

[comment]

I have a feeling this may be more relevant:

http://fmforums.com/forum/showpost.php?post/310496/

[mr_vodka]

Ah yes. :nahnah:

[Conartist]

Thank you for your help, it made me look at things in a new way. But I'm still stuck after I tried implementing your suggestions. After a few tries I was ending up with an "index missing" error. Which I now realize is based on having an unstored calculation involved in my value list. I'm having a hard time figuring out a way around this.

I misspoke in my original post, simply using date to base my relationship on doesn't quiet work because my main database contains shows that are in the future but haven't yet met the requirements to be an "active" show and I don't want them to show up on my "active shows" list. I currently have a checklist of things that make a show considered "active" - I use an unstored case calculation to evaluate what has been checked off the list and then give that show a status category.

So unfortunately for me, both of your suggestions were helpful but don't quiet work for my situation.

[Conartist]

One of the above suggestions refers to using an unstored calculation with "getcurrent date" as part of the relationship to create a value list. I thought you weren't able to base a value list on an unstored calculation. :B

[comment]

It's still possible, but it will be more difficult. In the Shows table, define an unstored calculation field cCondID =

Case ( Get(CurrentDate) < ShowDate and ShowStatus = "Active" ; ShowID )

Next, define two self-join relationships of the Shows table:

• Shows::CondID = Shows 2::ShowID

• Shows 3::ShowID x Shows::ShowID

Define your value list to show values from Shows 2, showing related values only starting from Shows 3.

Note that when you change the status of the show, you will need to do a serious refresh before the value list will update itself in a layout of another table, something like:

Freeze Window

Go to Layout [ Shows }

Go to Layout [ original layout ]

Refresh Window [ Flush cached joined results ]

[Conartist]

Thank you so much. This solution worked perfectly. Though, I wish I could wrap my brain around the concept.