Creating a record in a related table on insertion

[rjairtable]

I'm trying to make a small CRM. Two of the main datatypes are Contact and Company. Clients work for Companies. When I get a new lead, I enter the Contact's information. When I do that, I'd also like to be able to create a record for their company if it isn't already in the Company table. So, bottom line, I should be able to either select a company, or type in the company's name and have it create a new record in the Company Table.

I've created a simple interface to create a new contact, but as I understand it, interfaces only insert into one table. Is there any way around this? I thought about trying to add an automation, but it seems like the interface templates for adding new rows to a table don't support what I'm trying to do.

[ScottWorld]

Yes, you can do that as long as you have a "linked record" field for Company.

Add that linked record field to your interface, and you can enable it so that your users can either add a new company or choose an existing company. 

Hope this helps! If you’d like to hire an expert Airtable consultant to help you with anything Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld

[rjairtable]

I did put in a linked record field, and it allows the selection of an existing company. However, there's nothing to let you put in a new company. Am I missing a setting?

[ScottWorld · Accepted solution]

Oh, I see... thanks for the screenshot for clarification!

You are talking about a form to create new client records. What I was talking about is only available if you're editing an EXISTING client record, not creating a new client record.

Unfortunately, that is one of the major limitations of Airtable. It is not possible to create new linked records from a form.

There are 3 workarounds for this limitation:

1. EASY: On the first form, give the user a link to ANOTHER FORM which will allow them to add new records to the linked table. Then, they can come back to the first form to continue their data entry.

2. HARDER & ADDS LOTS OF CLUTTER: On the first form, in the linked record field, give the user an option to choose from that is called "new record" or something like that. Then, conditionally show additional fields where they can type in the new information. Then, use an automation to add the new record into the appropriate table.

3. VERY EASY: Use Fillout's advanced forms for Airtable, which already has this functionality natively built into its forms. Fillout is 100% free and offers hundreds of advanced features that aren't natively available in Airtable's native forms, such as adding new linked records from a form and updating existing Airtable records from a form.

Hope this helps! If you’d like to hire an expert Airtable consultant to help you with anything Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld

[rjairtable]

Do you have any materials I could review about how to do option #2? Although I intend to use the CRM, I'm doing this in part to get a better knowledge of Airtable.

[ScottWorld]

I do not have any materials on that. But you can start by going to the companies table and creating a fake company called “NEW COMPANY”. Then, in your form, when somebody chooses that fake company, you would show additional fields for them to fill out about that new company. After the form is submitted, your automation would create the new company in the companies table.

- ScottWorld, Expert Airtable Consultant 

[DisraeliGears01]

I do think there's a workaround method for you here as it sounds like you (or team members) are entering the data rather than the contacts (or other outside folks).

You have an interface to enter the data in, but instead of a form page, use a record review page for contacts. In the interface settings, enable creating new records through a form, which you can then modify to your liking, but the same linked record issue will still exist. What you can do now is add a filter to your record review to only show records without an attached company. Then in the record review you can add new companies to those records from another form. I've attached a screenshot here (green circle is record addition, red circle is filter, blue circle is enabling the sub-form) of something else I'm working on but that is similar. 

[TheTimeSavingCo · Accepted solution]

re: So, bottom line, I should be able to either select a company, or type in the company's name and have it create a new record in the Company Table

Hmm, if the new Company record only needs to have a name and be linked to that new Contact, this is pretty straightforward 
1. Create a new text field called "New Company Name" or something
2. Create an automation that'll trigger when the form is submitted and have a conditional for when "New Company Name" isn't empty
3. Have a "Create record" step that'll create the new record in the "Companies" table and link it to the new Contact record

If they need to fill in more data, you could create a system for that like the ones mentioned above.  A clunky workaround that you can consider if you don't have many "Companies" fields for them to key in is you could just create those fields in your "Contacts" table and reference them in the automation I mentioned above.  Might get annoying to maintain if you've got a lot of fields for Companies that change up though