Each ticket purchased from the table below is worth $100
I would like the end result to render:
Donor 1, Sample Company $200
Donor 2, Sample Company $200
...And so forth. Currently, it renders:
Donor 1, Sample Company $100
Donor 1, Sample Company $100
Donor 2, Sample Company $100
Donor 2, Sample Company $100
I'm looking for a way to group rows by Donor Name and then multiply the count of the group by $100 to get the total donation amount for each donor. This is the preferred method over listing them all individually.
Why don't you change the orderBy to the Doner Name? This would change the order by grouping them.
You can also apply multiple orderBy to order by your order number and name.
Code will be like this:
{% set db = module.db %}
{% set queryparam = '&orderBy=name&orderBy=order' %}
{% set rows = hubdb_table_rows(db, queryparam) %}
{% for row in rows %}
{{ row.hs_id }} - {{ row.name }}
{% endfor %}
With the above code there is no option to add up the total for each donor. For this, you should make a custom code.
What you could do instead is make the donor name a select field.
Now you can loop through the options by getting a list of the donor.
Code will look like this:
{# Set variable #}
{% set db = module.db %}
{% set queryparam = '&orderBy=name&orderBy=order' %}
{% set rows = hubdb_table_rows(db, queryparam) %}
{% set donors = hubdb_table_column(module.db, 'donor').options %}
{# Loop though the donors field from the HubDB #}
{% for donor in donors %}
<div style="border: 1px solid #000; margin: 20px 0;">
{# Set empty value for donations #}
{% set donations = {"value": 0 } %}
{# Loop though all rows #}
{% for row in rows %}
{# Check if donor is equel to the donor name #}
{% if donor.name == row.donor.name %}
{# Update the donation number #}
{% do donations.update( {"value": donations.value + row.order} ) %}
{{ row.hs_id }} - {{ row.donor.name }} - {{ row.order }}
<div class="total">
{# Output the total #}
Total: {{ donations.value }}
</div>
{% endif %}
{% endfor %}
</div>
{% endfor %}
The only downside is that the above code retrives the rows multiple times for each donor. There is a technical limit for retreiving the hubdb_table_rows
Hi Indra, thank you for your recommendations. Regarding the first solution, it isn't the order that I am concerned with, it is the amount of times they are listed. For example, If someone buys 5 tickets, that is a $500 donation, as such, they should be rendered as:
Donor Name, $500
Not:
Donor Name, $100
Donor Name, $100
Donor Name, $100
Donor Name, $100
Donor Name, $100
From what I understand, the second solution would render:
Donor Name, $500
Donor Name, $500
Donor Name, $500
Donor Name, $500
Donor Name, $500
Unfortunately, this isn't what I'm hoping for either. The reason I cannot use a more simple HubDB table with columns "total donation" is that this same DB table is rendering e-tickets, it is set up to handle multiple things.
My hope is that it can also ultimately list our donors in addition to rendering the e-tickets.