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

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

  • 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.

  • 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
  • Query entity is working fine separately.
    Infact in the code as I mentioned, my user filters are also working fine. The issue is if I incorporate the generalSearch logicalExpression.

    Any suggestions.
  • Ok got the issue,

    Your If condition is incorrectly placed, to check the local!generalSearch is null

    /*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: 
    		
    		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
            		  ) 
                   
                 }
              ) 
    		)
          ),
          pagingInfo:local!queryInformation.pagingInfo
        )
      )
    )

  • Hi

    One more question.

    My record list view would be created just like the way we create for an entity backed record right?

    However, I am facing a weird issue that when I am opening my record via. the link in the record list view, everytime the first record that shows up in the grid opens and not the corresponding expected record.

     

    Below is the code snippet for the link:

     

    a!recordLink(
      label: rf!code,
      recordType: rp!type,
      identifier: rp!id
    )
    

     

    Kindly suggest.

  • Hi,

    You can not populating unique Identifier on your Source expression,

    local!uniqueIdentifier: displayvalue(
    "rp!id",
    local!filterFields,
    local!filterValues,
    null
    ),

    when user click on the record link the local!uniqueIdentifier holds the value,

    local!uniqueIdentifier -> holds primary key value.

    please check for null, if local!uniqueIdentifier is null then query all the values with pagination else get the detail by local!uniqueIdentifier



    Thanks
    Vinay

  • Hi

    Yes search issue is fixed, thanx again!!

    If I am not wrong by using unique identifier you mean I modify my code as below:

     

    /*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)
      ),
      
      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
              ),
              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
            		  ) 
                   
                 }
              ) 
    		)
          ),
          pagingInfo:local!queryInformation.pagingInfo
        )
      )
    )

    If I am doing something like this, then on clicking the link I get the error of retrieval of data.

     

    Kindly suggest.

  • Hi

    Please try the below 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!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
              )
            }
           ),
           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
            		  ) 
                   
                 }
              ) 
    		)
          ),
          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)
        )
      )
         
      
      )
      
      
    )

  • Hi

    Thanx for your prompt response.

    I have tried your suggestion, however I am getting the error of retrieval of data when I click on link in record grid list.

    Any suggestion.
Reply Children
  • please try

    filter:a!queryFilter(
    field:"id" /* Your Primary Key Column*/,
    operator:"=",
    value:tointeger(local!uniqueIdentifier)
    ),
  • Hi

    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 Komal,

    Operator "include" only work with String.

    Please use "=" in case of Integer or date

    Thanks
    Vinay
  • Hi  

    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)
    						  ),{}	
    					)  
                   
                 }
              ) 
    		)

  • Hi

    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,

    to check if the data is valid,

    You must fix the date format in Record Search like (dd-mm-yyyy),

    and in the rule Utils_ValidDate

    use Split(ri!Input,"-")
    so on the first index you get Day, 2nd index Month, 3rd Index Year,
    then use date(local!DateSplit[3],local!DateSplit[2],local!DateSplit[1])
  • Hi

    I have tried the following code for rule Utils_ValidDate():

    load(
    local!dateSplit:split(touniformstring(ri!input)," "),
    date(year(local!dateSplit),month(local!dateSplit),day(local!dateSplit))
    )

    where ri!input is of Any Type

    But it is not working, getting error.

    Also if you can kindly elaborate what do you mean by:

    "You must fix the date format in Record Search like (dd-mm-yyyy),"

    Thanks!!
  • Hi Komal,

    The Users should enter the date in some format, so you need to tell date format to user like dd-mm-yyy or mm-dd-yyyy or yyyy-mm-dd and soon, so that it is easy to validate the date format in your source expression.