Skip to main content
ladislavm0002
Known Participant
March 24, 2023
Solved

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

  • March 24, 2023
  • 2 replies
  • 7 views

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
    )
  )
)

Best answer by mikes0011

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.

2 replies

mikes0011
mikes0011Answer
Brainy
March 24, 2023

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
March 24, 2023

it worked, thank you