Trying to replicate Microsoft Access Lookup Wizard

💬 Queries and Resources
, ,
1
  • Goal:
  • I am trying to set up something that will emulate the functionality of Microsoft Access' lookup field. This lookup functionality, as you may be aware, allows the population of a field from a dependent table (that whole foreign key thing). The beauty of it is that you can set it up to make the key field invisible and, when entering the value for this dependent field, you can just start typing the corresponding readable value for that field. Access will show a drop down box of available values depending on what's been typed so far. All throughout this process the actual key field is invisible. Once you've narrowed it down to one value and tab out of that field, it is the key field value that gets written as the actual field value.

This basic functionality allows for the population of Access table without defining anything else other than those tables (and maybe some lookup queries). No forms or other artifacts are required.

  • Steps:

  • Right now, I've been able to connect to a self-hosted PostgreSQL database and have added Retool tables / grids that appear to connect to underlying tables in this self-hosted PostgreSQL database. I am having trouble even adding a row to a table that does not have any foreign keys. I assume, in order to add rows, I would need to define an insert query that is triggered when, well, I don't know. Do I need to create a button? Is there not something intrinsic to the Retool grid provides for 'add row' functionality (which then in turn triggers verious SQLs). I cannot get past this, let alone replicate MS Access' lookup wizard functionality. Please see subsequent messages for progress on this (although, if anyone wants to jump in and short circuit this, please do)

  • Details:

  • Screenshots:

  • App json export:

3

It appears I may have partially resolved the 'add row' issue (just to a base table with no foreign key fields). Seems that I can add a '+' icon to the grid, then do something like this for the query...

insert into state (label) values({{ t_state.newRows[0].label }})

...where the grid name is t_state and has a single field ('label'), with the primary key auto incrementing. However, I still need to figure out how to make the save / cancel dialog go away and the table to refresh with the new data.

4

Now the fun is, upon hitting enter after adding the new record data, get this save / cancel dialog that does not go away when save is clicked and the underlying table is not refreshed despite calling table_name.refresh upon successful execution of the insert clause attached to the add row toolbar button. Am not sure why this is so hard when Visual Basic solved this in the 90s.

5

Looks like I have to call .trigger() on the underlying query (that populates the table). This call is automatically populated if the 'control query' action is set upon a successful insert

6

For those of you, like me, who think a column type of "drop down" is still available (based on the numerous Google search results alluding to such), alas no. Instead use a format (which appears to be the new name for 'column type') called 'tag'.

7

don't forget to await that call:

await query1.trigger();

8

Thanks, I assume if you use 'control query' instead of 'run script', Retool puts an await in

9

ya, the only time you need to remember to use await is 'run script' in an app/module or a 'JS Code Block' in workflows

10

I assume all that successive JS calls also need this (to avoid callback hell)

11

only if they are in the same query... but to play it safe I'd suggest always using it:

// if 
variable1.vaule = "test"

// if your query1 is:
await variable1.setValue("foobar");

console.log(variable1.value)  //print 'test'
const ret = await query1.trigger();
console.log(variable1.value)  //print 'foobar'

console.log(variable1.value) //print 'test'
const ret = query1.trigger();
console.log(variable1.value)  //print 'test'
// promise is not resolved so variable value hasn't changed yet.
await ret;
console.log(variable1.value) // print 'foobar'

basically you just need to remember that .trigger() returns a promise, so if you need the updated value of something after calling .trigger() you'll need to await it. hopefully I didn't just confuse you, but if so feel free to let me know and i'll see if i can put together an example app for you

1 Like
12

Thanks, @bobthebear !

@lastfreedom, how's it going with your use case?