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

# Can I use a Value List in Calculation?

### Title

Can I use a Value List in Calculation?

### Post

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,

Roger

• ###### 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.