Help with an inventory app

AppSheet AppSheet Q&A
, ,
1

Hi!

So, my app is for managing inventory in various containers. I have multiple containers and multiple items that each have variable quantities to be distributed amongst each container. As such, I have the “Items”, “Containers”, and “Move Quantity” tables. I need a way to move certain quantities of an item from one container to another from the “Items” table view. The “Move Quantity” table has ID, Date, From Container (Ref), To Container (Ref), Item Reference, and Quantity columns. The “Items” table each has a reference to a single container, so any of the same item that is in a different container I have set to be a new line item.

  1. Ideally, I want it to open a form from the “Items” table that auto-populates the “Item Reference” and “From Container” fields to be from the current item selected.
  2. Then I want to be able to edit the “To Container” and “Quantity” fields in the opened form.
  3. Then when the form is saved, I’d like it to create the new row in the “Move Quantity” table
  4. and finally EITHER create a new row in the “Items” table if the item does not already have a relationship with the “To Container” AND edit the quantities in both the “To” and “From” containers in the related “Items” rows, OR edit the quantities in both rows in the “Items” table if they already have those references. (This last part seems inelegant to me. I was playing around with different ideas of having different quantities of the same item in different containers).
2

How are you? I asked GPT’s appster, and this was the answer he gave me. I’m going to sleep right now and couldn’t read everything. It’s a pain. Try this, and if not, I’ll help you tomorrow! Cheers.

Alright, let’s break this down step by step so you can implement a smooth inventory transfer system in your AppSheet app.

  1. Open a Form from the “Items” Table with Pre-Filled Data

To open a pre-filled form from the “Items” table, you’ll need an action that navigates to the “Move Quantity” form with pre-set values.

Steps:

  1. Create an action on the “Items” table

• Name: Move Item

• Type: “App: Go to another view within this app”

• Target:

LINKTOFORM(“Move_Quantity_Form”,

“Item_Reference”, [Item_ID],

“From_Container”, [Container_ID]

)

• This ensures that when the action is triggered, the form opens with the Item Reference and From Container pre-filled.

  1. Attach this action to a button or use a UX behavior to show it in the detail view of an item.

  2. Edit the “To Container” and “Quantity” Fields in the Form

Once the form is opened via LINKTOFORM(), the user will be able to manually edit:

• The To_Container (choosing which container to move to)

• The Quantity (how many to transfer)

  1. Create the New Row in “Move Quantity” Table on Save

This happens automatically when a user submits the form, as it’s a standard AppSheet form behavior. The row gets added to the “Move Quantity” table.

  1. Update or Create Items in the “Items” Table

Now comes the trickier part: adjusting inventory.

Approach:

• If an entry for the same item already exists in the destination container → just update the quantity.

• If it does not exist → create a new entry in “Items.”

Steps to Implement:

  1. Create a Bot to Trigger on “Move Quantity” Add

• Trigger: “When a new row is added to Move Quantity”

• Process: Runs these actions in sequence:

  1. Action 1: Decrease Quantity in the “From” Container

• Table: “Items”

• Type: “Data: Set the values of some columns”

• Condition: [Item_Reference] = [_THISROW].[Item_Reference] AND [Container_ID] = [_THISROW].[From_Container]

• Set values:

[Quantity] = [Quantity] - [_THISROW].[Quantity]

  1. Action 2: Increase Quantity in the “To” Container (or Create New)

• Table: “Items”

• Type: “Grouped Action” → Calls either:

• Update Action (if item already exists)

• Add Action (if item does not exist)

• Update Action Condition:

AND(

[Item_Reference] = [_THISROW].[Item_Reference],

[Container_ID] = [_THISROW].[To_Container]

)

• Set values:

[Quantity] = [Quantity] + [_THISROW].[Quantity]

• Add Action Condition (Opposite of Update Condition):

NOT(

IN([_THISROW].[Item_Reference],

SELECT(Items[Item_Reference], [Container_ID] = [_THISROW].[To_Container])

)

)

• Creates a new “Items” entry with:

• Item_Reference = [_THISROW].[Item_Reference]

• Container_ID = [_THISROW].[To_Container]

• Quantity = [_THISROW].[Quantity]

Summary of the Flow

  1. User clicks “Move Item” button → Opens pre-filled form.

  2. User selects “To Container” & quantity → Saves the form.

  3. A bot triggers on form submission → Updates/decreases the old item.

  4. The bot checks for an existing item in the new container:

• If found → increases quantity.

• If not → creates a new row.

This ensures efficient inventory tracking while avoiding duplicate items.

Would you like help setting up the bot actions in AppSheet?