Conditional Value List

[Steve G]

I'm 99% done with this database (hooray!), having figured out just about all of my woes and worries. Now, after sitting in front of my screen for nearly eight hours getting everything ready, I've run into a snag that my sleep deprived mind can't connect.

Here's the relevant part of my relationship graph:

On the PAYMENTS table, when a new payment is received the user first selects the exhibitor from which the payment was received. This is established through the relationship between the PAYMENTS and EXHIBITORS 2 tables. I have a value list that's created from all of the exhibitor names in the EXHIBITORS 2 table and it works nicely.

I run into my problem once the exhibitor has been selected. After the exhibitor has been selected, the next thing to select is the show that the exhibitor is signed up for. This is established between the PAYMENTS and LINKS EXHIBITORS TO SHOWS 2 tables, the latter of which containing a match list of shows to exhibitors (i.e., exhibitor #18 to attend show #1, exhibitor #27 to attend show #1, exhibitor #7 to attend show #3, etc).

What I need is a value list (to be put in a pop-up menu on the PAYMENTS table) that contains ONLY the shows that the exhibitor is signed up for, according to the information in LINKS EXHIBITORS TO SHOWS 2. I've never done a value list that was conditional like this so I have to admit I'm lost here. I have been experimenting with the "Include only related values starting from" option for the past 45 minutes and it never works, so I know I'm missing something important and probably painfully simple.

Thanks for any help. Hopefully with some help here and a good night's sleep this will be resolved by morning. :

[Vaughan]

One of the things that traps me (still!) when doing conditional value lists is first making sure that there are actually values that can be displayed.

Many time I've spent an hour or more only to work out that is the correct answer. :

So, make a portal on the payments layout and get it to display the correct shows for each Payment record (this may take the most time). Then make the conditional value list and set it up with the same TO as the portal, and it should work.

[comment]

You need another relationship for this. Once you have selected your exhibitor, you want to see the shows they're in. This information is in the table linking exhibitors to shows, so you have to connect to a TO of that, matching on exhibitor's ID.

If you have enough information in the link table to identify the show, then that's all you need. If not, you have to attach another TO of Shows to the new TO of the link table.

Your value list will be showing values from the show ID field in the new TO of the links table (or of the Shows table, if you have attached it), showing only related values starting from Payments.

[Steve G]

Sorry, I don't understand what you mean when you say "you have to connect to a TO of that, matching on exhibitor's ID." What does "connect to a TO" mean?

From what you wrote I understand that I need to create another relationship. I'm just not sure where that relationship should be, nor do I understand which table I need to include values from. When I have the value list set to "Include all values" it comes up with every entry in the LINKS EXHIBITORS TO SHOWS 2 table. I know that by selecting "Include only related values starting from:" I'll be able to prune the list down, but I don't understand FileMaker's verbiage. Related values *starting from*? Shouldn't it be "related to"?

Sleep didn't help, obviously.

[comment]

By "connecting" I mean creating a relationship (as in connecting two TO's on the relationships graph). The missing relationship is between the Payments table and the Links Exhibitors To Shows table, and the matchfield on both sides needs to be the exhibitor's ID.

A relationship is nothing more than a set of criteria ("predicates") that distinguish between related and unrelated records - very much like performing a find separates between found and omitted records. When you look at the relationships graph, you can mentally position yourself in a specific TO, and ask yourself: which records can I see from here? Looking at the graph fragment in your original post, if I am in the Shows 2 TO, I can see all registrations for the current show. Since relationships are cascading, I can also see all the payments made for this show, and - further down - the details of the exhibitor making each payment. That's what meant by "related values starting from ... ".

[Steve G]

**adds this thread to bookmark list**

Thank you for the clear and concise explanation; it makes sense now. I was particularly unaware of the cascading aspect. I now see how that can be extremely useful, and have been able to simplify my relationship table a bit because of it.

Thanks again.

[Steve G]

On a somewhat related question....

Is there a way to auto-populate a text field with the first value of a specified value list?

[Raybaudi]

GetValue ( YourValueList ; 1 )

isn't good for you ?

[comment]

Try something like:

GetValue ( ValueListItems ( Get(FileName) ; "YourValueList" ) ; 1 )

However, that may not work until some field is populated (if the value list is dependent on it), so you might need to reference that field as a trigger, enable 'replace existing value' and make sure it doesn't re-populate when you have already selected another value (if this sounds complicated, it's because it can be).

[Steve G]

Well, apparently I'm not as clear on it as I thought I was. Bleah. : Here's what I did.

First, I added a relationship as shown here:

I then tweaked my value list accordingly:

Then I cleared out all payment and link records in the database; the only information there were the records of shows (only two) and the records of exhibitors (about 60). I added an exhibitor (ID #56) to a show (ID #2) which created a new record (ID #1) in my LINKS EXHIBITORS TO SHOWS table as it was supposed to. I then went to my PAYMENTS table and created a new payment. I selected the same exhibitor (ID #56) and verified that the numbers matched up.

My next step would have been to select a show. When I clicked on my pop-up menu I should have seen the title of the show appear. Instead I get the always-lovely "no values defined".

Where is my error?

[comment]

The value list needs to show values from Links…3, not Links…2, and it should start at Payments. There will be no related values from Links…2 until the 'ID Link ExhibitShow' is populated (which I believe is the purpose of this exercise, so by the time that happens, the value list won't be needed).

[Steve G]

Okay, NOW I get it. : The conditional statement works backwards from how I was envisioning it; it makes sense now. (At least, until I get stuck again!) Thanks a lot.