In order to check out formulas and/or to modify make a copy of this document:
Use the contact form to see the automation in action, the automations only work on form submits. In your own copy, go to the contact table options, go to published forms, fix the settings as per the screenprint at the bottom of this page and publish the form by creating a link (you have to do that once because this is a new doc), preview the form and save the URL for future use.
When a form with a new ‘relation type’ is submitted, it can take a little while (usually within one minute) before you see the new relation type in the relation type field (and in the ‘relations’ lookup table). On your next use of the form (after the automation has triggered), the new relation type is available in the lookup field (relation type) (a form refresh is required to see new relation types).
The contacts table has two hidden columns, one is used to store new lookup items, the other one is needed for advanced error checking when the form is submitted (see below - this is my choice and optional, it is not required for the automation to work).
The automation leaves the hidden columns unchanged, so you can check (later) who added a new relation type.
The automation is a bit more complicated than (minimal) necessary, because I want to ignore case and accents when checking whether new relation types already exist (in which case it is not added to the relations table, but the form will be processed to fill the ‘relation type’ field.
An automation is executed if the submitted relation type is blank, and the new relation type is not blank. I want the user to fill one of the fields (but not both), but Coda does not allow for that kind of ‘error’ checking. I also want ‘submitted by’ to be a required field, but only if the ‘new relation’ field is filled.
I have added the checkbox as the last field, which is a required field, to check on several conditions before the form can be submitted. Obviously, this is only for showing some advanced custom error checking, you can set this up whichever way suits your needs.