I figured it out. I had everything correct except one thing - the script attached to my button to add the resource needed a "Refresh window [Flush cached join results]. Working on all resources now.
I spoke to soon, It works intermittently. Still having issues.
And what do you have set up in Manage | Value Lists for this dwindling value list?
Is PhotoShootLocationDivKey used as a match field in a relationship? What are the details of that relationship? (I use the ≠ operator with such a list of "used" values to omit them from the value list.)
You may also want to put the PhotoShootLocationDivKey on a layout or watch it's value in the DataViewer (Filemaker Advanced only), to see if it is returning the expected list of values.
I attached a screen shot of the value list for locations. I have it to include "only related values starting from the photo shoot". I tried changing the "...values starting from" to the join table (photoshootresources) but I get the same results. Whats odd is if I create a new record in the photoshoot table and begin adding resources to one of the portals the diminishing list works. But when I add to one of the other portals the diminishing list does not work - all values are present.
The PhotoShootLocationDivKey is a match/calculation field in the photoshoot table with the calculation:
If ( IsEmpty ( PhotoShootResources::_fkLocationResourcesID ) ; 0 ; List ( PhotoShootResources::_fkLocationResourcesID ) )
The relationship operator used is ≠. My original post shows a screen grab of this relationship.
I have two other calculation fields in the photoshoot table for the other portals.
This is the same solution you gave me on another project that works perfectly. The only difference is the photoshootresources join table has three resource fields (fktalentResourcesID, fkLocationResourcesID, fkCompanyResourcesID) instead of one (see figure 3 post). I'm wondering if the calculation is not working due to this change?
If you put a portal to LocationList on your layout, do you see the expected list of active, but not yet selected locations?
What happens if you temporarily leave out the other match field pair? (LocationStatus = ActiveCal)
Phil - thanks so much for responding. I tried the portal with location list but none of the values populate - I even changed the value list (starting from values). I also tried deleting the other match field pair but still no luck.
What is odd is if I create a new record in the photoshoot table and the first portal (I have three) that I begin adding resources, the value list works. When I add resources to the other two portals they do not work, that is they are not diminishing. Any portal I start with this is true - with the other two not working. I'm perplexed.
The portal is a test of your relationship. If it does not show the expected records neither will your conditional value list. The next step is to figure out why the relationship is not matching to the records you expect it to.
What is odd is if I create a new record in the photoshoot table and the first portal (I have three) that I begin adding resources, the value list works.
That suggests that your existing records may have some empty fields where you expect there to be a value--especially if this is an auto-entered value and the records were created before the auto-entered setting was added to a field in the table.
Thanks Phil I will do some more digging. You are correct about the empty fields, not just existing but every time a new record is created. Take a look at fig 3 above. That is the photoshootresources table and as you can see each time a resource is added (from the portal), a record is created. Eavery time a resource is added the _fkphotoshootID receives a value as well as a resourseID (either fkTalentresourseID, fkLocationresourseID, and fkcompany) thus leaving the two other resourceID fields blank. Could this be causing some of my issues?
BTW- I wanted to clarify. When i put the location list portal on my layout it did show all values. When I tried my drop down value list they were no values displayed.
When i put the location list portal on my layout it did show all values. When I tried my drop down value list they were no values displayed.
A layout based on what table occurrence? Your value list specifies Photoshoot as the "starting from" table occurrence. Therefore, a portal to LocationList placed on a Photoshoot layout should show the same values as the value list.
Thanks Phil. As you know I have 3 portals located on the photoshoot layout. When I eliminated the relationship between the PhotoShoot table and two of the three table instances (TalentList, LocationList, CompanyList) the diminishing list worked perfectly every time. If I reconnect another table instance (two relationships) the diminishing values do not work. I'll keep at it - process of elimination.
Adding a different relationship should not change how another relationship functions. It could, however, affect the context in which you are using the conditional value list if you try to use it inside portals based on the other relationships.
I will continue to work on this functionality - I'm sure I'll eventually figure it out. As this is not a "must have" my team will begin using this application which will save a lot of time! I wanted to thank you Phil for all the help as I'm sure others on this board do. I know I've asked my share of dumb questions and you are never condescending with your response. It is greatly appreciated. I know I'll have more questions in the future : ).