I have two list fields (say FieldA and FieldB). FieldA contains: A, B, C, D, E, F
FieldB contains: X, Y, C, L, M, A
As you can see the common elements in both fields are C and A.
Is it possible (using @Formula) to return ‘True’ if one or more value(s) in FieldB that is also contains in FieldA? (i.e. the example above should return True)
The example below should return False as none of the elements of FieldB is in FieldA:
FieldA contains: A, B, C, D, E, F
FieldB contains: X, Y, Z
Subject: The Easy way: FieldA *= FieldB
From the Help (its hard to find)Operations on lists are of two types:
Pair-wise – Pairwise operators act on two lists in parallelelement fashion. The first element of list 1 pairs with the first element of list 2, the second element of list 1 pairs with the second element of list 2, and so on. If one list has fewer elements than the other, the last element in the shorter list is repeated for operations with the remaining elements of the longer list. If list 1 consists of “A”:“B”:“C” and list 2 consists of “1”:“2,” the operation is performed as though list 2 contained “1”:“2”:“2.” For pairwise equality tests, only one match is needed for the statement to return True, or 1.
Permuted – Permutation operators act on two lists, pairing every possible combination of their values. The resulting list has an element for each pairing in the following order: list 1 element 1 paired with each element in list 2, list 1 element 2 paired with each element in list 2, and so on through the last element in list 1.
If an operation occurs on a list and a non-list value, the non-list value is paired with each element in the list.
The table below shows the pairwise and permutation operators.
Pair-wise operator
Permutation operator
Meaning
**
Multiplication
/
*/
Division
*+
Addition
*-
Subtraction
*>
Greater than
<
*<
Less than
=
*>=
Greater than or equal to
<=
*<=
Less than or equal to
=
*=
Equal
!=
*!=
Not equal
The table below shows how pairwise and permutation operators differ.
Operation
Statement
Yields
Concatenation, pair-wise
“A”:“B”:“C”+“1”:“2”:“3”
“A”:“B”:“C”+“1”:“2”
“A”:“B”:“C”+“1”
“A1”:“B2”:“C3”
“A1”:“B2”:“C2”
“A1”:“B1”:C1"
Concatenation, permutation
“A”:“B”:“C”*+“1”:“2”:“3”
“A”:“B”:“C”*+“1”:“2”
“A1”:“A2”:“A3”:“B1”:“B2”:“B3”:“C1”:“C2”:“C3”
“A1”:“A2”:“B1”:“B2”:“C1”:“C2”
Addition, pair-wise
1:2:3+10:20:30
1:2:3+10:20
1:2:3+10
11:22:33
11:22:23
11:12:13
Addition, permutation
1:2:3*+10:20:30
1:2:3*+10:20
11:21:31:12:22:32:13:23:33
11:21:12:22:13:23
Text equality, pair-wise
“A”:“B”:“C”=“B”:“C”:“A”
“A”:“B”:“C”=“B”:“C”
“B”:“B”:“C”=“B”:“C”
0 False
1 True
1
Text equality, permutation
“A”:“B”:“C”*=“B”:“C”:“A”
“A”:“B”:“C”*=“B”:“C”
“B”:“B”:“C”*=“D”:“E”
1
1
0
Number equality, pair-wise
1:2:3=2:3:1
1:2:3=2:3
2:3:3=2:3
2:3:3=3:1
0
1
1
0
Number equality, permutation
1:2:3*=2:3:1
1:2:3*=2:3
1:2:3*=4:5
1
1
0
Date equality, pair-wise
1
1
1
Date equality, permutation
1
1
0
Subject: Compare values in lists
This is what @Keywords is for. You might have to fiddle with the separators, depending on the real data values.