Adding a value from one Table to another?

[do2]

Hi everyone, I need help with adding a number from one table to another table.

This is a simple scenario, I'll give you an example for my requirement.

I have 2 tables. Inventory and Incoming Products

Inventory : Shows the quantity of items in stock.
Incoming Product : Shows the quantity of incoming products.

When I select a row from Incoming Products table and mark it as "Received" using a button, I want the product count in Inventory table to be increased by the amount (by matching the ItemID)

I'm quite new to platform and learning as I go, I currently have included a status column to Incoming Products table and managed to mark it as received. Not quite sure how to go about with adding the two values up in the main Inventory table.

A detailed explanation will be greatly helpful and appreciated.

[minijohn]

Hey @do2, r2 here

I'm assuming these are two individual queries that populate the inventory and incoming_products tables.

• Create a new JS query, triggered by the "Received" button
• filter for the correct itemId and take the QTY from the incoming_products table, then add the inventory Qty and commit to the DB.

that would look something like this:

// new JS query triggered by button

var inventoryItem = inventory_table.selectedRow;
var existingQty = incoming_products.table.filter(row => row.ItemID == inventoryItem.ItemID)[0].Qty;
var newQty = (existingQty + inventoryItem.Qty);

// create an update_db_query, you can use `{{qty}} / {{itemId}}` in your query to access the scope
update_incoming_products_query.trigger({
  additionalScope: {
    itemId: inventoryItem.ItemId,
    qty: newQty
  }
})

Aprox this :)) Would that work for you?

[do2]

Hi r2

Thank you for the response, I've tried it but the method to write it back to the inventory_table did not work.

not exactly sure how and where to trigger the update_db_query you have mentioned.

Apologies as I'm new to all these and retool.

[minijohn]

No worries :))

So assuming your two tables have been populated (meaning the queries getting that data have loaded).

Create a new "update" db query for your incoming_products table.

Then, create a new JS query with the code of the previous answer and run it. What do you get?

[griffxbio]

Thanks but how would this look if you wanted to find and replace all items in the inventory list with the new values from the incoming table and append the new entries to the bottom? This would be a great help - especially for those who want to bulk update table based on two primary keys.

[Kabirdas]

Hey @griffxbio!

Sorry about the late reply here, posting in case this is still something you're looking into!

SQL resources in Retool typically have a bulk upsert via primary key option. To use that, you'll just need to construct an array of rows you'd like to update or insert which can be done using Query JSON with SQL and something like the following:

SELECT ItemID, Item, SUM(Qty) AS Qty 
FROM (
  SELECT * FROM {{formatDataAsArray(inventory_table.data)}} 
  UNION ALL CORRESPONDING 
  SELECT * FROM {{formatDataAsArray(incoming_products_table.data)}}
) 
GROUP BY ItemID, Item

You can then reference that result in your update query.

Does that work for you?