Help

Re: Creating a Order Merging automation Script

281 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Yannick_Flink
5 - Automation Enthusiast
5 - Automation Enthusiast

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



1 Reply 1
Marcelo
6 - Interface Innovator
6 - Interface Innovator

i can help with that using make.com

it can be donde by searching records and the aggregate them and group by "field xxxx".

then you can do some other modifications with the result.

here you have some screenshots of a work i've done. It's in spanish, but you can see the idea.

Marcelo_0-1706119473062.png

Marcelo_1-1706119488706.png