You are viewing limited content. For full access, please sign in.

Question

Question

Mileage Expense Form Help Javascript

asked on September 22, 2017
$(document).ready(function () 
{
$('.cf-table-block').on('blur', 'input', reimbursement);
$('.totalKilo').on('change', 'input', reimbursement);
$('.annualKiloTotal').on('change', 'input', reimbursement);
  function reimbursement() 
   {
      var result = 0; //value of the mileage reimbursement
      var newKilometers = 0; //store value of kilometers this month
      var dbKilometer = 0; //store value of kilometers in database(previous kilometers)
      var band1 = 0; //band rate 1
      var band2 = 0; //band rate 2
      var band3 = 0; //band rate 3
      var band4 = 0; //band rate 4
      var calc1 = 0; //store calculation values for result
      var calc2 = 0; //store calculation values for result
      var calc3 = 0; //store calculation values for result
      var calc4 = 0; //store calculation values for result
      var totalKilo = 0; //store value of newkilometers + dbKilometer
          	
      $('.cf-table-block tbody tr').each(function () 
         {
           var newKilometers= parseNumber($('.totalKilo input').val());
           var dbKilometer = parseNumber($('.dbKilo input').val());
	   var band1 = parseNumber($('.band1 input').val());
	   var band2 = parseNumber($('.band2 input').val());
	   var band3 = parseNumber($('.band3 input').val());
	   var band4 = parseNumber($('.band4 input').val());
	   var totalKilo = (newKilometers + dbKilometer);
	if (dbKilometer > 25000)    
	   {
	      result = newKilometers * band4;
	    }
	else if (dbKilometer > 5500)      
	   {
	      if (totalKilo > 25000)      
		{
		   calc1 = ((totalKilo - 25000) * band4);
		   calc2 = ((25000 - dbKilometer) * band3);
		   result = (calc1 + calc2);
		 }
                 else
                  {
	             result = (newKilometers * band3);
		   }
	    }
          else if(dbKilometer > 1500)
            {
	        if (totalKilo > 25000)
                  {
		     calc1 = ((totalKilo - 25000) * band4);
		     calc2 = ((25000 - 5500) * band3);
		     calc3 = ((5500 - dbKilometer) * band2);
		     result = (calc1 + calc2 + calc3);
		   }
                 else if (totalKilo > 5500)
                   {
		      calc1 = ((totalKilo - 5500) * band3);
		      calc2 = ((5500 - dbKilometer) * band2);
		      result = (calc1 + calc2);
	            }
                  else
                    {
		       result = (newKilometers * band2);
		    }	
		}
             else if (dbKilometer >= 0) 
               {
		   if (totalKilo > 25000)
                     {
			calc1 = ((totalKilo - 25000) * band4);
			calc2 = ((25000 - 5500) * band3);
			calc3 = ((5500 - 1500) * band2);
			calc4 = ((1500 - dbKilometer) * band1);
			result = (calc1 + calc2 + calc3 + calc4);
	 	      }
                    else if (totalKilo > 5500)
                      {
			calc1 = ((totalKilo - 5500) * band3);
			calc2 = ((5500 -1500) * band2);
			calc3 = ((1500 - dbKilometer) * band1);
			result = (calc1 + calc2 + calc3);
		       }
                    else if (totalKilo > 1500)
                       {
			 calc1 = ((totalKilo - 1500) * band2);
			 calc2 = ((1500 - dbKilometer) * band1);
			 result = (calc1 + calc2);
			}
                     else
                        {
			 result = (newKilometers * band1);
			}
		}
               else
                  {
			result = 0;
		  }
                 });
                	$('kiloTotal input').val(result).change();
  	
		}
  function parseNumber(n) 
  	{
        var f = parseFloat(n); //Convert to float number.
        return isNaN(f) ? 0 : f; //treat invalid input as 0;
    }
  				
});

The above code calculates the reimbursement for a form and includes 4 mileage rates.

Based on the amount of kilometers an employee has traveled for this month and the amount of kilometers they have traveled so far this year, the mileage rate is decided. 

All the above fields with values are pre-populated with look-up rules.

when a user inputs a value in the Kilometers field in the table it populates the Total Kilometers field also and adds kilometers to Annual Total Kilometers.

Band rate table:

I want to calculate the Mileage Rate for the Total Kilometers field.

As per the table above,

As the band rate changes at 1500, only 2km is calculated at band rate 1 and 98km at band rate 2.

I have written Java code to test my logic and it worked fine but having difficulty transferring this logic to Javascript.

Java Code:

Output:

Calculation:  (2*0.3986) + (98*0.7321) = 72.543 which is correct.

 

Wondering if I am not taking in the fields values correctly or something?

Any help would be great!

0 0

Answer

SELECTED ANSWER
replied on September 25, 2017

I think the issue is the MINUS formula.  It shows up in the formula picker, but it isn't listed in the help file, and I get errors when I try to use it.  I don't know the expected syntax, but based on playing around in Excel, I'm guessing it just means something like this:

