How do I create an email for details to be checked and corrected

Rodney_Beadon · ‎Jun 01, 2022

Good Day

I am very new to Airtable and loving it.

I am creating a data base from a csv file. Some of the information is missing, it’s a customer list with lots of personal information.

I would like to email it to the customer and get them to check the information and update it or correct it.

Please can you explain how I go about this?

Many thanks

Vivid-Squid · ‎Jun 02, 2022

I can try to help.

Here I have a table with a person that is missing a detail, in this case Age

I have added a few extra fields:

a formula field with the formula: record_ID() this gives me the unique id of that row
a formula field that creates a form URL (using the Prefilled Forms app in the marketplace) I created this formula

"https://airtable.com/shrsooN4LstLlseRy?" & 
"prefill_First%20Name=" & ENCODE_URL_COMPONENT({First Name} & "")  & "&" & 
"prefill_Last%20Name=" & ENCODE_URL_COMPONENT({Last Name} & "")  & "&" & 
"prefill_Age=" & ENCODE_URL_COMPONENT({Age} & "")  & "&" & 
"prefill_recordIDMatch=" & ENCODE_URL_COMPONENT({recordID} & "")  & "&" &"hide_recordIDMatch=true"

A text field called recordIDMatch, this stays empty for now.

In the formula I am prefilling the form with the records I have and if I dont have it, it will just be blank for the person to add to the form. Importantly I am prefilling the recordIDMatch with the row’s ID.

Now I have a URL to send to each person (note: each row’s URL is unique to that person, so dont send it to the wrong person.

When the URL is opened in a browser, it will open the form, prefill with info I have and leave missing info blank. It will also add the recordID in the background, The user will not see this happen.

Now create your automation to update the record.

Trigger: when row is created
Action: Find Records where recordID is recordIDMatch

Action: Update Records we want to update the record found in step 1 and all other fields too.

This will let the person filling out the form correct any mistakes in the existing data too.

Rodney_Beadon · ‎Jun 03, 2022

Thank you I will go through this slowly so I understand. I will give you feed back later

Rodney_Beadon · ‎Jun 05, 2022

Hi

I have started working through this. on the Form URL I have put in the following formula. I actually copied and pasted yours as I am cluless still on writting and understanding the formulas.

“Help24Seven Registration” &

“prefill_Surname%20Name=” & ENCODE_URL_COMPONENT({Surname} & “”) & “&” &

“prefill_DOB=” & ENCODE_URL_COMPONENT({DOB} & “”) & “&” &

“prefill_recordIDMatch=” & ENCODE_URL_COMPONENT({Record ID} & “”) & “&” &“hide_recordIDMatch=true”

I changed the fields to “Surname” and “DOB” I included the field Record ID as per the above instructions

I changed the form URL to the form I had earlier created. I suspect this is why i am having a problem.

I dont want that form sent from this link but the person getting the link to be able to click on the URL and be taken to their row for editing.

Not sure if I am on the right track or which URL to inclued

Many thanks

Rodney

Vivid-Squid · ‎Jun 06, 2022

Hi @Rodney_Beadon
I think you are on the right track. Let me know if you are getting hung up on anything.

Rodney_Beadon · ‎Jun 07, 2022

Hi,

This is starting to make sense to me.

If the Base is already populated and some of the information is missing or just needs to be checked and up dated then, I would need to share the Base URL giving access to the relevant Row only. Is this correct so far.

If so how do I send that unique row to someone to check? I have given the row an ID but not sure how to share the row.

I have added this as a formula in a field called “Form URL” However when I try to save it it returns Invalid Formula.

CONCATENATE(https://airtable.com/app6DqS2bqGtz03sG/tblr4Zd7ZVWvs3DwV/viwKCe5b58hIV3glc/”,RECORD_ID())

Having less than limited experiance with formulas I am not sure what I am doing wrong.

I have been trying to follow the How to obtain record URL found at

https://support.airtable.com/hc/en-us/articles/360058897573-How-to-obtain-a-record-URL

Many thanks

Rod

ScottWorld · ‎Jun 07, 2022

Hi @Rodney_Beadon,

For help with your form’s prefill URL, you may want to check out @kuovonne’s excellent Prefilled Forms app. I don’t think it supports hidden fields yet, but it should be a great reference tool for you:

Rodney_Beadon · ‎Jun 08, 2022

Thank you,

I am pulling my hair out. Mostly because I dont really understand the way to do the formulas

ScottWorld · ‎Jun 08, 2022

If you want to use your formula above, you are missing the opening quotation mark around the URL.

Rodney_Beadon · ‎Jun 08, 2022

@ScottWorld

CONCATENATE”(https://airtable.com/app6DqS2bqGtz03sG/tblr4Zd7ZVWvs3DwV/viwKCe5b58hIV3glc/”,RECORD_ID())

I wanted to see if I could get this to work I, if I understood correctly put the opening quotation marks around the URL.

I still am getting the error message “Sorry, there was a problem saving this field. Invalid formula. Please check your formula text”

I thought my URL may be wrong so on the row I am wanting to create the unique URL for I, right clicked on the row, copy URL and was given this URL “Sign in - Airtable”

I have been trying to follow the example https://support.airtable.com/hc/en-us/articles/360058897573-How-to-obtain-a-record-URL

Also from the help from @Vivid-Squid I assumed that i needed to remove th “?blocks=hide” part of the URL, the reason when looking at examples I did not see it in any.

I then added the" /”,RECORD_ID())" but it is still not working.

In all honesty this is just guess work on my behalf and trial and error with no working result.

Possibly it would be better if I did a course on understanding the way a formula is constructed and laid out I would not be such a nusiance on the forum.

Any advice to learning and understanding the formulas will be greatly appreciated and possibly and explanation on why this formula is not working

Many thanks

Rodney

Vivid-Squid · ‎Jun 08, 2022

Hi @Rodney_Beadon ,
It would be a good idea to read up on Airtable formulas, you can find documentation here: https://support.airtable.com/hc/en-us/articles/203255215-Formula-Field-Reference

Have you installed the Prefilled Forms app? It will write the formula for you.