How save qty in a variable after i change status?

[agaitan026]

Hi i got the following app

this table have actual quantities for each product and thats the one that should be updated with the new quantities sum up the original + new

when i change status to 'Recibida' how i can save the quantity (table may have 1 or more records or lines for sure) in the table above? to save it in a variable and use it in another insert query. But it should happen only when status = Recibida, if status changes to other it should run another query too.

At the end, the main idea is: to save qty for each product and modify the original qty in product table so if had 10 before and after another 10, it should sum +10 = 20 for that product

thank you

[ScottR]

You can edit the quantity in the tables using Bulk Update - assuming here that you need to write back to the database first to update the quantity?

Write data to SQL databases

[agaitan026]

i first tried this to sum but i think im missing something on QTY_NEW for the first product

Note: nevermind my mistake i had to order and group by, now its summing ok

[agaitan026]

Im trying this

{{ [{ Productid: {{listaProductosCompra_Editar_Suma.data.product_id}} }] }}

but im wrong right?

tried this

{
[{
Productid: formatDataAsArray({{ listaProductosCompra_Editar_Suma.data.product_id }})

}]

error: * message:"Data must be an array."

[ScottR]

try
{{ formatDataAsArray(listaProductosCompra_Editar_Suma.data.product_id)}}

[agaitan026]

@ScottR

{{ formatDataAsArray(listaProductosCompra_Editar_Suma.data.product_id)}}

{
[{
id: {{ formatDataAsArray(listaProductosCompra_Editar_Suma.data.product_id)}},
quantity: {{ formatDataAsArray(listaProductosCompra_Editar_Suma.data.QTY_NEW)}}
}]
}

like this?

[agaitan026]

i tried also this

[ScottR]

You are already including the product_id as the primary key so if you are editing the table use {{yourTableName.recordUpdates}} Check out the documentation

[agaitan026]

@agaitan026

{{ formatDataAsArray(listaProductosCompra_Editar_Suma.data.QTY_NEW)}}

not sure how to use this correctly, im confused

[agaitan026]

yes but what im doing wrong? whats the best approach

[agaitan026]

should i use update and existing record only? or update and existing record with primary key

[agaitan026]

i cant use recordupdates, as im not updating table itself, just showing data from query

[agaitan026]

thats why i used this

but shows empty array

[agaitan026]

at the end i had used a transformer instead

const categorizedData = formatDataAsArray({{ listaProductosCompra_Editar_Suma.data }}).map((row) => ({id: row.id, product_id: row.product_id, qty_available: row.QTY_NEW}));

return categorizedData;

and works good