Error - rule-picker

For UX and system performance typically you will want to reduce your picker's returned values to say 10-15 via pagingInfo in the queryEntity.  As the user types in the picker field, the search function reduces results based on their input and the top 10-15 are shown.  If the picker field is attempting to return thousands of results, the user would have to scroll for pages and pages to locate what they need.  And as Stewart notes, queryEntity is limited to the amount of data it can return. 

Thanks for the reply Chris.

But my problem is that they want to select by means of a picker the possible values ​​of the customer table, which can reach 12000 lines. 

This is the code I have in the rule that loads the picker:

a!queryEntity(
entity: cons!NAME_TABLE,
query: a!query(
logicalExpression: a!queryLogicalExpression(
operator: "AND",
filters: {
a!queryFilter(
field: "EstadoClient",
operator: "=",
value: "A"
)
},
ignoreFiltersWithEmptyValues: true
),
pagingInfo: a!pagingInfo(
startIndex: 1,
batchSize: 11400,
sort:a!sortInfo(
field:"IdClient",
ascending: true
)
)
),
fetchTotalCount: false
).data

But the table has more than 12000 lines.

Thx

You'll need to find a way to present the list in smaller chunks. You have options:

• as Chris has said, you can present the list in smaller chunks by order - say, the first  10, and then the next 10 and so forth. This is not helpful if the list is very large and the value a person wants is at the end of the list (although you could optionally allow the User to pick a Sort Order value to reverse the list)
• you can chunk up by filtering - in a custom picker allow the User to type in a few characters and then filter the list by the value entered (this does pre-suppose that the User knows what they're looking for and can type in something meaningful)
• you can try and organise your data into a hierarchy by some logical grouping (which would have to be held in a separate table), and use a drop-down as a filter to populate another drop-down. This will work if you can guarantee that your second drop-down will always contain less than the query limits described above 

thanks for your answer Stewart.
We do not have a hierarchy, they are clients and therefore individual records.
At first we had a dropdown but it was not very optimal as you say since it was an infinite list, so it was switched to a picker so that the user could enter some characters and recommend it, but it seems that this option does not work eith

"Does not work either" - are you still encountering the same error? ("Expression evaluation error at function a! queryEntity: An error occurred while retrieving the data.")

Looking at your code, if you want to filter by, say, the Client's name, then your filter needs to be something like:

filters: {
a!queryFilter(
field: "EstadoClient",
operator: "starts with",
value: ri!startsWith

If you call this rule ONLY when the User has added, say, 3 or more characters then the returned list will be much smaller (and you can safely set the 'batchSize' to -1 when you know that the list will not break the query limits)

...and now I've started to have other thoughts which may take your design in a totally different direction....in what context are you wanting to pick from this list? What is the User going to do (or is doing) at the point a Client is picked?

I think I have two problems:
1 - the rule to load the picker does not return the whole table to me. The table has 12000 lines and returns only 11400, but I start to think that it is a data problem, I don't know ... the error is the following "Expression evaluation error in function a! QueryEntity: an error occurred while retrieving the data ".
2 - Even forcing it to return less amount of data so that it does not fail, when I look for the selector it does not offer me certain information that I know exists in the database and that the rule returns.

But I come to the conclusion that I am not creating the picker in an optimal way:

local!arrayClient: rule!STD_GetListCliente(),
a!pickerFieldCustom(
label: "Client",
labelPosition: "ABOVE",
maxSelections: 1,
suggestFunction: rule!STD_ucArrayPickerFilter(
filter: _,
labels: a!forEach(local!availableClients,
concat(fv!item.IdClienteSTD, " - ", fv!item.NameClient)),
identifiers: local!availableClients.IdClient
),
selectedLabels: a!forEach(
items: local!clientId,
expression:concat(
local!availableClients.IdClienteSTD[
wherecontains(
tointeger(
fv!item
),
tointeger(
local!availableClients.IdClient
)
)], " - ",
local!availableClients.NameClient[
wherecontains(
tointeger(
fv!item
),
tointeger(
local!availableClients.IdClient
)
)]
)
),
value: local!clientId,
saveInto: {
local!clientId,
a!save(
target: ri!securityTransfer.portfolio.IdClient,
value: rule!SAN_CLS_GetClientByRealID(
local!clientId
)
),
a!save(
target: local!selectedPortfolio,
value: null
)
},
validations: {}
)

Thx

In the end I was able to find a pattern to get fewer records.
Thanks for your time

the best way is to call the query entity inside the suggestFunction (STD_ucArrayPickerFilter), parameter *filter* get the entered value

also you can customize the suggest function if the filter value have more than 3 characters then call the Query Entity by paging into 100

/* STD_ucArrayPickerFilter */

a!localVariables(
  if(
  len(ri!filter) > 3,
  a!queryEntity(
entity: cons!NAME_TABLE,
query: a!query(
logicalExpression: a!queryLogicalExpression(
operator: "AND",
filters: {
a!queryFilter(
field: "EstadoClient",
operator: "starts with",
value: ri!filter
)
},
ignoreFiltersWithEmptyValues: true
),
pagingInfo: a!pagingInfo(
startIndex: 1,
batchSize: 100,
sort:a!sortInfo(
field:"IdClient",
ascending: true
)
)
),
fetchTotalCount: false
),
todatasubset({})
  
)

Thank you very much

Thank you very much