How do I build a form that pulls options from multiple "catalog" sql tables and creates a new record in another sql table?

[raven-the-grouch]

Hi all,

I got started with Budibase a couple of days ago, since I was looking for an easy-to-use low-code application dev platform that also allows self-hosting (zero-knowledge encryption is important for my use cases).

I'm stumped at creating a simple entry form that is supposed to use multiple option pickers in drill-down fashion (each pulling from its own sql table) to create a new record in another table. I'd appreciate your support, since I can't seem to wrap me head around this.

Goal:
I want to build a job entry form with three related option pickers that draw from different sql tables, each. But I can't pull this off. I only know how to build a form that uses one sql table.

Context:
I run Budibase in self-hosted mode. I'm using a MySQL database. I'm building a blank application (as opposed to using templates or auto-created screens). The sql tables have been imported and the relationships have been set, accordingly. I can also run queries ok. I'd really appreciate your help, since I haven't been able to get this to work.

Form structure:

• "name" text field
• "field" option picker (dropdown menu)
• "category" option picker (dropdown menu)
• "subcategory" option picker (dropdown menu)
• "register" submit button

Desired behavior:

1. The "field", "category" and "subcategory" option pickers are related to each other in drill-down fashion: after selecting a "field" option, the "category" option picker is populated with corresponding options. Each of these option pickers draws from their own respective sql table (e. g. catalogField, catalogCategory, catalogSubcategory). catalogField.id is a FKEY in catalogCategory, catalogCategory.id is a FKEY in catalogSubcategory.

2. In the same vein, after picking a "category" option, the "subcategory" option picker is populated.

3. Before picking an initial "field" option, the "category" option picker is empty. Before picking a "category" option, the "subcategory" option picker is empty.

4. Once the "name" text field has been specified with a string and the three options have been selected, I want to register a new record. This record would be added in a different sql table: jobEntries.

** Impediments encountered: **
On the "Design" screen, I can't map the option pickers to their respective sql tables via the "Options source" component setting. Neither using the Schema option, nor with the Data Provider option.