Question

JavaScript calculation to multiply an hours total with a rate price and a row total

Forms

Updated December 20, 2016

asked on December 19, 2016

I have a form where a user inputs and start and finish time to give a total of hours then i want to multiply that total with a rate and have it subtotal per row.

So far i have used some JavaScript to do the time calculations and give me the total hours, but i don't know how to multiply the total with the rate.

$(document).ready(function () {
  
  
  $('.cf-table-block').on('blur', 'input', sumtotal);
  $(document).on('click', '.form-del-field', sumtotal);
  
  function sumtotal() {
    var stime = 0;
    var ftime = 0;
    var total = 0;
    var subtotal = 0;   
    $('.cf-table-block tbody tr').each(function () {
      stime = moment($(this).find('.stime input').val(), 'HHmm');
      ftime = moment($(this).find('.ftime input').val(), 'HHmm');
      subtotal = stime.diff(ftime, 'Hours', true);
      $(this).find('.total input').val(subtotal.toFixed(2));
      total += subtotal;
    });  
    $('.Grandtotal input').val(total.toFixed(2));       
  }
  
  function parseNumber(n) { 
    var f = parseFloat(n); //Convert to float number. 
  	return isNaN(f) ? 0 : f; //treat invalid input as 0; 
  } 

});

There is the option for a Total outside the table but i'm not going to use that part.

The rate is populated from a database lookup.

I just need to multiply the total hours with the rate

Can someone help please ?

0 0

Answer

SELECTED ANSWER

replied on December 20, 2016 • Show version history

I setup a similar table as yours:

for "Total Hours", I use following calculation:

=SUB(INDEX(Table_3.Finish_Time,ROW()),INDEX(Table_3.Start_Time,ROW()))/100

For "Sub Total", I use following calculation:

=PRODUCT(INDEX(Table_3.Total_Hours,ROW()),INDEX(Table_3.Task_Price,ROW()))

And "Task Price" value is filled by lookup rule:

And it works when fill the form:

3 0

replied on December 20, 2016

That's an awesome solution thank you.

After some testing i found that the formulas are giving me errors in my form, but on another test system the formulas work OK.

Thanks all for your input its most appreciated.

0 0

replied on December 20, 2016

What error did you get ? Please check the variable names used in the formula are correct.

0 0

Replies

replied on December 19, 2016

Hi Ian

with Forms 10.1 you now have built-in table table calculations built in that you can use.

in your table, open the subtotal field and use the following calculation in the Advanced Tab, calculation area

=PRODUCT(INDEX(tablename.firstfieldname,ROW()),INDEX(tablename.secondfieldname,ROW()))

you can select the variable names from the >

you can also do calculations such as SUM, SUB and DIV in a table.

if you look for other post in answers for dalculations in a table you will find lots of others like this we have answered.

2 0

replied on December 19, 2016 • Show version history

Hi Steve

I have used some of the calculation formulas in this form, but for some reason this one doesn't work (the subtotal just stays at 0) in this particular part.

=PRODUCT(INDEX(Hourly.Total_Hours,ROW()),INDEX(Hourly.task_price_hourly,ROW()))

This is why i thought JavaScript might be a better solution in this case.

0 0

replied on December 20, 2016

Perhaps double check the variable name for the table and fields. Frequently the variable name will get a _1,_2,_3, etc. tacked on at the end if you change the variable type. (I.E. Single Line to Number).

That tends to by the number one culprit for me if the table calculations are off.

0 0

You are not allowed to follow up in this post.

Question

Question

JavaScript calculation to multiply an hours total with a rate price and a row total

Answer

Replies

Sign in to reply to this post.