Script not running functions, console log test working

[Ali_Pirani]

The code below is really long, sorry about that, I’m still cleaning up the functions.

My script isn’t running, it goes through the code as the console log tests return but none of the functions are working.

The last function I created was an async function called revExp that I’ve commented out incase that was the reason but it still isn’t working.

Any help would be appreciated.

let table = base.getTable('MASTER');
let tableTwo = base.getTable('Truck Breakdown');
let view = table.getView('Master View');
let viewTwo = tableTwo.getView('Grid view');
let tableThree = base.getTable('Customer Breakdown');
let viewThree = tableThree.getView('Grid view');

//records from Master file
let result = await view.selectRecordsAsync();
//records from By truck file
let resultTwo = await viewTwo.selectRecordsAsync();
//records from Customer breakdwon file
let resultThree = await viewThree.selectRecordsAsync();

const truck = "Truck No", customer = "Customer";

//read array for Truck No
function readArray(inputResult, typeOf)
{
    let i = 0, returnArray = [];

    for(let record of inputResult.records)
    {
        returnArray[i] = record.getCellValueAsString(typeOf);
        i++;
    }
    return returnArray;
}

async function currentLargerThanMaster(current, newVal, resultNeeded)
{
    for(let j = 0, k = current.length; j < k; j++)
    {
        for(let l = 0, m = newVal.length; l < m; l++)
        {
            if(newVal[l] === current[j])
                {
                    let recordId = resultNeeded.records[j].id;
                    await tableThree.deleteRecordAsync(recordId);
                }
        }
    }
}

async function addNewToCurrent(newVal, typeOf, tableNumber)
{
    for(let j = 0, k = newVal.length; j < k; j++)
    {
        await tableNumber.createRecordAsync({typeOf: newVal[j]});
    }
}

// async function revExp(current, inputResult, typeOf, table)
// {
//     let expense, revenue, rec = 0;

//     for(let i = 0; i < current.length; i++)
//     {
//         for(let record of inputResult.records)
//         {
//             if(record.getCellValueAsString(typeOf) == current[i])
//             {
//                 if(record.getCellValueAsString("Trans. Type") == "Expense")
//                 {
//                     expense = record.getCellValue("Amnt") + expense;
//                     await table.updateRecordAsync(inputResult.records[i].id, {"Expense": expense});
//                 }

//                 else if(record.getCellValueAsString("Trans. Type") == "Revenue")
//                 {
//                     revenue = record.getCellValue("Amnt") + revenue;
//                     await table.updateRecordAsync(inputResult.records[i].id, {"Revenue": revenue});
//                 }

//                 if(record.getCellValueAsString("Receivable") == "Yes")
//                 {
//                     rec = record.getCellValue("Amnt") + rec;
//                     await table.updateRecordAsync(inputResult.records[i].id, {"Receivable": rec});
//                 }
//             }
//         }

//         expense = 0, revenue = 0, rec = 0;
//     }
// }

//fix this function. Does not make sense.
function byTruckSheet()
{
    //Remove value from current sheet as it does not exist in master file
    if(currentCustomer.length > customerName.length)
    {
        currentLargerThanMaster(currentCustomer, newValue, resultThree);
    }

    //add value to customer sheet as name does not exist
    else if(currentCustomer.length <= customerName.length)
    {
        addNewToCurrent(newValue, customer, tableThree);
    }

    if(currentIDs.length > driverID.length)
    {
        currentLargerThanMaster(currentIDs, newValue, resultTwo);
    }

    else if(currentIDs.length <= driverID.length)
    {
        addNewToCurrent(newValue, truck, tableTwo);
    }
}

//comapres value in a single array and returns unique values
const uniqueSingleArray = (readArray) =>
{
    let returnArray = [];

    for (var i=0, l=readArray.length; i<l; i++)
    {
        if (returnArray.indexOf(readArray[i]) === -1 && readArray[i] !== '')
        {
            returnArray.push(readArray[i]);
        }
    }
    return returnArray;
}

//return unique contents
const haveSameContents = (masterArray, currentArray) => 
{
    let retArray = [], i = 0;

    for (const v of new Set([...currentArray, ...masterArray]))
    {
        if (masterArray.filter(e => e === v).length !== currentArray.filter(e => e === v).length)
        {
            retArray[i] = v;
            i++;
        }
    }
    return retArray;
}

console.log("Test");

//read driver IDs from Master file
let driverID = readArray(result, truck);
//return uniqe driver IDs from master file
driverID = uniqueSingleArray(driverID);

//read current driver IDs in truck file
let currentIDs = readArray(resultTwo, truck);

//compare Master and truck sheet for same values
let newValue = haveSameContents(driverID, currentIDs);

//read customer name from Master file
let customerName = readArray(result, customer);
customerName = uniqueSingleArray(customerName);

//read customer name from current sheet
let currentCustomer = readArray(resultThree, customer);

//compare master and customer sheet for same values
let newCustomer = haveSameContents(customerName, currentCustomer);

//Need to fix
byTruckSheet();

console.log("test 2");

