7 Replies Latest reply on Sep 28, 2009 1:44 PM by hschlossberg

    ValueListItems()  The_Related_Valuelist



      ValueListItems()  The_Related_Valuelist

      Description of the issue

      I have three tables for this much-simplified example: Class, Enrollees, Students Class has one field: ClassIDEnrollees has three fields: ClassID, FirstName, LastName Students has two fields: FirstName, LastName  There are two relationships between four table occurences:1) From Class to Enrollees1, via ClassID in both tables 2) From Enrollees2 to Students, via LastName  I have two value lists:1) VL_Last_Names, which includes ALL Students::LastName2) VL_First_Names_by_LastName, which includes Students::FirstName, RELATED from Enrollees2 My layout is based on Class, with a portal to Enrollees1.   In the portal, the Enrollees1::LastName field has a drop down using VL_Last_Names.  Also in the portal, is Enrollees1::FirstName, which has a drop down using VL_First_Names_by_LastName.  The goal is that the user selects a last name and is then given a choice of only the valid first names for that last name. It may or may not surprise you (because of splitting the two relationships between two separate table occurrence groups) that all of this works so far. However: I now need to set conditional values on the FirstName field so that if the selected value is not in the valuelist, then it will display a different color.  I do this based on the ValueListItems() function and it does not work.  I test it using the data viewer, with my cursor in the portal row, using this calc:  ValueListItems(get(filename); "VL_First_Names_by_LastName").  Shouldn't this calc give me the same results as the actual drop-down gives me?

        • 1. Re: ValueListItems()  The_Related_Valuelist

          I'd like to see two items:

          1. The actual expression for your conditional format
          2. What you see when you use data viewer to test the value list items function.
          • 2. Re: ValueListItems()  The_Related_Valuelist

            1. case(not isempty(self) and not patterncount(valuelistitems(get(filename); "ValListName"))


            Now, I admit that this may or may not have worked anyhow, since each individual related record may or may not still recognize its own related value list after I've exited that portal row. This conditional formatting is not really the bug.


            2) Using the data viewer, nothing is returned, i.e. an empty value list.  For what it's worth, an empty value list is also what I get if I attempt the dropdowns on related fields taken out of the portal.

            • 3. Re: ValueListItems()  The_Related_Valuelist

              patterncount(valuelistitems(get(filename); "ValListName")) ?


              Syntax for patterncount is Patterncount ( text ; search text )


              You seem to be missing a parameter here.


              To avoid possible partial string matches, I'd use FilterValues instead of PatternCount.

              • 4. Re: ValueListItems()  The_Related_Valuelist

                Sorry, I'd just typed that calc manually into the forum posting, as I've already replaced the calc in my actual solution by adding some more table occurrences  to the relation graph mess.  


                I don't want want to distract from the actual bug report with the failure of the conditional formatting, as that is not really the bug.  The bug is in the ValueListItems() function as demonstrated by its use in the web viewer.


                Nonetheless, here is more likely the calc I had in my conditional formatting:

                patterncount(valuelistitems(get(filename); "ValListName"); self) 


                • 5. Re: ValueListItems()  The_Related_Valuelist

                  Yet it was in the conditional format expression that you reported problems with ValueList Items. I've used that function a lot without issue, though not with a conditional value list. Since working with lists can be a bit tricky, it's useful to rule out other possible issues.


                  I'm not sure if data viewer is demonstrating a bug or expected behavior in this situation.


                  I'm going to gin up a sample DB with the same basic structure and see if I can replicate this issue.

                  • 6. Re: ValueListItems()  The_Related_Valuelist

                    I can replicate the issue in Windows XP, sp3 and also share a work around.


                    I think this is a issue with the conditional format's behavior, rather than the ValueListItems function.


                    The ValueListItems function appears to evaluate from the context of the parent record even when the formatted object is part of a portal of related records.


                    Work around:

                    Define an unstored calculation in Enrollees, NameList, ValueListItems ( Get(FileName) ; "VL_First_Names_by_LastName" ) set to return text.


                    write your conditional format expression as:

                    not IsEmpty (Self) and not PatternCount ( Enrollees::NameList  ; Self )


                    And now the contents of the conditional value list are available to the expression in the context of the related table rathter than the main table.


                    Note that the patternCount function is not the best choice for this. If "Jenny" is in the value list, "Jen" will be counted as a member of the value list even if it is not. Use the following expression instead:

                    not IsEmpty (Self) and IsEmpty ( FilterValues ( Enrollees::NameList  ; Self ) )

                    • 7. Re: ValueListItems()  The_Related_Valuelist

                      PhilModJunk wrote:

                      I can replicate the issue in Windows XP, sp3 and also share a work around.

                      Define an unstored calculation in Enrollees, NameList, ValueListItems ( Get(FileName) ; "VL_First_Names_by_LastName" ) set to return text.

                      Thanks -- good idea for the workaround.