How do I set display data when no records found?

r_enriquez09 · July 8, 2020, 3:36pm

I have a database that holds projects my sales reps submit. I’ve set up a count and aggregate filter to gather the sales rep stats for how many kilowatts they signed, kilowatts installed and kilowatts cancelled to be displayed upon login (dataset is filtered by owner as logged in user on page).

I have buttons “QTR 1” “QTR 2” “QTR 3” “QTR 4” and “YTD”. That will trigger the display data. My code is functional, except that when I click on QTR 2 (button5), where I have 0 cancels and I’d like text9 to display “0.000”, it’s not changing from the previous calculated amount and same issue when I click QTR 4 (button7), where I have 0 installs and 0 cancels and I’d like text8 and text9 to display “0.000”, it’s not changing from the previous calculated amount.

How do I set these texts to “0.000” if the sum is 0, since no records are found?

Here is my page URL for editors: https://commission5.wixsite.com/mysite-1

And my complete code (I have about 2 months experience, so any tips for improvement are appreciated!!):

import wixUsers from 'wix-users';
import wixData from 'wix-data';

var days = 7;
var date = new Date();
var last = new Date(date.getTime() - (days * 24 *60 * 60 *1000)); 
let ytd1 = new Date(2020, 0, 1);
let ytd2 = new Date(2020, 11, 31);
let qtr1_1 = new Date(2020, 0, 1);
let qtr1_2 = new Date(2020, 2, 31);
let qtr2_1 = new Date(2020, 3, 1);
let qtr2_2 = new Date(2020, 5, 30);
let qtr3_1 = new Date(2020, 6, 1);
let qtr3_2 = new Date(2020, 8, 30);
let qtr4_1 = new Date(2020, 9, 1);
let qtr4_2 = new Date(2020, 11, 31);

$w.onReady(function () {
 if(wixUsers.currentUser.loggedIn) {
        $w('#box1').hide();
        $w('#box2').show();
    }
 else {
        $w('#box1').show();
        $w('#box2').hide();
    }
$w('#button8').style.backgroundColor = "#D3D3D3"

wixData.query("Projects").between("contractSigned1", ytd1, ytd2)
.count()
.then((results) => {
$w('#text3').text = results.toString()
})

wixData.query("Projects").isNotEmpty("installDate").between("installDate", ytd1, ytd2)
.count()
.then((results) => {
$w('#text2').text = results.toString()
})

wixData.query("Projects").isNotEmpty("cancelDate").between("cancelDate", ytd1, ytd2)
.count()
.then((results) => {
$w('#text1').text = results.toString()
})

const filterSigned = wixData.filter().between("contractSigned1", ytd1, ytd2);
wixData.aggregate("Projects")
.filter(filterSigned)
.sum("systemSize", "kwSigned")
.run()
.then( (result) => {
var kwSigned = result.items[0].kwSigned
$w('#text7').text = kwSigned.toString()
$w('#text13').text = kwSigned.toString()

const filterInstalls = wixData.filter().isNotEmpty("installDate").between("installDate", ytd1, ytd2);
wixData.aggregate("Projects")
.filter(filterInstalls)
.sum("systemSize", "kwInstalled")
.run()
.then( (result) => {
var kwInstalled = result.items[0].kwInstalled
$w('#text8').text = kwInstalled.toString()

const filterCancels = wixData.filter().isNotEmpty("cancelDate").between("cancelDate", ytd1, ytd2);
wixData.aggregate("Projects")
.filter(filterCancels)
.sum("systemSize", "kwCancelled")
.run()
.then( (results) => {
var kwCancelled = results.items[0].kwCancelled
$w('#text9').text = parseFloat(kwCancelled).toFixed(3);

})
})
})
});

export function button8_click(event) {
$w('#button8').style.backgroundColor = "#D3D3D3"
$w('#button7').style.backgroundColor = "#F00212"
$w('#button6').style.backgroundColor = "#F00212"
$w('#button5').style.backgroundColor = "#F00212"
$w('#button4').style.backgroundColor = "#F00212"
wixData.query("Projects").between("contractSigned1", ytd1, ytd2)
.count()
.then((results) => {
$w('#text3').text = results.toString()
})

wixData.query("Projects").isNotEmpty("installDate").between("installDate", ytd1, ytd2)
.count()
.then((results) => {
$w('#text2').text = results.toString()
})

wixData.query("Projects").isNotEmpty("cancelDate").between("cancelDate", ytd1, ytd2)
.count()
.then((results) => {
$w('#text1').text = results.toString()
})

const filterSigned = wixData.filter().between("contractSigned1", ytd1, ytd2);
wixData.aggregate("Projects")
.filter(filterSigned)
.sum("systemSize", "kwSigned")
.run()
.then( (result) => {
var kwSigned = result.items[0].kwSigned
$w('#text7').text = kwSigned.toString()
$w('#text13').text = kwSigned.toString()

const filterInstalls = wixData.filter().isNotEmpty("installDate").between("installDate", ytd1, ytd2);
wixData.aggregate("Projects")
.filter(filterInstalls)
.sum("systemSize", "kwInstalled")
.run()
.then( (result) => {
var kwInstalled = result.items[0].kwInstalled
$w('#text8').text = kwInstalled.toString()

const filterCancels = wixData.filter().isNotEmpty("cancelDate").between("cancelDate", ytd1, ytd2);
wixData.aggregate("Projects")
.filter(filterCancels)
.sum("systemSize", "kwCancelled")
.run()
.then( (results) => {
var kwCancelled = results.items[0].kwCancelled
$w('#text9').text = parseFloat(kwCancelled).toFixed(3);

})
})
}); 
}

