Custom picker for dummies

Hi

Fetch all the address from the table
 save in the local variable : local!allAddress

Create a suggest function :  rule!EEDM_ArrayPickerFilter
  - this funtion return all the search results by key
  
  =with(
        if(
		    len(ri!filter) > 3,
			  with(
			    {
				  local!matches: where(
					a!forEach(
					  items: ri!labels, 
					  expression: search( ri!filter, fv!item)
					)
				  ),
				  a!dataSubset(
					data: index( ri!labels, local!matches), 
					identifiers: index( ri!identifiers, local!matches)
				  )
				},
			),
            todatasubset({})			
		)    
	)
	
In the main rule
a!pickerFieldCustom(
  label: "Pick address",
  labelPosition: "ABOVE",
  instructions: "",
  helptooltip: "Start typing address text ID and address containing input string will be shown",
  placeholder: "---Type address text ID ---",
  maxselections: 1,
  suggestFunction: rule!EEDM_ArrayPickerFilter(
					  filter:_ , 
					  labels: index(local!allAddress,"ADDRESS_TEXT_ID",null), 
					  identifiers: index(local!allAddress,"ID",null)
					),
  selectedLabels: a!forEach(
					  items: ri!employee.addressId,
					  expression: index(index(local!allAddress,"ADDRESS_TEXT_ID",null),, wherecontains(fv!item, index(local!allAddress,"ID",null)))
					),
 
  value: ri!employee.addressId,
  saveInto: ri!employee.addressId,
  validations: {}
)

Reference : https://docs.appian.com/suite/help/20.1/recipe-configure-an-array-picker.html

OK, I've loaded all the addreses into local variable at the beginning of my form layout as you suggested:

a!localVariables(
  local!activeStep: 1,
  local!steps: {
    "Select employee",
    "Personal Data",
    "Address Data",
    "Review"
  with(
    local!supervisors: rule!EEDM_GetAllSupervisors(),
    local!employees: rule!EEDM_GetAllEmployees(),
    local!allAddress: rule!EEDM_GetAllAddresses(),
    ...

This is the rule for loading it:

a!queryEntity(
  entity: cons!EEDM_ADDRESS_DATA_STORE_ENTITY,
  query: a!query(
    pagingInfo: a!pagingInfo(
      startIndex: 1,
      batchSize: 50
    )
  ),
  fetchTotalCount: false
).data

Then I've created suggest function rule but can't test it. What am I doing wrong? Rule inputs are of wrong type or something like that (in my database table ID is number, ADDRESSTEXTID is text)?

Also when I define my custom picker with this code you've posted above, I get this error:

a!pickerFieldCustom(
  label: "Pick address",
  labelPosition: "ABOVE",
  instructions: "",
  helptooltip: "Start typing address text ID and address containing input string will be shown",
  placeholder: "---Type address text ID ---",
  maxselections: 1,
  suggestFunction: rule!EEDM_ArrayPickerFilter(
					  filter:_ , 
					  labels: index(local!allAddress,"ADDRESS_TEXT_ID",null), 
					  identifiers: index(local!allAddress,"ID",null)
					),
  selectedLabels: a!forEach(
					  items: ri!employee.addressId,
					  expression: index(index(local!allAddress,"ADDRESS_TEXT_ID",null),, wherecontains(fv!item, index(local!allAddress,"ID",null)))
					),
 
  value: ri!employee.addressId,
  saveInto: ri!employee.addressId,
  validations: {}
)

Hi,

please find the updated code

with(
  
    local!matches: where(
      a!forEach(
        items: ri!labels, 
        expression: search( ri!filter, fv!item)
      )
    ),
    
  if(
    len(ri!filter) >= 3,
        a!dataSubset(
          data: index( ri!labels, local!matches), 
          identifiers: index( ri!identifiers, local!matches)
        ),
        todatasubset({})
   )
  
)



a!pickerFieldCustom(
  label: "Pick address",
  labelPosition: "ABOVE",
  instructions: "",
  helptooltip: "Start typing address text ID and address containing input string will be shown",
  placeholder: "---Type address text ID ---",
  maxselections: 1,
  suggestFunction: rule!EEDM_ArrayPickerFilter(
					  filter:_ , 
					  labels: index(local!allAddress,"ADDRESS_TEXT_ID",null), 
					  identifiers: index(local!allAddress,"ID",null)
					),
  selectedLabels: a!forEach(
					  items: ri!employee.addressId,
					  expression: index(index(local!allAddress,"ADDRESS_TEXT_ID",null), wherecontains(tointeger(fv!item), tointeger(index(local!allAddress,"ID",null))))
					),
 
  value: ri!employee.addressId,
  saveInto: ri!employee.addressId,
  validations: {}
)

Thank you very much! Now I understand these custom picker stuff much better. I've modified this code a bit to add some more functionalities and it works like a charm.

Although, I have one more question.

Because I have to load whole address table it may become tricky from the performance viewpoint.

My table here is some demo table and has only few rows. What if I have to use the whole codebook table? In my country it would be around 53 000 streets or more than 2 million house numbers. 

Does this picker always require loading into local variables? Is this the best or only practice?

I know I can redesign this address issue to have multiple dropdowns for street, house number, city, post office etc. instead of one picker. But let's say that I must use some pretty big table for my picker.

Are there any best practices to manage performance issues?

My approach here would be to allow the search be run directly against the database and to be narrowed down to something that would reduce the result set as quickly as possible e.g. a post-code. So your picker could still display the entire address but the search is just using the input postcode to return what should be a very small subset of the overall data.If you ensure that the address is stored in its component parts then postcode would be in its own column, and could have a clustered index on it so that all of the adjacent addresses are retrieved in as few psychical fetches from the disk as possible.

My approach here would be to allow the search be run directly against the database and to be narrowed down to something that would reduce the result set as quickly as possible e.g. a post-code. So your picker could still display the entire address but the search is just using the input postcode to return what should be a very small subset of the overall data.If you ensure that the address is stored in its component parts then postcode would be in its own column, and could have a clustered index on it so that all of the adjacent addresses are retrieved in as few psychical fetches from the disk as possible.