Switch band rate in calculation depending on value in FORMS, not affecting previous values

asked on August 30, 2017

I am trying to create a table that allows the user to enter in number of kilometers they drive and it multiplies by a certain rate: 0.4, 0.6, 0.2, 0.8 depending on the total number of kilometers they have driven.

=IF(DB_TOTAL_KILOMETERS<1501,0.3986,IF(DB_TOTAL_KILOMETERS<5501,0.7321,IF(DB_TOTAL_KILOMETERS<25001,0.2903,0.2223)))

This works but when calculating the total, once the rate switches to the next, all the previous values are updated to be calculated with the current rate.

Here is where it is being calculated:

=SUM(PRODUCT(BAND_RATE, INDEX(MILEAGE_EXPENSE_CLAIM.KILOMETERS,ROW()))).

*happening within a table*

Is the any way of making the already calculated row field values FINAL and not change as i have to get the total of all subtotals also?

0 0

SELECTED ANSWER

replied on August 30, 2017

Hey,

I think I have an idea of what you are trying to achieve. I would use javascript to do this tho.

This should be a good starting point for you.

function sumKilo()
{
  var sum = 0;
  $('#q14 table tr').each(function() {
    var kilo = $(this).find('[data-title="Kilometers"] input[type="text"]');
    if(parseInt(kilo.val()) > 0)
    {
      sum = sum + parseInt(kilo.val());
      kilo.data("ksum",sum);
  	}
  });
}


$(document).ready(function () {
  $(document).on('change','#q14 tr td[data-title="Kilometers"] input[type="text"]',function() {
   	 sumKilo();
     var band = 0;
    var ksum = parseInt($(this).data("ksum"));
     if(ksum <=1500)
     {
       band = $('.band1 input').val();
     }else if(ksum <=5500)
     {
       band = $('.band2 input').val();
     }
    else if(ksum <=25000)
    {
      band = $('.band3 input').val();
    }
   	else
    {
      band = $('.band4 input').val();
    }
    $(this).parents('tr').find('[data-title="Reimbursement"] input[type="text"]').val(parseInt($(this).val()) * band);

  });
  
});

I tried to make the code pretty generic so it should work almost out of the box for you. Just need to do the following:

1) change all references in the code from '#q14' to what ever the ID value is for your table.

2) add the class .band1, .band2, .band3, .band4 to the appropriate band text field.(advanced tab)

I would clone your form before doing this tho in case you have problems because you will have to remove all your formulas for it to work properly.

The code is a little tacky and could deff be cleaned up but it should work as a starting point.

If you have any issues let me know :).

2 0

replied on August 31, 2017 • Show version history


function sumKilo()
{
  var sum = 0;
  $('#q8 table tr').each(function() {
    var kilo = $(this).find('[data-title="Kilometers"] input[type="text"]');
    if(parseInt(kilo.val()) > 0)
    {
      sum = sum + parseInt(kilo.val());
      kilo.data("ksum",sum);
    }
  });

}
 

$(document).ready(function () {
  $(document).on('change','#q8 tr td[data-title="Kilometers"] input[type="text"]',function() {
     sumKilo();
     var band = 0;
    var ksum = parseInt($(this).data("ksum"));
     if(ksum <=1500)
     {
       band = $('.band1 input').val();
     }

    else if(ksum <=5500)
    {
       band = $('.band2 input').val();
     }

    else if(ksum <=25000)
    {
      band = $('.band3 input').val();
    }

    else
   {
      band = $('.band4 input').val();
    }

    $(this).parents('tr').find('[data-title="Reimbursement"] input[type="text"]').val(parseFloat($(this).val()) * band);
    
  });
});

Thanks for the help, after I added the javascript shown above and gave the each band rate the class the rows became unaffected by each other and band rate changed normally,

but now it breaks if the number is 1000 or above.

Any ideas why it would be doing this or how it can be fixed?

0 0

replied on August 31, 2017

Ah its the comma, you can fix that in one of 2 ways.

1) remove the comma separator from the currency field.

2) change the following lines of code:

- parseInt(kilo.val()); to parseInt(kilo.val().replace(',',''));

- parseFloat($(this).val()) to parseFloat($(this).val().replace(',',''))

1 0

replied on September 1, 2017

Thanks Aaron,

Huge help, Working now, Happy days!

~Dean

0 0

Question

Question

Switch band rate in calculation depending on value in FORMS, not affecting previous values

Answer

Replies

Sign in to reply to this post.