Trying to add the value of a cell from a value in a different table

Hello! I am working on an inventory function for an existing app. All I need to be able to do is add the value of a column to a related column in a different table. I have three tables that are part of it… ORDERS, LINE ITEMS, and INVENTORY. LINE ITEMS relate to INVENTORY by “sku” column. INVENTORY has three other columns “In Stock”, “Allocated” and “On Hand.” I would like for the manager to be able to click on a button that triggers the following: “Allocated Date” in the LINE ITEMS becomes populated, the “allocated” column in INVENTORY is increased by the “qty” column from LINE ITEMS, and the “On Hand” column becomes the sum of “In Stock” - “Allocated”.

I have tried quite a few solutions including virtual columns and a complex string of actions/workflows, which works when a line item is allocated one at a time, however the typical workflow would be the manager clicking the check boxes of many line items and allocating them all at once. My current method fails.

I feel like I must be missing something simple here…

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:

  1. An action can only directly modify the table to which it’s attached; it cannot modify data of another table.

  2. 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)

  • Action name: Record Allocation
  • 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: TODAY()
  • Only if this condition is true:
    AND(
      ISNOTBLANK([Allocated Date]),
      ([Allocated Date] = "1/1/1970")
    )
    

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:

  1. User taps Allocate for a row in LINE ITEMS.

  2. Mark to Allocate marks the row for allocation.

  3. 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.

  4. The row in the INVENTORY table receives the signal and performs Record Allocations to find the pending allocations and make the appropriate updates.

  5. 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.

  6. Update On-Hand updates the INVENTORY row’s On Hand count to reflect the added allocations.

  7. Record Allocations in Line Items reaches back to the now-processed LINE ITEMS rows to prompt them to record their completed allocation.

  8. Back in the LINE ITEMS table, Record Allocation replaces the marker with the actual allocation date.

4 Likes

Thanks for this suggestion! I began working through a similar scenario but was concerned with how it might behave when the Manager selects multiple records via the checkbox interface and applies the behavior at once. Thoughts on how your method would work in that scenario?

The process I outlined above is designed with that scenario in mind.

Awesome! Thanks!!!

1 Like

Hi Steve - I hope you are well. I’ve been wrestling with this issue for 2 days and cannot solve it. Hopefully you can sort it out for me.

Similar but simpler issue.

  1. Table ORDER DETAILS with a link to PRODUCTS via PRODUCTID has a column qtyDelivered
  2. Table PRODUCTS with column Inventory - needs only a running tally ie. when items are delivered (qtyDelivered) that value should be subtracted from the Inventory for that product.

I’ve been trying to adapt your logic but cannot get an Action similar to what you called Add New Allocations to work.

I don’t need the date of delivery so I modified the Mark to Allocate step. I use a SetInv column (YES/No) and have a default value of FALSE when a new row is added. My intention is to set that to TRUE once the inventory routine has been completed.

The process should work for every row that is added to the ORDER DETAILS table. There is one minor condition - not every item needs to be inventoried. My plan is to put a condition on the action that checks for a marker (in the Products table) that indicates whether the item is inventoried or not (Track) - so the condition would be Track = TRUE

Step by Step:

  1. Add new row - default MarkInv to False
  2. Update Products Inventory column by subtracting ORDER DETAILS QtyDelivered from existing Inventory value
  3. Set MarkInv to TRUE

Any assistance would be appreciated.

Cheers
Ray