I am using aggregate functions to perform calculations on a page on our site. I need to have a drop down of various years to help filter out the specific data used in the aggregation codes. Is there a way for the aggregate filters to search and group the date / time field entries in a database for the calculations based on the year only and not have to enter the whole date / time format (mm/dd/yyyy 0:00:00)? When looking at the wix guide giving various details, the examples they use all involve the year being in its own column. Or, is there a way to use hooks or front end code to separate the year from the whole date time entry once a form is submitted to the database and have the year placed into its own column within the database which can then be used for the aggregate filter?
Hi Chad,
To answer your question, you could do it either way. If you wanted to store the year when the form is submitted, the Javascript function to utilize would be getFullYear(). A simple example using a hook is as follows:
export function myCollection_afterInsert(item, context) {
let item.yearField = item.dateField.getFullYear();
return item;
}
You could also work with the date field on your aggregate filtering page like this:
export function FilterDates(){
let yearValue = Number($w('#yearDropdown').value);
let monthValue = 1;
let dayValue = 1;
let date1 = new Date(yearValue,monthValue,dayValue,0,0,0);
yearValue = Number($w('#yearDropdown').value);
monthValue = 12;
dayValue = 31;
let date2 = new Date(yearValue,monthValue,dayValue,23,59,59);
$w('#dataset1').setFilter(wixData.filter()
.between("dateField", date1,date2)
)
.then(() => {
console.log("Dataset is now filtered");
})
.catch((err) => {
console.log(err);
});
}
This code could be made generic with the use of variables in order to accommodate a number of different fields.
For some reason, most of the hooks I have tried putting in are not firing whenever an insert function is performed on the forms. So far, only my original afterQuery hook for the full name concatenate is still working without issues. I saw several others commenting about the same issue too in the forums. Instead of stressing out more on it, I took the JavaScript function you mentioned and added in a front end code with the other codes I had in place when a form was submitted. It seems to be working beautifully now for that part at least. I was hoping this would make it easier using the aggregate filter from the examples shown in the Aggregate API tutorial but alas it is not working right. Here is the front end code I came up that pulls the year into its own column.
//Year only extraction for database
let yearonly = ($w('#datePicker1').value).getFullYear()
$w('#dataset2').setFieldValue('year', yearonly);
I am still having issues getting the aggregate functions to properly filter out the data based on the year drop down list I have on the page. I am not using a dataset element on the page like your 2nd code above has listed. It am guessing that it is designed for a dataset vs directly with a collection database?. My aggregate codes currently are tied directly to the collection database like the tutorial examples and then the results get placed into a text element on the page. Here is a cut down version of the code (since there are a lot data fields involved). I figured to prevent future issues and maybe it will be more helpful when dealing with the filters, I changed the code from onReady like in other examples to a Button click to retrieve the info.
import wixData from 'wix-data';
$w.onReady(function () {
//TODO: write your page related code here...
});
export function retrievebutton_click(event) {
wixData.aggregate("Volunteer_Entries")
//Family Activities Sum
.sum("familyFamiFormHrs", "sum8")
.sum("familyFamiPrayHrs", "sum9")
//Community Activities Sum
.sum("communityAthlHrs", "sum14")
.sum("communityCoatKidsHrs", "sum15")
//Total Hours Sum
.sum("totalHrsAdded", "sumTotal")
.run()
.then((results) => {
//Family Activities Display
$w('#text240').text = results.items[0].sum8.toString();
$w('#text242').text = results.items[0].sum9.toString();
//Community Activities Display
$w('#text252').text = results.items[0].sum14.toString();
$w('#text254').text = results.items[0].sum15.toString();
//Total Hours Display
$w('#text305').text = results.items[0].sumTotal.toString();
});
}
wixData.aggregate("Volunteer_Entries")
.group("memId")
.count()
.ascending("_id")
.run()
.then((results) => {
let volunteerCount = results.length;
$w('#text303').text = volunteerCount.toString();
})
}
I tried using the aggregate filter pattern shown in the tutorial using the value of the drop down however no results get returned. I also had a little trouble restructuring your other year filter option to work with the database without dataset. I also tried adding a dataset to the page linked to the database but could not get it to work right with the aggregate code above. Granted I may be missing something easy.
Maybe I wasn’t clear about the two examples being an either/or solution. If you are getting the yearonly field updated properly, then you use that in your aggregate query and forget about the second option entirely.
Filter conditions are applied differently on aggregate queries .
export function retrievebutton_click(event) {
// Get value from your year dropdown, whatever it's called. It's a string, so you need
// to convert it to a number.
let filterOnYear = Number($w('#yearDropdown').value);
// create a variable to house the filter condition to apply to the aggregate
let filterCondition = wixData.filter().eq("yearonly", filterOnYear);
wixData.aggregate("Volunteer_Entries")
.filter(filterCondition)
//Family Activities Sum
.sum("familyFamiFormHrs", "sum8")
.sum("familyFamiPrayHrs", "sum9")
//Community Activities Sum
.sum("communityAthlHrs", "sum14")
.sum("communityCoatKidsHrs", "sum15")
//Total Hours Sum
.sum("totalHrsAdded", "sumTotal")
.run()
.then((results) => {
//Family Activities Display
$w('#text240').text = results.items[0].sum8.toString();
$w('#text242').text = results.items[0].sum9.toString();
//Community Activities Display
$w('#text252').text = results.items[0].sum14.toString();
$w('#text254').text = results.items[0].sum15.toString();
//Total Hours Display
$w('#text305').text = results.items[0].sumTotal.toString();
});
}
@tony-brunsman My apologies, your earlier explanation was clear. I understood it was an either / or situation for each of the codes and that they were not meant to be working together. I probably was not fully clear when responding. I had meant that I tried each scenario independently to determine which would have been the better route but was having issues with both ways on the filtering aspect on the aggregate data. I had tried both ways and attempted to rewrite the code a couple different ways based on the tutorials to see if I could get it to work but was not having any luck.
The filterCondition above worked great. The link you provided was one of the resources I was researching. I believe it became more confusing since I did not see the filter condition function you just gave listed in that guide’s examples or in any of the other resources that I happened to find so far.
So to build off of this, if I have another page that is similar but was unique / specific to the site member so they could see their individual volunteer hours, then I should only need to add another filter condition in the “and()” format so it filters both on the year and the person’s member ID (which will auto populate when they access the page), correct? Something like:
let filterCondition = wixData.filter()
.eq("yearonly", filterOnYear);
.eq("memId", filterOnId);
Or would this need to be added differently in another format? Just asking ahead because I have not tested that theory yet since I am still finishing up building the fields on the individual’s page.
Thanks so much for all the guidance you have given, Anthony. Besides a huge thank you… you definitely deserve a beer for all the help you have given recently. LoL.
No problem, Chad. Right, that code should return the desired results if you drop the semi-colon on the second line.
let filterCondition = wixData.filter()
.eq("yearonly", filterOnYear)
.eq("memId", filterOnId);