Sequence of actions on different tables

[Gabriel_Vidal]

Hi everybody, I am working on a project where I have a products table, an order table and a cart table. To make the long story short and illustrate the problem I have I have made an example with just one user.The orders tables has only to columns:
id (key)
state (text, ie. open, closed…)
When a new row is added to the orders table an id is generated automatically and the state is set to “open”

The cart table has 3 columns:
id (key)
product id (a ref to a product)
order id (a ref to the order id)

The idea is to be able to add products to the cart and that they all belong to the same order. The app will be quite simple: for example, to start with we will not allow the user to have more than one order opened at the same time. The solution I came with is the following (bear in mind that I am a python developer and I have been with app sheet for a week):

1. Products are added to the cart table with an action on the products table doing “Data: add a new row to another table using values from this one”.
   1.1) We set the column: product id=[id] in the action
   1.2) the reference to the order is auto computed (it is set up as “auto computed” in the data columns and the formula is: =ANY(FILTER(order,[state]=“open”). This is a bit tricky but since we only allow for one open order at the same time at the most it should work… once we have created the order (adding a row to the order table)

Now the question is:

How and when do we create the new entry in the order table?. My original idea was to make the AddToCart action a group of actions: the first act on the order table creating a new row when there is no previous order with an “open” state: the second would act on the products table and would just add the product to the cart table (all columns would be filled as in point 1 above).

But I can’t make it work: the “Grouped: execute a sequence of actions” can only work on ONE table and I would need it to act on two of them: the order table and the product table. Any ideas on how to do this? Thank you very much in advance!

Gabriel Vidal

[Gabriel_Vidal]

I just did it from the products table with a “add a new row to another table using values from this row” adding a row to the order table only when the following condition is met:
ISBLANK(FILTER (order, [state]=“open”))