Answering your PS first, Both advanced and regular versions of filemaker pro handle value lists in the same manner. Set up correctly, a change in the "category" field used in the relationship will immediately result in a different set of values in the conditional value list--which is how I read "on the fly" in your post.
The key to getting a conditional value list working correctly depends on the one part you didn't describe about your value list setup: When you use the "show only related records" option, you also have to specify a "starting from" table occurrence. (A table occurrence is one of the "boxes" found on your relationship graph in Manage | Database | Relationships.)
Think of conditional value lists this way: If you can get a portal to list only the desired values on your layout, then the same relationship can be used in a conditional value list so that they list the same values you see in the portal. Normally, the "staring from" table should be the table occurrence specified in "show records from" in Layout Setup... though there are exceptions and this can be one of those exceptions.
In your case, you have Season in one table that is separate from both invoices and the line items records in your portal. That will work if you have a relationship linking Invoices to user preferences and you specify user preferences as the "starting from" table occurrence. You'll also need a relationship linking user preferences::Season to Products::Season. I'd use a relationship between user preferences and invoices that uses the X operator instead of = so that any invoice will match to any record in your user preferences table.
I kind of get it but the light hasn't quite gone on yet in my head. I'm waiting anxiously for that "ah-ha" moment as I try to implement what you've said as well as what I've culled from other posts.
One think I keep thinking is kind of the opposite of what you said.
"If you can get a portal to list only the desired values on your layout, then the same relationship can be used in a conditional value list so that they list the same values you see in the portal"
... is that if I can get a conditional value list to only show the classes for that season I wouldn't have to worry about what the portal is filtering for for the line items. So long as the portal is linked properly to the Related Product IDs (or class IDs as I have changed it), then that should be fine. Know what I mean?
I guess it all comes down to a relationship anyway.
Your last sentence is key.
What I was trying to say was that the same relationship that enables you to put a portal that only lists records for products in the specified season is exactly the same relationship you'd use in the value list setup to list only the values for the specified season.
In fact, before we had conditional value lists, this was pretty much our only options short of a plug in for implementing a conditional value list.
Here's a demo file, I played with to check my assumptions before I made my first response to your question:
Note how the portal and value list on Main show exactly the same list of values. You can use the Preferences layout to change the group of values that appear in both. If you check the design of the value list, you'll find it uses the same relationship as the portal.
I get this file :)
I need to modify it such that the popup with the classes for the current season is in the invoice portal showing the line item records. The popup retrieves the Class ID and populates the rest of the fields in that row. Recall that I'm using the Invoice Starter Solution in FM 11.
I'll let you know how it turns out. I'm off to a cottage this week so you may not hear for a week.
Thanks for your sample. I see how your file works but I can't reproduce the same in my file where portal creates line items in the invoice that are filtered for the category, or in my case the season. I'm trying to inject a line items table in your sample to see if I can isolate the parts I need in my other fp7 file.
I've uploaded to my file to my server for reference. (I tried the free 4shared.com service but its free service is a slow upload. 0.1KB/s, but I'll post that link when its done. The starter solution is beefy compared to your sample.)
Recap: I started with the Invoices Starter Solution and added a Seasons table and a Current Season field to the User Preferences. When I create a new invoice I auto-enter the current season value in the Invoices::Current Season Field.
From this point on I want the drop down list of classes in the portal rows to only show the classes for the current season. Pretty sure you understand all that but I'm repeating myself to make sure it makes sense. :)
This file is a bit messy since I've been smashing away on it, sorry about that, but there are 2 seasons of class data to work with.
Cheers and TIA,
I'd already downloaded the sample from your earlier link...
There were several things not set up quite right in your file.
In my sample, the preferences table was located between invoices and Lineitems so the value list could be set up correctly. You'd need a second relationship and table occurrence to implement the line items portal.
Your file makes things easier by setting up a field that auto-enters the current season and we can use that in a simpler arrangement to set up the value list.
I made a new table occurrence of Products, Related Products for Season and linked it in this way:
Invoices::__fk_season = Related Products for Season::season
I then when to the Invoices layout and modified the Class ID value list used with LineItems::ClassID so that it was set up like this:
Column1: Related Products for Season::ClassID //you had LineItems::ClassID and this is the wrong field for this
Column2: Related Products for Season::calc_name
Include only related values starting from invoices //this option was not selected in your copy
Show only values from second field
For testing purposes, I formatted __fk_season with the seaons value list so I could easily pick different seasons and see just the classes for that season appear in the Class ID value list.
Or should I call you Phil? Anyway, the solution seems obvious now but I'll chalk that up to being rusty in Filemaker which I haven't used since version 7. When you throw in multiple table occurances and portals and lists it starts to get kind of abstract.
I have it working now, thanks a ton for your speedy responses. Now I can start cleaning this mess up and massaging it into a reall app.
Here's a link on table occurrence's that may help you sort things out a bit. Since you are already familiar with filemaker, you may want to drill down to the second article which discusses how filemaker table occurrences are misnamed as tables throughout the system--yet using them corrrectly is vital to getting the desired results.
http://forums.filemaker.com/posts/60a91f2dd7 Oops, wrong link. See:
Thanks for the help. I checked that link about TO but it goes to a thread about "Easier, Better Script Posting". You must be multi-tasking.