Confiforms QueryAndAggregate

[Matěj Mazák]

I have been walking around this function, but I am still at a loss as to how to use it.

I made two forms. One is for registering company events, the other is for collecting feedback.

Feedback form has an eventid field, which holds the id value of the event from the event form. This way I can have all of the feedback in one DB and do reports easily.

I want to do a rating (1-5) in feedback and then calculate an average from these values and I want to display it with information from the company events table.

queryCount can get me the count of all feedbacks, but I need a way to sum up the value of the rating field for all feedback entries where eventid:[entry.id] and finally I need to divide these two values to get the average.

I know QueryAndAggregate should be able to sum up the values, but I actually have no idea how to use it. QueryAndRender or QueryCount can be simply inserted to Tableview to display extra values, but I have no idea how to use the aggregate function.

Any help, or advice to improve this is welcomed.

[Alex Medved _ConfiForms_ · Accepted solution]

Hi @Matěj Mazák 

Similar to queryCount, but with an ability to tell what to count (actually - what to sum up)

id.queryAndAggregate(DQeventsFeedback@self;eventid:[entry.id];[entry.rating])

 Alex

[Matěj Mazák]

Thanks, that works.

Can I join the queryAndAggregate and queryCount to one field to do the full calculation?

something like 

id.queryAndAggregate(DQeventsFeedback:@self;eventid:[entry.id];[entry.rating])/id.queryCount(DQeventsFeedback:@self;eventid:[entry.id])

[Alex Medved _ConfiForms_]

May be something like this

id.queryAndAggregate(DQeventsFeedback:@self;eventid:[entry.id];[entry.rating]).divide([entry.id.queryCount(DQeventsFeedback:@self;eventid:[entry.id])])

or by introducing a formula field with a custom calculation

[Matěj Mazák]

That works, but it rounds up to whole number.

Is there a way this could be done to keep one decimal (i.e. 2.5, 3.2, etc.)

[Matěj Mazák]

Got it, the formula field worked

[entry.id.queryAndAggregate(DQeventsFeedback:282919633;eventid:[entry.id];[entry.rating])]/[entry.id.queryCount(DQeventsFeedback:282919633;eventid:[entry.id])]

[Matěj Mazák]

As it turns out the formula field works only on editing the entry. And it doesn't update dynamically. That's on me for not reading the documentation properly.

So I'm back to the previous issue, when I use the field macro in table view and input the:

id.queryAndAggregate(DQeventsFeedback:@self;eventid:[entry.id];[entry.rating]).divide([entry.id.queryCount(DQeventsFeedback:@self;eventid:[entry.id])])

 It works well, but it rounds to whole number. I'm trying to think of a way to do it properly, so it shows one decimal. Any hints on this?

Thank you

[Alex Medved _ConfiForms_]

id.queryAndAggregate(DQeventsFeedback:@self;eventid:[entry.id];[entry.rating]).divide([entry.id.queryCount(DQeventsFeedback:@self;eventid:[entry.id])].0)

[Matěj Mazák]

Sorry that doesn't seem to work. It still rounds up to a whole number.

What is the .0 supposed to do in this context? I couldn't find anything about it in Virtual Functions documentation.

[Alex Medved _ConfiForms_]

OK, I guess this is something what needs to be fixed on ConfiForms side (as it rounds up without checking the "scale")

[Matěj Mazák]

Hello,

Do you have a timeframe of when this could get fixed? Just wondering if I should look for different solution (I can put it in a Formula field and send IFTTT updates to make it work).

[Alex Medved _ConfiForms_]

That will be fixed in the upcoming 3.16.1 and is already fixed in the cloud version