Hello All,
I''ve a tempo report that consists of 4 dropdown filters. I can load the grid data find using alternate 'wherecontains()' on index function on these 4 dropdown values, but I cannot pair the filters and run into a datasubset is not of complex type, in other ways i tried using a!queryEntity and currently getting the logicalExpression error. Can anyone suggest or point me to some thread where multiple filters in Logical expressions were used? Thanks
load( local!gettokendata: rule!TMS_getMultipletokenOwners(), local!selectedTeamListFilter, local!selectedUserFilter, local!selectedtokenstatusFilter, local!quarterlyFilter, local!ReportpagingInfo: a!pagingInfo( startIndex: 1, batchSize: 25, sort: a!sortInfo( field:"tokenId", ascending: false ) ), with( local!quarterlys: index(local!gettokendata,"quarterly",{}), local!tokenstatuses: index(local!gettokendata,"tokenStatus",{}), local!teamList: { cons!TMS_Teams_Names, cons!TMS_REG_TEAM_NAMES }, local!coUser: touniformstring(union( getdistinctusers( cons!TMS_Teams ), getdistinctusers( cons!TMS_REG_Teams ) )), local!tokenstatus: if( isnull(local!gettokendata), {}, sorttextarray( filternulls( union(local!tokenstatuses,local!tokenstatuses) ), false ) ), local!quarterly: if( isnull(local!gettokendata), {}, sortintegerarray( filternulls( union(local!quarterlys,local!quarterlys) ), false ) ), local!quarterlyData: if( isnull(local!quarterlyFilter),local!gettokendata, index(local!gettokendata,wherecontains(local!quarterlyFilter,local!gettokendata.quarterly),{}) ), local!teamdata: if( isnull(local!selectedTeamListFilter),local!gettokendata, index(local!gettokendata,wherecontains(local!selectedTeamListFilter,local!gettokendata.GroupDisplayName)) ), local!tokenstatusdata: if( isnull(local!selectedtokenstatusFilter),local!gettokendata, index(local!gettokendata,wherecontains(local!selectedtokenstatusFilter,local!gettokendata.tokenStatus)) ), local!selectedUserdata: if( isnull(local!selectedUserFilter),local!gettokendata, index(local!gettokendata,wherecontains(local!selectedUserFilter,local!gettokendata.NewtokenOwner)) ), local!finaldata: if( isnull(local!selectedUserdata),local!selectedUserdata, local!selectedUserdata ), /* local!reportdatasubset: a!queryEntity(*/ /* entity: cons!TMS_MULTIPLE_token_OWNERS_DS,*/ /* query: a!query(*/ /* selection: a!querySelection(*/ /* {a!queryColumn(field: "Id"),*/ /* a!queryColumn(field: "tokenId"),*/ /* a!queryColumn(field: "GroupDisplayName"),*/ /* a!queryColumn(field: "tokenNumber"),*/ /* a!queryColumn(field: "tokenStatus"),*/ /* a!queryColumn(field: "NewtokenOwner"),*/ /* a!queryColumn(field: "OldtokenOwner"),*/ /* a!queryColumn(field: "CreatedDateTime"),*/ /* a!queryColumn(field: "quarterly")}*/ /* ),*/ /* logicalExpression: {*/ /* a!queryLogicalExpression(*/ /* operator: "AND",*/ /* filters: {*/ /* apply(*/ /* a!queryFilter(*/ /* field: "quarterly",*/ /* operator: "=",*/ /* value: _*/ /* ),*/ /* if(isnull(local!quarterlyFilter),{}, tointeger(local!quarterlyFilter))*/ /* ),*/ /* if(isnull(local!quarterlyFilter),null,*/ /* a!queryFilter(*/ /* field: "tokenStatus",*/ /* operator: "includes",*/ /* value: local!selectedtokenstatusFilter*/ /* ) )*/ /* }*/ /* ),*/ /* a!queryLogicalExpression(*/ /* operator: "AND",*/ /* filters: {*/ /* apply(*/ /* a!queryFilter(*/ /* field: "GroupDisplayName",*/ /* operator: "includes",*/ /* value: _*/ /* ),*/ /* if(isnull(local!selectedTeamListFilter),{},touniformstring(local!selectedTeamListFilter))*/ /* ),*/ /* if(isnull(local!selectedUserFilter),{},*/ /* a!queryFilter(*/ /* field:"NewtokenOwner",*/ /* operator:"=",*/ /* value: local!selectedUserFilter*/ /* ))*/ /* }*/ /* )*/ /* }, */ /* pagingInfo:local!ReportpagingInfo*/ /* )*/ /*),*/ local!tokendatasubset: fn!todatasubset( local!finaldata, local!ReportpagingInfo ), a!dashboardLayout( firstColumnContents:{ a!sectionLayout( firstColumnContents:{ a!dropdownField( label:"Fiscal Year", choiceLabels: local!quarterly, choiceValues: local!quarterly, placeholderLabel:"All", value: local!quarterlyFilter, saveInto: { local!quarterlyFilter, a!save(local!ReportpagingInfo.startIndex,1), /*a!save(local!selectedtokenstatusFilter,null),*/ /*a!save(local!selectedTeamListFilter,null),*/ /*a!save(local!selectedUserFilter,null)*/ } ), a!dropdownField( label:"Team", choiceLabels: local!teamList, choiceValues: local!teamList, placeholderLabel:"All", value: local!selectedTeamListFilter, saveInto: { local!selectedTeamListFilter, a!save(local!ReportpagingInfo.startIndex,1) } ) }, secondColumnContents:{ a!dropdownField( label:"token Status", choiceLabels: local!tokenstatus, choiceValues: local!tokenstatus, placeHolderLabel: "All", value: local!selectedtokenstatusFilter, saveInto: { local!selectedtokenstatusFilter, a!save(local!ReportpagingInfo.startIndex,1) } ), a!dropdownField( label:"vigilance Officer", choiceLabels: local!coUser, choiceValues: local!coUser, placeHolderLabel: "All", value: local!selectedUserFilter, saveInto: { local!selectedUserFilter, a!save(local!ReportpagingInfo.startIndex,1) } ) } ), a!sectionLayout( label:"Multiple token Owners Report", firstColumnContents:{ a!gridField( label:"", totalCount: local!tokendatasubset.totalCount, columns:{ a!gridTextColumn( label:"Fiscal Year", field:"quarterly", data: index(local!tokendatasubset.data,"quarterly",{}), links:"" ),a!gridTextColumn( label:"token Status", field:"tokenStatus", data: index(local!tokendatasubset.data,"tokenStatus",{}), links:"" ),a!gridTextColumn( label:"Team", field:"GroupDisplayName", data: index(local!tokendatasubset.data,"GroupDisplayName",{}), links:"" ), a!gridTextColumn( label:"token Number", field:"tokenNumber", data: index(local!tokendatasubset.data,"tokenNumber",{}), links:"" ), a!gridTextColumn( label:"Old token Owner", field:"OldtokenOwner", data: index(local!tokendatasubset.data,"OldtokenOwner",{}) ), a!gridTextColumn( label:"New token Owner", field: "NewtokenOwner", data: index(local!tokendatasubset.data,"NewtokenOwner",{}) ), a!gridTextColumn( label:"token Created Date", field:"CreatedDateTime", data: index(local!tokendatasubset.data,"CreatedDateTime",{}) ) }, value: local!ReportpagingInfo, saveInto: local!ReportpagingInfo ) }, secondColumnContents:{} ) }, secondColumnContents:{} ) ) )
Discussion posts and replies are publicly visible
https://docs.appian.com/suite/help/19.1/Query_Recipes.html#querying-on-multiple-conditions
https://docs.appian.com/suite/help/19.1/Query_Recipes.html#querying-on-nested-conditions
https://docs.appian.com/suite/help/19.1/Query_Recipes.html#searching-on-multiple-fields
Hi Matthew, thank you for those links. The combination of two query filters in AND/OR works fine until I select an integer dropdown value, that's where everything stops working. The requirement is the 4 dropdowns be independent and as well filter the grid if selected a combination together.
Does the Searching on Multiple fields recipe meet your use case?
If the value of any dropdown is null, do not use it. If the value of any dropdown is populated, use it.
a!queryLogicalExpression( operator: "AND", filters: { if( isnull(ri!lastName), {}, a!queryFilter(field: "lastName", operator: "includes", value: ri!lastName) ), if( isnull(ri!title), {}, a!queryFilter(field: "title", operator: "includes", value: ri!title) ), if( isnull(ri!department), {}, a!queryFilter(field: "department", operator: "=", value: ri!department) ) } )
Please note, if you are on 19.1+, you will want to take a look at using "ignoreFiltersWithEmptyValues" parameter.
I'm using 16.3v old version, it worked now. As you pointed out the above empty value check param is not available for me, so I was doing inside an AND() an isnull() check for only two dropdowns instead of all the four for the entire a!queryLogicalExpression. It's working fine now! Thank you so much for the replies!