Hello I am looking for a solution to the following problem.
we have an internal Order pipeline of equipment orders coming in and we need to consolidate these Orders on a "Hub Code" which are our branches. We will then proceed with the data for picking and shipping purposes
An example of the task would look like the following:
Case 1: different Items ordered for same Hub
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"
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"
So basically the order is distributed into multiple columns (our max is 7 different items)
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"
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"
It should basically auto stacking the quantity for the same item in the Quantity Pick 1 Column and is adding both Order IDs as comma seperated.
My latest Version of a non running Chatgpt Script looks like this.
Thank you for any advice
// The table from which the data should be read
const inputTable = base.getTable("Input Rider Gear Orders");
// The table into which the data should be written
const outputTable = base.getTable("Rider Gear Output Test");
// The name of the view that shows only records with the status "Pending"
const pendingViewName = "Pending";
// Function to aggregate orders
async function aggregateOrders() {
// Object to store aggregated data
const aggregatedData = {};
// Retrieve all records from the view with status "Pending"
const view = inputTable.getView(pendingViewName);
const records = await view.selectRecordsAsync();
// Iterate through all records
for (const record of records.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");
// Check if the Hub Code already exists in the aggregated data object
if (!aggregatedData[hubCode]) {
aggregatedData[hubCode] = {};
}
// Check if the item already exists in the aggregated data object
if (!aggregatedData[hubCode][itemName]) {
aggregatedData[hubCode][itemName] = { quantity: 0, orderNumbers: [] };
}
// Aggregate the quantity
aggregatedData[hubCode][itemName].quantity += quantity;
// Save the Order Number for each item
aggregatedData[hubCode][itemName].orderNumbers.push(orderNumber);
}
// Iterate through all aggregated data and create/update the output table
for (const hubCode in aggregatedData) {
const recordData = aggregatedData[hubCode];
// Create a new record in the output table for each Hub Code
const newRecord = await outputTable.createRecordAsync({ "Hub Code": hubCode });
// Iterate through the aggregated data and update the corresponding fields
let pickNumber = 1;
const orderIds = [];
for (const itemName in recordData) {
const { quantity, orderNumbers } = recordData[itemName];
// Retrieve the fields for Pick and Quantity
const pickField = outputTable.getFieldByName(`Pick ${pickNumber}`);
const quantityField = outputTable.getFieldByName(`Quantity Pick ${pickNumber}`);
// Update the Pick and Quantity fields
await newRecord.setAsync({
[pickField.id]: itemName,
[quantityField.id]: quantity,
});
// Save the Order IDs for each item
orderIds.push(...orderNumbers);
pickNumber++;
// If more than 7 Picks are reached, create a new row in the output table
if (pickNumber > 7) {
pickNumber = 1;
}
}
// Update the Order ID fields
const orderIdsString = orderIds.join(', '); // Join Order IDs with commas
await newRecord.setAsync({ "Order ID 1": orderIdsString });
// Save the updated record
await outputTable.updateRecordAsync(newRecord);
}
}
// Run the function to aggregate orders
aggregateOrders();