export function button7_click(event) {
$w('#button8').style.backgroundColor = "#F00212"
$w('#button7').style.backgroundColor = "#D3D3D3"
$w('#button6').style.backgroundColor = "#F00212"
$w('#button5').style.backgroundColor = "#F00212"
$w('#button4').style.backgroundColor = "#F00212"
wixData.query("Projects").between("contractSigned1", qtr4_1, qtr4_2)
.count()
.then((results) => {
$w('#text3').text = results.toString()
})

wixData.query("Projects").isNotEmpty("installDate").between("installDate", qtr4_1, qtr4_2)
.count()
.then((results) => {
$w('#text2').text = results.toString()
})

wixData.query("Projects").isNotEmpty("cancelDate").between("cancelDate", qtr4_1, qtr4_2)
.count()
.then((results) => {
$w('#text1').text = results.toString()
})

const filterSigned = wixData.filter().between("contractSigned1", qtr4_1, qtr4_2);
wixData.aggregate("Projects")
.filter(filterSigned)
.sum("systemSize", "kwSigned")
.run()
.then( (result) => {
var kwSigned = result.items[0].kwSigned
$w('#text7').text = kwSigned.toString()
$w('#text13').text = kwSigned.toString()

const filterInstalls = wixData.filter().isNotEmpty("installDate").between("installDate", qtr4_1, qtr4_2);
wixData.aggregate("Projects")
.filter(filterInstalls)
.sum("systemSize", "kwInstalled")
.run()
.then( (result) => {
var kwInstalled = result.items[0].kwInstalled
$w('#text8').text = kwInstalled.toString()

const filterCancels = wixData.filter().isNotEmpty("cancelDate").between("cancelDate", qtr4_1, qtr4_2);
wixData.aggregate("Projects")
.filter(filterCancels)
.sum("systemSize", "kwCancelled")
.run()
.then( (results) => {
var kwCancelled = results.items[0].kwCancelled
$w('#text9').text = parseFloat(kwCancelled).toFixed(3);

})
})
});  
}

export function button6_click(event) {
$w('#button8').style.backgroundColor = "#F00212"
$w('#button7').style.backgroundColor = "#F00212"
$w('#button6').style.backgroundColor = "#D3D3D3"
$w('#button5').style.backgroundColor = "#F00212"
$w('#button4').style.backgroundColor = "#F00212"
wixData.query("Projects").between("contractSigned1", qtr3_1, qtr3_2)
.count()
.then((results) => {
$w('#text3').text = results.toString()
})

wixData.query("Projects").isNotEmpty("installDate").between("installDate", qtr3_1, qtr3_2)
.count()
.then((results) => {
$w('#text2').text = results.toString()
})

wixData.query("Projects").isNotEmpty("cancelDate").between("cancelDate", qtr3_1, qtr3_2)
.count()
.then((results) => {
$w('#text1').text = results.toString()
})

const filterSigned = wixData.filter().between("contractSigned1", qtr3_1, qtr3_2);
wixData.aggregate("Projects")
.filter(filterSigned)
.sum("systemSize", "kwSigned")
.run()
.then( (result) => {
var kwSigned = result.items[0].kwSigned
$w('#text7').text = parseFloat(kwSigned).toFixed(3);
$w('#text13').text = parseFloat(kwSigned).toFixed(3);

const filterInstalls = wixData.filter().isNotEmpty("installDate").between("installDate", qtr3_1, qtr3_2);
wixData.aggregate("Projects")
.filter(filterInstalls)
.sum("systemSize", "kwInstalled")
.run()
.then( (result) => {
var kwInstalled = result.items[0].kwInstalled
$w('#text8').text = kwInstalled.toString()

const filterCancels = wixData.filter().isNotEmpty("cancelDate").between("cancelDate", qtr3_1, qtr3_2);
wixData.aggregate("Projects")
.filter(filterCancels)
.sum("systemSize", "kwCancelled")
.run()
.then( (results) => {
var kwCancelled = results.items[0].kwCancelled
$w('#text9').text = parseFloat(kwCancelled).toFixed(3);

})
})
});  
}

