Move record from one table to another based on field matching and using next available field

Are your voucher code sequential in a numerical format? Or are you trying to have the automation find the first record in the Voucher Codes table that isn’t linked to an Order?

Kinda.  We place them into the Voucher Codes table in blocks of 10 every few days, or when they sell out.  And yes, I am trying to have it find the first voucher code that is not linked to a Order # that matches the Course field.

Here's a way to do it, but you need an autonumber assigned to the Voucher records and another formula field to subtract 1 from that autonumber. Basically, the automation triggers when you check the box and then it

FINDS all of the Orders that have already been attached to Vouchers (in a 1-to-1 relationship)
FINDS the Voucher that has the autonumber-1 that matches the number of records that have already been linked to Orders
UPDATES the Voucher record that you just found with the Airtable ID of the Order you clicked the box to trigger the automation for.

Link to base here. Short video attached.

This is very helpful.  I'm assuming I could use a field such as Order #, since we collect that now and it's unique and use that instead.  Well, no that wouldn't work as sometimes there are multiple things with a Order #.  We could just add another column for the auto number.  Let me dissect this a bit and I'll post back once I understand it better.

That link you posted, it's a view only base.  Is there a version where I can see what the field types are, the automations, etc?

Oops I changed the settings so that you can copy the base - that will get you the formulas. For the automations, I'm attached a bunch of screenshots that show the parts so you can duplicate them.

So I copied the automation as you have it and it looks right up until the Update Record step.

The 'List of RecordID', is that from the Find Records of where Voucher Codes is not empty or where Autonumber -1?

And then the final part Order Fulfillment Spreadsheet Airtable Record ID?  Where is that from?  From the where Autonumber-1 find record or something else?

I've attached 2 screenshots that I hope help.

In the Update Record step

1. The record you're updating is the one you just found in the Vouchers table using the Autonumber-1=length of the Order records that aren't empty. Airtable says this is a list, and in most cases the FIND records is a list, but in your case the number of records that it finds will always be 1.
2. The field that you're updating is the RecordID of the original record that you clicked the checkbox in.

In case step 2 wasn’t clear: you’re updating the linked Orders field in the Vouchers table with the RecordID of the record that, when you checked the box, triggered the automation. So if you’re triggering the automation with Order 3, you want the RecordID of Order 3 to go in that linked record field. 

That makes sense and it works.  It chose the next available record.  However, how would I make it choose the next available based on what the Course column says?

And it just adds the link to the record in Voucher code, is there a way to have the actual field value show instead of just a link to the record?

So I added a condition to the Find records to be AND Course is Course field from Order Fulfillment table.  Is this correct or am I missing something to make it match the course?

No, that won't work. I didn't realize that you had different vouchers based on different courses. In that case, you're going to need to use a formula-created numbering series for each course. Give me a few minutes and I'll send you the link to a base.

OK - this is going to be a long response, so please bear with me.

In order to play the "grab the next voucher" game, you need a way of next-ing. Since you're next-ing based on courses, you need a way of next-ing within each course. So

Course 1 - Voucher 1
Course 1 - Voucher 2
Course 1 - Voucher 3
Course 2 - Voucher 1
Course 2 - Voucher 2
etc

To create this nested sequential numbering system, you need to set up a bunch of fields in your Voucher table *and* an automation that you will have to run on all of the records every time you dump a new set of vouchers into the table. So if you dump in 10 records at a time, you'll need to run the automation 10 times - once on each record. (Could you do this by pressing a button in an interface and having the automation do a repeating loop through the new records you dumped? Yes.) The fields you'll need to do this are:

1. a {Voucher Number} field (number)
2. a {Next Voucher Number field (formula: {Voucher Number} +1)
3. an {Is Latest} field (single-select Yes/No) - you will need to manually set this for each course the first time you set this up

The Automation you'll need will be triggered by checking a box (or whatever) in the Voucher record. Let's say that you check the box for Course 1 - Voucher 3. The automation actions are:

1. FIND records in the Vouchers table where the {Is Latest} field is Yes and the Course=Course. The record that it finds will be the Course 1 - Voucher 2 record)
2. UPDATE the Course 1 - Voucher 2 record by changing {Is Latest} from Yes to No
3. UPDATE the Course 1 - Voucher 3 record as follows
   1. take the Next Voucher Number from the Course 1 - Voucher 2 record it found in the previous step and putting it in the Voucher Number field
   2. change {Is Latest} from Course 1 - Voucher 3 to Yes.

See the Voucher Numbering video attached.

To connect the vouchers to the courses, you need a {Voucher Number - 1}, which you will use the same way you used the {Automation - 1} field in the previous iteration.

See the Vouchers to Orders video attached.

I can put the whole thing into a Free Workspace and invite you as a Creator if you want so that you can see the back end of all of it. PM me if that would help you.

This is almost what I'm trying to do and gets me going in the right direction.  I sent you a PM for the free workspace invite.  Thanks.