Question:
Hi guys,
I have a collection like below and I’d like to sum up the same strainnames amounts into one row and display it on the page. i’m not a programmer so i’m not sure how to link this and a table you drag onto the page much less how to sum them.
Product:
Wix Editor
You probably need to be a bit more clear on what amounts you are adding to gether.
i.e. strainnamounts + strainncashamounts. ?
and do you mean duplicate all the fields or just the name and sum…
Hi,
apologies, yes both.
So it should only display on a table one row like this:
Kush | 65 | 840
Hash | 4 | 100
Sorry that not really clear as you may have typos in that or not!.
Do you mean
|trainname| strainnamounts | sum of strainnamounts + strainncashamounts|
Update: If so then try this, if not then let me know.
Possibly simplest set up.
Add a Sum field to the CMS. You do not need to enter data.
On the Page, Connect your Table to a Dataset for the CMS .
Make sure the fields connect ok. They should all just appear in the Table.
Still with the Table selected, click the Manage Table option.
Click any fields three dots ( … ) button , you do not want to display in the table and delete the field.
Add this code to the Pages Code. Make sure the IDs of the elements , table fields and dataset match yours
import wixData from 'wix-data';
$w.onReady(function () {
//== Wait for dataset to load
$w("#dataset12").onReady(() => {
//== Get all dataset items
$w("#dataset12").getItems(0, $w("#dataset12").getTotalCount())
.then((result) => {
const items = result.items;
//== Map dataset items to table rows
const tableRows = items.map((item) => ({
trainname: item.trainname,
strainnamounts: item.strainnamounts, // Match table's column ID
sum: (item.strainnamounts + item.strainncashamounts) // Match table's column ID
}));
//== Update table rows
$w("#table1").rows = tableRows;
})
.catch((err) => {
console.error("Error loading dataset items:", err);
});
});
});
Hi
I adapt one of my code (but don’t test the adaptation) :
import wixData from ‘wix-data’;
export function analyzeSalesDataByStrain(strainName) {
return wixData.query(“Sales”) // Query the “Sales” collection
.eq(“Strainname”, strainName) // Filter by the provided Strainname
.find()
.then((results) => {
let totalStrainAmount = 0;
let totalStrainCashAmount = 0;
// Iterate through sales records to sum up Strainamount and Straincashamount
results.items.forEach(saleEntry => {
totalStrainAmount += saleEntry.Strainamount ?? 0;
totalStrainCashAmount += saleEntry.Straincashamount ?? 0;
});
// Return the totals for the given Strainname
return {
strainName: strainName, // The Strainname passed as argument
totalStrainAmount, // Total sum of valid Strainamount for this strain
totalStrainCashAmount // Total sum of valid Straincashamount for this strain
};
})
.catch((err) => {
console.error("Error querying Sales data:", err);
throw new Error("Failed to analyze sales data");
});
}
Best regards
Oups I was not good. My code is only for one name in argument !
try this :
import wixData from ‘wix-data’;
export function analyzeAllSalesData() {
return wixData.query(“Sales”) // Query the entire “Sales” collection
.find()
.then((results) => {
const sales = results.items; // Fetch all records from the Sales collection
const strainTotals = {}; // Object to store totals for each Strainname
// Iterate through all sales records
sales.forEach(saleEntry => {
const strainName = saleEntry.Strainname; // Name of the strain
// Ensure the strain exists in the totals object
if (!strainTotals[strainName]) {
strainTotals[strainName] = {
totalStrainAmount: 0,
totalStrainCashAmount: 0
};
}
// Accumulate the Strainamount and Straincashamount
strainTotals[strainName].totalStrainAmount += saleEntry.Strainamount ?? 0;
strainTotals[strainName].totalStrainCashAmount += saleEntry.Straincashamount ?? 0;
});
// Convert the totals object into an array of results
const resultArray = Object.keys(strainTotals).map(strainName => ({
Strainname: strainName,
totalStrainAmount: strainTotals[strainName].totalStrainAmount,
totalStrainCashAmount: strainTotals[strainName].totalStrainCashAmount
}));
// Return the array of consolidated totals compatible with Wix Table
return resultArray;
})
.catch((err) => {
console.error("Error querying Sales data:", err);
throw new Error("Failed to analyze sales data");
});
}
and display result as
import { analyzeAllSalesData } from ‘backend/myModule’; // Import your function
$w.onReady(() => {
analyzeAllSalesData().then((data) => {
$w(“#myTable”).rows = data; // Bind the data to your Wix Table
}).catch((error) => {
console.error(“Error loading data:”, error);
});
});
I have update my Answer above. rather than wait. Let me know…
This is what I’m trying to achieve 
i want it to take both records named ‘kush’ and sum the strain amount and strain cash amount separately in the columns like below
Ah right, Think I get you.
Try this,
I got my code to work and then asked ChatGPT to clean it up.
This code just needs to be added to code for the page with the tables.
import wixData from 'wix-data'
$w.onReady(function () {
const myCollection = 'Sales'; //= Replace with your actual CMS ID
let tableRows = [];
wixData
.query(myCollection)
//== Only return these fields
.fields("trainName", "strainnAmounts", "strainnCashAmounts")
.find()
.then((results) => {
if (results.items.length > 0) {
//== Group items by trainName
const groupedData = results.items.reduce((groupedData, item) => {
if (item.trainName) {
if (!groupedData[item.trainName]) {
groupedData[item.trainName] = { strainnAmounts: 0, strainnCashAmounts: 0 };
}
groupedData[item.trainName].strainnAmounts += item.strainnAmounts || 0;
groupedData[item.trainName].strainnCashAmounts += item.strainnCashAmounts || 0;
}
return groupedData;
}, {});
//== Convert grouped data to table rows/ The trainname. ,totalStrainnAmounts, totalStrainnCashAmounts match the Table field names
tableRows = Object.entries(groupedData).map(([trainName, totals]) => ({
trainname: trainName,
totalStrainnAmounts: totals.strainnAmounts,
totalStrainnCashAmounts: totals.strainnCashAmounts,
}));
// Update the table on the page
$w("#table1").rows = tableRows; // Replace #table1 with your actual table ID
console.log("Updated tableRows:", tableRows);
} else {
console.log("No items found in the collection.");
}
})
.catch((err) => {
console.error(err);
});
});
CMS
Table is not linked to CMS, just given columns with Labels and field names
Result
Im getting a funny view on my table back. can you just see if i messed up the code pls :).
i’ve updated it slightly to have my CMS column names correctly.
import wixData from ‘wix-data’
$w.onReady(function () {
const myCollection = ‘Sales’; //= Replace with your actual CMS ID
let tableRows = ;
wixData
.query(myCollection)
//== Only return these fields
.fields("StrainName", "StrainAmount", "StrainCashAmount")
.find()
.then((results) => {
if (results.items.length > 0) {
//== Group items by trainName
const groupedData = results.items.reduce((groupedData, item) => {
if (item.StrainName) {
if (!groupedData[item.StrainName]) {
groupedData[item.StrainName] = { StrainAmount: 0, StrainCashAmount: 0 };
}
groupedData[item.StrainName].StrainAmount += item.StrainAmount || 0;
groupedData[item.StrainName].StrainCashAmount += item.StrainCashAmount || 0;
}
return groupedData;
}, {});
//== Convert grouped data to table rows/ The trainname. ,totalStrainAmount, totalStrainCashAmount match the Table field names
tableRows = Object.entries(groupedData).map(([StrainName, totals]) => ({
StrainName: StrainName,
totalStrainAmount: totals.StrainAmount,
totalStrainCashAmount: totals.StrainCashAmount,
}));
// Update the table on the page
$w("#SalesTable").rows = tableRows; // Replace #table1 with your actual table ID
console.log("Updated tableRows:", tableRows);
} else {
console.log("No items found in the collection.");
}
})
.catch((err) => {
console.error(err);
});
});
Your fields in the CMS are set as Text, so the text strings are being concatenated.
You can change the fields in the CMS to numbers.
After you have change the field from Text to Number. you will get a yellow warning on each cell, hover the mouse over the warning and use the convert option. do this for each warning.
Or
Change these lines
groupedData[item.StrainName].StrainAmount += item.StrainAmount || 0;
groupedData[item.StrainName].StrainCashAmount += item.StrainCashAmount || 0;
to
groupedData[item.StrainName].StrainAmount += Number(item.StrainAmount )|| 0;
groupedData[item.StrainName].StrainCashAmount += Number(item.StrainCashAmount )|| 0;