Symptom
- We have a Custom table in CPQ and we are using a combination of CTX tag and Table tag to get and match the result.
- When we use the standard [IN] conditional operator it is resulting incorrect result.
For example:
Using <*CTX ( Quote.CustomField(<CustomFieldName>).AttributeValueCode )*> we are getting A1 as a value.
Using <* TABLE ( SELECT ColumnName1 FROM TableName WHERE ColumnName2= 'Value' ) *> we are getting values as A0,A1,A2 from the custom table.
- But when we use the below formula we get incorrect result:
[IN](<*CTX ( Quote.CustomField(<CustomFieldName>).AttributeValueCode )*>,<* TABLE ( SELECT ColumnName1 FROM TableName WHERE ColumnName2= 'Value' ) *>) is returning 0 instead of returning 1.
- When we use the same formula using the below formula,
[IN](A1,A0,A1,A2) we are getting the correct result as 1.
Environment
SAP SALES CLOUD CPQ
Reproducing the Issue
- Login to CPQ tenant
- Go to any Quote and make sure the custom table is added to the quote
- The Query is:
[IN](<*CTX ( Quote.CustomField(<CustomFieldName>).AttributeValueCode )*>,<* TABLE ( SELECT ColumnName1 FROM TableName WHERE ColumnName2= 'Value' ) *>) - The expectation is that the result should return as True (1), but it's returning False (0) instead.
Resolution
Seems that this is expected behaviour and here is the explanation why:
Below is how the IN operator works:
Definition for [IN] operator looks like this: [IN](value, value1, value2, value3, ...) => IN operator tries to find value in following values (value1, value2, value3, ...)
The TABLE tag will return value A0, A1, A2 and as per the above definition it will look like this:
[IN](A1, A0,A1,A2) where:
- A1 is value from the above definition
- A0,A1,A2 is value1 from above definition
In this case, after all formulas are evaluated, we will have following:
- <*CTX ( Quote.CustomField(<CustomFieldName>).AttributeValueCode )*> will return A1
- <* TABLE ( SELECT ColumnName1 FROM TableName WHERE ColumnName2= 'Value' ) *> will return A0,A1,A2
Then the IN operator will look like this: [IN] (A1, A0,A1,A2) and because IN operator will try to match same strings, in this case, IN operator will return FALSE (0) since here it is comparing A1 (value) with A0,A1,A2 (value1), so value and value1 are not equal, which is why it is returning 0, instead of 1.
So this is not a product limitation at all and is expected behaviour and we need to try some other formula if we want to get value True (1).
Keywords
[IN] operator, IN operator, IN, CPQ, TABLE tag, CTX tag, definition, value, formula, True, False, 1, 0, [IN](value, value1, value2, value3, ...) , KBA , CEC-SAL-CPQ , Sales Cloud CPQ , Problem