How to build a Custom Picker to search on all columns of a CDT. Similar to the search box in a Record Type on Grid Data.

Scenario: Building a custom picker that retrieves records based on the keyword entered. the picker should search on all columns in a CDT and retrieve name of the assets that have a matching keyword in any of the it's columns.

Please advise how to search on all columns of CDT, Similar to the search box in a Record Type on Grid Data.

Currently, we are searching only on two columns ProjectName and Id, to reduce the payload which is limited to 1GB.

  

Search entity code and retrieve only 2 columns to prevent reaching payload limit 1GB.

a!queryEntity(
entity: cons!NSARequest_Entity_Cons,
query: a!query(
selection: {
a!querySelection(
columns: {
a!queryColumn(
field: "ProjectName",
visible: true
),
a!queryColumn(
field: "Id",
visible: true
)
}
)
},
pagingInfo: a!pagingInfo(1,-1,a!sortInfo(field: "ProjectName", ascending: true))
)
).data

 

Picker Code

 

a!pickerFieldCustom(
label: "",
instructions: "Type to select and retrieve an asset details. You can select a maximum of 20 assets.",
placeholder: "Type to select and retrieve an asset details. You can select a maximum of 20 assets.",
maxSelections: 20,
suggestFunction: rule!NSA_SearchFilterMatch_ExprRule(
filter: _,
labels: local!assetLabels,
identifiers: local!assetIds
),
selectedLabels: a!forEach(
items: ri!pickedAsset,
expression: index(
local!assetLabels,
wherecontains(
fv!item,
tointeger(
local!assetIds
)
)
)
),
value: ri!pickedAsset,
saveInto: ri!pickedAsset
),

 

Code used to match the keyword with above search results:

  Discussion posts and replies are publicly visible

  • Hi,

    For your requirement, it is difficult to write an expression which search all the columns,

    try to use query entity (used OR operator) inside the expression : NSA_SearchFilterMatch_ExprRule.

    don't pull all the details at once, try pagination, it will help improve performance issue.

    I used the batch size of 10, you can update accordingly. 

    Update the expression : NSA_SearchFilterMatch_ExprRule

    - remove the columns : labels and identifiers

    a!queryEntity(
       entity: cons!NSARequest_Entity_Cons,
       query: a!query(
                selection: {
                    a!querySelection(
                    columns: {
                        a!queryColumn(
                            field: "ProjectName",
                            visible: true
                        ),
                        a!queryColumn(
                            field: "Id",
                            visible: true
                        )
                    }
                    )
                },
                logicalExpression: a!queryLogicalExpression(
                                      operator: "OR",
                                      filters: {
                                        a!queryFilter(
                                          field: "ProjectName",
                                          operator: "starts with",
                                          value: ri!filter
                                        ),
                                        a!queryFilter(
                                          field: "title", /* Update here */
                                          operator: "starts with",
                                          value: ri!filter
                                        )
                                        /*
                                         add columns as per need
                                        */
                                      }
                                    ),
    pagingInfo: a!pagingInfo(1,10,a!sortInfo(field: "ProjectName", ascending: true))
    )
    )

     

    Thanks'

    Vinay

  • A good way to accomplish this is to create a view with a field that contains a concatenated string of all the other fields in the CDT you are trying to retrieve. If you are not already using a view to query the data from this picker, you will need to create one and you will also need to create the related CDT and Query Entity. In the query entity, search the field containing the concatenated string using the "includes" operator. This will allow you to search for any of your fields in the CDT and the performance will be excellent.
  • 0
    Certified Senior Developer
    Either of the listed responses should address your issue, but I believe Jacob's solution will be the best performance-based solution. You can apply the entered filters to the query /filter for the VW with the single concatenated string and filter the actual CDT by the remaining ids from the VW query.