Decrement variable value using JS

[BenDeLevi]

I am trying to write a JS to run a query X times. The number X is manually entered by the user in numberInput133.value.

Each time the query runs, I want the X to decrement and be added in a table as a value along with some other values in my table. I used a 'variable' for this.

The script makes the query run X times but it doesn't change the value of the variable.

here is the query:

INSERT INTO energy_bank ("site_id", "ess_id", "bank_index")
VALUES
  (
  (SELECT "id" FROM site WHERE "name" = {{select13.value}}),
  (SELECT "id" FROM ess WHERE "name" = {{variable_ess2.value}}),
  {{variable_ess2_bank.value}}
)
RETURNING
  "id";

Here is the Java Script:

let ess2_bank = parseInt(numberInput133.value);

while (ess2_bank > 0){
  console.log(ess2_bank);
  ess2_energy_bank.trigger() ; 
  await variable_ess2_bank.setValue(ess2_bank);
  ess2_bank -= 1;
  console.log(ess2_bank)}

Does anyone have any suggestions on how I can make it work?

[Oscar_Ortega]

Hello, checking the following checkbox works for you in your JS query?

It makes the query to get the current value of the variable each time you call them.

[kschirrmacher]

This might be a good use case for additionalScope as well. Rather than setting a variable with each loop, pass the value directly to your DB query.

let ess2_bank = parseInt(numberInput133.value);

while (ess2_bank > 0){
  ess2_energy_bank.trigger({
additionalScope: {
   bankIndex: ess2_bank
}}) ; 
  ess2_bank -= 1;

and then in your SQL

INSERT INTO energy_bank ("site_id", "ess_id", "bank_index")
VALUES
  (
  (SELECT "id" FROM site WHERE "name" = {{select13.value}}),
  (SELECT "id" FROM ess WHERE "name" = {{variable_ess2.value}}),
  {{bankIndex}}
)
RETURNING
  "id";

[BenDeLevi]

In the SQL query, I get the error "bankIndex is not defined"

[BenDeLevi]

Same results. The values don't decrement. It just inserts the lowest value into all rows.

[Oscar_Ortega]

@BenDeLevi

let ess2_bank = parseInt(numberInput133.value);

while (ess2_bank > 0){
  console.log(ess2_bank);
  ess2_energy_bank.trigger() ; // 1. trigger query, bank_id will be current variable value or null if empery
  await variable_ess2_bank.setValue(ess2_bank); // 2. set value
  ess2_bank -= 1; // 3. decrement value
  console.log(ess2_bank)}

i think it should be:

let ess2_bank = parseInt(numberInput133.value);
while (ess2_bank > 0){
  console.log(ess2_bank);
  await variable_ess2_bank.setValue(ess2_bank);
  await ess2_energy_bank.trigger();
  ess2_bank -= 1;
  console.log(ess2_bank)}

[dcartlidge]

This is the issue, you need to await the trigger before continuing - otherwise the code will trigger 5 times and set the variable 5 times but setting the variable happens before the trigger completes so it'll take the value at the execution time which will be the lowest value.
ie setting a variable takes a few ms, updating the DB takes a couple of seconds

Personally, I'd not use await though and would use the additionalScope option here but ideally I would bypass this loop completely and do a single command using the bulk insert option for the query.

[BenDeLevi]

Thank you!! it worked as expected. I did some smoke testing and all had the proper values.

[Oscar_Ortega]

If you want to use additionalScope you need to define those scopes, otherwise you will get that error:

[kschirrmacher]

My apologies, I accidentally put the decrement outside of the loop.

I'd still argue that you don't need to use a state variable and await here. The query doesn't depend on the result of the previous one, so it's fine to just generate the query as the index is decremented. See here:

let ess2_bank = parseInt(numberInput133.value);
console.log(numberInput133)

while (ess2_bank > 0){
  console.log(ess2_bank);
  query5.trigger({
    additionalScope: {
      bankIndex: ess2_bank
    }
  }) ; 
  ess2_bank -= 1;
}

console.log(`Logging bankIndex(${bankIndex}) from the other query`)

You can see that the ops happen out of order but it doesn't matter, each instance of the query receives the correct index by using additionalScope.

Ultimately @dcartlidge is right, if the starting value is on the larger side it's best to generate an array of objects to upload in a single DB query. JS query to return insert object and then fire the query with a success handler.