Here’s a bit of a wild idea.
- Create a column in your confirmed orders sheet that will contain only email and business. This can be 2 separate column joined with a template, or you can write it to the sheet later already joined from the form button. Doesn’t matter. We just need email and buiness only.
- Next on your products, sheet follow the instructions here to get a lookup column of the user’s signed in email into the products sheet. List Relation "Data"
- Next in the products sheet, create a template column of the signed in user email and business.
- Next create a relation column that links the template to the email-business template in the confirmed orders sheet.
- Next create a rollup column to count the number of confirmed orders for that email-business.
- Next create a math column that will add 1 to the rollup value.
- Next create a template column that joins the math value and the email-business.
(Each product should now have a column that includes the email, the business and the next available order number for that email-business.)
Now when you click on the form button to purchase the product, you will include the order number - Email - Business template column and write it to the sheet. This will replace your date - email - business column.
Your orders sheet should still work the same using Unique.
When you confirm an order, it will write a record to the confirmed orders sheet. When you later purchase a production from the same business, the rollup column will increment by 1 and the math column will increment by 1 giving you the next available order number for that email-business combo.
I haven’t tested this, so I hope it works for you.
Edit: Sorry, the first paragraph in this post want meant to be posted. It was a first draft idea that I don’t think was going work. I meant to delete it and it’s removed now. Sorry if that led to any confusion.