How can I calculate the last payment of the year to be the sum of all previous payments for that year and subtracting it from Research Costs value?

Joined
Oct 24, 2013
Messages
47
Reaction score
0
How can I calculate the last payment of the year to be the sum of all previous payments(adding all data-amount) for that year and then subtracting the result from Research Costs value from the first row for that year and then assign the value to the last payment’s text and data-amount?

e.g if I pass 26/27 value to the function then it should get all the payments where data-payquarters contains 27/28 and then it should do sum of first 11 payments(in this case, adding all data-amount) and then subtract it from 250,254.21 And assign 20854.49 to the last payment’s text and data-amount (in this case it is the 12th payment.

e.g if I pass 27/28 value to the function then it should get all the payments where data-payquarters contains 27/28 and then it should do sum of first 5 payments(in this case) and then subtract it from 205,121.01 And assign 34,186.81 to the last payment’s text and data-amount (in this case it is the 6th payment.

HTML:
table class="table table-sm table-bordered">
                    <thead id="thPayments">
                        <tr>
                            <th style="width:50%" class="headercell"></th>
                            <th style="width:10%" class="headercell text-right">Research Costs</th>
                            <th style="width:10%" class="headercell text-center">Payment Schedule</th>
                            <th style="width:10%" class="headercell text-center">Status</th>
                            <th style="width:10%" class="headercell text-right">Amount</th>
                            <th style="width:10%" class="headercell text-right">Reprofiled Amount</th>
                        </tr>
                    </thead>
                    <tbody id="tbPayments">
   </tbody>
   <tbody id="tbPayments">    
      <tr id="trPayment_23322651" data-year="4" data-type="payment" data-eventid="23322651">
         <th>1 Apr 2026 - 31 Mar 2027</th>
         <td class="text-right">250,254.21</td>
         <td class="text-center" data-date="2026-04-01">01-04-2026</td>
         <td class="text-center">Requested</td>
         <td class="text-right " id="tdPayment_23322651" data-type="payment" data-amount="20,854.52">20,854.52</td>
         <td class="text-right " id="tdNewPayment_23322651" data-type="newpayment" data-month="3" data-payquarters="Q1-26/27" data-paymonth="4" data-amount="20854.52">20,854.52</td>
      </tr>
      <tr id="trPayment_23322652" data-year="4" data-type="payment" data-eventid="23322652">
         <th></th>
         <td></td>
         <td class="text-center" data-date="2026-05-01">01-05-2026</td>
         <td class="text-center">Requested</td>
         <td class="text-right " id="tdPayment_23322652" data-type="payment" data-amount="20,854.52">20,854.52</td>
         <td class="text-right " id="tdNewPayment_23322652" data-type="newpayment" data-month="3" data-payquarters="Q1-26/27" data-paymonth="5" data-amount="20854.52">20,854.52</td>
      </tr>
      <tr id="trPayment_23322653" data-year="4" data-type="payment" data-eventid="23322653">
         <th></th>
         <td></td>
         <td class="text-center" data-date="2026-06-01">01-06-2026</td>
         <td class="text-center">Requested</td>
         <td class="text-right " id="tdPayment_23322653" data-type="payment" data-amount="20,854.52">20,854.52</td>
         <td class="text-right " id="tdNewPayment_23322653" data-type="newpayment" data-month="3" data-payquarters="Q1-26/27" data-paymonth="6" data-amount="20854.52">20,854.52</td>
      </tr>
      <tr id="trPayment_23322654" data-year="4" data-type="payment" data-eventid="23322654">
         <th></th>
         <td></td>
         <td class="text-center" data-date="2026-07-01">01-07-2026</td>
         <td class="text-center">Requested</td>
         <td class="text-right " id="tdPayment_23322654" data-type="payment" data-amount="20,854.52">20,854.52</td>
         <td class="text-right " id="tdNewPayment_23322654" data-type="newpayment" data-month="3" data-payquarters="Q2-26/27" data-paymonth="7" data-amount="20854.52">20,854.52</td>
      </tr>
      <tr id="trPayment_23322655" data-year="4" data-type="payment" data-eventid="23322655">
         <th></th>
         <td></td>
         <td class="text-center" data-date="2026-08-01">01-08-2026</td>
         <td class="text-center">Requested</td>
         <td class="text-right " id="tdPayment_23322655" data-type="payment" data-amount="20,854.52">20,854.52</td>
         <td class="text-right " id="tdNewPayment_23322655" data-type="newpayment" data-month="3" data-payquarters="Q2-26/27" data-paymonth="8" data-amount="20854.52">20,854.52</td>
      </tr>
      <tr id="trPayment_23322656" data-year="4" data-type="payment" data-eventid="23322656">
         <th></th>
         <td></td>
         <td class="text-center" data-date="2026-09-01">01-09-2026</td>
         <td class="text-center">Requested</td>
         <td class="text-right " id="tdPayment_23322656" data-type="payment" data-amount="20,854.52">20,854.52</td>
         <td class="text-right " id="tdNewPayment_23322656" data-type="newpayment" data-month="3" data-payquarters="Q2-26/27" data-paymonth="9" data-amount="20854.52">20,854.52</td>
      </tr>
      <tr id="trPayment_23322657" data-year="5" data-type="payment" data-eventid="23322657">
         <th></th>
         <td></td>
         <td class="text-center" data-date="2026-10-01">01-10-2026</td>
         <td class="text-center">Requested</td>
         <td class="text-right " id="tdPayment_23322657" data-type="payment" data-amount="20,854.52">20,854.52</td>
         <td class="text-right " id="tdNewPayment_23322657" data-type="newpayment" data-month="3" data-payquarters="Q3-26/27" data-paymonth="10" data-amount="20854.52">20,854.52</td>
      </tr>
      <tr id="trPayment_23322658" data-year="5" data-type="payment" data-eventid="23322658">
         <th></th>
         <td></td>
         <td class="text-center" data-date="2026-11-01">01-11-2026</td>
         <td class="text-center">Requested</td>
         <td class="text-right " id="tdPayment_23322658" data-type="payment" data-amount="20,854.52">20,854.52</td>
         <td class="text-right " id="tdNewPayment_23322658" data-type="newpayment" data-month="3" data-payquarters="Q3-26/27" data-paymonth="11" data-amount="20854.52">20,854.52</td>
      </tr>
      <tr id="trPayment_23322659" data-year="5" data-type="payment" data-eventid="23322659">
         <th></th>
         <td></td>
         <td class="text-center" data-date="2026-12-01">01-12-2026</td>
         <td class="text-center">Requested</td>
         <td class="text-right " id="tdPayment_23322659" data-type="payment" data-amount="20,854.52">20,854.52</td>
         <td class="text-right " id="tdNewPayment_23322659" data-type="newpayment" data-month="3" data-payquarters="Q3-26/27" data-paymonth="12" data-amount="20854.52">20,854.52</td>
      </tr>
      <tr id="trPayment_23322660" data-year="5" data-type="payment" data-eventid="23322660">
         <th></th>
         <td></td>
         <td class="text-center" data-date="2027-01-01">01-01-2027</td>
         <td class="text-center">Requested</td>
         <td class="text-right " id="tdPayment_23322660" data-type="payment" data-amount="20,854.52">20,854.52</td>
         <td class="text-right " id="tdNewPayment_23322660" data-type="newpayment" data-month="3" data-payquarters="Q4-26/27" data-paymonth="1" data-amount="20854.52">20,854.52</td>
      </tr>
      <tr id="trPayment_23322661" data-year="5" data-type="payment" data-eventid="23322661">
         <th></th>
         <td></td>
         <td class="text-center" data-date="2027-02-01">01-02-2027</td>
         <td class="text-center">Requested</td>
         <td class="text-right " id="tdPayment_23322661" data-type="payment" data-amount="20,854.52">20,854.52</td>
         <td class="text-right " id="tdNewPayment_23322661" data-type="newpayment" data-month="3" data-payquarters="Q4-26/27" data-paymonth="2" data-amount="20854.52">20,854.52</td>
      </tr>
      <tr id="trPayment_23322662" data-year="5" data-type="payment" data-eventid="23322662">
         <th></th>
         <td></td>
         <td class="text-center" data-date="2027-03-01">01-03-2027</td>
         <td class="text-center">Requested</td>
         <td class="text-right " id="tdPayment_23322662" data-type="payment" data-amount="20,854.52">20,854.52</td>
         <td class="text-right " id="tdNewPayment_23322662" data-type="newpayment" data-month="3" data-payquarters="Q4-26/27" data-paymonth="3" data-amount="20854.52">20,854.52</td>
      </tr>
     
     
     
     
      <tr id="trPayment_23322663" data-year="5" data-type="payment" data-eventid="23322663">
         <th>1 Apr 2027 - 31 Mar 2028</th>
         <td class="text-right">205,121.01</td>
         <td class="text-center" data-date="2027-04-01">01-04-2027</td>
         <td class="text-center">Paid</td>
         <td class="text-right nochange paid" id="tdPayment_23322663" data-type="payment" data-amount="34,186.84">34,186.84</td>
         <td class="text-right nochange paid" id="tdNewPayment_23322663" data-type="newpayment" data-month="3" data-payquarters="Q1-27/28" data-paymonth="4" data-amount="34,186.84">34,186.84</td>
      </tr>
      <tr id="trPayment_23322664" data-year="5" data-type="payment" data-eventid="23322664">
         <th></th>
         <td></td>
         <td class="text-center" data-date="2027-05-01">01-05-2027</td>
         <td class="text-center">Paid</td>
         <td class="text-right nochange paid" id="tdPayment_23322664" data-type="payment" data-amount="34,186.84">34,186.84</td>
         <td class="text-right nochange paid" id="tdNewPayment_23322664" data-type="newpayment" data-month="3" data-payquarters="Q1-27/28" data-paymonth="5" data-amount="34,186.84">34,186.84</td>
      </tr>
      <tr id="trPayment_23322665" data-year="5" data-type="payment" data-eventid="23322665">
         <th></th>
         <td></td>
         <td class="text-center" data-date="2027-06-01">01-06-2027</td>
         <td class="text-center">Paid</td>
         <td class="text-right nochange paid" id="tdPayment_23322665" data-type="payment" data-amount="34,186.84">34,186.84</td>
         <td class="text-right nochange paid" id="tdNewPayment_23322665" data-type="newpayment" data-month="3" data-payquarters="Q1-27/28" data-paymonth="6" data-amount="34,186.84">34,186.84</td>
      </tr>
      <tr id="trPayment_23322666" data-year="5" data-type="payment" data-eventid="23322666">
         <th></th>
         <td></td>
         <td class="text-center" data-date="2027-07-01">01-07-2027</td>
         <td class="text-center">Paid</td>
         <td class="text-right nochange paid" id="tdPayment_23322666" data-type="payment" data-amount="34,186.84">34,186.84</td>
         <td class="text-right nochange paid" id="tdNewPayment_23322666" data-type="newpayment" data-month="3" data-payquarters="Q2-27/28" data-paymonth="7" data-amount="34,186.84">34,186.84</td>
      </tr>
      <tr id="trPayment_23322667" data-year="5" data-type="payment" data-eventid="23322667">
         <th></th>
         <td></td>
         <td class="text-center" data-date="2027-08-01">01-08-2027</td>
         <td class="text-center">Paid</td>
         <td class="text-right nochange paid" id="tdPayment_23322667" data-type="payment" data-amount="34,186.84">34,186.84</td>
         <td class="text-right nochange paid" id="tdNewPayment_23322667" data-type="newpayment" data-month="3" data-payquarters="Q2-27/28" data-paymonth="8" data-amount="34,186.84">34,186.84</td>
      </tr>
      <tr id="trPayment_23322668" data-year="5" data-type="payment" data-eventid="23322668">
         <th></th>
         <td></td>
         <td class="text-center" data-date="2027-09-01">01-09-2027</td>
         <td class="text-center">Paid</td>
         <td class="text-right nochange paid" id="tdPayment_23322668" data-type="payment" data-amount="34,186.84">34,186.84</td>
         <td class="text-right nochange paid" id="tdNewPayment_23322668" data-type="newpayment" data-month="3" data-payquarters="Q2-27/28" data-paymonth="9" data-amount="34,186.84">34,186.84</td>
      </tr>
   </tbody>


                   
                </table>
 
Last edited:
Joined
Oct 24, 2013
Messages
47
Reaction score
0
SORRY Updated. There was a typo.

How can I calculate the last payment of the year to be the sum of all previous payments(adding all data-amount) for that year and then subtracting the result from Research Costs value from the first row for that year and then assign the value to the last payment’s text and data-amount?

e.g if I pass 26/27 value to the function then it should get all the payments where data-payquarters contains 26/27 and then it should do sum of first 11 payments(in this case, adding all data-amount) and then subtract it from 250,254.21 And assign 20854.49 to the last payment’s text and data-amount (in this case it is the 12th payment.

e.g if I pass 27/28 value to the function then it should get all the payments where data-payquarters contains 27/28 and then it should do sum of first 5 payments(in this case) and then subtract it from 205,121.01 And assign 34,186.81 to the last payment’s text and data-amount (in this case it is the 6th payment.

The goal is to balance the book, using the last value of the table to make up for any difference for a specific year using data-amount of "tdNewPayment_”.
 
Joined
Nov 13, 2020
Messages
302
Reaction score
38
I ran the code you provided to understand what you're asking, but the results were gibberish. Try it and see.
You also forgot to include the CSS. Please correct the errors and give us the CSS.
 
Joined
Jul 4, 2023
Messages
400
Reaction score
44
Check, is that what you expected?

[ on-line ]
HTML:
<style>
  tr:has(th + td:not(:empty)) { /* tr:has(td:first-of-type:not(:empty)) */
    background-color: hsl(0 0% 0% /.1);
  }
</style>

<table class="table table-sm table-bordered">
  <thead id="thPayments">
    <tr>
      <th style="width:50%" class="headercell"></th>
      <th style="width:10%" class="headercell text-right">Research Costs</th>
      <th style="width:10%" class="headercell text-center">Payment Schedule</th>
      <th style="width:10%" class="headercell text-center">Status</th>
      <th style="width:10%" class="headercell text-right">Amount</th>
      <th style="width:10%" class="headercell text-right">Reprofiled Amount</th>
    </tr>
  </thead>
  <tbody id="tbPayments">  
    <tr id="trPayment_23322651" data-year="4" data-type="payment" data-eventid="23322651">
      <th>1 Apr 2026 - 31 Mar 2027</th>
      <td class="text-right">250,254.21</td>
      <td class="text-center" data-date="2026-04-01">01-04-2026</td>
      <td class="text-center">Requested</td>
      <td class="text-right " id="tdPayment_23322651" data-type="payment" data-amount="20,854.52">20,854.52</td>
      <td class="text-right " id="tdNewPayment_23322651" data-type="newpayment" data-month="3" data-payquarters="Q1-26/27" data-paymonth="4" data-amount="20854.52">20,854.52</td>
    </tr>
    <tr id="trPayment_23322652" data-year="4" data-type="payment" data-eventid="23322652">
      <th></th>
      <td></td>
      <td class="text-center" data-date="2026-05-01">01-05-2026</td>
      <td class="text-center">Requested</td>
      <td class="text-right " id="tdPayment_23322652" data-type="payment" data-amount="20,854.52">20,854.52</td>
      <td class="text-right " id="tdNewPayment_23322652" data-type="newpayment" data-month="3" data-payquarters="Q1-26/27" data-paymonth="5" data-amount="20854.52">20,854.52</td>
    </tr>
    <tr id="trPayment_23322653" data-year="4" data-type="payment" data-eventid="23322653">
      <th></th>
      <td></td>
      <td class="text-center" data-date="2026-06-01">01-06-2026</td>
      <td class="text-center">Requested</td>
      <td class="text-right " id="tdPayment_23322653" data-type="payment" data-amount="20,854.52">20,854.52</td>
      <td class="text-right " id="tdNewPayment_23322653" data-type="newpayment" data-month="3" data-payquarters="Q1-26/27" data-paymonth="6" data-amount="20854.52">20,854.52</td>
    </tr>
    <tr id="trPayment_23322654" data-year="4" data-type="payment" data-eventid="23322654">
      <th></th>
      <td></td>
      <td class="text-center" data-date="2026-07-01">01-07-2026</td>
      <td class="text-center">Requested</td>
      <td class="text-right " id="tdPayment_23322654" data-type="payment" data-amount="20,854.52">20,854.52</td>
      <td class="text-right " id="tdNewPayment_23322654" data-type="newpayment" data-month="3" data-payquarters="Q2-26/27" data-paymonth="7" data-amount="20854.52">20,854.52</td>
    </tr>
    <tr id="trPayment_23322655" data-year="4" data-type="payment" data-eventid="23322655">
      <th></th>
      <td></td>
      <td class="text-center" data-date="2026-08-01">01-08-2026</td>
      <td class="text-center">Requested</td>
      <td class="text-right " id="tdPayment_23322655" data-type="payment" data-amount="20,854.52">20,854.52</td>
      <td class="text-right " id="tdNewPayment_23322655" data-type="newpayment" data-month="3" data-payquarters="Q2-26/27" data-paymonth="8" data-amount="20854.52">20,854.52</td>
    </tr>
    <tr id="trPayment_23322656" data-year="4" data-type="payment" data-eventid="23322656">
      <th></th>
      <td></td>
      <td class="text-center" data-date="2026-09-01">01-09-2026</td>
      <td class="text-center">Requested</td>
      <td class="text-right " id="tdPayment_23322656" data-type="payment" data-amount="20,854.52">20,854.52</td>
      <td class="text-right " id="tdNewPayment_23322656" data-type="newpayment" data-month="3" data-payquarters="Q2-26/27" data-paymonth="9" data-amount="20854.52">20,854.52</td>
    </tr>
    <tr id="trPayment_23322657" data-year="5" data-type="payment" data-eventid="23322657">
      <th></th>
      <td></td>
      <td class="text-center" data-date="2026-10-01">01-10-2026</td>
      <td class="text-center">Requested</td>
      <td class="text-right " id="tdPayment_23322657" data-type="payment" data-amount="20,854.52">20,854.52</td>
      <td class="text-right " id="tdNewPayment_23322657" data-type="newpayment" data-month="3" data-payquarters="Q3-26/27" data-paymonth="10" data-amount="20854.52">20,854.52</td>
    </tr>
    <tr id="trPayment_23322658" data-year="5" data-type="payment" data-eventid="23322658">
      <th></th>
      <td></td>
      <td class="text-center" data-date="2026-11-01">01-11-2026</td>
      <td class="text-center">Requested</td>
      <td class="text-right " id="tdPayment_23322658" data-type="payment" data-amount="20,854.52">20,854.52</td>
      <td class="text-right " id="tdNewPayment_23322658" data-type="newpayment" data-month="3" data-payquarters="Q3-26/27" data-paymonth="11" data-amount="20854.52">20,854.52</td>
    </tr>
    <tr id="trPayment_23322659" data-year="5" data-type="payment" data-eventid="23322659">
      <th></th>
      <td></td>
      <td class="text-center" data-date="2026-12-01">01-12-2026</td>
      <td class="text-center">Requested</td>
      <td class="text-right " id="tdPayment_23322659" data-type="payment" data-amount="20,854.52">20,854.52</td>
      <td class="text-right " id="tdNewPayment_23322659" data-type="newpayment" data-month="3" data-payquarters="Q3-26/27" data-paymonth="12" data-amount="20854.52">20,854.52</td>
    </tr>
    <tr id="trPayment_23322660" data-year="5" data-type="payment" data-eventid="23322660">
      <th></th>
      <td></td>
      <td class="text-center" data-date="2027-01-01">01-01-2027</td>
      <td class="text-center">Requested</td>
      <td class="text-right " id="tdPayment_23322660" data-type="payment" data-amount="20,854.52">20,854.52</td>
      <td class="text-right " id="tdNewPayment_23322660" data-type="newpayment" data-month="3" data-payquarters="Q4-26/27" data-paymonth="1" data-amount="20854.52">20,854.52</td>
    </tr>
    <tr id="trPayment_23322661" data-year="5" data-type="payment" data-eventid="23322661">
      <th></th>
      <td></td>
      <td class="text-center" data-date="2027-02-01">01-02-2027</td>
      <td class="text-center">Requested</td>
      <td class="text-right " id="tdPayment_23322661" data-type="payment" data-amount="20,854.52">20,854.52</td>
      <td class="text-right " id="tdNewPayment_23322661" data-type="newpayment" data-month="3" data-payquarters="Q4-26/27" data-paymonth="2" data-amount="20854.52">20,854.52</td>
    </tr>
    <tr id="trPayment_23322662" data-year="5" data-type="payment" data-eventid="23322662">
      <th></th>
      <td></td>
      <td class="text-center" data-date="2027-03-01">01-03-2027</td>
      <td class="text-center">Requested</td>
      <td class="text-right " id="tdPayment_23322662" data-type="payment" data-amount="20,854.52">20,854.52</td>
      <td class="text-right " id="tdNewPayment_23322662" data-type="newpayment" data-month="3" data-payquarters="Q4-26/27" data-paymonth="3" data-amount="20854.52">20,854.52</td>
    </tr>




    <tr id="trPayment_23322663" data-year="5" data-type="payment" data-eventid="23322663">
      <th>1 Apr 2027 - 31 Mar 2028</th>
      <td class="text-right">205,121.01</td>
      <td class="text-center" data-date="2027-04-01">01-04-2027</td>
      <td class="text-center">Paid</td>
      <td class="text-right nochange paid" id="tdPayment_23322663" data-type="payment" data-amount="34,186.84">34,186.84</td>
      <td class="text-right nochange paid" id="tdNewPayment_23322663" data-type="newpayment" data-month="3" data-payquarters="Q1-27/28" data-paymonth="4" data-amount="34,186.84">34,186.84</td>
    </tr>
    <tr id="trPayment_23322664" data-year="5" data-type="payment" data-eventid="23322664">
      <th></th>
      <td></td>
      <td class="text-center" data-date="2027-05-01">01-05-2027</td>
      <td class="text-center">Paid</td>
      <td class="text-right nochange paid" id="tdPayment_23322664" data-type="payment" data-amount="34,186.84">34,186.84</td>
      <td class="text-right nochange paid" id="tdNewPayment_23322664" data-type="newpayment" data-month="3" data-payquarters="Q1-27/28" data-paymonth="5" data-amount="34,186.84">34,186.84</td>
    </tr>
    <tr id="trPayment_23322665" data-year="5" data-type="payment" data-eventid="23322665">
      <th></th>
      <td></td>
      <td class="text-center" data-date="2027-06-01">01-06-2027</td>
      <td class="text-center">Paid</td>
      <td class="text-right nochange paid" id="tdPayment_23322665" data-type="payment" data-amount="34,186.84">34,186.84</td>
      <td class="text-right nochange paid" id="tdNewPayment_23322665" data-type="newpayment" data-month="3" data-payquarters="Q1-27/28" data-paymonth="6" data-amount="34,186.84">34,186.84</td>
    </tr>
    <tr id="trPayment_23322666" data-year="5" data-type="payment" data-eventid="23322666">
      <th></th>
      <td></td>
      <td class="text-center" data-date="2027-07-01">01-07-2027</td>
      <td class="text-center">Paid</td>
      <td class="text-right nochange paid" id="tdPayment_23322666" data-type="payment" data-amount="34,186.84">34,186.84</td>
      <td class="text-right nochange paid" id="tdNewPayment_23322666" data-type="newpayment" data-month="3" data-payquarters="Q2-27/28" data-paymonth="7" data-amount="34,186.84">34,186.84</td>
    </tr>
    <tr id="trPayment_23322667" data-year="5" data-type="payment" data-eventid="23322667">
      <th></th>
      <td></td>
      <td class="text-center" data-date="2027-08-01">01-08-2027</td>
      <td class="text-center">Paid</td>
      <td class="text-right nochange paid" id="tdPayment_23322667" data-type="payment" data-amount="34,186.84">34,186.84</td>
      <td class="text-right nochange paid" id="tdNewPayment_23322667" data-type="newpayment" data-month="3" data-payquarters="Q2-27/28" data-paymonth="8" data-amount="34,186.84">34,186.84</td>
    </tr>
    <tr id="trPayment_23322668" data-year="5" data-type="payment" data-eventid="23322668">
      <th></th>
      <td></td>
      <td class="text-center" data-date="2027-09-01">01-09-2027</td>
      <td class="text-center">Paid</td>
      <td class="text-right nochange paid" id="tdPayment_23322668" data-type="payment" data-amount="34,186.84">34,186.84</td>
      <td class="text-right nochange paid" id="tdNewPayment_23322668" data-type="newpayment" data-month="3" data-payquarters="Q2-27/28" data-paymonth="9" data-amount="34,186.84">34,186.84</td>
    </tr>
  </tbody>
</table>

<script>
  window.onload = load;

  function load() {
    showAllFirstPayments('26/27');
    showAllFirstPayments('27/28');
  }

  function showAllFirstPayments(payquarter='none') {
    const allRowsMatchedByPayquarter = [...document.querySelectorAll(`tr:has([data-payquarters*="${payquarter}"])`)];
    if (allRowsMatchedByPayquarter.length > 0) {
      const lastRowMatchedByPayquarter = allRowsMatchedByPayquarter.pop(); // first n payments - last
      const researchCostsByPayquarter = parseToAmount(allRowsMatchedByPayquarter[0].querySelector('th + td').textContent);
      const sumAllFirstPayments = sumAllPreviousPayments(allRowsMatchedByPayquarter);
      lastRowMatchedByPayquarter.querySelector('td[data-type="payment"]')
        .textContent = amountToUSNotation((researchCostsByPayquarter - sumAllFirstPayments).toFixed(2));
    } else {
      console.warn(`Couldn't find matched rows by payquarter for ${payquarter} value.`);
    }
  }

  function sumAllPreviousPayments(data) {
    return [...data]
      .map(element => element.querySelector('td[data-type="payment"]').dataset.amount)
      .map(element => parseToAmount(element))
      .reduce((total, current) => total + current);
  }

  function parseToAmount(value) {
    try {
      return parseFloat(value.replace(',', ''));
    } catch {
      return 0;
    }
  }

  function amountToUSNotation(value) {
    try {
      //return value.toLocaleString('en-US', { style: 'decimal', minimumFractionDigits: 2, maximumFractionDigits: 2 });
      return value.toString().replace(/\B(?=(\d{3})+(?!\d))/g, ',');
    } catch {
      return 0;
    }
  }
</script>
 
Last edited:
Joined
Oct 24, 2013
Messages
47
Reaction score
0
Check, is that what you expected?

[ on-line ]
HTML:
<style>
  tr:has(th + td:not(:empty)) { /* tr:has(td:first-of-type:not(:empty)) */
    background-color: hsl(0 0% 0% /.1);
  }
</style>

<table class="table table-sm table-bordered">
  <thead id="thPayments">
    <tr>
      <th style="width:50%" class="headercell"></th>
      <th style="width:10%" class="headercell text-right">Research Costs</th>
      <th style="width:10%" class="headercell text-center">Payment Schedule</th>
      <th style="width:10%" class="headercell text-center">Status</th>
      <th style="width:10%" class="headercell text-right">Amount</th>
      <th style="width:10%" class="headercell text-right">Reprofiled Amount</th>
    </tr>
  </thead>
  <tbody id="tbPayments"> 
    <tr id="trPayment_23322651" data-year="4" data-type="payment" data-eventid="23322651">
      <th>1 Apr 2026 - 31 Mar 2027</th>
      <td class="text-right">250,254.21</td>
      <td class="text-center" data-date="2026-04-01">01-04-2026</td>
      <td class="text-center">Requested</td>
      <td class="text-right " id="tdPayment_23322651" data-type="payment" data-amount="20,854.52">20,854.52</td>
      <td class="text-right " id="tdNewPayment_23322651" data-type="newpayment" data-month="3" data-payquarters="Q1-26/27" data-paymonth="4" data-amount="20854.52">20,854.52</td>
    </tr>
    <tr id="trPayment_23322652" data-year="4" data-type="payment" data-eventid="23322652">
      <th></th>
      <td></td>
      <td class="text-center" data-date="2026-05-01">01-05-2026</td>
      <td class="text-center">Requested</td>
      <td class="text-right " id="tdPayment_23322652" data-type="payment" data-amount="20,854.52">20,854.52</td>
      <td class="text-right " id="tdNewPayment_23322652" data-type="newpayment" data-month="3" data-payquarters="Q1-26/27" data-paymonth="5" data-amount="20854.52">20,854.52</td>
    </tr>
    <tr id="trPayment_23322653" data-year="4" data-type="payment" data-eventid="23322653">
      <th></th>
      <td></td>
      <td class="text-center" data-date="2026-06-01">01-06-2026</td>
      <td class="text-center">Requested</td>
      <td class="text-right " id="tdPayment_23322653" data-type="payment" data-amount="20,854.52">20,854.52</td>
      <td class="text-right " id="tdNewPayment_23322653" data-type="newpayment" data-month="3" data-payquarters="Q1-26/27" data-paymonth="6" data-amount="20854.52">20,854.52</td>
    </tr>
    <tr id="trPayment_23322654" data-year="4" data-type="payment" data-eventid="23322654">
      <th></th>
      <td></td>
      <td class="text-center" data-date="2026-07-01">01-07-2026</td>
      <td class="text-center">Requested</td>
      <td class="text-right " id="tdPayment_23322654" data-type="payment" data-amount="20,854.52">20,854.52</td>
      <td class="text-right " id="tdNewPayment_23322654" data-type="newpayment" data-month="3" data-payquarters="Q2-26/27" data-paymonth="7" data-amount="20854.52">20,854.52</td>
    </tr>
    <tr id="trPayment_23322655" data-year="4" data-type="payment" data-eventid="23322655">
      <th></th>
      <td></td>
      <td class="text-center" data-date="2026-08-01">01-08-2026</td>
      <td class="text-center">Requested</td>
      <td class="text-right " id="tdPayment_23322655" data-type="payment" data-amount="20,854.52">20,854.52</td>
      <td class="text-right " id="tdNewPayment_23322655" data-type="newpayment" data-month="3" data-payquarters="Q2-26/27" data-paymonth="8" data-amount="20854.52">20,854.52</td>
    </tr>
    <tr id="trPayment_23322656" data-year="4" data-type="payment" data-eventid="23322656">
      <th></th>
      <td></td>
      <td class="text-center" data-date="2026-09-01">01-09-2026</td>
      <td class="text-center">Requested</td>
      <td class="text-right " id="tdPayment_23322656" data-type="payment" data-amount="20,854.52">20,854.52</td>
      <td class="text-right " id="tdNewPayment_23322656" data-type="newpayment" data-month="3" data-payquarters="Q2-26/27" data-paymonth="9" data-amount="20854.52">20,854.52</td>
    </tr>
    <tr id="trPayment_23322657" data-year="5" data-type="payment" data-eventid="23322657">
      <th></th>
      <td></td>
      <td class="text-center" data-date="2026-10-01">01-10-2026</td>
      <td class="text-center">Requested</td>
      <td class="text-right " id="tdPayment_23322657" data-type="payment" data-amount="20,854.52">20,854.52</td>
      <td class="text-right " id="tdNewPayment_23322657" data-type="newpayment" data-month="3" data-payquarters="Q3-26/27" data-paymonth="10" data-amount="20854.52">20,854.52</td>
    </tr>
    <tr id="trPayment_23322658" data-year="5" data-type="payment" data-eventid="23322658">
      <th></th>
      <td></td>
      <td class="text-center" data-date="2026-11-01">01-11-2026</td>
      <td class="text-center">Requested</td>
      <td class="text-right " id="tdPayment_23322658" data-type="payment" data-amount="20,854.52">20,854.52</td>
      <td class="text-right " id="tdNewPayment_23322658" data-type="newpayment" data-month="3" data-payquarters="Q3-26/27" data-paymonth="11" data-amount="20854.52">20,854.52</td>
    </tr>
    <tr id="trPayment_23322659" data-year="5" data-type="payment" data-eventid="23322659">
      <th></th>
      <td></td>
      <td class="text-center" data-date="2026-12-01">01-12-2026</td>
      <td class="text-center">Requested</td>
      <td class="text-right " id="tdPayment_23322659" data-type="payment" data-amount="20,854.52">20,854.52</td>
      <td class="text-right " id="tdNewPayment_23322659" data-type="newpayment" data-month="3" data-payquarters="Q3-26/27" data-paymonth="12" data-amount="20854.52">20,854.52</td>
    </tr>
    <tr id="trPayment_23322660" data-year="5" data-type="payment" data-eventid="23322660">
      <th></th>
      <td></td>
      <td class="text-center" data-date="2027-01-01">01-01-2027</td>
      <td class="text-center">Requested</td>
      <td class="text-right " id="tdPayment_23322660" data-type="payment" data-amount="20,854.52">20,854.52</td>
      <td class="text-right " id="tdNewPayment_23322660" data-type="newpayment" data-month="3" data-payquarters="Q4-26/27" data-paymonth="1" data-amount="20854.52">20,854.52</td>
    </tr>
    <tr id="trPayment_23322661" data-year="5" data-type="payment" data-eventid="23322661">
      <th></th>
      <td></td>
      <td class="text-center" data-date="2027-02-01">01-02-2027</td>
      <td class="text-center">Requested</td>
      <td class="text-right " id="tdPayment_23322661" data-type="payment" data-amount="20,854.52">20,854.52</td>
      <td class="text-right " id="tdNewPayment_23322661" data-type="newpayment" data-month="3" data-payquarters="Q4-26/27" data-paymonth="2" data-amount="20854.52">20,854.52</td>
    </tr>
    <tr id="trPayment_23322662" data-year="5" data-type="payment" data-eventid="23322662">
      <th></th>
      <td></td>
      <td class="text-center" data-date="2027-03-01">01-03-2027</td>
      <td class="text-center">Requested</td>
      <td class="text-right " id="tdPayment_23322662" data-type="payment" data-amount="20,854.52">20,854.52</td>
      <td class="text-right " id="tdNewPayment_23322662" data-type="newpayment" data-month="3" data-payquarters="Q4-26/27" data-paymonth="3" data-amount="20854.52">20,854.52</td>
    </tr>




    <tr id="trPayment_23322663" data-year="5" data-type="payment" data-eventid="23322663">
      <th>1 Apr 2027 - 31 Mar 2028</th>
      <td class="text-right">205,121.01</td>
      <td class="text-center" data-date="2027-04-01">01-04-2027</td>
      <td class="text-center">Paid</td>
      <td class="text-right nochange paid" id="tdPayment_23322663" data-type="payment" data-amount="34,186.84">34,186.84</td>
      <td class="text-right nochange paid" id="tdNewPayment_23322663" data-type="newpayment" data-month="3" data-payquarters="Q1-27/28" data-paymonth="4" data-amount="34,186.84">34,186.84</td>
    </tr>
    <tr id="trPayment_23322664" data-year="5" data-type="payment" data-eventid="23322664">
      <th></th>
      <td></td>
      <td class="text-center" data-date="2027-05-01">01-05-2027</td>
      <td class="text-center">Paid</td>
      <td class="text-right nochange paid" id="tdPayment_23322664" data-type="payment" data-amount="34,186.84">34,186.84</td>
      <td class="text-right nochange paid" id="tdNewPayment_23322664" data-type="newpayment" data-month="3" data-payquarters="Q1-27/28" data-paymonth="5" data-amount="34,186.84">34,186.84</td>
    </tr>
    <tr id="trPayment_23322665" data-year="5" data-type="payment" data-eventid="23322665">
      <th></th>
      <td></td>
      <td class="text-center" data-date="2027-06-01">01-06-2027</td>
      <td class="text-center">Paid</td>
      <td class="text-right nochange paid" id="tdPayment_23322665" data-type="payment" data-amount="34,186.84">34,186.84</td>
      <td class="text-right nochange paid" id="tdNewPayment_23322665" data-type="newpayment" data-month="3" data-payquarters="Q1-27/28" data-paymonth="6" data-amount="34,186.84">34,186.84</td>
    </tr>
    <tr id="trPayment_23322666" data-year="5" data-type="payment" data-eventid="23322666">
      <th></th>
      <td></td>
      <td class="text-center" data-date="2027-07-01">01-07-2027</td>
      <td class="text-center">Paid</td>
      <td class="text-right nochange paid" id="tdPayment_23322666" data-type="payment" data-amount="34,186.84">34,186.84</td>
      <td class="text-right nochange paid" id="tdNewPayment_23322666" data-type="newpayment" data-month="3" data-payquarters="Q2-27/28" data-paymonth="7" data-amount="34,186.84">34,186.84</td>
    </tr>
    <tr id="trPayment_23322667" data-year="5" data-type="payment" data-eventid="23322667">
      <th></th>
      <td></td>
      <td class="text-center" data-date="2027-08-01">01-08-2027</td>
      <td class="text-center">Paid</td>
      <td class="text-right nochange paid" id="tdPayment_23322667" data-type="payment" data-amount="34,186.84">34,186.84</td>
      <td class="text-right nochange paid" id="tdNewPayment_23322667" data-type="newpayment" data-month="3" data-payquarters="Q2-27/28" data-paymonth="8" data-amount="34,186.84">34,186.84</td>
    </tr>
    <tr id="trPayment_23322668" data-year="5" data-type="payment" data-eventid="23322668">
      <th></th>
      <td></td>
      <td class="text-center" data-date="2027-09-01">01-09-2027</td>
      <td class="text-center">Paid</td>
      <td class="text-right nochange paid" id="tdPayment_23322668" data-type="payment" data-amount="34,186.84">34,186.84</td>
      <td class="text-right nochange paid" id="tdNewPayment_23322668" data-type="newpayment" data-month="3" data-payquarters="Q2-27/28" data-paymonth="9" data-amount="34,186.84">34,186.84</td>
    </tr>
  </tbody>
</table>

<script>
  window.onload = load;

  function load() {
    showAllFirstPayments('26/27');
    showAllFirstPayments('27/28');
  }

  function showAllFirstPayments(payquarter='none') {
    const allRowsMatchedByPayquarter = [...document.querySelectorAll(`tr:has([data-payquarters*="${payquarter}"])`)];
    if (allRowsMatchedByPayquarter.length > 0) {
      const lastRowMatchedByPayquarter = allRowsMatchedByPayquarter.pop(); // first n payments - last
      const researchCostsByPayquarter = parseToAmount(allRowsMatchedByPayquarter[0].querySelector('th + td').textContent);
      const sumAllFirstPayments = sumAllPreviousPayments(allRowsMatchedByPayquarter);
      lastRowMatchedByPayquarter.querySelector('td[data-type="payment"]')
        .textContent = amountToUSNotation((researchCostsByPayquarter - sumAllFirstPayments).toFixed(2));
    } else {
      console.warn(`Couldn't find matched rows by payquarter for ${payquarter} value.`);
    }
  }

  function sumAllPreviousPayments(data) {
    return [...data]
      .map(element => element.querySelector('td[data-type="payment"]').dataset.amount)
      .map(element => parseToAmount(element))
      .reduce((total, current) => total + current);
  }

  function parseToAmount(value) {
    try {
      return parseFloat(value.replace(',', ''));
    } catch {
      return 0;
    }
  }

  function amountToUSNotation(value) {
    try {
      //return value.toLocaleString('en-US', { style: 'decimal', minimumFractionDigits: 2, maximumFractionDigits: 2 });
      return value.toString().replace(/\B(?=(\d{3})+(?!\d))/g, ',');
    } catch {
      return 0;
    }
  }
</script>
Hey txs for the soultion but I get Uncaught DOMException: Document.querySelectorAll: 'tr:has([data-payquarters*="26/27"])' is not a valid selector error when I run it.
 
Joined
Jul 4, 2023
Messages
400
Reaction score
44
Document.querySelectorAll: 'tr:has([data-payquarters*="26/27"])'
The code written in this way is invalid.
1. document - all lower case
2. document.querySelectorAll: - no colon at the end

JavaScript:
document.querySelectorAll('tr:has([data-payquarters*="26/27"])')
 
Joined
Oct 24, 2013
Messages
47
Reaction score
0
The code written in this way is invalid.
1. document - all lower case
2. document.querySelectorAll: - no colon at the end

JavaScript:
document.querySelectorAll('tr:has([data-payquarters*="26/27"])')
Same error: Uncaught DOMException: Document.querySelectorAll: 'tr:has([data-payquarters*="26/27"])' is not a valid selector
 
Joined
Jul 4, 2023
Messages
400
Reaction score
44
I think the problem is with :has Check compatibility with your browser.
If your browser not support :has, that mean we need to find different way for this part of code.

JavaScript:
const allRowsMatchedByPayquarter = [...document.querySelectorAll(`tr:has([data-payquarters*="${payquarter}"])`)];
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,828
Messages
2,569,736
Members
45,518
Latest member
vlonesshirt

Latest Threads

Top