I can see in "MATS Code Studio Reference – v10.1 " that the COUNT aggregation function is supported for groupings (SQL Group By), and it works as expected.
Are the other standard SQL aggregation functions (MAX, MIN, SUM, AVG) supported as well? If so, what is the correct syntax for using them?
I’ve tried the following with no success. It works, as in no errors are thrown, but the SUM value is always 0. If I replace “SUM” with “COUNT” it does return an accurate count of records.
var timerec_object = mats.ref(‘Time_record’);
var time_records = mats.search
({// get all time records for the timesheet
‘base_object_id’ : timerec_object,
‘selects’ : [ ‘Timerec_timesheet_id’,
‘Timerec_date’,
‘Timerec_sow’,
‘Timerec_activity’,
‘Timerec_subclass’,
‘Timerec_billable_hours’,
‘SUM(Timerec_billable_hours)’,
‘Timerec_nonbillable_hours’
],
‘filters’ : [{ ‘field_path’: ‘Timerec_timesheet_id’,
‘comparator’: ‘equal_to’,
‘value’ : invoice_record.Invoice_timesheet_id
}],
‘groupings’ : [ ‘Timerec_sow’,
‘Timerec_activity’,
‘Timerec_subclass’
],
‘return’ : ‘data’
});// get all time records for the timesheet
mats.log(JSON.stringify(time_records));
Hi Randy
The issue here is you can’t use references within aggregates in this manner; they can only be used when the reference makes up the full string.
Thanks, Mark. I’ll check it out. It will entirely eliminate an ugly bit of code when I get it to work!
Another curiosity question though…
In your example and in the documentation, “mats.r()” is used to get a reference. However, the cut-n-paste example code provided by Code Studio when a reference is defined uses “mats.ref()”. I’ve been using “mats.ref()” since the syntax is cleaner. It seems to have worked in all cases. Is one or the other preferred and/or deprecated? Is there any difference between the two?
