Hi., I have an entity backed record with a grid view. As per the documentation, the 'Search' is applied across all columns of the grid.
Our requirement is to make the search happen against selective columns say 4 out of 10. For this purpose i am trying to create a service (Expression) backed record which has control over the search functionality.
My search works fine if i configure it against any one column but it stops working if i add multiple queryFilters (with OR condition).
Attaching the code for reference. Any pointer would be helpful.
with( /* Get the first CDT containing one or more of the following: Logical Expression, Query Filter, Search Query. It is called a logicalExpression|filter|search -- abbreviated as LFS */ local!parentLFS: index( ri!rspQueryObj, "logicalExpression|filter|search", null ), /* Check to see if the above CDT has a nested LFS in it. This can happen if the user has clicked multiple filters or searched on filtered results. */ local!childLFS: index( local!parentLFS, "logicalExpression|filter|search", null ), /* Get the LFS containing the filter(s) and/or search. Basically, if the child exists, it will contain all the information. If it doesn't then we want to parent. */ local!mainLFS: if( isnull( local!childLFS ), local!parentLFS, local!childLFS ), /* Get runtime types of LFS (type!QueryFilter or type!Search) */ local!types: apply( fn!runtimetypeof, { local!mainLFS } ), /* Get the search CDT, if there is one. It has its own type that we can look for. */ local!searchCdt: index({local!mainLFS}, where(local!types='type!{http://www.appian.com/ae/types/2009}Search'), null), /* Gets filter CDTs if there are any. We can find them by their type. */ local!filterCdts: index({local!mainLFS}, where(local!types='type!{http://www.appian.com/ae/types/2009}QueryFilter'), {}), local!recSearchText: if( isnull( local!searchCdt ), "", tostring( index( cast( 'type!{http://www.appian.com/ae/types/2009}Search', local!searchCdt ), "searchQuery", "" ) ) ), local!recFilters: if( rule!APN_isEmpty( local!filterCdts ), {}, cast('type!{http://www.appian.com/ae/types/2009}QueryFilter?list', local!filterCdts) ), /* Get filters' fields and values to avoid function repitition */ local!filterFields: if(rule!APN_isEmpty(local!recFilters), {}, index(local!recFilters, "field", {})), local!filterValues: if(rule!APN_isEmpty(local!recFilters), {}, index(local!recFilters, "value", {})), /* Get filtered 'Request Status' */ local!requestStatusFilterValues: if( rule!APN_isEmpty(local!filterFields), "", displayvalue( "requestStatus_txt", local!filterFields, local!filterValues, null ) ), /* Get filtered 'Request Type' */ local!requestTypeFilterValues: if( rule!APN_isEmpty(local!filterFields), "", displayvalue( "requestType_txt", local!filterFields, local!filterValues, null ) ), a!queryEntity( entity: cons!WMG_GBL_REQ_TRACK_VIEW_ENTITY, query: a!query( logicalExpression: a!queryLogicalExpression( operator: "AND", filters: { /* Default Filter */ a!queryFilter( field: "businessUnit_txt", operator: "=", value: ri!businessUnit_txt ), if( rule!APN_isEmpty( local!requestTypeFilterValues ), {}, a!queryFilter( field: "requestType_txt", operator: "=", value: local!requestTypeFilterValues ) ), if( rule!APN_isEmpty( local!requestStatusFilterValues ), {}, a!queryFilter( field: "requestStatus_txt", operator: "=", value: local!requestStatusFilterValues ) ) logicalExpression: a!queryLogicalExpression( operator: "OR", filters: { if( rule!APN_isBlank( local!recSearchText ), {}, { a!queryFilter( field: "requestId_int", operator: "=", value: local!recSearchText ), a!queryFilter( field: "accountNumber_txt", operator: "=", value: local!recSearchText ), a!queryFilter( field: "clientName_txt", operator: "=", value: local!recSearchText ), a!queryFilter( field: "taxIdValue_txt", operator: "=", value: local!recSearchText ) } ) } ) } ), pagingInfo: a!pagingInfo( startIndex: 1, batchSize: 10, sort: a!sortInfo( field: "createdDate_dt", ascending: false ) ) ) ) )
Discussion posts and replies are publicly visible
Hi Vinay Kumar Rai
I am facing an issue with the general search.If any one of my column been fetched is of type other than text; then the record throws an error An eror occured while retrievng data for the record.By other type I mean, in the code, the code written for general search i.e.:
a!queryFilter( field:"fullName", operator:"includes", value:local!generalSearch )
Actually I want to do a general search on a field which is either integer or date.Any suggestions on what tweaking has to be done in the code to handle such scenarios.
Below is full code for reference where launchDate is a date filed and accountNumber is an integer field
*Code for rule!PH_QE_getDetailsFromGenericView() */ with( local!queryInformation: rule!parseRspQuery(ri!rspQuery), local!filterFields: index(local!queryInformation.filters, "field", {}), local!filterValues: index(local!queryInformation.filters, "value", {}), local!generalSearch:local!queryInformation.searchText, local!uniqueIdentifier: displayvalue( "rp!id", local!filterFields, local!filterValues, null ), local!status: displayvalue( "status", local!filterFields, local!filterValues, "" ), local!typeDetail: a!forEach( displayvalue( "typeDetail", local!filterFields, local!filterValues, "" ), tostring(fv!item) ), if( rule!APN_isBlank(local!uniqueIdentifier), a!queryEntity( entity:cons!GENERIC_VW_DE, query:a!query( selection: a!querySelection( columns:{ a!queryColumn( field:"id", visible:true ), a!queryColumn( field:"code", visible:true ), a!queryColumn( field:"number", visible:true ), a!queryColumn( field:"typeDetail", visible:true ), a!queryColumn( field:"status", visible:true ), a!queryColumn( field:"fullName", visible:true ), a!queryColumn( field:"launchDate", visible:true ), a!queryColumn( field:"accountNumber", visible:true ) } ), logicalExpression: a!queryLogicalExpression( operator: "AND", filters: { /* Default Filter */ a!queryFilter( field:"typeDetail", operator:"in", value:cons!TYPES_OF_DETAIL ), if( rule!APN_isBlank(local!uniqueIdentifier), {}, a!queryFilter( field:"id", operator:"=", value:local!uniqueIdentifier ) ), /*Status and Type Detail are user filters */ if( rule!APN_isBlank(local!status), {}, a!queryFilter( field:"status", operator:"=", value:local!status ) ), if( rule!APN_isBlank(local!typeDetail), {}, a!queryFilter( field:"typeDetail", operator:"in", value:local!typeDetail ) ) }, logicalExpressions: if( rule!APN_isBlank(local!generalSearch), {}, a!queryLogicalExpression( operator: "OR", filters: { a!queryFilter( field:"code", operator:"includes", value:local!generalSearch ), a!queryFilter( field:"number", operator:"includes", value:local!generalSearch ) , a!queryFilter( field:"typeDetail", operator:"includes", value:local!generalSearch ), a!queryFilter( field:"status", operator:"includes", value:local!generalSearch ), a!queryFilter( field:"fullName", operator:"includes", value:local!generalSearch ), a!queryFilter( field:"launchDate", operator:"includes", value:local!generalSearch ), a!queryFilter( field:"accountNumber", operator:"includes", value:local!generalSearch ) } ) ) ), pagingInfo:local!queryInformation.pagingInfo ) ), a!queryEntity( entity:cons!GENERIC_VW_DE, query:a!query( selection: a!querySelection( columns:{ a!queryColumn( field:"id", visible:true ), a!queryColumn( field:"code", visible:true ), a!queryColumn( field:"number", visible:true ), a!queryColumn( field:"typeDetail", visible:true ), a!queryColumn( field:"status", visible:true ), a!queryColumn( field:"fullName", visible:true ) } ), filter:a!queryFilter( field:"id" /* Your Primary Key Column*/, operator:"=", value:local!uniqueIdentifier ), pagingInfo:topagingInfo(1,1) ) ) ) )
Thanks in advance!!
Hi Vinay Kumar Rai Thanks for replying!! But "=" is not working either.
Just a thought, is there any tweaking we need to do in the rule!PH_parseRspQuery() from where actually the general search result is obtained:
https://docs.appian.com/suite/help/18.2/Service-Backed_Record_Tutorial.html
Any other suggestion please.
Yes you need to cast first, and need to check if the local!generalSearch is a Text/Number/date, I have used some custom function to check weather the input is Number or Text.
please find my code below
if( rule!APN_isBlank(local!generalSearch), {}, a!queryLogicalExpression( operator: "OR", filters: { /* Please check if input is Number*/ if( rule!Utils_IsOnlyNumber(local!generalSearch), a!queryFilter( field:"code", operator:"=", value:tointeger(local!generalSearch) ),{} ), /* Please check if input is Number*/ if( rule!Utils_IsOnlyNumber(local!generalSearch), a!queryFilter( field:"number", operator:"=", value:tointeger(local!generalSearch) ),{} ), a!queryFilter( field:"typeDetail", operator:"includes", value:local!generalSearch ), a!queryFilter( field:"status", operator:"includes", value:local!generalSearch ), a!queryFilter( field:"fullName", operator:"includes", value:local!generalSearch ), /* Please check If valid Date*/ if( rule!Utils_ValidDate(local!generalSearch), a!queryFilter( field:"launchDate", operator:"=", value:todate(local!generalSearch) ),{} ), /* Please check if input is Number*/ if( rule!Utils_IsOnlyNumber(local!generalSearch), a!queryFilter( field:"accountNumber", operator:"=", value:tointeger(local!generalSearch) ),{} ) } ) )
With your guidance, I am able to handle the integer field.The rule that I have created for rule!Utils_IsOnlyNumber() is:
len(ri!input)=len(tointeger(ri!input)) /*where input is of Any Type*/
However, I am not able to create a generic rule for Date field.
I tried out with something like below:
len(ri!input)=len(datetext(todate(ri!input), "dd" &" "& "MMM" &" "& "yyyy")) /* ri!input is of Any Type */
but it is not helping me out.
Any suggestions on how to handle date field, I mean the general rule to check if entered value is date.
Also just FYI, the date field is stored in my DB as:
22-JUL-2018 but in my record list view I am displaying it to the user as 22 Jul 2018, this is achieved with following code:
if(isnull(ri!date), null, datetext(ri!date, "dd" & ri!separator & "MMM" & ri!separator & "yyyy") ) /* ri!date is of type Date ri!separator is of type Text */
Hi Vinay Kumar Rai Thanks for replying!! But this field would be a general search on record list from which user can enter the date and he will most likely enter in the same format as that being displayed to user on record list view which in my case is 22 Jul 2018. So are you saying explicitly tell this to user..?