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.

Question

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:

Vinay Kumar Rai · Answer

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

Jacob Grafenstein · Answer

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.