I’m working on setting up a paper revision process. I have a Paper Submission table, a Reviewers table and a Review Form Data with the review itself. So once authors submit papers, they get 2 reviewers assigned in the Paper Submission table. Once that happens, an automatic email is sent to the reviewer with the paper, the Review Form that they need to fill out, and the due date. If they submit a review, a record is created in the Review Form Data table. So the outcome would be to have 2 review records for paper.
So what I’m struggling is with setting up reminder for reviewers when they have 3 days left to submit the review. I created a Send Reminder view in the paper Submission table, but I’m struggling with the coditions since I don’t want reviewers to get reminders if they already submitted the review form.
So let’s say that Reviewer 2 submits the review, so I get a record in my table, but I’m still waiting on Reviewer 1 and it’s 3 days before the due date therefore, I want to send an email to the specific reviewer that hasn’t submitted the review yet.
I want to use the condition: IF there is a Review Record then don’t send the reminder, ELSE, send it.
So my question is How can I tell Airtable to match a specific record from the Review Form Data table with a reviewer?? In this case I have a record created in my Paper Submission table (via Lookup field), but it’s hard to tell if it belongs to reviewer 1 or 2 unless I open the record info, but I want to automatize this process.