Skip to main content

How to make linked record "reusable" in a field

  • October 18, 2017
  • 4 replies
  • 46 views
K

I apologize if the title of this makes no sense, but here is what I’m trying to accomplish:
I am running a certificate program where students must complete certain tasks for points to earn the certificate. I want to track these in airtable. I have two tables; One for Participants and One for Tasks, and they are linked so I can look at a participant’s progress in one table and can see who is completing which tasks in the other table. Each table has specific unique data for the tasks or participants.
I have run into a problem in that some of the tasks are able to be repeated, so I can’t use a linked record field to add a task twice to one participant. Here is a visual:
This is what I have currently (obviously simplified)
Participant 1: Task 1, Task 2, Task 3

In my participant sheet I need the ability to do this:
Participant 1: Task 1, Task 1, Task 2, Task 3

The participant sheet is set up to automatically calculate point totals for individuals, so my options are somewhat limited in how I can approach this.

To further complicate things, I am using a Jotform (connected to the base via Zapier) to allow the students to submit their tasks and would like to automate entry. This is set up and works, but the problem of not being able to add a value twice kills the functionality of the form.

I’m open to any suggestions and am happy to restate or further clarify if that helps.

4 replies

J
Forum|alt.badge.img+4

Hi @Kendrick

Do you need both versions of tasks?

If not you could use find to locate a record and if found update and if not add new (in your zap)

Or if you do need both, you could look at adding to your Zap so that after adding the new record it looks to finds another record (searching for the same person and task but excluding the record just added) and if found marks it as an old record. You would then have a formula field to only show the marks if the record was a current one (i.e not a previous one). It would be this calculated value you would sum in the Participants record.

It’s late here so this probably doesn’t make sense - let me know how you get on!

K
  • Kendrick
  • Author
  • New Participant
  • October 19, 2017

Hi @Kendrick

Do you need both versions of tasks?

If not you could use find to locate a record and if found update and if not add new (in your zap)

Or if you do need both, you could look at adding to your Zap so that after adding the new record it looks to finds another record (searching for the same person and task but excluding the record just added) and if found marks it as an old record. You would then have a formula field to only show the marks if the record was a current one (i.e not a previous one). It would be this calculated value you would sum in the Participants record.

It’s late here so this probably doesn’t make sense - let me know how you get on!


@Julian_Kirkness
Thanks for offering your thoughts. I don’t necessarily need both versions of the tasks in the participant table as long as I have a record, within the table, of the student having completed said tasks.

To your first suggestion, this is mostly how the zap is currently set up, and it works for single instances, but zapier throws an error, “cannot accept value”, if the entered task has already been completed once by that participant. My zap is three steps: 1. Jotform Trigger, 2. Find or create record, 3. Update record.

To your second suggestion, I’ll be honest that I’m not sure how to accomplish this. This is my first experience with zapier, so excluding a record or marking the record as old aren’t options I have encountered. Would those require custom values?

J
Forum|alt.badge.img+4

@Julian_Kirkness
Thanks for offering your thoughts. I don’t necessarily need both versions of the tasks in the participant table as long as I have a record, within the table, of the student having completed said tasks.

To your first suggestion, this is mostly how the zap is currently set up, and it works for single instances, but zapier throws an error, “cannot accept value”, if the entered task has already been completed once by that participant. My zap is three steps: 1. Jotform Trigger, 2. Find or create record, 3. Update record.

To your second suggestion, I’ll be honest that I’m not sure how to accomplish this. This is my first experience with zapier, so excluding a record or marking the record as old aren’t options I have encountered. Would those require custom values?


@Kendrick - your last question would use a complex search formula in the find record step in the zap - for example:

You can find information to help in the following article:

In your case you would search for a record who’s ID was not equal to the one just created and the task was the same.

K
  • Kendrick
  • Author
  • New Participant
  • October 23, 2017

@Kendrick - your last question would use a complex search formula in the find record step in the zap - for example:

You can find information to help in the following article:

In your case you would search for a record who’s ID was not equal to the one just created and the task was the same.


@Julian_Kirkness
Thanks again for the suggestions. As it turns out I was overthinking this process. I simply added a “Submissions” table within Airtable and linked to the appropriate participant and task tables. This accomplished what I needed without further editing my zap.

Terms & ConditionsAccessibility statement