export function button5_click(event) {
$w('#button8').style.backgroundColor = "#F00212"
$w('#button7').style.backgroundColor = "#F00212"
$w('#button6').style.backgroundColor = "#F00212"
$w('#button5').style.backgroundColor = "#D3D3D3"
$w('#button4').style.backgroundColor = "#F00212"
wixData.query("Projects").between("contractSigned1", qtr2_1, qtr2_2)
.count()
.then((results) => {
$w('#text3').text = results.toString()
})

wixData.query("Projects").isNotEmpty("installDate").between("installDate", qtr2_1, qtr2_2)
.count()
.then((results) => {
$w('#text2').text = results.toString()
})

wixData.query("Projects").isNotEmpty("cancelDate").between("cancelDate", qtr2_1, qtr2_2)
.count()
.then((results) => {
$w('#text1').text = results.toString()
})

const filterSigned = wixData.filter().between("contractSigned1", qtr2_1, qtr2_2);
wixData.aggregate("Projects")
.filter(filterSigned)
.sum("systemSize", "kwSigned")
.run()
.then( (result) => {
var kwSigned = result.items[0].kwSigned
$w('#text7').text = kwSigned.toString()
$w('#text13').text = kwSigned.toString()

const filterInstalls = wixData.filter().isNotEmpty("installDate").between("installDate", qtr2_1, qtr2_2);
wixData.aggregate("Projects")
.filter(filterInstalls)
.sum("systemSize", "kwInstalled")
.run()
.then( (result) => {
var kwInstalled = result.items[0].kwInstalled
$w('#text8').text = kwInstalled.toString()

const filterCancels = wixData.filter().between("cancelDate", qtr2_1, qtr2_2);
wixData.aggregate("Projects")
.filter(filterCancels)
.sum("systemSize", "kwCancelled")
.run()
.then( (result) => {
var kwCancelled = result.items[0].kwCancelled
$w('#text9').text = kwCancelled.toString()
})
})
}); 
}

export function button4_click(event) {
$w('#button8').style.backgroundColor = "#F00212"
$w('#button7').style.backgroundColor = "#F00212"
$w('#button6').style.backgroundColor = "#F00212"
$w('#button5').style.backgroundColor = "#F00212"
$w('#button4').style.backgroundColor = "#D3D3D3"
wixData.query("Projects").between("contractSigned1", qtr1_1, qtr1_2)
.count()
.then((results) => {
$w('#text3').text = results.toString()
})

wixData.query("Projects").isNotEmpty("installDate").between("installDate", qtr1_1, qtr1_2)
.count()
.then((results) => {
$w('#text2').text = results.toString()
})

wixData.query("Projects").isNotEmpty("cancelDate").between("cancelDate", qtr1_1, qtr1_2)
.count()
.then((results) => {
$w('#text1').text = results.toString()
})

const filterSigned = wixData.filter().between("contractSigned1", qtr1_1, qtr1_2);
wixData.aggregate("Projects")
.filter(filterSigned)
.sum("systemSize", "kwSigned")
.run()
.then( (result) => {
var kwSigned = result.items[0].kwSigned
$w('#text7').text = kwSigned.toString()
$w('#text13').text = kwSigned.toString()

const filterInstalls = wixData.filter().isNotEmpty("installDate").between("installDate", qtr1_1, qtr1_2);
wixData.aggregate("Projects")
.filter(filterInstalls)
.sum("systemSize", "kwInstalled")
.run()
.then( (result) => {
var kwInstalled = result.items[0].kwInstalled
$w('#text8').text = kwInstalled.toString()

const filterCancels = wixData.filter().isNotEmpty("cancelDate").between("cancelDate", qtr1_1, qtr1_2);
wixData.aggregate("Projects")
.filter(filterCancels)
.sum("systemSize", "kwCancelled")
.run()
.then( (results) => {
var kwCancelled = results.items[0].kwCancelled
$w('#text9').text = parseFloat(kwCancelled).toFixed(3);

})
})
});  
}

Topic		Replies	Views
No results to display Ask the community code , question	6	426	October 21, 2019
No results found text to display for nothing found in a database help Ask the community code , question	2	195	November 21, 2019
I want to display data in a table from a dataset based on user input and give a message if no data exists for the given input Ask the community code , wix-stores , question	0	145	June 4, 2021
How to show "No Search Results" when no results were found in filter? Ask the community code , question	2	301	September 15, 2019
Displaying "no results found" on filter search? Ask the community code , question	5	1510	June 24, 2020

How do I set display data when no records found?

Related topics