Help

Re: One to many relationships and webforms

1635 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Bob_Blightman
5 - Automation Enthusiast
5 - Automation Enthusiast

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?

11 Replies 11

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
5 - Automation Enthusiast
5 - Automation Enthusiast

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

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
5 - Automation Enthusiast
5 - Automation Enthusiast

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?

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

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

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
5 - Automation Enthusiast
5 - Automation Enthusiast

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

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
5 - Automation Enthusiast
5 - Automation Enthusiast

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

Bob_Blightman
5 - Automation Enthusiast
5 - Automation Enthusiast

kuovonne - appreciate your help
What I’m trying to do is create an online form, and when a record is generated in my table (input_table), I then want an automation to generate 1 record in my family table and potentially many linked records in my children table. I need these records to be linked, so I was thinking that I’d use the RECORD_ID() value of the record created in the input_table, and use this value as the Primary ID of the record created in my family table, and also in the linked record field in the all the records created in the children table. You’ve kindly told me that the record ID is available in future steps of the automation - how do I access this - will I need to write a script (which I haven’t done before)? Or is there a better way of doing this?
Thanks
Bob

Bob_Blightman
5 - Automation Enthusiast
5 - Automation Enthusiast

kuovonne - I think I see it. When I tell it how to set a field value when I create a record, one of the options is to use the “Airtable Record ID” - that should work?
Thanks
Bob