//START OF PART 2
resultThree = await viewThree.selectRecordsAsync();
resultTwo = await viewTwo.selectRecordsAsync();
result = await view.selectRecordsAsync();

// read new reesults from current sheet
currentIDs = readArray(resultTwo, truck);
currentCustomer = readArray(resultThree, customer);

// revExp(currentIDs, result, truck, tableTwo);
// revExp(currentCustomer, resultThree, customer, tableThree);

console.log("Test 3");

//let fieldName = "Ali";

////const fieldId = await tableTwo.createFieldAsync(fieldName, "multilineText");

[Rose_Haft1]

What is the error?

Have you tried troubleshooting it and commenting out sections to see if it runs?

When that happens to me, I put chunks of the code between

/* */

and it helps me to find the line where there’s an error.

[Ali_Pirani]

I did try it and still wasn’t able to figure it out. But I have since then updated the code. Here is the updated code:

Ive changed the byTruckSheet function with writeToFile

let table = base.getTable('MASTER');
let tableTwo = base.getTable('Truck Breakdown');
let view = table.getView('Master View');
let viewTwo = tableTwo.getView('Grid view');
let tableThree = base.getTable('Customer Breakdown');
let viewThree = tableThree.getView('Grid view');

//records from Master file
let result = await view.selectRecordsAsync();
//records from By truck file
let resultTwo = await viewTwo.selectRecordsAsync();
//records from Customer breakdwon file
let resultThree = await viewThree.selectRecordsAsync();

const truck = "Truck No", customer = "Customer";

//read array for Truck No
function readArray(inputResult, typeOf)
{
    let i = 0, returnArray = [];

    for(let record of inputResult.records)
    {
        returnArray[i] = record.getCellValueAsString(typeOf);
        i++;
    }
    return returnArray;
}

async function currentLargerThanMaster(current, newVal, resultNeeded, table)
{
    for(let j = 0, k = current.length; j < k; j++)
    {
        for(let l = 0, m = newVal.length; l < m; l++)
        {
            if(newVal[l] === current[j])
                {
                    let recordId = resultNeeded.records[j].id;
                    await table.deleteRecordAsync(recordId);
                }
        }
    }
}

async function addNewToCurrent(newVal, typeOf, tableNumber)
{
    for(let j = 0, k = newVal.length; j < k; j++)
    {
        await tableNumber.createRecordAsync({typeOf: newVal[j]});
    }
}

async function revExp(current, inputResult, typeOf, table)
{
    let expense, revenue, rec = 0;

    for(let i = 0; i < current.length; i++)
    {
        for(let record of inputResult.records)
        {
            if(record.getCellValueAsString(typeOf) == current[i])
            {
                if(record.getCellValueAsString("Trans. Type") == "Expense")
                {
                    expense = record.getCellValue("Amnt") + expense;
                    await table.updateRecordAsync(inputResult.records[i].id, {"Expense": expense});
                }

                else if(record.getCellValueAsString("Trans. Type") == "Revenue")
                {
                    revenue = record.getCellValue("Amnt") + revenue;
                    await table.updateRecordAsync(inputResult.records[i].id, {"Revenue": revenue});
                }

                if(record.getCellValueAsString("Receivable") == "Yes")
                {
                    rec = record.getCellValue("Amnt") + rec;
                    await table.updateRecordAsync(inputResult.records[i].id, {"Receivable": rec});
                }
            }
        }

        expense = 0, revenue = 0, rec = 0;
    }
}

//fix this function. Does not make sense.
//current, master, new, result, table
async function writeToFile(current, master, newVal, inputResult, inputTable, typeOf)
{
    //Remove value from current sheet as it does not exist in master file
    if(current.length > master.length)
    {
        currentLargerThanMaster(current, newVal, inputResult, inputTable);
    }

    else if(current.length <= master.length)
    {
        addNewToCurrent(newVal, typeOf, inputTable)
    }
}

//comapres value in a single array and returns unique values
const uniqueSingleArray = (readArray) =>
{
    let returnArray = [];

    for (var i=0, l=readArray.length; i<l; i++)
    {
        if (returnArray.indexOf(readArray[i]) === -1 && readArray[i] !== '')
        {
            returnArray.push(readArray[i]);
        }
    }
    return returnArray;
}

//return unique contents
const haveSameContents = (masterArray, currentArray) => 
{
    let retArray = [], i = 0;

    for (const v of new Set([...currentArray, ...masterArray]))
    {
        if (masterArray.filter(e => e === v).length !== currentArray.filter(e => e === v).length)
        {
            retArray[i] = v;
            i++;
        }
    }
    return retArray;
}

console.log("Test");

//read driver IDs from Master file
let driverID = readArray(result, truck);
//return uniqe driver IDs from master file
driverID = uniqueSingleArray(driverID);
console.log(driverID);

//read current driver IDs in truck file
let currentIDs = readArray(resultTwo, truck);
console.log(currentIDs);

//compare Master and truck sheet for same values
let newValue = haveSameContents(driverID, currentIDs);
console.log(newValue);

//read customer name from Master file
let customerName = readArray(result, customer);
customerName = uniqueSingleArray(customerName);

