Bind picker's searchTerm.

[pavel-orlov]

Hi,

I just started using Budibase and run into some issues:

• Is it possible to bind searchTerm in a picker to custom SQL query so that we can search across thousands of records without loading them all.
• How to set pickers default value and label and not value only?
• Is it possible to set field value. E.g. using conditions.

[mjashanks]

Hey @pavel-orlov

Is it possible to bind searchTerm in a picker to custom SQL query so that we can search across thousands of records without loading them all.

I answered a similar question here: #2676 (comment) - it talks about ordering, but the same principle applies.

How to set pickers default value and label and not value only

Again - see link above. You will see that I set the default value of a picker. Provided that the default value is one of the allowed key-value pairs, this should work as you describe

Is it possible to set field value. E.g. using conditions.

It's possible to set the "Default Value" with conditions, but not the actual value. Set the default value by

1. Configure conditions
2. Update setting
   

[pavel-orlov]

Hi Michael,

Thank you.

In the example it binds picker's field value to a query. My issue is using autocomplete when there are too many records in a data source. Let's say I have 50000 records and I need to select one using option picker by searching it using autocomplete feature. But we need to pass searchTerm to the query.

In Option picker we can set Label column and Value column to different values using Data provider but when I set default value, it sets both columns to the same default value. So if I save an editing form without touching a picker, it provides option label instead of value.

[mjashanks]

Its is currently possible to

• add an options picker, whose values load from a data provider (which could be linked to a SQL table or API, for example)
• set the options picker to Autocomplete, allowing you to type to search it.

However, typing into the autocomplete does not search the database/API directly. All the items are loaded into the autocomplete, which are filtered in the browser. This might cause poor performance with 50,000 records - but you should try anyway!

Here's my setup:

Budibase.4.mp4

Regarding the options picker - I cannot reproduce this. My setup is

1. I have a form, with a "Countries" options picker
2. Options label:values are United Kingdom : UK and United States : USA
3. I Set the default value to UK <--- I.e. this refers to the value, not the label
4. Because of this bug: Cannot use an options picker on a text column (SQL or internal table) #2674, I have to name the options picker's field as "Countries_picker" and map it to "Countries" in the Save Row action
5. I then open my app, open the "Create new Contact". The picker defaults to "United Kingdom". Without changing it, I save, which correctly persists the value "UK" to the table.

[pavel-orlov]

I've tried loading all records into a browser, it works but as expected there is a performance issue. It would be great if we can pass search string to the backend to avoid loading too many records.

Regarding the issue with Option picker's default value. I tried using the same setup with Country : CountryCode and it worked when the value is a string. When I use integer values the picker defaults to country integer number. For example: United Kingdom : 23 and United States : 24.

[mjashanks]

I am not able to reproduce your issue with saving numbers - It's working ok for me, as you can see in my video below.

Also, I have put together something that might work for searching your dropdown values:

1. Add a "Country Search" text input - set the "Field" to countrySearch
2. Add a Data provider, linked to my countries table (or other external source)
3. Add a filter to the Data Provider - where Name starts with {{ Form.Fields.countrySearch }}
4. Add a condition to the data provider so that it hides if countrySearch is empty (thus does not load the full data set)

All in video below:

Drodowns.mp4

[pavel-orlov]

Hi Michael,

I recorded a video with my setup and demo. My data provider is MS SQL query

BB-picker.mp4

: