One to many relationships and webforms

[Bob_Blightman]

I have 2 tables - Families and Individuals, and I want to create a one to many relationship between these.
So I’ve marked fields in the 2 tables as linked.
I then want to create a webform so that external users can create records which give details of both the family and individuals within the family. But when I share this form, and the user clicks on the + for individuals, it shows all the individuals in all the other families. I don’t want any individuals to show. I want the external user to be able to add multiple records in the individuals sub form.
Can this be done? Have I structured the relationship as many to many when it should be one to many? How do I define it as one to many?

[Alessio_Monino]

hi @Bob_Blightman,
I believe you made the right choice making the relationship one to many and not many to many as it is unlikely that an individual is part of multiple families (can happen if someone divorces and then has two wives/husbands but I believe that would be unlikely that you would have to track that often in your database → just making an hypothesis of course, might be wrong depending on your data/business).

I believe the correct way to set up your form is to set it up from the individuals table so that when the external user fills it in, he creates a new individual, he will then be able to select one of the existing families to allocate that individual in. You would of course still have an issue if the family does not yet exist (he will not find it in the dropdown). Usually, to solve this issue for clients, I would add an additional field to the form called “New Family” and make it a text field so that if the external user does not find the relevant family among the options, he can create a new one by filling in the field. The creation of a new family will then happen through a script keeping track of whether new records in the Individuals table have the “New Family” field not empty.

Hope this helps!

Website: alessiomonino.com
Calendly: Calendly - Alessio Monino
Email: alessio.monino@gmail.com

[Bob_Blightman]

Many thanks for your help but the family data will definitely not exist.
This is a site we’re putting together to help match hosts in the UK with refugees from Ukraine. We’re trying to capture all the details of the Ukrainian family and the individuals in one wedform

[kuovonne]

A native Airtable form cannot create records in multiple tables by itself.

It is possible to have one [input] table with conditional fields. For example, have a number field to state how many people there are. Then based on that number, show or hide the appropriate fields for each person. Then have an automation parse out the information from the [input] table into the proper records in the different tables.

Another possibility is to use a MiniExtensions form that can create line items.

[Bob_Blightman]

kuovonne - that’s really helpful. The documentation suggests that conditional fields only work on desktops but that may not be an issue.
Just one further question about how to ensure that the records I create in the two different tables are linked. I was thinking that in the input table I could have a field which was the Record ID, and then I could copy this into fields in both the tables where I am creating the records. How do I then force the records in the two tables to be linked on these fields rather than on the native ID which has been created in the “one” of the “one to many” tables?

[Bob_Blightman]

Sorry, I think this is obvious isn’t it? Just make sure that’s the first field in the family table

[kuovonne]

Bob_Blightman:

The documentation suggests that conditional fields only work on desktops but that may not be an issue.

Forms with conditional fields can be filled in on mobile devices. You just need a computer to create the form.

Bob_Blightman:

how to ensure that the records I create in the two different tables are linked.

If you have a script creating the records, the script takes care of it all.

If you do not have a script, there are different methods depending on whether or not the family name is unique, how many linked records there are, whether the input table is the same as the family table, and whether or not you need to minimize names automations or automation runs.

I recommend using a method based on the record ID of the family record, and not the family name, as you cannot be sure that the family name is unique.

Here is one option.

• One automation runs when the firm is submitted, and it creates the family record and fills in the record ID of the family record, either in a single line text field.
• other automations run when the field with the family record ID has a value and create the linked records using the family record ID. Due to the limit of 25 actions per automation and the limits of conditional automation actions, you may need to group the creation of multiple linked records.

[Bob_Blightman]

kuovonne
Many thanks for that
In the input table, I will be able to find the ID of the record that has been created there using RECORD_ID(). Can I populate the Family table (which will be just 1 record) telling it to use this ID in that table, and then creating the multiple records in the Individuals table, each with a field containing this matching ID.
So can you tell Airtable what ID to use, rather than letting it generate one itself? If I can’t do this, how do I find out the ID of the record that it’s just created in the Family table? I don’t think the limit of 25 actions per automation should be a problem
Thanks for your help again
Bob

[kuovonne]

When you create a record using an automation, the record ID of the created record is available in future steps of the same automation.

[Bob_Blightman]

Many thanks

I hope to be building this automation in the next few days and come back to you to get advice on how I access this