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