Array ForEach, but queries get executed only once

[mbruijnpff]

Hi,

I'm trying to automate some manual labour by fetching data from various sources, combining these and offer it to the user in an excel/csv file.

I'm more Python minded than JS, but I've given it a try....and I failed.

The user will paste a range of comma separated codes in a textfield, these are getting split and an array is being created to work with.

Then I iterate over the array and per code I'll have to gather data from various sources. Some sources will depend on the output of another source, so the order of the queries is important.
So far so good, that I'm able to loop through the array and for each I execute a query and add the result to the json output.
However, it seems that the queries are only triggered once and not for each. The output in the json shows the resulting data from the first code for each other code.

My guess is that things are being executed simultaneously and not after each other.
See below my code and also the result. How to get this working properly? Do I need Promises/Awaits? Not sure how to use these.

const array = textPasteMesc.value.split(',')

const json = []

array.forEach(item => {
  getCcode.trigger({
    additionalScope: {
      mescno : parseInt(item)
    }
  })
  
  if (getCcode.data.ccode1 != "") {
    getCcodeDetails.trigger({
      additionalScope: {
        ccode : getCcode.data.ccode1
      }
    })
  }
  
  json.push({ mescno: item, ccode1: getCcode.data.ccode1['0'], ccode1_descript: getCcodeDetails.data['0'].DESCRIPT, ccode1_qty: getCcodeDetails.data['0'].QTY_STK_AVAIL, ccode2: getCcode.data.ccode2['0'], ccode2_descript: getCcodeDetails.data['0'].DESCRIPT, ccode2_qty: getCcodeDetails.data['0'].QTY_STK_AVAIL,});
});

return json;

Output

▶
0:{} 7 keys
mescno:"7430050691"
ccode1:"001.001.001.004.009.001.002.001"
ccode1_descript:"Seamless pipe PE SRL 1/2" S80 A106B Table Y-1 EN10204/3.1 "
ccode1_qty:"1693.64"
ccode2:null
ccode2_descript:"Seamless pipe PE SRL 1/2" S80 A106B Table Y-1 EN10204/3.1 "
ccode2_qty:"1693.64"
▶
1:{} 7 keys
mescno:"7430050281"
ccode1:"001.001.001.004.009.001.002.001"
ccode1_descript:"Seamless pipe PE SRL 1/2" S80 A106B Table Y-1 EN10204/3.1 "
ccode1_qty:"1693.64"
ccode2:null
ccode2_descript:"Seamless pipe PE SRL 1/2" S80 A106B Table Y-1 EN10204/3.1 "
ccode2_qty:"1693.64"
▶
2:{} 7 keys
mescno:"7430050181"
ccode1:"001.001.001.004.009.001.002.001"
ccode1_descript:"Seamless pipe PE SRL 1/2" S80 A106B Table Y-1 EN10204/3.1 "
ccode1_qty:"1693.64"
ccode2:null
ccode2_descript:"Seamless pipe PE SRL 1/2" S80 A106B Table Y-1 EN10204/3.1 "
ccode2_qty:"1693.64"

EDIT: according to the debug window, the queries actually do get executed each time, it's just the wrong result being added to the json. How to solve that?

▶
loopArray ran successfully (0.012s).
loopArray

▶
getCcode ran successfully (0.461s).
getCcode

▶
getCcode ran successfully (0.572s).
getCcode

▶
getCcode ran successfully (0.56s).
getCcode

▶
getCcodeDetails ran successfully (1.044s).
getCcodeDetails

▶
getCcodeDetails ran successfully (1.343s).
getCcodeDetails

▶
getCcodeDetails ran successfully (1.338s).
getCcodeDetails

[pyrrho]

I think you are correct in that the final json push is using data from previous calls of the getCcode event and not the latest results from the continuing loop. I believe you do want to use 'await' on any triggers in the loop.

array.forEach(item => {
  let data = await getCcode.trigger({
    additionalScope: {
      mescno : parseInt(item)
    }
  })
  
  if (data.ccode1 != "") {
    let details = await getCcodeDetails.trigger({
      additionalScope: {
        ccode : data.ccode1
      }
    })
  }
  
  json.push({ mescno: item, ccode1: data.ccode1['0'], ccode1_descript: details.data['0'].DESCRIPT, ccode1_qty: details.data['0'].QTY_STK_AVAIL, ccode2: data.ccode2['0'], ccode2_descript: details.data['0'].DESCRIPT, ccode2_qty: details.data['0'].QTY_STK_AVAIL,});
});

I used data and details as the variables to call upon for your additional trigger and final JSON push.

ETA: you probably would also want to null check the pushed object for when there are no details to use

[mbruijnpff]

Hi @pyrrho , thanks,

but just adding "await" doesn't seem to work. I get an error that 'await' is not defined.
to use await, shouldn't I need an async function with a promise?

I will add some checks, although null values can occurs and are fine as long as they aren't required for other queries.

[pyrrho]

You will need to wrap it in an async function as described in this thread:

How to use await in Promises?

Hey @rolandapercur, you would need to wrap your script in an async function where await is permitted. Try this: (async () => { var array = table15.data; for (let i = 0; i < array.length; i++) { await new Promise((resolve) => { get_did_pagex7.trigger({ additionalScope: { criteria4: array[i].Dealer_s_Id_associ_s.id }, onSuccess: (data) => { resolve(data); } }) }); }; })(); Does this work for you?

