How would you construct this workflow?

[Alex_Lidell]

I want to use airtable to distribute audiobook codes to reviewers who request them. I need to keep track of who got which code, and have an easy way of seeing and evaluating new requests. Any ideas on how to configure this on Airtable so don’t have to double enter information?

Details

1. Audiobook codes are unique. They are also assigned to a specific book and specific country (UK/US)
2. I need to keep track of which codes were already given and which are still available.
3. I need to keep track of who got which code
4. When a reviewer makes a request, they need to provide their email, the name of the book they want, their country, and a screenshot of meeting prerequisites

SO FAR:

1. I have a Table 1 with audiocodes. Next to each code is the book its associated with, the country its good for (US/UK) and whether it has been given out or not.

2. I have a form in a separate table - Table 2. This form ask the potential reviewer for his name, which book he wants, a screenshot of pre-requisite, and the country.

Problem… I am having trouble coming up with a workflow where I fulfill requests - something that would associate the requester with a code I assigned him while changing the code to “used” status. Ideas?

Some considerations: I think doing a LookUp for requestor email in TABLE 1 would be impractical - with hundreds of requests, it would be a pain to figure out who the new one is, or to scroll through everyone.

would love your ideas!
Alex

[JonathanBowen]

Hi @Alex_Lidell - have a look at this base that I have mocked up:

Airtable

Audiocodes - Airtable

Explore the "Audiocodes" base on Airtable.

The Audiocodes table has two views:

The Requesters table has a link to Audiocodes (based on the view “Unused codes”).

My workflow for this would be:

1. Requester makes request via your form and populates the Requests table with Name, Book and Country.

2. For an open request, you click on the {Link to Codes} field and this will show only unused codes. I’ve put the book name and country in the ID for the Audiocodes table as this will allow you to search by book name (and country):
   

3. You select the code you want to apply to this request - the {Link to Codes} field is populated and for ease of reading the code itself is pulled through via a lookup.

4. Back in the Audiocodes table, your selected audiocode record is now flagged as “Used”:

and will no longer be available to select in the Requester table look-up.

Does this work for you?

JB

[Alex_Lidell]

OMG!!! This is brilliant!!! The Xs factor I had not been able to figure out without your formulas are
<<{Link to Codes}` field and this will show only unused codes.>>
and how you got the “used” flag to auto work. I’ve copied your base over my workspace and will go marvel and the solution. THANK YOU