SAP Knowledge Base Article - Public

3316651 - [IN] Operator is returning value as False (0) when used with CTX and TABLE tag in CPQ

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

  1. Login to CPQ tenant
  2. Go to any Quote and make sure the custom table is added to the quote
  3. The Query is:
    [IN](<*CTX ( Quote.CustomField(<CustomFieldName>).AttributeValueCode )*>,<* TABLE ( SELECT ColumnName1 FROM TableName WHERE  ColumnName2= 'Value' ) *>)
  4. 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

Product

SAP CPQ 2020