One form view to update two related tables (one-to-many)

[Carlos_Seguin-L]

Hi everyone,
needs some help to work out a probably common challenge for noobs like me.

Base setup

• Two tables, Product and Tags.
• A product can have many tags.
• Tag is a field in Product.
• It’s set to allow linking multiple tags.

Challenges

1. Basic:
   Create a form view to add a product where user can add multiple tags. Both tables need to be updated upon submission, and it’s fields linked.

2. Advanced:
   Show in the form a multiselect showing existing tags and allow to create new ones, upon submission new ones will be added to Tags table linked to the corresponding Product.

Is it possible to create something like this?

Thank you all

[TheTimeSavingCo]

Hey Carlos, if I were you I would:

1. Create a linked field between the Product and Tags table
2. Create a form in the Product table and have the field from point 1 be in the form
   • This allows users to select from existing tags, and once the form is submitted a new record will be created in Product linked to the Tags records the user selected
3. Create a new long text field in the Product table called “New Tags” or something, and have that included in the form. In the form, tell users that they can key in comma separated tags into this field and they’ll be created and attached to this product
4. Have an automation that triggers when this New Tags field is not empty, and make its action to add the values of the New Tags field to the current values of the linked field

This should accomplish everything you’ve outlined above I think

[Carlos_Seguin-L]

wattt? man you’re good, thanks a lot :tada:

[Carlos_Seguin-L]

Actually struggling with the automation to:

When New Tags not empty

• Iterate the New Tags field tags
• create a new Tag record per each comma separated tag.

The automation seems to be a bit limited on what you can do, other than basic modifers (capitalize, trim…) I guess you can’t do everything within just one automation.

It probably needs a bit more of work, the only solution I can think of seems quite cumbersome:

• Create new formula fields, one for each new tag (removing the commas). Limit number of tags to 5 for instance
• Create automations for each formula field that create a new Tag record given formula fields (1 to 5) are not empty

Is there a short cut or more elegant way to solve this?

I’m on the free tier btw, maybe that’s the problem

[Russell_Findlay]

Your advanced needs to go down the prefilled / portal / user interface / advanced on2air forms route)

For tour basic case an idea of data structure would help

Is the relationship between tags and products a many to many relationship (ie product 1 and 2 could have Tag 1 and Product 1 could also have tag 2) or is it a case where a tag needs to have a specific product (called a master detail relationship) …

Are “tags” predefined (ie you have a list already) or are they created new for each product

Same question for products

Do you have a maximum number of tags per product or per submission in mind …

With the answer to these there may well be far simpler solutions to what you are looking to do

[Carlos_Seguin-L]

thanks @Russell_Findlay,

• It’s a many to many relationship.
• Tags are not predefined, are created by users when uploading products. They could be anything.
• Max number not sure yet, but it’ll be 3 or 5 max.

If not possible, I’ll just give up and try to live without them. Way too many subscriptions already…

[Russell_Findlay]

If you have budget then I think On2air forms may be where your best solution is

Otherwise it feels like you want to create a third table - a junction table (often thought of as a detail or line items) which is a combination of products and tags - let’s call this ProductTags

Then

1: put the form in products & create 3 text fields one for each tag

2: in the producttags table create 2 linked record fields - one for tags and one for products

3: then create an automation with three conditional actions as follows

The trigger form submission

Action 1 conditional on the tag 1 field being not empty to be

Create new record in recordtags with the product name in the product linked record field and the tag name in the tag linked record field. (This will then link to existing or create new tags as appropriate)

Then you have your products linked to your tags via your producttags table

Action 2 and 3 (and more) are replicas for the other fields

Then you can use lookup fields via the junction table to show the relationships you want …

Hope that helps …

[Carlos_Seguin-L]

It does, I’ll give it a try and let you know, thanks a lot.

[Moe]

Another way to achieve this is by using a 3rd party extension. Our Advanced Form for Airtable allows you to create new linked records on different tables.

miniExtensions for Airtable

Create and Update Linked Records in miniExtensions Form (Line Items)

Add new linked records -with all their details- without leaving the form page. Making one-to-many, parent/child, master/detail relationships. e.g. invoices.