=MINUS(10, 7)     results in 3     (i.e. 10-7=3)

If that is correct, then I think you can probably change things like this:     MINUS(Annual_Total_Kilometers, 25000)     to this:     (Annual_Total_Kilometers - 25000)     and achieve the expected result for that part of the formula.

1 0

Replies

replied on September 23, 2017 Show version history

I see one syntax error without testing.  It is on line 100: 

$('kiloTotal input').val(result).change();

 

Is missing a period before the class name: 

$('.kiloTotal input').val(result).change();

 

I tried to replicate your form to test further, but I'm not entirely certain which class name belongs to which field, and I also don't know which fields (if any) have calculated values through the Layout screen as opposed to values calculated in the Javascript.  If you're still having trouble, it may be helpful to get more details or an actual copy of your process.

You may also want to consider trying to do this with field calculation instead of Javascript - you can use IF statements and nested IF statements similar to how you would in Excel or other spreadsheet programs.

Also - I could be wrong - I can't confirm this since I wasn't able to fully replicate your form, but it looks like you are going to be kicking off your calculations much more often than you probably intend to, and could potentially end up in a situation where your reimbursement function is being called over and over again in a potentially endless loop.  For one thing, you are running your entire calculation on every single row in your table and I don't think that is necessary - you may just need to get the value from each row in the table, add it to the combined total, and then run all the rest of your calculations.  Additionally, kicking the function off on the blur of the table may be causing it to kick-off more often than you think.  Try putting an alert statement (i.e.   alert('test');   ) at the beginning of your reimbursement function.  Add a few rows to your table and then try clicking in and out of your table (or try clicking across various fields within your table) and count how many times the alert window pops up.  Good luck!

1 0
replied on September 25, 2017

I have taken your advice and now tried to do it within a nested IF condition in forms itself.

I am under the assumption that the layout of an IF condition is IF(condition, value if condition true, value if condition false), is this correct?

As shown below in 1), this function is returning back with:

Calculation does not contain the correct number of  input parameters.

Wondering if someone can spot something I can't as i have been checking the brackets and commas are all correct for the past 2 hours and cannot see any problems.  

Find below: 1) The IF function,

2) Function as text (If wish to copy) and also attached it as a .txt file

and 3) the form with the variables names for each field.

Any help would be much appreciated :)

 

1)

2)

=IF(DB_KILOMETERS>25000, PRODUCT(Total_Kilometers, BAND_4), IF(DB_KILOMETERS>5500, IF(Annual_Total_Kilometers>25000, SUM(PRODUCT(MINUS(Annual_Total_Kilometers, 25000), BAND_4), PRODUCT(MINUS(25000, DB_KILOMETERS), BAND_3)), PRODUCT(Total_Kilometers, BAND_3)), IF(DB_KILOMETERS>1500, IF(Annual_Total_Kilometers> 25000, SUM(PRODUCT(MINUS(Annual_Total_Kilometers, 25000), BAND_4), PRODUCT(19500, BAND_3), PRODUCT(MINUS(5500, DB_KILOMETERS),BAND_2)),IF(Annual_Total_Kilometers> 5500, SUM(PRODUCT(MINUS(Annual_Total_Kilometers, 5500), BAND_3), PRODUCT(MINUS(5500, DB_KILOMETERS), BAND_2)), PRODUCT(Total_Kilometers, BAND_2))), IF(DB_KILOMETERS>0, IF(Annual_Total_Kilometers> 25000, SUM(PRODUCT(MINUS(Annual_Total_Kilometers, 25000), BAND_4), PRODUCT(19500, BAND_3), PRODUCT(4000, BAND_2), PRODUCT(MINUS(1500, DB_KILOMETERS), BAND_1)), IF(Annual_Total_Kilometers> 5500, SUM(PRODUCT(MINUS(Annual_Total_Kilometers, 5500), BAND_3), PRODUCT(4000, BAND_2), PRODUCT(MINUS(1500, DB_KILOMETERS), BAND_1)), IF(Annual_Total_Kilometers> 1500, SUM(PRODUCT(MINUS(Annual_Total_Kilometers, 1500), BAND_2), PRODUCT(MINUS(1500, DB_KILOMETERS), BAND_1)), PRODUCT(Total_Kilometers, BAND_1)))),0))))

3)

 

0 0
SELECTED ANSWER
replied on September 25, 2017

I think the issue is the MINUS formula.  It shows up in the formula picker, but it isn't listed in the help file, and I get errors when I try to use it.  I don't know the expected syntax, but based on playing around in Excel, I'm guessing it just means something like this:

=MINUS(10, 7)     results in 3     (i.e. 10-7=3)

If that is correct, then I think you can probably change things like this:     MINUS(Annual_Total_Kilometers, 25000)     to this:     (Annual_Total_Kilometers - 25000)     and achieve the expected result for that part of the formula.

1 0
replied on September 26, 2017

Super, It was the MINUS function causing the issue! Thanks for all the help! :)

1 0
You are not allowed to follow up in this post.

Sign in to reply to this post.