Skip to main content
ladislavm0002
Known Participant
Mar 24, 2023 3:00 PM UTC
Solved

Is it possible to use operator "includes" in a!queryLogicalExpression for an Array for Text type

Hello folks,

in my current solution i use an expression rule which queries a View in DB and returns the data based on filter. The Contract Number is an array of Text type, so far as operator i used "in" which worked perfectly even with multiple values. Now there is a new requirement from the business that instead of using exact values they want to have something like contains ("includes" or "start with"). It works if i change the type from array to single value, but not working with array. Is there any way how to make this work even for array?

I am getting the following error message while using array:

a!queryEntity_22r2(
  fetchTotalCount: TRUE,
  entity: cons!ICH_ENTITY_ICH_V_ContractReferenceData,
  query: a!query(
    selection: if(
      rule!GBL_isBlankOrEmpty(ri!fields),
      null,
      a!querySelection(
        columns: a!forEach(
          items: ri!fields,
          expression: a!queryColumn(field: fv!item)
        )
      )
    ),
    logicalExpression: a!queryLogicalExpression(
      ignoreFiltersWithEmptyValues: true,
      operator: "AND",
      filters: {
        a!queryFilter(
          field: "contractNumber",
          operator: "includes",
          value: ri!contractNumber,
          applyWhen: rule!GBL_isNotBlank(ri!contractNumber)
        ),
        a!queryFilter(
          field: "contractId",
          operator: "in",
          value: ri!contractId,
          applyWhen: rule!GBL_isNotBlank(ri!contractId)
        ),
        a!queryFilter(
          field: "sHfmCode",
          operator: "in",
          value: ri!sHfmCode,
          applyWhen: rule!GBL_isNotBlank(ri!sHfmCode),
        ),
        a!queryFilter(
          field: "rHfmCode",
          operator: "in",
          value: ri!rHfmCode,
          applyWhen: rule!GBL_isNotBlank(ri!rHfmCode),
        ),
        a!queryFilter(
          field: "sLedgerId",
          operator: "in",
          value: ri!sLedgerId,
          applyWhen: rule!GBL_isNotBlank(ri!sLedgerId),
        ),
        a!queryFilter(
          field: "rLedgerId",
          operator: "in",
          value: ri!rLedgerId,
          applyWhen: rule!GBL_isNotBlank(ri!rLedgerId),
        ),
        a!queryFilter(
          field: "totalContractAmount",
          operator: "in",
          value: ri!totalContractAmount,
          applyWhen: rule!GBL_isNotBlank(ri!totalContractAmount),
        ),
        a!queryFilter(
          field: "currency",
          operator: "in",
          value: ri!currency,
          applyWhen: rule!GBL_isNotBlank(ri!currency),
        ),
        a!queryFilter(
          field: "accountingPeriod",
          operator: "in",
          value: ri!accountingPeriod,
          applyWhen: rule!GBL_isNotBlank(ri!accountingPeriod),
        ),
        a!queryFilter(
          field: "status",
          operator: "in",
          value: ri!status,
          applyWhen: rule!GBL_isNotBlank(ri!status),
        ),
        a!queryFilter(
          field: "contractName",
          operator: "in",
          value: ri!contractName,
          applyWhen: rule!GBL_isNotBlank(ri!contractName),
        ),
        a!queryFilter(
          field: "sBusinessOwner",
          operator: "in",
          value: ri!sBusinessOwner,
          applyWhen: rule!GBL_isNotBlank(ri!sBusinessOwner),
        ),
        a!queryFilter(
          field: "sFinanceOwner",
          operator: "in",
          value: ri!sFinanceOwner,
          applyWhen: rule!GBL_isNotBlank(ri!sFinanceOwner),
        ),
        a!queryFilter(
          field: "rBusinessOwner",
          operator: "in",
          value: ri!rBusinessOwner,
          applyWhen: rule!GBL_isNotBlank(ri!rBusinessOwner),
        ),
        a!queryFilter(
          field: "rFinanceOwner",
          operator: "in",
          value: ri!rFinanceOwner,
          applyWhen: rule!GBL_isNotBlank(ri!rFinanceOwner),
        ),
        a!queryFilter(
          field: "createdBy",
          operator: "in",
          value: ri!createdBy,
          applyWhen: rule!GBL_isNotBlank(ri!createdBy),
        ),
        a!queryFilter(
          field: "completedOn",
          operator: "is null",
          applyWhen: rule!GBL_isNotBlank(ri!onlyNotCompleted)
        ),
        a!queryFilter(
          field: "active",
          operator: "=",
          value: ri!active,
          applyWhen: rule!GBL_isNotBlank(ri!active)
        ),
        a!queryFilter(
          field: "isCancelled",
          operator: "=",
          value: ri!isCancelled,
          applyWhen: rule!GBL_isNotBlank(ri!isCancelled)
        ),
        a!queryFilter(
          field: "masterContractId",
          operator: "in",
          value: ri!masterContractId,
          applyWhen: rule!GBL_isNotBlank(ri!masterContractId)
        ),
        a!queryFilter(
          field: "contractType",
          operator: "in",
          value: ri!contractType,
          applyWhen: rule!GBL_isNotBlank(ri!contractType)
        ),
        a!queryFilter(
          field: "childScheduleDate",
          operator: "=",
          value: ri!childScheduleDate,
          applyWhen: rule!GBL_isNotBlank(ri!childScheduleDate)
        ),
        a!queryFilter(
          field: "createdOn",
          operator: ">=",
          value: todatetime(ri!startDateFrom),
          applyWhen: rule!GBL_isNotBlank(ri!startDateFrom)
        ),
        a!queryFilter(
          field: "createdOn",
          operator: "<=",
          value: todatetime(ri!startDateTo) + 0.5,
          applyWhen: rule!GBL_isNotBlank(ri!startDateTo)
        ),
        a!queryFilter(
          field: "completedOn",
          operator: ">=",
          value: todatetime(ri!endDateFrom),
          applyWhen: rule!GBL_isNotBlank(ri!endDateFrom)
        ),
        a!queryFilter(
          field: "completedOn",
          operator: "<=",
          value: todatetime(ri!endDateTo) + 0.5,
          applyWhen: rule!GBL_isNotBlank(ri!endDateTo)
        )
      }
    ),
    pagingInfo: if(
      rule!GBL_isBlankOrEmpty(ri!pagingInfo),
      a!pagingInfo(
        startIndex: 1,
        batchSize: - 1,
        sort: a!sortInfo(field: "createdOn", ascending: false)
      ),
      ri!pagingInfo
    )
  )
)

mikes0011
mikes0011Answer
Brainy
Mar 24, 2023 3:16 PM UTC

You'll need to create a nested QueryLogicalExpression, presumably utilizing the "OR" operator, where you evaluate each separate item in the Rule Input array on the "contains" operator.

The Expression Guru

2 replies

mikes0011
mikes0011Answer
Brainy
Mar 24, 2023 3:16 PM UTC

You'll need to create a nested QueryLogicalExpression, presumably utilizing the "OR" operator, where you evaluate each separate item in the Rule Input array on the "contains" operator.

The Expression Guru
ladislavm0002
Known Participant
Mar 24, 2023 4:04 PM UTC

it worked, thank you