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.

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

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

  • Yes you need to explicitly tell this to user, ok suppose the format is 22 Jul 2018,

    so you have to create two new expression
    1) Validate the Date
    2) Convert the TEXT(User Input) to Date format
  • Hi Komal,

    I have create a rule to validate the Date Format

    =with(
    /* 22 Jul 2018 */
    local!SplitValues:
    split(ri!Input," "),

    local!AllMonths:{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},


    if(
    length(local!SplitValues) = 3,
    and(
    rule!Utils_IsOnlyNumber(local!SplitValues[1]),
    rule!Utils_IsOnlyNumber(local!SplitValues[3]),
    contains(
    local!AllMonths,
    tostring(local!SplitValues[2])
    )
    ),
    false()
    )

    )
  • Hi

     

    Thanx, the code to create a rule to validate the Date Format is working as expected.

     

    Now to convert user input to the date format expected by the DB i.e. 22_JUL-18, have created the following rule:

     

    with(
    /* 22 Jul 2018 */
    local!splitValues:split(ri!input," "),
    local!monthUpper:upper(local!splitValues[2]),
    local!year:right(local!SplitValues[3],2),
    joinarray({local!SplitValues[1],local!monthUpper,local!year},"-")
    )

     

    Here ri!input is of Any Type

     

    this rule gives me an output: 

    "22-JUL-18"

     

    This is my expected format but not the expected output type. So now to convert it to date format I have used todate() but the output with todate() is: 22/07/2018 which is again different from DB's format.

     

    Any suggestions on how to handle it so that I can pass my general search in this i.e. the value part of the code:

     

    if(
    rule!Utils_ValidDate(local!generalSearch),
    a!queryFilter(
    field:"launchDate",
    operator:"=",
    value:todate(local!generalSearch)
    ),{}
    ),

     

    Thanks!!

  • Hi Komal,

    For converting the TEXT to Date format you must create a new rule

    Utils_DateConversion

    =with(
      /* 22 Jul 2018 */
      local!SplitValues:
        split(ri!Input," "),
      
      local!AllMonths:{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},
       
       
      date(
        local!SplitValues[3],
         wherecontains(
            tostring(local!SplitValues[2]),
            local!AllMonths
          ),
        local!SplitValues[1]
      )
     
      
    )
    

     

    if(
    rule!Utils_ValidDate(local!generalSearch),
    a!queryFilter(
    field:"launchDate",
    operator:"=",
    value:rule!Utils_DateConversion(local!generalSearch)
    ),{}
    )

  • Hi  

    Thanks for replying!!
    But if you go through my code in the previous comment then from that also I am getting the date as 22/07/2018 however I want it as 22-JUL-2018, which I have achieved via. joinarray but format is not date.

    Any other suggestions please.

  • Hi Komal,

    Can you please let me know

     

    1.Which database you are using?

    2.Whether the data source for your record is a view or a table in the db?

    3.How many rows are there in the view or the table from where we are fetching the data?

     

    Before continuing further debugging I suggest you to try the following steps

     

    1.Remove the search code for the date field and now perform search on the record from tempo to make sure that the actual issue is with the date filed and not anything else.

    2.If the record still breaks then we need to further investigate whether this a large data issue.

    3.If Step 1 works fine then there's one thing we can do i.e. create a view and convert the date column in the desired date format on the database end rather then manipulating date format on the Appian side  this small poc can be conducted  to see if it resolves the problem.

     

     


    Thanks

  • Hi

    Below are the answers for your query:

    1.Which database you are using?
    Oracle (Third Party)

    2.Whether the data source for your record is a view or a table in the db?
    (View)

    3.How many rows are there in the view or the table from where we are fetching the data?
    (15)



    Before continuing further debugging I suggest you to try the following steps



    1.Remove the search code for the date field and now perform search on the record from tempo to make sure that the actual issue is with the date filed and not anything else.
    Already performed this action and that's why came to the conclusion that the problem was because of Number and Date fields. Number field issue is resolved with guidance.


    2.If the record still breaks then we need to further investigate whether this a large data issue.
    As an answer of above question, if it had been a data issue then it would not have worked even when date field was commented out. But without date field it is working fine.

    3.If Step 1 works fine then there's one thing we can do i.e. create a view and convert the date column in the desired date format on the database end rather then manipulating date format on the Appian side this small poc can be conducted to see if it resolves the problem.
    Have to conduct this POC to conclude.
Reply
  • Hi

    Below are the answers for your query:

    1.Which database you are using?
    Oracle (Third Party)

    2.Whether the data source for your record is a view or a table in the db?
    (View)

    3.How many rows are there in the view or the table from where we are fetching the data?
    (15)



    Before continuing further debugging I suggest you to try the following steps



    1.Remove the search code for the date field and now perform search on the record from tempo to make sure that the actual issue is with the date filed and not anything else.
    Already performed this action and that's why came to the conclusion that the problem was because of Number and Date fields. Number field issue is resolved with guidance.


    2.If the record still breaks then we need to further investigate whether this a large data issue.
    As an answer of above question, if it had been a data issue then it would not have worked even when date field was commented out. But without date field it is working fine.

    3.If Step 1 works fine then there's one thing we can do i.e. create a view and convert the date column in the desired date format on the database end rather then manipulating date format on the Appian side this small poc can be conducted to see if it resolves the problem.
    Have to conduct this POC to conclude.
Children
  • Since we are getting data from a view we are free to modify it in any form in our view we want at oracle side just convert that into a plain text field and don't try to manipulate date format on Appian side basically the idea is to make the date formats consistent on both Appian and Oracle side since Appian doesn't allow much manipulation,lets do it on db side to make it consistent with Appian.


    For Example :

    If we are expecting users to conduct search using this particular format

    "12-Jul-2018"

    then we can modify our column in view which contains data as
    "12Jul2018"

    and we will manipulate the date input in the logical expression to be same as "12Jul2018" and perform a search.

    However if we are expecting different type of date formats in the search field by the user we have to create an
    expression rule that will convert all different type of date formats entered by user into our target format i.e. "12Jul2018"
  • Hi

    Thanks for repling!!

    I do agree with your point of modifying the view.
    But I am keen to handle it in the similar way the number field has been handled above. Any suggestions on this please.

    And for handling the user input in search field in expected format, we have created an expression rule!Utils_DateConversion() (Above).
  • Hi

    The way I have implemented date search is something like this

    a!queryFilter(
    field: "EmployeeId",
    operator: "=",
    value: todate(
    ri!searchTerm
    )
    )

    and the format in which date is stored in db is like '1988-10-10' i.e. yyyy-mm-dd.

    1.As you have mentioned that the date format in DB is like "22-JUL-18" I would request you to create a query entity on that view with just one date filter and figure out the exact format of date for which the query entity works.

    2.Also check in your views CDT that the data type is date and not date and time.

    please let me know the above two queries so that I can think of an approach.
  • Hi

    Kindly go through the above comments.

    1. I have created the query entity with date filter and it is working with the same date format in DB i.e. "22-JUL-18".
    2. The field is of type date in CDT.

    Thanks
  • Thanks for clarifying above two points as we are sure that "22-JUL-18" works in query entity the only
    option will work now is to change data type of the date column in the view and cdt to text and then implement search with
    "includes" parameter just like a normal text search instead of date and If you want to keep the date column intact then you can add another column in the view and cdt with data type as text and use this new column to perform date search on record.

    a!queryFilter(
    field: "EmployeeDob",
    operator: "includes",
    value: ri!searchTerm
    )
  • Hi  

    Thanks for your suggestion.
    Will go this way if nothing comes up from any one to handle the date field.