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.
