9 Replies Latest reply on Jul 8, 2015 4:50 AM by OliverMartin

    Conditional value list returns values

    OliverMartin

      Title

      Conditional value list returns values & "?"

      Post

      Hi,

      I have two tables: Loads and Vehicles

      Loads contains a list of dates and a list of vehicles IDs. There is a relationship between the two based on the Vehicle ID.

      I want to count the number of days a vehicle runs for, but as it might do anything from 1-4 loads per day (also 0 loads, but then there would be no load record)  I need to count only unique instances.

      I have created a value list a conditional value list, starting from Vehicles that lists all the delivery dates.

      But when I test this value list from the Vehicles table, it contains some of the dates, but not all, and the last value is always a "?"

       

      Untitled.png

        • 1. Re: Conditional value list returns values & "?"
          philmodjunk

          I've published a "how to" file that shows 10 different ways to set up a conditional value list and know of an 11th option.

          Which method did you use? I would guess that you have set up what I call a "basic conditional value list" where a relationship controls what records supply values to the value list.

          Is this what you have set up?

          If so, a portal can list the same data. If you set up a portal to show this same data, what do you get in the portal? Might you have a field with an invalid date in your table?

          Adventures in FileMaking #1 - conditional value lists

          • 2. Re: Conditional value list returns values & "?"
            OliverMartin

            Yes, the "basic conditional value list".

            The portal of related records in the screenshot actually shows records using the same relationship.

            The field used to create the relationship may contain more than one line of data as the vehicle that collects the load may not be the one that delivers it, therefore one load can show under two vehicles (although that shouldn't make a difference).

            There are a total of 14 vehicles, which all show "?" as the last option unless they have no loads at all in which case they do not show a drop-down. One of the vehicles has only done 2 loads, but shows just the "?" in the drop-down.

            • 3. Re: Conditional value list returns values & "?"
              philmodjunk

              The portal of related records in the screenshot actually shows records using the same relationship.

              But it doesn't list the same values as does your value list. I would like you to compare such a portal to the value list with identical context--in which case, you should see the same values in both portal and value list and that might provide a clue as to what record is the source of the ? result.

              The field used to create the relationship may contain more than one line of data as the vehicle that collects the load may not be the one that delivers it,

              Which means that on the other side of your relationship, this record may match to records with one of two different vehicle IDs. That complicates running this down but shouldn't keep the value list from working.

              • 4. Re: Conditional value list returns values & "?"
                OliverMartin

                Sorry, not sure what you mean.

                The portal shown is the list of the vehicles loads, showing the collection dates of each related load. It uses the same relationship as the value list. The value list shown dropped-down should show the same list of dates (although only unique ones), but it doesn't and I can't work out why, plus it shows the "?".

                I have created a new vehicle "Test" and a new load dated today using Test as the vehicle. The value list does not drop down at all for this vehicle.

                I tried changing the value list to include all values, this does not result in a "?" in the list, although the list does reformat to US style (mm/dd/yyyy) rather than the format it was inputted (dd/mm/yyyy), possibly inconsequential, but odd.

                • 5. Re: Conditional value list returns values & "?"
                  OliverMartin

                  Actually it does drop down the correct date and no "?" (I hadn't entered the date into the correct field)

                  • 6. Re: Conditional value list returns values & "?"
                    philmodjunk

                    Does this last, brief post indicate that it now works for you?

                    • 7. Re: Conditional value list returns values & "?"
                      OliverMartin

                      Sorry no. It works on the Test vehicle, but persists with all the others. That suggests it's bad data in the date field, but I've checked them all and they all contain valid dates.

                      • 8. Re: Conditional value list returns values & "?"
                        philmodjunk

                        Try reindexing the file.

                        Launch FileMaker without opening the file.

                        Select recover, select this file, but use Advanced Recover options to select:

                        Copy File Blocks As-Is
                        Rebuild Indexes Now

                        Test this copy and see if you still get the ? result. IF that fixes the problem, this is the one case where I do go ahead and use the recovered copy instead of replacing with a back up copy.

                        • 9. Re: Conditional value list returns values & "?"
                          OliverMartin

                          That sounded promising, but didn't solve it sadly.

                          I've trawled through the related records, but I can't find any data that would cause this (although I'm sure there must be some reason).

                          Some vehicles have no ?, but only have one or two loads, but most show the ?. Those with a ? are missing at least one load date in the value list.