I am brand new to Retool, SQL, etc (no other coding experience). I've taken some basics tutorials on SQL queries. Trying to get some simple tables and forms going, but really stuck on a foreign key issue, which is exclusively a result of my inexperience.
These are My Tables and columns
- Parts
- id
- part_name,
- part_number, etc
- fkey_machine = foreign key column referencing machine_name column of Machines - a part can be assigned to multiple machines
- Machines
- id
- machine_name
I've got a table component (parts_table) on my page for viewing parts, which includes the fkey_machine column, all of which is displaying correctly. I have a form component with all the relevant fields for parts in the parts_table, including a multi-select (machines_multiselect) for the fkey_machine column. I have the dropdown for the multi-select populating from the Machines table with my fetch_machines query.
The issues I'm having are:
-
I can't get machines_multiselect to display the current fkey_machine value for the selected row in the table. All other fields show in the form upon row selection.
- Data source = fetch_machines,
- value = item.id
- label = item.machine_name
- Default value = parts_table.selectedRow.machines
-
I can't get machines data from the form to update in the table. All other fields submit and update correctly. This is my update_parts query, with an event handler to refresh the parts table.
SET
part_name = {{ part_name_field.value }},
part_number = {{ part_number_field.value }},
qt_in_stock = {{ qt_stock_field.value }},
markup = {{ markup_field.value }},
min_stock = {{ min_stock_field.value }},
retail_price = {{ retail_price_field.value }},
fkey_machines = (
SELECT
machine_name
FROM
machines
WHERE
machine_name =ANY ({{ machines_multiselect.value }})
)
WHERE
id = {{ parts_table.selectedRow.id }};`
Any help would be appreciated. I'm banging my head against the wall here.
