HubDB Donor Board

cives · ‎Jun 30, 2022

Hi all, I'm using a HubDB table to render donations made by our members to benefit an annual fundraiser.

My current markup is:

<div class="pwr-sub-services pwr-sec-services--style-2 aos-init aos-animate" data-aos="fade-in" data-aos-delay="50">
      <div class="pwr-services pwr-services--style-2">
        {% for row in hubdb_table_rows('5438374', "&orderBy=-sort_number") %}
        {% if row.first_name %}
  <div class="pwr-services-item  pwr--light " style="padding-left: calc( 40px + 15px );">
    <div class="pwr-services-front-wrapper">
    <span class="pwr-services-item__icon" style=" font-size: calc( 40px - 2px );">
      <img class="pwr-services-item__img" src="{{ row.photo }}?width=40" alt="Donor Portrait" width="40" style="max-height: 40px; max-width: 40px; height: 40px; width: auto;" sizes="(max-width: 40px) 100vw, 40px">
    </span>
    <span style="text-transform:none !important;" class="pwr-services-item__title">{{ row.first_name }}</span>
    </div>  
    <span class="pwr-rich-text pwr-services-item__desc">{{ row.buying_group }}</span>
  </div>
         {% endif %}
        {% endfor %}
      </div>
</div>

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 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.

Any ideas?

cives · ‎Sep 19, 2022

Update: I have the above working about 90%. Check out: https://nhada.com/raffle

Ideally, I'd like the donors to show in order of greatest to least. Does anyone know how to amend my below code to accomplish that?

{% set newRecords = [] %}
{% macro checkNewRecords(currentDonation, currentPhoto, currentBuyingGroup, currentFirstName, currentLastName)  %}
{% if newRecords != "[]" %}
      {% set obj = {val : -1} %}
      {% for newBook in newRecords %}
        {% if currentBuyingGroup == newBook.buying_group %}
          {% do obj.update({val: loop.index0 }) %}
        {% endif %}
      {% endfor %}
      {% if obj.val == -1 %}
           {% do newRecords.append({ "donation": currentDonation, "photo": currentPhoto, "buying_group": currentBuyingGroup, "first_name": currentFirstName, "last_name" : currentLastName}) %}
      {% else %}
          {% do newRecords[obj.val].update({"donation": newRecords[obj.val].donation + currentDonation, "photo": currentPhoto, "buying_group": currentBuyingGroup, "first_name": currentFirstName, "last_name" : currentLastName}) %}
      {% endif %}
    {% else %}
      {% do newRecords.append({"donation": currentDonation, "photo": currentPhoto, "buying_group": currentBuyingGroup, "first_name": currentFirstName, "last_name" : currentLastName}) %}
    {% endif %}
{% endmacro %}

{% for row in hubdb_table_rows('5438374', "&orderBy=donation") %}
{% if row.first_name %}
{{ checkNewRecords(row.donation, row.photo, row.buying_group, row.first_name, row.last_name) }}
{% endif %}
{% endfor %}


<div class="pwr-sub-services pwr-sec-services--style-2 aos-init aos-animate" data-aos="fade-in" data-aos-delay="50">
      <div class="pwr-services pwr-services--style-2">
        {% for item in newRecords %}
  
  <div class="pwr-services-item  pwr--light ">
    <div class="pwr-services-front-wrapper">
    <span style="text-transform:none !important;" class="pwr-services-item__title">{{ item.buying_group }}</span>
    </div>  
    <span class="pwr-rich-text pwr-services-item__desc">${{ item.donation }}</span>
  </div>
        {% endfor %}
      </div>
</div>

Indra · ‎Jul 1, 2022

Hi @cives,

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

Bureau Vet

Did my post solve your question? Help the community by ☑marking it as a solution

cives · ‎Jul 1, 2022

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

From what I understand, the second solution would render:

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.

CMS Development