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

[rbeason]

I've posted here before, but never was able to find a solution, thanks those who tried to help me though.  I've since recreated what I'm trying to do in hopes that it is simpler and can be done.  Here goes.

Use Case: I have orders getting added to a table "Order Fulfillment" by Zapier.  The Course can be several different things "ITIL 4 Foundation, ITIL 4 Foundation w/TAKE2, etc".  What needs to happen is, when the order gets created into this table and I check the "Run Automation" checkbox, it needs to find the next available Voucher Code from the table Voucher Code and move the information in Voucher Code table to the Order Fulfillment table and update the record.  The Course is what should tell it which to match and use.

Is this at all possible to do?

[pressGO_design]

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?

[rbeason]

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.

[pressGO_design]

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.

[rbeason]

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.

[rbeason]

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?

[pressGO_design]

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.

[rbeason]

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?

[pressGO_design]

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.

[pressGO_design]

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.