How to automate collecting client testimonials, edit them and have clients approve them?

[0800-grizzly]

Hi,

We need to collect longer testimonials (round one A4) from hundreds of clients during a year, process them (re-write and polish before publishing) and have the clients approve the edits.

Ideally I would just create fields for this in Airtable (draft -> approve button -> final -> published), but to my understanding Airtable doesn't allow free user to edit anything sent out form the base. And forms are made to be one-to-many, not enabling a form per client to be sent out, non?

We use Google Sheets so I somehow vaguely think this should be doable with some creative Sheets integrations, but I can't envision it concretely.

Anybody out there who could see this clearly, what would be the elegant and robust way to do it?

Björn

PS. We use Zapier also, so that is a viable option. For cost and maintenance reason I'd prefer not to use other third party systems.

[TheTimeSavingCo · Accepted solution]

Hmm, assuming we did this with an Airtable form, I would try:
1. Create a field called "Client Submission" that they fill out during the initial form submission
2. Create a field called "Updated Submission" where the refinements are made
3. Have a new table that's linked to these testimonial records and create a form view.  The fields in this table are:
  - A single select field with options "Approved" or "Rejected"
  - A linked field to the testimonials table
4. Have an automation that would send them an email with that updated submission value and a prefilled form link from step 3 that links to the testimonial record in question.  The linked field would be hidden
5. Have a lookup field in the testimonials table that shows the select field value

The workflow would thus be:
1. Client submits testimonial
2. Testimonial is updated internally, trigger an automation to send the updated testimonial to the client with the prefilled form link
3. Client receives email and opens the prefilled form link that's automatically linked to the testimonial record
4. Client selects "Approve"
5. In the testimonials table, this record's lookup field would display "Approve"

---
If you used Fillout.com (https://www.fillout.com/) it'd be a lot simpler I reckon, but I understand you'd like to avoid using third party systems.  You could just sent them a form that's linked to the testimonal record that shows the updated submission and I think you should be able to make that read-only.  They'd then be able to approve or reject it from that form.  Overall a smoother process for the client and you don't need an extra table to handle stuff

[0800-grizzly]

Thank you again @TheTimeSavingCo. The AT concept seemed so promising, that I decided to test it. As far as I can assess so far, the concept seems to be working.

But I can't figure out how to connect the form to a certain user. This actually comes already with the initial testimonial, because I thought to send out the invitation to submit that one with a form.

Building the pre-filling URL is difficult enough in itself, but what would be the logic to connect the form submit data to the recipient of the mail? What field should be used, and how pre-fill it?

Rgds,

Björn

[ScottWorld]

@0800-grizzly 

Have you tried using Fillout's advanced forms for Airtable?

Fillout offers hundreds (possibly thousands) of features that Airtable's forms don't offer, including the ability to update pre-existing Airtable records with a form.

Fillout gives you the formula that you copy & paste into your Airtable base, and then you would automatically have the unique link for each person's record that you could email to them.

Hope this helps!

— ScottWorld, Expert Airtable Consultant

[TheTimeSavingCo]

Hm I reckon I'd add a single line text field called "Email" and prefill it with the recipients email?  The "Testimonials" table should have the client's email I'm assuming, and so I'd prefill it with that

[0800-grizzly]

Thank you @ScottWorld. Based on your and @TheTimeSavingCo mutual recommendation of Fillout, I took it for a test drive, and it was indeed easy to get the basics up and running.

Cheers,

Björn

[0800-grizzly]

@TheTimeSavingCo, based on your @ScottWorld mutual recommendation, I took Fillout and tested, and got the basics working easily.

From there I actually learnt how to get connection to the record that should be edited. In the Airtable formula for the URL, you need to append RECORD_ID(), e.g. your.domain.com/form.html?RECORD_ID(). Fillout uses it to channel the update back to that record, then. Or so it looks so far with little testing... ;).

Rgds,

Björn