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?
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!
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.
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?
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.
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
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?