Sub total values

  • willowtree
  • Topic Author
  • Offline
  • Fresh Boarder
  • Fresh Boarder
  • Posts: 12
  • Thank you received: 0

willowtree created the topic: Sub total values

Hi,

I have 2 breezing forms
A- dog profile so has dog name, handler name etc
B- results form where handers can add results they have had. This includes radio buttons with numerical values for points.

I also have a CB view that shows the records of the results form. This is a row for each result.

I would like to total the points for each dog and sort them high to low on the points. (ideally also showing the handler from the dog profile). I don't need each result separately listed.

I'm not sure how to do this as the data is all in different rows. If it was single rows I could SUM the points values grouped by dog id etc, but the structure of the db is more complicated than that?

sample code that won't work?
SELECT attest_rounds.dog_id, attest_dogs.dog_pet_name, SUM(attest_rounds.Points) 
FROM attest_rounds LEFT JOIN attest_dogs
ON attest_rounds.dog_id = attest_dogs.dog_id

I appreciate any pointing in the right direction!

Thanks
#235954
  • Posts: 2246
  • Karma: 77
  • Thank you received: 298

mihaela replied the topic: Sub total values

Hello,

Data submitted in a form gets saved to the #__facileforms_subrecords table in the database (unless you have connected the form to another database using the integrator).

Here is an example query that will retrieve the sum of points for a certain dog:
"select SUM(value) from #__facileforms_subrecords where id = pointsfieldID and record in (Select record From #__facileforms_records Where id = dogsnamefieldID and value = 'dog1') "
NOTE: This code assumes that the points for the dog were submitted through a form that contains both fields for dog's name and the number of points.
Therefore, in teh query you need to replace pointsfieldID with the ID if the field where points are entered. Furthermore, pointsfieldID has to be replaced with the ID of the field where the dog's name is entered.
Finally, replace dog1 with the acual name of the dog for which you are writing the query.

If you add a field to a form where the sum of points is presented and then include that points field in the view, you can sort records in the view based on that field.

I hope this helps.

Regards,

Mihaela
#236136
  • willowtree
  • Topic Author
  • Offline
  • Fresh Boarder
  • Fresh Boarder
  • Posts: 12
  • Thank you received: 0

willowtree replied the topic: Sub total values

Thanks Mihaela,

I'm trying to get this into the results table so it would be for each dog.

At the moment the contentbuilder view shows all the results for all dogs in a long table. I want the table to have 1 row for each dog and then the total of their points.

I have a breezing forms form for each dog, and one for each result. Some results are a 1 point value, some are a 2 point. The value is in the form and database so a sum should work?

Is there a way I can get the table to sub total the results for each dog?

Shep 10 pts
Bob 9 pts
Sally 7pts

etc
#236137
  • Posts: 2246
  • Karma: 77
  • Thank you received: 298

mihaela replied the topic: Sub total values

Hello,

What you want to achieve isn't a native ContentBuilder functionality.

The only way I see that you could achieve what you want would be to create an additional field in the dog's profile and connect this form to a new separate database table using the integrator update rule.

Here you can learn more about the integrator crosstec.org/en/support/online-documenta...-the-integrator.html and here crosstec.org/en/support/online-documenta...update-tutorial.html .

Then you can create a ContentBuiler view based on this database table so each dog will be represented together with sum of his points.

Furthermore, in the form where the user enters the results for a dog, you would have to write a query to that new database table to update number of points for a dog based on the result in that record.

Let me know whether this helps.

Regards,

Mihaela
#236139
  • willowtree
  • Topic Author
  • Offline
  • Fresh Boarder
  • Fresh Boarder
  • Posts: 12
  • Thank you received: 0

willowtree replied the topic: Sub total values

Thanks,

I thought perhaps to save the form entries to separate tables then I could query those tables and get the result. I have tried using the integrator but there is never any options in the Database Field (outgoing) dropdown. I already have the tables ready in the db and selected in the table dropdown.
#236141
  • Posts: 2246
  • Karma: 77
  • Thank you received: 298

mihaela replied the topic: Sub total values

Hello,

I'm not sure if I've understood you correctly and whether that is causing the issue, but please try to create database table with the same database prefix as other tables in joomla database.

Let me know the result.

Regards,

Mihaela
#236164
Moderators: ForumSupporttomeperica
Time to create page: 0.068 seconds

Community Reward

Help us to create new extensions and plugins! With only $5 you help us a lot and get unlimited download access to all of our products, professional support and even more. Get your reward now!

Read More Here

News and Updates

Get informed about new downloads, updates and more in our News and Updates newsletter.

All Extensions Subscription

Get 1 year access to all of our current and future products and 1 year of professional support -- 99€

No support per domain or website installation limits! Includes all of our current and future Joomla!® extensions, Joomla!® templates for the duration of your membership. This means, by purchasing an All Extensions Subscription you'll have it all covered!

Get it from here

3rd Party Discount - 25% Off

We help you to keep your costs under control. If you are a new member and purchased a form building tool from a different form vendor, then you'll get a 25% discount on our subscription plans.

How to receive the discount:

Send us a quick email to sales@crosstec.org with a proof of purchase (for example a paypal receipt), await payment instructions and enjoy your membership!

Community Reward

Help us to create new extensions and plugins! With only $5 you help us a lot and get unlimited download access to all of our products, professional support and even more. Get your reward now!

Read More Here