Automation Using Actions

[arrow_gh]

I have created an inventory management app with three tables (sales, inventory, restock). I'm facing an issue with creating automation using actions to update the inventory quantity when there's a sale or restock.

I have set up two actions:
1. The first action executes on a set of rows where the referenced row is filtered from the inventory table using an expression.
2. The second action is supposed to set the values of the quantity column in the referenced row using an expression.

Here is the expression I am using for the second action:
```
[Quantity] - INDEX(
SELECT(
sales[Quantity],
AND(
([Product ID] = [_THISROW].[Product ID]),
([_ROWNUMBER] = MAXROW("sales", "_ROWNUMBER", ([Product ID] = [_THISROW].[Product ID])))
)
),
1
)
```

Despite the expression being correct, when I trigger the action, nothing happens. Any suggestions on how to troubleshoot this issue would be greatly appreciated.

[AleksiAlkio]

You can write the formula without INDEX() or SELECT() because the result of the MAXROW() is just a key column's value from one record.

[Quantity] - LOOKUP(MAXROW("Sales", "_ROWNUMBER",[Product ID] = [_THISROW].[Product ID]),Sales,KeyColumn,Quantity)

But the reason for your challenge can also be the 1st action if it doesn't find any records to update.

[arrow_gh]

It still not updating when I trigger it. However when I change the expression to [Quantity] - SUM(SELECT(sales[Quantity], [Product ID] = [_THISROW].[Product ID])) it works perfectly, so I doubt it’s a problem with the first action.

[AleksiAlkio]

Excellent!