Configuring 'Search' for Service Backed Record

 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, You miss comma on the line number 134, and On the line number 135 please replace "logicalExpression" to "logicalExpressions"

    The logicalExpressions for OR filter should be declare into queryLogicalExpression not inside the filters

     

    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
                )
              )
                         
            },
    		logicalExpressions: 
    		  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
            )
          )
        )
      )
    )

  • Thanks Vinay.

    I was trying multiple ways before posting in the forum and hence the code had mistakes like comma and placing 'logicalExpressions' into filters. The actual mistake was using 'logicalExpression' instead of 'logicalExpressions' which you rightly pointed out.

    It worked fine after fixing the spell error. Thanks again.

     

    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
                )
              )
            },
            logicalExpressions: if(
              rule!APN_isBlank(
                local!recSearchText
              ),
              {},
              a!queryLogicalExpression(
                operator: "OR",
                filters: {
                  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
            )
          )
        )
      )
    )

  • Hi  

     

    I am facing a similar issue.

    The user filters are working fine however, when I am doing configuration for general search I get error in retrieval of data.

    I am working on version 17.4.

     

    Below is my code:

     

    /*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!status: displayvalue(
        "status",
        local!filterFields,
        local!filterValues,
        ""
      ),
      local!typeDetail: a!forEach(
        displayvalue(
          "typeDetail",
          local!filterFields,
          local!filterValues,
          ""
        ),
        tostring(fv!item)
      ),
      
      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
              )
            }
           ),
           logicalExpression: a!queryLogicalExpression(
            operator: "AND",
            filters: {
              /* Default Filter */
          		 a!queryFilter(
                field:"typeDetail",
                operator:"in",
                value:cons!TYPES_OF_DETAIL
              ),
    		  /*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: 
      		  a!queryLogicalExpression(
                  operator: "OR",
                  filters: {
            			if(
            				rule!APN_isBlank(local!generalSearch),
            				{},
                        {
                        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
            		  ) 
                    }
                  )
                 }
              ) 
          ),
          pagingInfo:ri!rspQuery.pagingInfo
        )
      )
    )
     

     

     

    /* Code for rule!parseRspQuery() */
    
    with(
    
        /* 
        * Get the first data type 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!rspQuery, "logicalExpression|filter|search", null),
        
        /* 
        * Check to see if the above data type has a nested LFS in it. This can happen
        * if the user has clicked multiple filters. If all filters were in a single
        * set, for example a group of states on a multi select filter, we want to use the
        * parent as the main LFS so that the expression correctly consolidates the filters.
        */
        local!childLFS: if(
        index(local!parentLFS, "operator", "") = "OR",
        null,
        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: cast(
        'type!{http://www.appian.com/ae/types/2009}Type?list',
        a!forEach({local!mainLFS}, runtimetypeof(fv!item))
        ),
        
        /*
        Get the search data type, if there is one. It has its own type that we can look for.
        */
        local!searchData: index({local!mainLFS}, where(local!types='type!{http://www.appian.com/ae/types/2009}Search'), null),
        
        /* Gets filter data types if there are any. We can find them by their type. */
        local!singleSelectFilters: index({local!mainLFS}, where(local!types='type!{http://www.appian.com/ae/types/2009}QueryFilter'), {}),
        
        /* Gets logical expression that, for our case, could hold multiple state filters */
        local!multiSelectFilterLogicalExpressions: index(
        {local!mainLFS},
        where(local!types='type!{http://www.appian.com/ae/types/2009}LogicalExpression'),
        {}
        ),
        
        /*
        * Converts multiple filters to single filter for each multiple filter.
        * This assumes that all options in a single filter operate on the same field
        */
        local!multiSelectSingleFilter: if(
        length({local!multiSelectFilterLogicalExpressions}) = 0,
        {},
        a!forEach(
        local!multiSelectFilterLogicalExpressions,
        a!queryFilter(
        field: fv!item.'logicalExpression|filter|search'[1].field,
        operator: "in",
        value: fv!item.'logicalExpression|filter|search'.value
        )
        )
        ),
        
        /* Gets paging info */
        local!paging: index(ri!rspQuery, "pagingInfo", topaginginfo(1,100)),
        
        /* 
        Creates and returns a Dictionary containing the pagingInfo, search text, and filters 
        currently applied to the record list.
        */
        {
        pagingInfo: local!paging,
        searchText: tostring(
        index(
        cast('type!{http://www.appian.com/ae/types/2009}Search',local!searchData), 
        "searchQuery", 
        ""
        )
        ),
        filters: cast(
        'type!{http://www.appian.com/ae/types/2009}QueryFilter?list',
        {
        local!singleSelectFilters,
        local!multiSelectSingleFilter
        }
        )
        }
    )

     

    Kindly suggest.

  • Hi Komal,

    You have only issue with the Search or the record is showing error on the first time?
  • Hi

    The record shows me the error the first time itself!!
  • Hi
    On the rule rule!PH_QE_getDetailsFromGenericView(), please replace the PagingInfo on the line number 123, use below pagination congifuration

    local!queryInformation.pagingInfo
  • Hi

    I changed the pagingInfo but I didn't help me either still getting the same error in retrieval.
    Any other suggestions
  • Ok, can you please remove all the filters, and try to check if its work,

     

    /*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!status: displayvalue(
        "status",
        local!filterFields,
        local!filterValues,
        ""
      ),
      local!typeDetail: a!forEach(
        displayvalue(
          "typeDetail",
          local!filterFields,
          local!filterValues,
          ""
        ),
        tostring(fv!item)
      ),
      
      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
              )
            }
           ),
             pagingInfo:topagingInfo(1,1)
        )
      )
    )

  • No this is not working  

    Any other suggestion.

  • Ok strange,

    please try to run the queryEntity separately,I think you may have the issue on the VIEW