Airtable Forms

[Alexis_Louie]

Hello,

My organization requires our companies to submit quarterly reports via an Airtable form. Because companies cannot see the previous quarterly report form submitted, sometimes, they will duplicate answers from the previous quarter. I'm wondering if there is a way that companies can see the last quarterly report they provided when they are completing the current quarterly report.

Thanks!

[TheTimeSavingCo]

If you'd like the data shown within the form itself you could use the prefilled form functionality? 

[Micki_O_Neil]

You could also share read-only views with them of just their responses. (You would have to make individual views for every company so that may not be practical.)

[Alexis_Louie]

Thank you! Do you know if there's a way to show an entire record within a form similar to how you can link a record within a form, but only show for a specific company for the most recent form previously submitted?

[TheTimeSavingCo · Accepted solution]

Yeap, you'd just need to consolidate the submitted form data somehow, and you can either use an automation or a rollup for that.

The automation method involves making an automation that triggers on form submit, looks for the correct record in your consolidated data table, and updates that

The other method involves using a rollup with `MAX(values)` that'll grab the latest submitted form.  Both methods are equally valid but I favour the rollup version, and here's a base that does that.  I mostly like it because, in the event I want to add more fields to the form etc, I don't need to mess with the automations anymore, I just make more lookups.

It has two tables, one for form submissions ("Form Submissions") and one for consolidated data ("People"), and assumes that you have a unique ID to identify the party submitting it, in my case, the email. 

On form submission, it links the form to the correct record in "People" via the email.  This is done via an automation that updates the new form submission by pasting the email into the linked field, and this works for me as I made the email the primary field of the "People" table.  If your primary field isn't the unique ID, you'll need to put in a "Find Record" action in there

After it's linked, I've got a rollup that outputs that person's latest form submission time (The "Latest Submission for Person" field in the "People" table), and I use that to identify which form submission is the latest one.I then have a conditional lookup that displays the data from the latest submitted form. 

The formulas should be accessible once you've duplicated the base too!

[Alexis_Louie]

Thanks, this is helpful!