When browsing form, allow insert to companion tables

The form table is supported by query FormTableQuery which has information about an SEO project. I'd like to allow the user to select a table row and insert 1) Notes, 2) Follow-Up Activities and 3) Page types. I envision three text input fields and three buttons. For the Notes table, once the user enters text information and selects the corresponding button, the note will be inserted into a separate Notes table (keyed by id, version, variation). None of these fields is in the primary SEO report table, but the FormTableQuery comes from a view that links the tables together and therefore is displayed on the form. The other two tables (Follow-Up Activities and Page types) are supported by a separate table each. The user must choose from a pick list and, when submitted, the page id or message id will be inserted into the table keyed by id, version, variation. Questions: Can the text input box have a pick list? Can I select a record in a form and insert the text value to a separate table with the proper key information from the form table? If the user inserts information on the first pass and later wants to modify it, how can I update the information rather than inserting it? When setting up the table structure, I didn't include these fields because not every record will have this type of information. Would it be easier to include everything in one table and then just use update action? Is there a significant database size consideration? Can I use a pick list in the text input field for this?

Hi @adrooney I'm sending over some initial thoughts! It's a bit hard to visualize this, so if you have any extra context or screenshots that could be helpful :blush:

  1. Can the text input box have a pick list? I don't believe so. Would the select component be better? If not, what about adding validation to the text input?
  2. Can I select a record in a form and insert the text value to a separate table with the proper key information from the form table? This sounds doable! It may require some JS to filter for the right data. Some screenshots would be helpful to better understand the use case
  3. If the user inserts information on the first pass and later wants to modify it, how can I update the information rather than inserting it? You should be able to use an update query in our GUI (as opposed to insert)
  4. When setting up the table structure, I didn't include these fields because not every record will have this type of information. Would it be easier to include everything in one table and then just use update action? Is there a significant database size consideration? Can I use a pick list in the text input field for this? If it's not all in the table that should be fine as long as you're ok with using JS to transform your data a bit. I'm not sure if I understand the question for the database yet. Could you elaborate a bit on what you're thinking about in regards to db size?

\

Thanks Tess. Based on other functionality we chose to include, I'm approaching this from a different perspective. Your input will help me in future development. Have a great day!

1 Like

Regarding inserting text value to a separate table with the proper key info, I just wanna add that, yes, this is possible. You can capture the key info of the selected row in your main form table and use it when inserting into the companion tables for Notes, Follow-Up Activities, and Page types.
For updating records instead of inserting new ones, you could check for an existing record with the same key information when the user submits. If it exists, you update; otherwise, you insert a new record.
You mentioned your table is supported by FormTableQuery for SEO projects. If you're diving deep into SEO, you might also want to consider using tools like this web site analyzer. It's a comprehensive tool that can fetch a lot of SEO-related information, which might be beneficial in your project.

Hope that helps
Julia

1 Like