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?

A

asifakhtar

Joined
Oct 24, 2013
Messages
43
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:
A

asifakhtar

Joined
Oct 24, 2013
Messages
43
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_”.
 
BigDady

BigDady

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

VBService

Joined
Jul 4, 2023
Messages
366
Reaction score
41
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:
A

asifakhtar

Joined
Oct 24, 2013
Messages
43
Reaction score
0
VBService said:
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>
Click to expand...
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.
 
VBService

VBService

Joined
Jul 4, 2023
Messages
366
Reaction score
41
asifakhtar said:
Document.querySelectorAll: 'tr:has([data-payquarters*="26/27"])'
Click to expand...
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"])')
 
A

asifakhtar

Joined
Oct 24, 2013
Messages
43
Reaction score
0
VBService said:
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"])')
Click to expand...
Same error: Uncaught DOMException: Document.querySelectorAll: 'tr:has([data-payquarters*="26/27"])' is not a valid selector
 
VBService

VBService

Joined
Jul 4, 2023
Messages
366
Reaction score
41
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

Similar Threads

How can I calculate the last payment for Reprofiled Amount column with 2 decimal places to make the sum of all payments to be the same as RC amount? 2
How to create a JSON array with values from DOM(HTML TABLE) when I click a button using JQuery/Javascript? 0
Can someone tell me if this a real tracker? Or is it one designed to show you a different message at certain times, ie. acting like one? 0
How to create a JSON array with values from DOM(HTML TABLE) when I click a button using JQuery/Javascript? 0
When I send email as HTML, why do erroneous whitespaces getintroduced to the HTML source and a few < 2
Can anybody plz help me outof this problem in mah code 0
Help with my responsive home page 2
Javascript I want to hide the space occupied by the row in tabls based on value 0

Members online

Total: 31 (members: 2, guests: 29)
Robots: 354

Forum statistics

Threads
473,769
Messages
2,569,582
Members
45,058
Latest member
QQXCharlot

Latest Threads

Top