Make google appsheet recalculate the whole column values, depending on values of another columns

[CaringCoder]

Hi

I have created an inventory management database. There will be several users that using an app connected to that database. I assume that users will enter new entries when they will have time to do so, and, as a result entries will not be date ordered. I added two columns for storing giving warehouse remainder and receiving warehouse remainder that should be calculated after each change in database. As users can add new entry with earlier dates, the previous calculated values will not be fully correct in all of the rows. So I want to automate it, so that database would recalculate the values of the whole columns (remainders for giving and receiving warehouse) after each update of database. I am aware that it could be done via appsheet app's action and automation features, but I couldn't overcome it. Could you give a direction for me in that? Any help will be appreciated.

[Aurelien]

Hi @CaringCoder 

You didn't provide your table structure, but here is my suggestion:

- Table ITEM: columns initial_stock

- Table ORDER:  columns quantity (type decimal), ITEM (type Ref, source table ITEM), remaining_stock (type decimal), entry_type (type Enum, values in/out), date (type date)

1) create an action in table ITEM, let's name it "update_remaining_stock". Set the column remaining_stock with this expression:

[ITEM].[initial_stock]
-SUM(
  SELECT(ORDER[quantity],
    AND(
      [ITEM]=[_THISROW].[ITEM],
      [date]<=[_THISROW].[date],
      [entry_type]="out"
    )
  )
)
+SUM(
  SELECT(ORDER[quantity],
    AND(
      [ITEM]=[_THISROW].[ITEM],
      [date]<=[_THISROW].[date],
      [entry_type]="in"
    )
  )
)

2) create an action on table ITEM, let's name it "bulk_update_remaining_stock".

- do this:"execute an action on a set of rows", 

- table ORDER

- reference rows: [Related ORDERs]

- referenced action: "update_remaining_stock"

3) create a bot to execute daily, based on table ITEM, that will fire the action "bulk_update_remaining_stock"

Hope this helps!