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 ) ) )
Discussion posts and replies are publicly visible
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.
it worked, thank you