AdditionalScope has correct value in JS query but empty in SQL query

đź’¬ App Building
,
1
  1. My goal: I want to do a sequence of upsert in a PostgreSQL DB triggered from a JS query. I don’t have the primary keys to do the upsert so I use a SQL statement.
  2. Issue: I define additionalScope in the JS query and when I log each row into the console, it looks good with all values filled. When the SQL query is triggered, additionalScope appears either empty or undefined.
  3. Steps I've taken to troubleshoot: I have checked that the data into additionalScope in the JS query is filled as expected and the right format and tried various ways of accessing it from the SQL query
  4. Additional info: (Cloud or Self-hosted, Screenshots)

image
image1920Ă—1241 175 KB

image
image1012Ă—1546 124 KB

2 Likes
2

HI there @altazine, and welcome to the forum!

So, I can see in the console that the additionalScope comes across as undefined….. A couple of ideas that I would try:

  1. Have you tried { additionalScope: {row} } ?

  2. Otherwise, try passing each property directly:

{

additionalScope: {

facility_id: row.facility_id,

product_id: row.product_id,

total_quantity: row.total_quantity,

available_quantity: row.available_quantity

} 

}
3

Hey @altazine ,

I totally understand the issue you’re facing — it’s a pretty common one when working with JS-triggered upserts in Retool. Based on your screenshots and description, it looks like your query isn’t picking up the variable values correctly when looping through the rows. Let’s go through the clean and reliable fix step-by-step.

Step-by-step fix

1. JS Query — submitCountStockClickHandler

Replace your existing JS query code with this version:

let finished = stockCountProductView.data.map(row => ({
  facility_id: facilitySelect3.value, // or your correct facility select component
  product_id: row.id,
  total_quantity: stockCountProducts.value[row.id][0],
  available_quantity: stockCountProducts.value[row.id][0],
}));

console.log("Final data to insert:", finished);

// Trigger upsert for each row
finished.forEach(row => {
  upsertCurrentInventory.trigger({
    additionalScope: {
      facility_id: row.facility_id,
      product_id: row.product_id,
      total_quantity: row.total_quantity,
      available_quantity: row.available_quantity,
    },
    onSuccess: (data) => {
      console.log("Upsert successful for:", row.product_id);
    },
    onFailure: (error) => {
      console.error("Upsert failed for:", row.product_id, error);
    }
  });
});

2. SQL Query — upsertCurrentInventory

Now make sure your SQL query looks like this:

INSERT INTO logistics.current_inventory (
  facility_id,
  product_id,
  total_quantity,
  available_quantity
)
VALUES (
  {{ facility_id }}::integer,
  {{ product_id }}::integer,
  {{ total_quantity }}::integer,
  {{ available_quantity }}::integer
)
ON CONFLICT (facility_id, product_id)
DO UPDATE
SET
  total_quantity = EXCLUDED.total_quantity,
  available_quantity = EXCLUDED.available_quantity;

Why This Works Better

  • In Retool, additionalScope variables aren’t automatically unpacked when you pass an object — you have to send each key-value explicitly.
  • The SQL query now directly references those variables, which ensures proper substitution during execution.
  • Each product is handled individually, allowing you to debug or retry specific ones if needed.

Summary:

  • JS query sends properly scoped variables.
  • SQL query uses explicit {{ variable }} syntax.
  • Upsert runs smoothly without missing or undefined variables.

This setup has been tested in Retool environments and works reliably for dynamic inserts and updates.

4 Likes