Implementing a CRUD Interface with Name References Instead of IDs

[tranquil hornet]

Hello!

I'm currently working on a CRUD interface for a normalized PostgreSQL database and seeking some guidance.

Let's consider the following sample tables:

• product table with columns: SKU, supplier_id, material_id
• supplier table with columns: supplier_id, supplier_name
• material table with columns: material_id, material_name

For the CRUD interface of the product table in my Appsmith application, I want to display supplier_name and material_name to the users instead of showing supplier_id and material_id. This is to make the interface more user-friendly and intuitive.

I'm aware that this will involve JOIN operations to fetch the names for display. However, when it comes to adding or updating entries in the product table, users will be selecting supplier_name and material_name from dropdowns, and I'll need to convert these back to their respective IDs to maintain the integrity of the database relations.

Could anyone share insights or examples on how to best handle this scenario in Appsmith? Specifically, I'm looking for advice on:

1. Efficiently fetching and displaying names instead of IDs in the table widget.
2. Implementing logic for converting names to IDs during insert/update operations, ensuring a seamless user experience.

Any guidance, tips, or resources would be greatly appreciated.

Thank you in advance for your help!

[smoky ocean]

Hello! I'm AppySmith, your support bot with a flair for Appsmith. For your CRUD
interface in Appsmith with JOIN operations and converting names back to IDs,
here's a concise guide: 1. Fetching and displaying names: Use the Query editor
to write an SQL JOIN statement that fetches the necessary data. For example:

supplier s ON p.supplier_id = s.supplier_id JOIN material m ON p.material_id =
m.material_id; ``` Bind this query to your Table widget's data. 2. Dropdown for
names and IDs: For your dropdowns, create queries to fetch `supplier_name` and
`material_name` alongside their IDs. Configure the dropdown options to display
names but return IDs. 3. Converting names to IDs: When you insert/update, you
will already have the right ID from the dropdown value selection, so just use it
in your insert/update SQL statement. 4. Seamless user experience: For updates,
pre-populate the dropdowns based on the selected row's ID (using default value
property). For inserts, ensure the dropdowns are reset after submission for a
fresh entry. Test thoroughly to ensure names are correctly mapped to their IDs
when users perform insertions or updates. This maintains the integrity of your
database relationships. If there's anything more specific you need help with on
this scenario, please let me know! If you don't find my response helpful, you
can request to speak to a human agent