How to get sum of cms database using result from data query?

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

}

Are you getting any error messages on the console?