Support for forms

[kgodey]

Mathesar should support creating and sharing forms. Forms should save data into existing tables when they are filled out.

---

If you'd like us to build this feature, please comment below with more details about how you plan to use this functionality.

[AJamesPhillips]

This feature would enable diagnostics EUA submissions to the FDA where the FDA could spin up a table with an associated form for diagnostic manufacturers to populate. Depending on the FDA's workflow they might want to option to allow the manufacturers to securely edit a certain version of their submission until the manufacturer chose to submit and lock that entry. Entries from other manufacturers would not be visible publicly or by other manufacturers until they were publicly accepted and published by the FDA, perhaps by moving the entry to another table or setting an flag on the entry / row.

[kgodey]

Thanks @AJamesPhillips! This is very helpful.

[MrChadMWood]

Would it be possible to allow some kind of dynamic functionality for "smart forms"?
Not sure how to best describe this except through an example, so hear me out...

Example Scenario:
Consider a database with tables: bread, banana, pudding, each table containing the columns id, name, texture, and color. As well as a composite key table banana_bread_pudding, containing FK columns to each of the aforementioned base tables as well as a description column.

Our resulting hypothetical database table banana_bread_pudding holds delicious combinations of records from the tables bread, banana, and pudding, as well as a VARCHAR description for each record.

Schema (credit to ChatGPT):

+---------------------+        +----------------------+        +--------------------+
|       bread         |        |       banana         |        |      pudding       |
+---------------------+        +----------------------+        +--------------------+
| PK: id              |        | PK: id               |        | PK: id             |
|     name            |        |     name             |        |     name           |
|     texture         |        |     texture          |        |     texture        |
|     color           |        |     color            |        |     color          |
+---------------------+        +----------------------+        +--------------------+
         |                              |                              |
         |                              |                              |
         |                              |                              |
         +------------------------------+------------------------------+
                                       |
                                       |
                                       |
+--------------------------------------|
|        banana_bread_pudding         |
+--------------------------------------+
| PK: id                               |
| FK: bread_id (references bread.id)   |
| FK: banana_id (references banana.id) |
| FK: pudding_id (references pudding.id)|
|     description                      |
+--------------------------------------+

Now, addressing a form to input data into the composite key table, banana_bread_pudding.

Form Elements:

1. Visible Fields (prefilled dropdown fields):

   • Bread Name
   • Bread Texture
   • Bread Color
   • Banana Name
   • Banana Texture
   • Banana Color
   • Pudding Name
   • Pudding Texture
   • Pudding Color

2. Visible Fields Cont'd (user text input)

   • Description

3. Hidden Fields:

   • bread_id
   • banana_id
   • pudding_id

Functionality:

1. Mathesar queries the relevant database columns for all values necessary to prefill dropdown fields for the user to pick from.
2. Users select options for visible dropdown fields, (example: Bread Name, Bread Texture, etc.).
3. Mathesar frontend queries the database for matches in corresponding tables (example: bread)
4. If a match is found, the corresponding record ID is stored in the relevant hidden field (example: bread_id).
5. If no match is found, users can create a new record on-the-fly. rather than needing to go to the necessary table, add a record, then return to the form.
6. Users input text for visible input fields, (example: Description).
7. Once all hidden fields are populated, the IDs (and Description) are used to create a new record in the banana_bread_pudding table.

Additional Considerations:

• Fields may not always come from the same table; normalization might involve querying multiple tables.
• For instance, if texture and color are normalized, dropdown-selection fields fetch values from respective tables.

Benefits:

• Streamlined data input for non-technical users.
• On-the-fly record creation for unmatched entries.
• Flexible querying for normalized data structures.
• Abstracts away data model complexity.

[seancolsen]

Thanks for this example @MrChadMWood!

[MrChadMWood]

@seancolsen Sorry for all the edits. I'm slowly realizing over time how I could have better explained this. Hopefully I wasn't confusing!

[kgodey]

No worries @MrChadMWood, feel free to edit anytime. We'll look at the latest version when we're implementing forms.

[jeromeperez]

Hi
If we could create and use different forms to fill the database that would be very interesting because it would avoid having to develop an entire database management interface.
provided that it can be done in a simple way.
For example by allowing you to click/drag elements that you could link to the different fields and tables in our database.

[seancolsen]

Thanks for the feedback, @jeromeperez