Skip to main content
Solved

Order Merging Script Automation

  • January 24, 2024
  • 1 reply
  • 0 views
Y

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? 

Best answer by Yannick_Flink

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();
View original
Did this topic help you find an answer to your question?

1 reply

Y
  • Yannick_Flink
  • Author
  • New Participant
  • 2 replies
  • Answer
  • January 24, 2024

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();

Reply


Most helpful members this week

Terms & Conditions