None of the specifics here are tested, and are intended purely as suggestive guidance.
None of this may work.
Unfortunately, what you’re trying to do isn’t exactly simple. It’s a multi-part process made more difficult by some limitations. In AppSheet, actions have several limitations that are significant here:
-
An action can only directly modify the table to which it’s attached; it cannot modify data of another table.
-
An action cannot pass information to another action; an action can only work with information it can gather with expressions.
Presumably, the manager will be reviewing rows of the LINE ITEMS table for allocation. Because of (1) above, actions attached to the LINE ITEMS table cannot modify the INVENTORY table, so they cannot directly adjust the allocation and on-hand counts. What LINE ITEMS actions can do, though, is signal the INVENTORY table that there’s new information to process, leaving it to the INVENTORY table to respond in an appropriate way.
Because of (2) above, there’s no way for the LINE ITEMS table to directly communicate which rows the INVENTORY table should consider. Instead, we have to leave a marker in the significant LINE ITEMS rows that the INVENTORY table will recognize. After the marked rows have been processed, the markers need to be removed to ensure they don’t cause repeated processing later.
The first step will be creating an action to mark a row in the LINE ITEMS table for allocation. There are several possible ways to “mark” a row. I’ve chosen to set the Allocated Date column value to a date that should (presumably) never otherwise occur during proper functioning of your app: January 1, 1970. Any row in the LINE ITEMS table with the Allocated Date column value of 1/1/1970 will be considered allocation-in-process.
Mark to Allocate
- Action name: Mark to Allocate
- For a record of this table: LINE ITEMS
- Do this: Data: set the values of some columns
- Set this column: Allocated Date
- To this value:
"1/1/1970"
- Only if this condition is true:
ISBLANK([Allocated Date])
On the other side, the INVENTORY table needs to add the qty values from the marked LINE ITEMS rows to the existing Allocated count. To do so, this action is performed by a given INVENTORY row. It finds all of the marked LINE ITEMS rows that reference the given INVENTORY row’s key column value ([_THISROW]) and adds their qty column values to the INVENTORY row’s existing Allocated column value.
Add New Allocations
- Action name: Add New Allocations
- For a record of this table: INVENTORY
- Do this: Data: set the values of some columns
- Set this column: Allocated
- To this value:
(
[Allocated]
+ SUM(
SELECT(
LINE ITEMS[qty],
AND(
(ISNOTBLANK([Allocated Date]),
([Allocated Date] = "1/1/1970"),
([sku] = [_THISROW])
)
)
)
)
- Only if this condition is true:
TRUE
Having adjusted the Allocated count, the INVENTORY row’s On Hand column value needs be updated to reflect the change.
Update On-Hand
- Action name: Update On-Hand
- For a record of this table: INVENTORY
- Do this: Data: set the values of some columns
- Set this column: On Hand
- To this value:
([In Stock] - [Allocated])
- Only if this condition is true:
TRUE
Once the INVENTORY row has been suitably updated, the marker on each LINE ITEMS row needs to cleared, or–in this case–replaced with the date the allocation was successfully recorded. Note that this step is only done if the LINE ITEMS row is in fact properly marked.
Record Allocation (note singular)
The action above, Record Allocation, is performed by the LINE ITEMS table on marked rows in response to prompting from the INVENTORY table. The following INVENTORY table action identifies all of the LINE ITEMS rows processed and prompts those rows to record the allocation.
Record Allocations in Line Items
- Action name: Record Allocations in Line Items
- For a record of this table: INVENTORY
- Do this: Data: execute an action on a set of rows
- Referenced Table:
LINE ITEMS
- Referenced Rows:
FILTER(
"LINE ITEMS",
AND(
(ISNOTBLANK([Allocated Date]),
([Allocated Date] = "1/1/1970"),
([sku] = [_THISROW])
)
)
- Referenced Action: Record Allocation
- Only if this condition is true:
TRUE
Above, we’ve defined actions for the individual acts to perform for an INVENTORY row that’s been notified of allocations: add the new allocation quantities to the running count, update the on-hand count, and record successful inventory update. The action below groups those individual actions as one to make it easier to ensure all three are done together.
Record Allocations (note plural)
- Action name: Record Allocations
- For a record of this table: INVENTORY
- Do this: Grouped: execute a sequence of actions
- Actions:
- Add New Allocations
- Update On-Hand
- Record Allocations in Line Items
- Only if this condition is true:
TRUE
All of the INVENTORY actions above are pointless until something prompts them into action. That prompt actually comes from the LINE ITEMS table row that is being allocated. This action, performed by that row, notifies the INVENTORY table–specifically, the row corresponding to the LINE ITEMS row’s sku column value–of the intended allocation.
Record Allocation in Inventory
- Action name: Record Allocation in Inventory
- For a record of this table: LINE ITEMS
- Do this: Data: execute an action on a set of rows
- Referenced Table:
INVENTORY
- Referenced Rows:
LIST([sku])
- Referenced Action: Record Allocations (plural)
- Only if this condition is true:
ISNOTBLANK([sku])
Finally, the action to perform the full allocation process, bringing together everything above.
Allocate
- Action name: Allocate
- For a record of this table: LINE ITEMS
- Do this: Grouped: execute a sequence of actions
- Actions:
- Mark to Allocate
- Record Allocation in Inventory
- Only if this condition is true:
([qty] > 0)
The process works like this:
-
User taps Allocate for a row in LINE ITEMS.
-
Mark to Allocate marks the row for allocation.
-
Record Allocation in Inventory signals the row in the INVENTORY table corresponding to the LINE ITEMS row’s sku column value that an allocation is pending.
-
The row in the INVENTORY table receives the signal and performs Record Allocations to find the pending allocations and make the appropriate updates.
-
Add New Allocations finds all of the marked rows in the LINE ITEMS that correspond to the INVENTORY row, gathers the qty column values from the marked rows, and adds them all to the running Allocated total.
-
Update On-Hand updates the INVENTORY row’s On Hand count to reflect the added allocations.
-
Record Allocations in Line Items reaches back to the now-processed LINE ITEMS rows to prompt them to record their completed allocation.
-
Back in the LINE ITEMS table, Record Allocation replaces the marker with the actual allocation date.
LinkedIn
Twitter
