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?

[rbeason]

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?

[rbeason]

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?

[pressGO_design]

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.

[pressGO_design]

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.

[rbeason]

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.