13 Replies Latest reply on Feb 18, 2014 10:39 AM by ClayReed

    Dwindling Value list

    ClayReed

      Title

      Dwindling Value list

      Post

           I'm trying to create a dwindling value list and I cannot figure it out.
           I have a PhotoShoot Table and three resource tables (Talent, Location, Company). I have a Join table "PhotoShootResources" between the "PhotoShoot" table and all three "Resource Tables" (see fig 1).
           I have three portals (using tabs) on the PhotoShoot Layout (fig 2). The idea is to add resources to the photoshoot via the 3 portals. The data is posted to the "PhotoShootResources" join table related by the _fkPhotoShootID. Everything works perfectly but I want the values to be hidden if they already exist. That is when a location is added it disappears form the list to add.

           I need this for all three resources but If I could get help on one (Locations) to work I could get the rest to work. In (fig 3) you can see the fields in the "PhotoShootResources" as well as my attempt at the script. I used this script before on another solution but it is not working here. I'm sure the issue with the script has to do with empty _fkLocationResourseID fields with the same _fkPhotoShootID where other resources have been added.

           I have 3 images to upload but since I can add only one at a a time I will post three times with the fig numbers.

           Any help would be appreciated.

      fig1.jpg

        • 1. Re: Dwindling Value list
          ClayReed

               Figure 2

          • 2. Re: Dwindling Value list
            ClayReed

            Figure 3

            • 3. Re: Dwindling Value list
              ClayReed

                   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.

              • 4. Re: Dwindling Value list
                philmodjunk

                     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.

                • 5. Re: Dwindling Value list
                  ClayReed

                       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?

                  • 6. Re: Dwindling Value list
                    philmodjunk

                         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)

                    • 7. Re: Dwindling Value list
                      ClayReed

                           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.

                      • 8. Re: Dwindling Value list
                        philmodjunk

                             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.


                        Updating values in auto-enter calc fields without using Replace Field Contents

                        • 9. Re: Dwindling Value list
                          ClayReed

                               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.

                          • 10. Re: Dwindling Value list
                            philmodjunk
                                 

                                      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.

                            • 11. Re: Dwindling Value list
                              ClayReed

                                   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.

                              • 12. Re: Dwindling Value list
                                philmodjunk

                                     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.

                                • 13. Re: Dwindling Value list
                                  ClayReed

                                       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 : ).