[mbruijnpff]

I'm still struggling with this. Read about half the internet regarding async functions, awaits and promises and tried a alot already with varying results.

I did get that far that I've created a async function that actually seems to be triggered. Except tha the function doesn't return anything. Or maybe it doesn't wait for the first trigger to finish?

This is my code now. (textPasteMesc.value = 7430050281,7430050691,7430050181)

const array = textPasteMesc.value.split(',')

const json = []

async function queryCcode(mescno) {
  try {
    const queryResult = await getCcode.trigger({
      additionalScope: {
        mescno: parseInt(mescno)
      }
    });
    console.log(queryResult)
    return queryResult
  } catch (error) {
    console.error("An error occurred:", error);
  }
}

array.forEach(item => {
  let ccodes = queryCcode(item)
  console.log(ccodes)
  if (ccodes.ccode1 != "") {
    let details1 = getCcodeDetails.trigger({
      additionalScope: {
        ccode : ccodes.ccode1
      }
    })
  }
  
  if (ccodes.ccode2 != "") {
    let details2 = getCcodeDetails.trigger({
      additionalScope: {
        ccode : ccodes.ccode2
      }
    })
  }
  /* This probably doesn't work as details1 and details 2 aren't global. But first get the function to work.
  json.push({ mescno: parseInt(item), mesc_desc: ccodes.usc_short_desc['0'], ccode1: ccodes.ccode1['0'], ccode1_descript: details1.DESCRIPT, ccode1_qty: parseFloat(details1.QTY_STK_AVAIL), ccode2: ccodes.ccode2['0'], ccode2_descript: details2.DESCRIPT, ccode2_qty: parseFloat(details.QTY_STK_AVAIL), smartlist_price : ccodes.smart_price['0'], leadtime: ccodes.leadtime['0']});
*/
});

return json;

This is the console output:

So basically, for each mesccode some details need to be fetched by getCcode, which might return ccode1 and ccode2.
If those are returned, than for each of those ccodes getCcodeDetails needs to be triggered, which could return some other details.

In the end, I want to combine all that data in a singles json object.

Any help would be welcome.

@Tess, why has this topic been unlisted?

[matth]

You've entered a fairly challenging aspect of retool imo. I've battled with the recently.

Check out this post it may help

Best Practices for Synchronous Form Submission

Welcome to the forum @bentley You are on the right track, but you found an old post with an old way of doing it. Here is a query I just wrote today that used the correct pattern (with some comments added) // Make the data I am going to pass to the query let lineItems = [] tblInventory.selectedRows.forEach(item => { lineItems.push({ po_id: selMakePO_POs.selectedItem.po_id, sku: item.sku, date_checkedin: moment().format("YYYY-MM-DD HH:mm:ss") }) }) console.log(lineItems) // f…

Also with js query see the advanced setting "Keep variable references inside the query in sync with your app" if using setIn or setValue

[mbruijnpff]

Hi,

I was able to solve it after some help from @joeBumbaca during Discord Office Hours on Thursday.

This is what is working.

const array = createArray.value;

const stepSize = 100 / array.length
let progressCount = 0
progress.setValue(0)

const promises = array.map(async (item) => {
  const ccodes = await getCcode.trigger({
    additionalScope: {
      mescno: parseInt(item),
    },
  });

  let ccode1_details = null;
  let ccode2_details = null;

  if (ccodes.ccode1 !== '') {
    const details = await getCcodeDetails.trigger({
      additionalScope: {
        ccode: ccodes.ccode1[0],
      },
    });
    ccode1_details = details[0];
  }

  if (ccodes.ccode2 !== '') {
    const details = await getCcodeDetails.trigger({
      additionalScope: {
        ccode: ccodes.ccode2[0],
      },
    });
    ccode2_details = details[0];
  }
  
  progressCount = progressCount + stepSize
  progress.setValue(progressCount)
  
  return {
    mescno: parseInt(item),
    mesc_desc: ccodes.usc_short_desc[0],
    ccode1: ccodes.ccode1[0],
    ccode1_descript: ccode1_details ? ccode1_details.DESCRIPT : null,
    ccode1_qty: ccode1_details ? parseFloat(ccode1_details.QTY_STK_AVAIL) : null,
    ccode1_pffcost: ccode1_details ? parseFloat(ccode1_details.CUSTOM_STOCK_COST) : null,
    ccode2: ccodes.ccode2[0],
    ccode2_descript: ccode2_details ? ccode2_details.DESCRIPT : null,
    ccode2_qty: ccode2_details ? parseFloat(ccode2_details.QTY_STK_AVAIL) : null,
    ccode2_pffcost: ccode2_details ? parseFloat(ccode2_details.CUSTOM_STOCK_COST) : null,
    smart_price: ccodes.smart_price['0'],
    leadtime: ccodes.leadtime['0'],
  };

});

const json = await Promise.all(promises);

return json;

Massive speed improvement using async.
I'm going to try to speed things up further by querying the PostgresDB for all the ccodes at once , so it doesn't need to make separate queries and then merge/join it using JS.