//read customer name from current sheet
let currentCustomer = readArray(resultThree, customer);

//compare master and customer sheet for same values
let newCustomer = haveSameContents(customerName, currentCustomer);

//current, master, newVal, inputResult, inputTable, typeOf
//Need to fix
writeToFile(currentIDs, driverID, newValue, resultTwo, tableTwo, truck);
writeToFile(currentCustomer, customerName, newValue, resultThree, tableThree, customer);

console.log("test 2");

//START OF PART 2
resultThree = await viewThree.selectRecordsAsync();
resultTwo = await viewTwo.selectRecordsAsync();
result = await view.selectRecordsAsync();

// read new reesults from current sheet
currentIDs = readArray(resultTwo, truck);
currentCustomer = readArray(resultThree, customer);

revExp(currentIDs, result, truck, tableTwo);
revExp(currentCustomer, resultThree, customer, tableThree);

console.log("Test 3");

//let fieldName = "Ali";

////const fieldId = await tableTwo.createFieldAsync(fieldName, "multilineText");



    // if(currentCustomer.length > customerName.length)
    // {
    //     currentLargerThanMaster(currentCustomer, newValue, resultThree, tableThree);
    // }
        // //add value to customer sheet as name does not exist
    // else if(currentCustomer.length <= customerName.length)
    // {
    //     addNewToCurrent(newValue, customer, tableThree);
    //     console.log("Fucntion ",currentCustomer);
    // }


    // if(currentIDs.length > driverID.length)
    // {
    //     currentLargerThanMaster(currentIDs, newValue, resultTwo, tableTwo);
    // }

    // else if(currentIDs.length <= driverID.length)
    // {
    //     addNewToCurrent(newValue, truck, tableTwo);
    // }

[Rose_Haft1]

Without the tables and knowing what you want to achieve with the functions, it is hard to help.

[Ali_Pirani]

How can I share my tables?

basically this script is enabling me to take data from my master table whilst reading the truck numbers and customer names.

After which the script only pulls unique values from the truck and customer fields.

Then uses each unique value to iterate through the master table and adds up all the expenses, revenues and receivables related to each unique value from the customer and truck no records and displays them in respective tables namely “truck breakdown” and “Customer breakdown”

[Ali_Pirani]

I found one error

async function addNewToCurrent(newVal, typeOf, tableNumber)
{
console.log(“addNew function new val and size”, newVal, newVal.length);
for(let j = 0; j < newVal.length; j++)
{
console.log(“for loop in addNew working”, j);
await tableNumber.createRecordAsync({typeOf: newVal[j]});
}
}

the for loop is only running once, even though the newVal is 5.

[Justin_Barrett]

Ali_Pirani:

async function addNewToCurrent(newVal, typeOf, tableNumber) {
  console.log(“addNew function new val and size”, newVal, newVal.length);
  for(let j = 0; j < newVal.length; j++) {
    console.log(“for loop in addNew working”, j);
    await tableNumber.createRecordAsync({typeOf: newVal[j]});
  }
}

Not sure why that loop isn’t working. The structure looks solid, so the only other reason that I can think of would be that there’s only one item in the array. That aside, there are some things to fix/optimize here.

First, there’s an easier way to create the new records by looping directly through the array instead of using the indexing method:

async function addNewToCurrent(newVal, typeOf, tableNumber) {
  for(let item of newVal) {
    await tableNumber.createRecordAsync({typeOf: item});
  }
}

That said, this optimization still contains another flaw. It looks like you want to use the values of the truck and customer variables as field names, passing them through to this function using the variable typeOf. The problem is that by using the literal name typeOf, JavaScript thinks that the field name is typeOf. To use the value of the variable as the field name, wrap the variable name in square braces. This version fixes that problem:

async function addNewToCurrent(newVal, typeOf, tableNumber) {
  for(let item of newVal) {
    await tableNumber.createRecordAsync({[typeOf]: item});
  }
}

With that done, there’s still one more way to optimize this. Instead of waiting for each record to be created individually, store the new record data as an array of objects and create the records as a batch using createRecordsAsync.

async function addNewToCurrent(newVal, typeOf, tableNumber) {
  let newRecords = newVal.map(item => { return {fields: {[typeOf]: item}} })
  while (newRecords.length) await tableNumber.createRecordsAsync(newRecords.splice(0, 50))
}

[Ali_Pirani]

Thank you for you input Justin.

I never had any formal training in Java and your optimization method helps a lot!

The map function seems overwhelming but the for(let item of newVal) method is what I will be using going forward.

Furthermore, I did end up fixing the error with the help of airtable support.

The loop suddenly started working, so no idea what happened there. But the output of data wasn’t displaying as I had to wrap typeOf in brackets [ typeOf ] to use it as a computed property name.

Appreciate your help!

[Justin_Barrett]

Ali_Pirani:

I never had any formal training in Java and your optimization method helps a lot!

To clarify, Airtable uses JavaScript, not Java. While the two languages both contain the word “Java”, that’s their only commonality. JavaScript is not derived from Java.