Skip to main content

Hello,

I wrote this code and it works perfectly as I want in Script extension but in Automation it is a mess. 

The first part of the code works fine in automation but not the second part, so only the second part needs to be adjusted. Here is the whole code :

 

// first part of the cod
let poTable = base.getTable('PO');
let transpoTable = base.getTable('Output');
let poRecords = await poTable.selectRecordsAsync();
let transpoRecords = await transpoTable.selectRecordsAsync();
let artikelDatumQtyMap = {};

for (let poRecord of poRecords.records) {
  let artikelID = poRecord.getCellValue('Artikel ID');
  let eta = poRecord.getCellValue('ETA');
  let qty = poRecord.getCellValue('Qty');

  if (artikelID) {
    let artikelIDMatch = artikelID.match(/^(\d+)$/);
    if (artikelIDMatch) {
      let artikelIDNumeric = artikelIDMatch[1];
      let artikelColumn = `I ${artikelIDNumeric}`;
      if (!artikelDatumQtyMap[eta]) {
        artikelDatumQtyMap[eta] = {};
      }
      if (!artikelDatumQtyMap[eta][artikelColumn]) {
        artikelDatumQtyMap[eta][artikelColumn] = 0;
      }
      artikelDatumQtyMap[eta][artikelColumn] += qty;
    }
  }
}

let transpoUpdates = [];

for (let transpoRecord of transpoRecords.records) {
  let datum = transpoRecord.getCellValue('Datum');
  let updateFields = artikelDatumQtyMap[datum];

  if (updateFields && datum) {
    await transpoTable.updateRecordAsync(transpoRecord, updateFields);
  }
}

// Second part of the code
let columnNames = transpoTable.fields.map(field => field.name);

for (let columnName of columnNames) {
  if (columnName.startsWith('I ')) {
    let recordsArray = transpoRecords.records;
    let firstNonEmptyCell = recordsArray.find(record => record.getCellValue(columnName));
    if (firstNonEmptyCell) {
      let firstValue = firstNonEmptyCell.getCellValue(columnName);
      let updates = [];
      let total = 0;

      for (let i = 0; i < recordsArray.length; i++) {
        let transpoRecord = recordsArray[i];
        let cellValue = transpoRecord.getCellValue(columnName);

        if (cellValue !== null) {
          total += parseFloat(cellValue);
        }

        updates.push({
          id: transpoRecord.id,
          fields: {
            [columnName]: i >= recordsArray.indexOf(firstNonEmptyCell) ? total : null
          }
        });

        if (updates.length >= 50 || i === recordsArray.length - 1) {
          await transpoTable.updateRecordsAsync(updates);
          updates = [];
        }
      }
    }
  }
}
 
 
 
 
The result that I want and that I get in the extension is this :

 

but I get this when I run it in automation: 

 

I noticed that the extension and automation doesnt run the same way but i don't know what to change to make it work like in the extension. 

 

Thank you 🙂 

I don’t see anything in your code to explain the different results. But your code is a little hard to read, so I cannot say for sure. For example, you declare the variable transpoUpdates, but I cannot see where you use it. You also have an async call insides a loop, which sometimes causes problems, but doesn’t seem to be the issue here. Could you have different sorting or filtering going on in your views to account for the differences)

I don’t see anything in your code to explain the different results. But your code is a little hard to read, so I cannot say for sure. For example, you declare the variable transpoUpdates, but I cannot see where you use it. You also have an async call insides a loop, which sometimes causes problems, but doesn’t seem to be the issue here. Could you have different sorting or filtering going on in your views to account for the differences)


Hi, yes true you are right I dont need this variable, I removed it but it changes nothing to the result

I didn't expect removing the variable to help. I was just pointing it out as an example of why the code is hard to read. There are other reasons why I found the code hard to read, but that are more complex to explain.

Have you looked into the view filters?

I didn't expect removing the variable to help. I was just pointing it out as an example of why the code is hard to read. There are other reasons why I found the code hard to read, but that are more complex to explain.

Have you looked into the view filters?


I have done with and without filters, and it changed nothing, I cannot understand why it puts the result so randomly in the table 

Reply


Terms & Conditions