Help

Help Updating Record Based On Another Table

Topic Labels: Base design
2652 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Nick_Creed
4 - Data Explorer
4 - Data Explorer

Hi there,

I currently manage a student organisation at my university and every year members have to renew their memberships to remain an active member of the club (this is university policy). I chose to use airtable as it is an easy way to allow students to register for the club through airtable forms and the integration with mailchimp is very convenient.

At this stage, I’m trying to figure out a way that I can email students a link to an airtable form that allows them to reaffiliate and for the information to be updated on the membership table automatically. At this stage I have made another table called ‘Reaffiliation’ with a form that allows them to select their student ID from a linked record and click a checkbox to reaffiliate.

Ideally, I would like the checked box to be reflected in a column called ‘Reaffiliated’ on the ‘Members’ table making it easy for me to create a view that allows me to see all of the active members so I can send that information to my university.

If anyone knows a way to do this or could suggest a workaround it would be very much appreciated as I’m stuck.
I tried to attach images to my question to assist but it seems that it is not allowed.

Regards,
Nick.

2 Replies 2

Sounds like a great use case for Airtable. It cannot be 100% automated, but it can be pretty close.

It sounds like you already have

  • a [Members] table
  • a [Reaffiliations] table
  • links between the [Members] and [Reaffiliations] table
  • a form to populate the [Reaffiliations] table.

Propagating Reaffiliation Date to the [Members] table

In the [Reaffiliations] table, create a field called [Date Reaffiliated] that shows the date the record was created (either through the field type or a formula).

In the [Members] table, create the field {Latest Reaffiliation Date} as a rollup field that references the linked record and the [Date Reaffiliated] field. Use the MAX aggregation formula.

The {Latest Reaffiliaton Date} will either show the date of the most recent reaffiliation, or be blank.

If you need to display a check box in addition to the date, you can use a formula field with an IF formula. As the years progress, you can adapt the formula to calculate if the {Latest Reaffiliation Date} was recent enough or not.

Updating member information with the Scripting block

If you have the Scripting block, you can write a script to copy all the new member data from the [Reaffiliation] table to the [Members] table whenever you press a buttton.

(If you have the Scripting block, you can make the check box as part of this process, so that the the {Latest Reaffiliation Date} would be a date field instead of a rollup field.)

Updating member information with bulk copy/paste (free)


If you do not have the Scripting block, and are willing to delete out any old records from the [Reaffiliations] table, you can use lookups and formulas to make it easier to see whose data has changed.

For every input field in the [Reaffiliations] table, create a lookup field in the [Members] table.

Arrange both sets of of fields (original and lookup) in the [Members] table so that all the original fields are together and all the lookup fields are in order. Make sure that the order of both sets of fields is the same.

Multi-select all the data in the lookup fields, then paste it to the original fields in one fell swoop.

Checking who has updates (before you update them)

For every pair of fields (the field with the original data and the new lookup field) create a formula field with an IF formula that compares the two and says if they are different, and is blank if they are not. (Or create a single formula field that makes all the comparisons and reports if anything is different.)

Hiding the Member List

If you have a large membership or are concerned about privacy, you also might want to reconsider having students manually select their own ID from a linked record. The web form would be open to the public and anyone who sees the form could (1) see the entire membership list, and (2) easily submit the form for other people. College students are

You would need to manually make the links from the [Affiliations] table to the [Members] table, but it could be done in in a single copy/paste. You just need something to uniquely match what each student enters with info you already have.

  • something that rarely changes, such as cell phone number
  • exact name (if they are all unique and people type their names consistently)
  • a private code (which can be automatically generated by Airtable) for each student. This would require sending a slightly different email to each student with that student’s code.
Moe
10 - Mercury
10 - Mercury

If you’re simply just want students to click on a link to confirm their reaffiliation, then you can do that with this extension. Basically it’ll generate a unique URL that you can email to each student, and when it’s clicked, their own associated record in Airtable will be updated.