Question:
[Clearly ask your question.]
Hi, Im using wix editor.
What are you trying to achieve:
I have a 2 set of database:
- Sales Record - includes data such as date of sale, year of sales, car plate no and amount of sales
- Costing Record - includes data such as cost amount, carplate no.
What i am trying to do is to find the total costing amount for a particular year. the way i think of doing it is to use data.query to search “sales record” for the carplate no of cars sold in a particular year. and use this result as a filter on “costing record” to get the sum. but i cant seem to make it work.
import wixData from 'wix-data';
$w.onReady(function () {
populatesummary();
populatesalesrecord();
});
function populatesummary() {
let fyyear = String($w('#dropdown1').value) || "2024";
console.log("the fiscal year is " + fyyear)
$w("#dataset1").onReady(() => {
const filter = wixData.filter().eq("fyYear", fyyear);
wixData.aggregate("CarSalesRecord").filter(filter).sum("sellPrice", "spSum").sum("dpfAmount", "dpfnSum").count().run().then((results) => {
if (results.items.length > 0) {
let tdashunits = results.items[0].count;
let tdashsp = results.items[0].spSum;
let tdashdpfn = results.items[0].dpfnSum;
let tdashsp2 = 100 * tdashsp / tdashsp;
let tdashdpfn2 = 100 * tdashdpfn / tdashsp;
$w('#tdashunits').text = String(tdashunits);
$w('#tdashsales').text = formatNumber(tdashsp);
$w('#tdashdpfn').text = formatNumber(tdashdpfn);
$w('#tdashsales2').text = formatPercent(tdashsp2);
$w('#tdashdpfn2').text = formatPercent(tdashdpfn2);
wixData.query("CarSalesRecord").contains("fyYear", fyyear).fields("carplate").find().then((results2) => {
for (let index = 0; index < results2.items.length; index++) {
const element = results2.items[index];
let existingcarplate = element.carplate;
console.log(existingcarplate)
let filter2 = wixData.filter().eq("carplate", existingcarplate);
wixData.aggregate("CarSalesCosting").filter(filter2).sum("amount", "amountSum").run().then((existingcost) => {
if (existingcost.items.length > 0) {
var cost = Number(existingcost.items[0].amountSum);
console.log("cost for" + existingcarplate + "is RM " + cost)
var totalcost = totalcost + cost;
} else {
var cost = 0;
console.log("cost for" + existingcarplate + "is RM " + cost)
var totalcost = totalcost + cost;
}
let tdashgp = tdashsp - Number(totalcost);
let tdashnp = tdashgp - tdashdpfn;
let tdashgp2 = 100 * tdashgp / tdashsp;
let tdashnp2 = 100 * tdashnp / tdashsp;
$w('#tdashgp').text = formatNumber(tdashgp);
$w('#tdashnp').text = formatNumber(tdashnp);
$w('#tdashgp2').text = formatPercent(tdashgp2);
$w('#tdashnp2').text = formatPercent(tdashnp2);
})
}
})
} else {
$w('#tdashunits').text = String(0);
$w('#tdashsales').text = formatNumber(0);
$w('#tdashdpfn').text = formatNumber(0);
$w('#tdashgp').text = formatNumber(0);
$w('#tdashnp').text = formatNumber(0);
$w('#tdashsales2').text = formatPercent(0);
$w('#tdashdpfn2').text = formatPercent(0);
$w('#tdashgp2').text = formatPercent(0);
$w('#tdashnp2').text = formatPercent(0);
}
})
})
}