Automating from one sheet to another

[clairehunter]

Hello!

I am just starting out with AirTable and I am trying to create an automation that when an item is moved to the status "Backorder" (this should happen when the field "B/O Qty" is greater than 0) in my "Open Orders" project, it is then duplicated into another project called "Backorder Clip". Once the order status is not "Backorder" anymore (or the B/O Qty goes to 0), it should be deleted from the "Backorder Clip" project and moved to the "Not Started" status in my "Open Orders" project. I can't seem to figure out exactly how to achieve this. Any help is greatly appreciated. Thanks so much!

[Sistema_Aotearo]

It seems like you need 2 sets of automations. Though, I don't have a full grasp on what you're trying to achieve, here's what I came up with from my understanding. Please clarify if I'm not on the right track.

1. Duplicating to "Back Order" Table.

I assume you have the same fields in both your "Backorder Clip" & "Open Orders" tables. I also assume you want to create a new record in the "Open Orders". If you want to just update existing records in "Open Orders" we can also do that but it'll take a few more steps in the automation. I will also assume you have your "Not Started" as a single select and would also want that to auto-change to something like "In Progress".

Trigger: When record matches condition

• Table: [Open Orders]
• Condition: When "B/O Qty" [>] "0"

Action: Update Record

• Table: "Open Orders"
• Fields: Status - "In Progress"

Action: Create New Record

• Table: "Backorder Clip"
• Fields: Any field you want copied. - You'll want to change each to dynamic in the gearbox to the right. Then go through and select all the same fields but from the trigger step.
• Additional field: Link to "Open Orders". - dynamic - Airtable record ID from the trigger step
  This one is important for the next set of automations.

2. Deleting and set to "Not Started"

Trigger: When record matches condition

• Table: [Open Orders]
• Condition: When "B/O Qty" [=] "0"

Action: Update Record

• Table: "Open Orders"
• Fields: Status - "Not Started"

Action: Find Records

• Table: "Backorder Clip"
• Condition: If [New Link Field (we made from before)] contains "Airtable record ID" from trigger step

Advance Logic: Repeating Step

• Make a list from the found records in the previous step.

Action: Run a Script

let table = base.getTable("Open Orders");
let inputConfig = input.config();
let recordId = inputConfig['Delete Me']
await table.deleteRecordAsync(recordId);

You will also need to define which records are "Delete Me" as the ones from the repeating step. If you open up the script you should see a panel on the left that will let you do this. Remember to name these as "Delete Me" so it matches the code.

Let me know if this is what you're looking for.