Dynamically load available values from database for autocomplete field
I have a table in an external database in which I have a large number of records. In one of my tasks the user has to select a record from this table and I would like to present it in an autocomplete field. However, I don't want to load all record entries in a variable, but rather directly query the database using something like the query below
select id, name from patient where lower(name) like '%'|| lower(:query) || '%'
I searched around the forums and documentation but couldn't find anything about it. I found some hints on how to implement an autocomplete field, but it always assumes you have a preloaded variable with the available values, which is not the case here.
One strategy would be to use a server side database connector to populate a variable which then you could reference in a form. Database connector information can be found here: