We are trying to reproduce in Airtable what is essentially an abstract application system. It’s better than what we currently have but there is a process flow we are having issues with.
the flow we expect/want:
User completes Airtable form #1 - a standard abstract submission form. On that form they have the option of adding 3 attachments; a profile picture, a short ePoster abstract, and a 5-minute SciTalk video. Crucially this is optional at this time. They do not have to submit this content but can.
We send them a thank you email complete with the next steps. This includes a link to Airtable form #2 a simple form to submit their profile picture, ePoster, and SciTalk. When they submit the form it will update to ADD these new documents to the original form submission rather than replace it.
Once the user submits the 2nd form they receive a second thankyou email that tells them if they want to submit again they can go back to the 2nd form, resubmit, and it will add those new files again.
How I set it up:
User completes form 1. This flows into Airtable base 1.
automation 1: WHEN a record is created in Airtable base 1, IF field [profile image] is EMPTY, replace with table 3 > resource > attachment URL (missing avatar image). This ensures that no empty field is empty going into future steps.
automation 2: repeat automation one targeting [eposter] field to add Avatar image to field.
automation 3: repeat automation one targeting [scitalk] field to add Avatar image to field.
automation 4: send thank you email with Airtable link of form 2 included.
automation 5: When a record is put in Airtable Base 2, LOOK UP the corresponding email in base 1. If there is an email matching in base one, find the [profile image], [eposter] OR [scitalk] fields and APPEND all attachments to each field. (we do not want it to delete the primary contributions.)
Automation 6: send thank you email with Airtable link to form 2 included.
Where I am experiencing issues with Airtable:
It doesn’t take long to run into a problem. on the 1st, 2nd, and 3rd, automation I cannot get Airtable to respect the “is empty” qualifier that pinpoints only the empty cells. If the segment is empty it does add the profile image, but also if the field has something in it. It also adds the missing avatar image, deleting the initial contribution despite the filter. I need the field to ONLY update while empty.
Once a record has the missing avatar image in it it can no longer return empty values in a field. The reason this is necessary is because of a second problem. If a field in base 1 is empty when the automation for form 2 (step 6) occurs, it will return an error of an empty value or worse, lose the record ID. the workaround was to add the missing avatar but if I can make this work without the avatar that would be optimal because I’m eating up automation right now.
the next problem I run into is with the automation of base 2 updating. It will find corresponding email addresses and it will update multiple times if you duplicate the value. This suggests you can submit form 2 several times and it will always track to just the one record on base 1. This seems to work as intended. When you test in the live environment though, even though the base performs as needed on the back end. It fails, returning the record URL could not be found. (it finds 2 records and updates them both on the back end, but if submitted from the front end form it does not do this.)
The last spot I am having trouble is with the “append” rather than “replace” part. In the Airtable automation, I need to put a list of URLs of attachments in form 1, then a list of URLs of attachments in form 2. I don’t know the separation syntax that does this. As it stands I have tried multiple iterations. A field variable for table 1 and a variable for 2 after it. separated them with commas, then added space, I tried enters and semicolons. I wrapped the links in quotes and parentheses. I cannot get these two field variables to live with one another in the same field and update properly.
the best I can do to resolve 4 is build the system so that form 2 always replaces form 1’s fields instead of appending. That’s how it is set up right now.
You’ve got quite a lot of stuff in here, so I’ll just tackle them one at a time rather than dive deep all at once. :slightly_smiling_face:
In your description, you say that the automation triggers when a record is created. If that’s the trigger, how are you testing for an empty profile image field? A screenshot of this automation would be the preferred way to diagnose this.
