Connecting new data on linked entries

[artichokejoe]

I want to create a form that users can fill out to enter an awards. 

When they fill out the form, if their company or contact details already exist on the 'companies' or 'contacts' tables, I want them to be able to select this when they search. 

If their company or contact details do not exist, I want them to be able to enter their details so their company and contact details are entered on to the 'companies' and 'contacts' tables. 

This I can do. However, on the 'contacts' tables, all contact names are linked to a company on the 'companies' table, and vice versa. 

On the '2024 entries' table, if you enter a new 'contact name' this gets added to the 'contacts table'. If you enter a new 'company' this gets added to the 'companies' table. However, the contact name is then not associated with the company name on the 'companies' table, and the company is not associated with the contact name on the 'contacts' table. 

Is there a way to have it so when using the form to enter data on the '2024 entries' table, and entering new data into the contact name and company fields, the contact name and company automatically become linked on the 'contacts' and 'companies' tables?

Hope someone can help.

[Alyssa_Buchthal]

You'd probably benefit from an automation that, when a new form is submitted, checks if the associated contact and company are linked, and if not, links them. You won't be able to do it from directly in the form.

[artichokejoe]

Thanks, Alyssa. Appreciate it. If you or anyone else has an example of how to set up an automation like that I'd be grateful. 

[Alyssa_Buchthal]

Trigger would be "when a new form submitted".

Then do a find record step, using the linked contact record id from the form submission and searching in the contacts table.

Then a conditional logic, if the contact is not linked to the company.

Then an update record step, linking it to the company. 

There'll be some additional questions to ask yourself in here, i.e. what if the contact is already linked to a different company? Can they be linked to two, or just one, and how would you enforce this in form submissions? But at a base level, that should accomplish it for you.

[artichokejoe]

Thank you. I'll have a play and see if I can get it to work.