Order Merging Script Automation

[Yannick_Flink]

Hello, 
I have the following problem of not beeing able to merge Orders in our internal pipeline. 

We are getting Orders from our Hubs for Equipment, which need to be packed and shipped in batches. 
The Script should be able to merge same items ordered for the same Hub multiple times or split if different items ordered. 

I created the following 2 cases to make the request visible. 

Case 1: different items
Input Table "Input Rider Gear Orders"
View "Pending"

Order 1
Column: "Order Number" Value: "17884"
Column: "Hub Code" Value: "nl_til_west"
Column: "Full Name (from Master Data combinations)" Value: "Winterset - Male - M"
Column "Quantity Pick" Value: "1"

Order 2
Column: "Order Number" Value: "17984"
Column: "Hub Code" Value: "nl_til_west"
Column: "Full Name (from Master Data combinations)" Value: "Winterset - Male - L"
Column "Quantity Pick" Value: "1"

Output Table "Rider Gear Output Test"
Column: "Hub Code" Value: "nl_til_west"
Column: "Pick 1" Value: "Winterset - Male - M"
Column: "Order ID 1" Value: "17884"
Column: "Quantity Pick 1" Value: "1"
Column: "Pick 2" Value: "Winterset - Male - L"
Column: "Quantity Pick 2" Value: "1"
Column: "Order ID 2" Value: "17984"

Example 2: Same items ordered

Order 1 Column: "Order Number" Value: "17884"
Column: "Hub Code" Value: "nl_til_west"
Column: "Full Name (from Master Data combinations)" Value: "Winterset - Male - M"
Column "Quantity Pick" Value: "1"

Order 2
Column: "Order Number" Value: "17984"
Column: "Hub Code" Value: "nl_til_west"
Column: "Full Name (from Master Data combinations)" Value: "Winterset - Male - M"
Column "Quantity Pick" Value: "1"

Output Table "Rider Gear Output Test"
Column: "Hub Code" Value: "nl_til_west"
Column: "Pick 1" Value: "Winterset - Male - M"
Column: "Order ID 1" Value: "17884, 17984"
Column: "Quantity Pick 1" Value: "2" 

How can I make sure to achieve the stacking of orders in case of the same items? 

[Yannick_Flink · Accepted solution]

This Script is now working for us. It was an issue with the format of the Quantity fields which needed to be configured as numbers.

// Airtable Scripting Block Code

const inputTableName = "Input Rider Gear Orders";

const outputTableName = "Rider Gear Output Test";

// Funktion zum Aggregieren der Bestelldaten

async function aggregateOrders() {

    // Zugriff auf die Eingabe- und Ausgabetabellen

    const inputTable = base.getTable(inputTableName);

    const outputTable = base.getTable(outputTableName);

    // Abrufen des "Pending" Views in der Eingabetabelle

    const pendingView = inputTable.getView("Pending");

    // Abrufen der Datensätze im "Pending" View

    const pendingRecords = await pendingView.selectRecordsAsync();

    // Aggregierte Datenstruktur initialisieren

    const aggregatedData = {};

    // Durchlaufen der Datensätze im "Pending" View

    for (const record of pendingRecords.records) {

        const orderNumber = record.getCellValue("Order Number");

        const hubCode = record.getCellValue("Hub Code");

        const itemName = record.getCellValue("full name (from Master Data combinations)");

        const quantity = record.getCellValue("Quantity Pick");

        // Initialisieren der aggregierten Daten für den Hub Code

        if (!aggregatedData[hubCode]) {

            aggregatedData[hubCode] = {};

        }

        // Initialisieren der aggregierten Daten für das Item in diesem Hub Code

        if (!aggregatedData[hubCode][itemName]) {

            aggregatedData[hubCode][itemName] = {

                quantity: 0,

                orderNumbers: [],

            };

        }

        // Aggregieren von Quantity und Order Numbers

        aggregatedData[hubCode][itemName].quantity += quantity;

        aggregatedData[hubCode][itemName].orderNumbers.push(orderNumber);

    }

    // Durchlaufen aller aggregierten Daten und Erstellen/Aktualisieren der Ausgabetabelle

    for (const hubCode in aggregatedData) {

        const recordData = aggregatedData[hubCode];

        // Erstellen eines neuen Datensatzes in der Ausgabetabelle für jeden Hub Code

        const newRecord = await outputTable.createRecordAsync({ "Hub Code": hubCode });

        // Iterieren durch die aggregierten Daten und aktualisieren der entsprechenden Felder

        let pickNumber = 1;

        for (const itemName in recordData) {

            const { quantity, orderNumbers } = recordData[itemName];

            // Aktualisieren der Pick- und Quantity-Felder

            const updateFields = {

                [`Pick ${pickNumber}`]: itemName,

                [`Quantity Pick ${pickNumber}`]: quantity,

                [`Order ID ${pickNumber}`]: orderNumbers.join(', '), // Order IDs mit Komma separieren

            };

            try {

                await outputTable.updateRecordAsync(newRecord, updateFields);

            } catch (error) {

                console.error(`Error updating record: ${error.message}`);

                console.error("Record Data:", recordData);

                console.error("Update Fields:", updateFields);

            }

            pickNumber++;

            // Wenn mehr als 7 Picks erreicht sind, erstelle eine neue Zeile in der Ausgabetabelle

            if (pickNumber > 7) {

                pickNumber = 1;

            }

        }

    }

    console.log("Skript erfolgreich abgeschlossen!");

}

// Skript ausführen

aggregateOrders();