4 Replies Latest reply on Jun 19, 2013 7:38 AM by James_L

    Can I use a Value List in Calculation?



      Can I use a Value List in Calculation?


      I have a dumb-looking formula for conditional formatting:

      Self ≠ "45"   and Self ≠ "15" and Self ≠ "7" and Self ≠ ""and Self ≠ "A"and Self ≠ "T"

      Could I put these items into a ValueList and use that list in a calculation? - something like:

      Self does not contain members of ValueList ("Filename";"ValueList name") 

      Many thanks for any insights,


        • 1. Re: Can I use a Value List in Calculation?

          Why not try the PatternCount function to check if the PatternCount of Self in the ValueListItems function returns >= 1?

          Although it doesn't happen with the examples you give, to avoid confusion with possible future entries I'd set the PatternCount looked for to be "¶" & Self & "¶" or else you will get wrong results if one of the list values is "15" and Self is "5".  To complete that you'd need to start the ValueList with a return as well - something like "¶" & ValueListItems.  Check if you'd need it on the end also (can't see that it would do any harm).

          Not sure how it would handle a space in the value list so you may want to check for it separately.

          • 2. Re: Can I use a Value List in Calculation?

            IsEmpty ( FilterValues ( ValueListItems ( Get ( FieldName ) ; "NameOfValueListInQuotes" ) ; self ) )

            Will be true if the value in self is not a value or list of values found in "NameofValueListInQuotes".

            You can also just use:

            IsEmpty ( FilterValues ( list ( 45 ; 15 ; 7 ; "A" ; "T" ) ; self ) )

            If you don't want to explicitly define a value list in Manage Value Lists...

            • 3. Re: Can I use a Value List in Calculation?

                   Elegant solution, Phil: can you explain the logic in the "Get (Fieldname) line of your script? 

                   Also, how would I adapt this script if I wanted to filter a table for Field X matching values in Value List Z?  Enter find mode and set field X to....? 

              • 4. Re: Can I use a Value List in Calculation?

                     (NOTE: Never mind.  It works perfectly.  I had a letter in one expression not capitalized.  Duh.)

                     A further question: I've got a similar problem in a calculation field in table "COB_Entries."

                     What I want is to check with a related (one to many) join table "COB_Signatories_Join" which contains a field "Type" and compare that to a value list "Acquisition_Types."  (Thus, all records that have Type fields containing "purchaser," "transferee," or "Lessee" from that list will return true, but that list will grow.).

                     That join table should then produce a list of names from a third related (one to one) table, "Signatories,"  all of whom "Acquired" in the original record in COB_Entries.

                     I haven't had much luck with this calculation: everything works fine up until the list-value comparison in the last bolded line:

                     Date & 
                     "     " & 
                     c_COB_Full &
                     If (  not IsEmpty ( NA ) ; " (NA " & NA  & ")"; "" ) &
                      ", " & 
                     Type_of_Instrument & 
                If ( not IsEmpty (FilterValues (ValueListItems (Get (FileName); "Acquisition_Types") ; COB_Signatories_Join::Type)) ; "Acq'd " & List (Signatories::c_Signatory))
                     Any help is appreciated.