Question

Subtraction Formula in Forms 10.0

Version 10

Updated November 2, 2018

asked on March 29, 2016

What is the formula used in subtracting tables? I would like to subtract beginning miles from ending miles. This is the formula that I am currently using that does not work.

=SUM(Mileage_1.Mileage_End, -Mileage_1.Mileage_Begin)

0 0

Answer

SELECTED ANSWER

replied on April 6, 2016

Give this javascript a try. This should take care of both your "Total Miles" and your "Total amount of miles driven".

$(document).ready(function () {
  $('.table').on('blur','input',function () {
    $('.table tr').each(function () {
      var minuend = parseNumber($(this).find('.minuend input').val());
      var subtrahend = parseNumber($(this).find('.subtrahend input').val());
      var difference = minuend - subtrahend;
      $(this).find('.difference input').val(difference).trigger('change');
    });
  });
});

function parseNumber(n) {
  if (isNaN(parseInt(n))) {
    var f = 0;
  } else {
    var f = parseFloat(n.replace(/\,/g,'')); //Convert to float number.
  }
  return isNaN(f) ? 0 : f; //treat invalid input as 0;
}

2 0

Replies

replied on April 21, 2016

Hey,

Laserfiche 10.1 now let's you calculate table rows and collections using Formulas.

Add this Formula to your Total Mileage Column:

=SUM(INDEX(Mileage_1.Mileage_End,ROW())-SUM(INDEX(Mileage_1.Mileage_Begin,ROW())))

I know it's late but just in case anyone else finds this useful.

3 0

replied on November 13, 2017

This was definitely useful, thank you!

1 0

replied on March 29, 2016 • Show version history

Crystal,

You should be able to use the following formula. This is assuming that your total field is outside the table.

=SUM(Mileage_1.Mileage_End)-SUM(Mileage_1.Mileage_Begin)

If you are attempting to do this across a row on table (i.e. to show the total in another column in the table), you would receive the following error message.

1 0

replied on March 29, 2016

Hi Crystal,

You should be able to use "-1*Mileage_1.Mileage_Begin" instead of "-Mileage_1.Mileage_Begin".

Hope this helps!

0 0

replied on March 29, 2016

Hi James,

I inserted =SUM(Mileage_1.Mileage_End, -1*Mileage_1.Mileage_Begin) and still did not work. I am subtracting across a table. Taking Ending Odometer Reading from Beginning Odometer Reading to get the Total Miles Driven for that day.

0 0

replied on March 29, 2016 • Show version history

When you say "subtracting across a table", do you mean that you are subtracting values among different columns in the same row? If so that isn't to do with the SUM formula in particular; at present Laserfiche Forms does not support calculations across table or collection rows.

John's method is valid but will not work for multiple rows: it essentially will fill every row of the "Difference" column with (Sum of everything in first column) - (Sum of everything in second column), which wouldn't be what is desired.

You can work around this using JavaScript, though. Let the table have the CSS class "table" and the columns have the classes "minuend", "subtrahend", and "difference" for their respective roles. Then the following should work:

$(document).ready(function () {
  $('.table').on('blur','input',function () {
    $('.table tr').each(function () {
      var minuend = parseNumber(jQuery(this).find('.minuend input').val());
      var subtrahend = parseNumber(jQuery(this).find('.subtrahend input').val());
      jQuery(this).find('.difference input').val(minuend - subtrahend);
    });
  });
});

function parseNumber(n) {
  var f = parseFloat(n); //Convert to float number.
  return isNaN(f) ? 0 : f; //treat invalid input as 0;
}

Hope this helps!

EDIT: Cleaned up presentation of code

1 0

replied on March 29, 2016

As James mentioned, the formula I entered above will only give you a total of miles driven.

It won't break out miles driven on each leg of the trip. For that you would need to use Javascript as in James' answer above.

1 0

replied on March 29, 2016

Thank you, that worked great for subtracting. Now I need to know how to not have the total populate on the added row? Please see attachment

Answers Subtraction Calculation.PNG (30.38 KB)

| Download

0 0

replied on March 29, 2016

Are you using John's method or JavaScript? If using John's method, what you see is how the calculation will work; we each expound upon that in previous posts. To calculate the difference across the table rows, you would have to use the JavaScript workaround. If using JavaScript, remove the Calculation from the "Total Miles" column. However, you should be able to use the formula =SUM(Mileage_1.Total_Miles) to fill the "Total Amount of Miles driven" field. (Where Total_Miles should be replaced with the actual variable name of that column.)

1 0

replied on March 29, 2016

I spoke too soon; the SUM formula seems not to work for fields which were given values using JavaScript. I edited the JavaScript from earlier to also populate a stand-alone field with the CSS class "total" with the sum of the differences found in each row of the table:

$(document).ready(function () {
  $('.table').on('blur','input',function () {
    var total = 0;
    $('.table tr').each(function () {
      var minuend = parseNumber($(this).find('.minuend input').val());
      var subtrahend = parseNumber($(this).find('.subtrahend input').val());
      var difference = minuend - subtrahend;
      $(this).find('.difference input').val(difference);
      total += difference;
    });
    $('.total input').val(total);
  });
});

function parseNumber(n) {
  var f = parseFloat(n); //Convert to float number.
  return isNaN(f) ? 0 : f; //treat invalid input as 0;
}

0 0

replied on March 29, 2016

James,

I am using the Java Script. When doing the previous Java Script the calculations worked great but would populate the total onto the next row. I corrected what you asked, if understanding you correctly and now the calculation does not work. I added "sum" into the css class for ending miles, and begin miles. I added "total" in css class for total miles. I used the new java script you sent. I also added "=SUM(Mileage_1.Mileage_Total)" in Total Amount of Miles Driven. I know I am not doing something correctly.....?

0 0

View 13 previous replies

replied on March 29, 2016

Crystal,

If you are using James' javascript then you will need to remove the calculation that you have in the 'Total Amount of Miles Driven' field. (James' code takes care of updating this field for you.

Please note that the CSS class you gave to the field is case-sensitive. If you are using James' code, this needs to be 'total' (as opposed to 'Total').

Are the sub-totals for the individual days working correctly for you? If not, could you post a copy of your javascript for us and also let us know what css classes you have given to each of your columns in the table?

2 0

replied on April 4, 2016

I need the calculation to not populate on the added row. Is there an add-on in the java script that will take care of that?

Adding a row calculation.PNG (15.82 KB)

| Download

0 0

replied on April 4, 2016 • Show version history

Crystal,

Looking at James' javascript it appears that it should not be populating the totals in the extra rows. Can you post a copy of the javascript you have in place so we can ensure they match?

EDIT: Updated to correctly read 'extra rows'

0 0

replied on April 6, 2016

Hi John,

Here is the Screen shots of my calculations that I am using, as well as the java script that James sent me. I am also using the CSS class "table" for that column field. Please let me know what I can do to not have the total miles NOT population in the next row. Thank you!

Total Amount of Miles Driven Calculation.PNG

Begin Miles Calculation.PNG (27.7 KB)

| Download

Total Miles Calculation.PNG (26.12 KB)

| Download

Total Amount of Miles Driven Calculation.PNG (20.98 KB)

| Download

Java Script for Mileage Calculation.PNG (45.11 KB)

| Download

0 0

replied on April 6, 2016

Crystal,

You need to take the calculation out of the "Total Miles" column.

James' javascript will take care of updating this column.

0 0

replied on April 6, 2016

Additionally, you need to make sure each of your columns has the correct CSS classes.

Based on the javascript, these should be as follows.

Begin miles should have a CSS class of "subtrahend" (without quotes)

End miles should have a CSS class of "minuend" (without quotes)

Total miles should have a CSS class of "difference" (without quotes)

0 0

replied on April 6, 2016

Thanks John!

When changing that, the calculation does not use 1,000. See screen shot. Also what would I use for the Total Amount of Miles Driven calculations then?

Answers Subtraction Calculation.PNG (18.34 KB)

| Download

0 0

replied on April 6, 2016

Double-check that your table has a CSS class of "table" (without quotes)

0 0

replied on April 6, 2016

It does, see screen shot.

Double Check Table.PNG (35.44 KB)

| Download

0 0

replied on April 6, 2016

Crystal,

Change line 12 in your javascript to read:

var f = parseFloat(n.replace(/\,/g,''));

The problem is that the existing code does not accept commas in your input fields. This should resolve that problem. Once we have that working, we will look at your "Total number of miles driven" field.

0 0

replied on April 6, 2016

John,

I have tried it with both java scripts James sent me and changing line 12. Both does not seem to work. They are not adding calculations at all. See screen shots

Changed Java Script.PNG (43.07 KB)

| Download

Second Java Script.PNG (50.11 KB)

| Download

0 0

replied on April 6, 2016

Make sure you are using the javascript shown in your "Changed Java Script.png" file.

What is happening when this is in place? Before when you had "46,000" in End Miles and "45,000" in Begin Miles it showed "1" in Total Miles. What does it show now?

I will need to know the inputs and what output it is showing in order to help get this working for you.

0 0

replied on April 6, 2016

I really appreciate this. There is no total miles being calculated now. There is no digit showing and there used to be at least a 0. Here is the java script I used.

New Java Script.PNG (42.09 KB)

| Download

John's changed Java.PNG (12.23 KB)

| Download

0 0

SELECTED ANSWER

replied on April 6, 2016

Give this javascript a try. This should take care of both your "Total Miles" and your "Total amount of miles driven".

$(document).ready(function () {
  $('.table').on('blur','input',function () {
    $('.table tr').each(function () {
      var minuend = parseNumber($(this).find('.minuend input').val());
      var subtrahend = parseNumber($(this).find('.subtrahend input').val());
      var difference = minuend - subtrahend;
      $(this).find('.difference input').val(difference).trigger('change');
    });
  });
});

function parseNumber(n) {
  if (isNaN(parseInt(n))) {
    var f = 0;
  } else {
    var f = parseFloat(n.replace(/\,/g,'')); //Convert to float number.
  }
  return isNaN(f) ? 0 : f; //treat invalid input as 0;
}

2 0

replied on April 6, 2016

Worked perfect!!!! Thank you so much!

0 0

replied on November 2, 2018

what do you put in the CSS of the total miles driven then?

0 0

replied on April 4, 2016

All the calculations are working. I was wondering if it is possible to use columns to fill in a .pdf, and when adding a row it fills that row into the .pdf? I have multiple columns that I need to fill into the same .pdf. See screenshot

Screen Shot Days.PNG (22.04 KB)

| Download

Expense Report- Fillable.pdf (219.48 KB)

| Download

0 0

replied on April 4, 2016 • Show version history

Crystal,

There's a great answer here detailing using Workflow to fill a PDF. https://answers.laserfiche.com/questions/91928/Create-token-from-Form-table-to-Fill-out-a-PDF

If possible, try to keep each of your posts focused on a single question. It helps to ensure readability for future people looking for answers.

EDIT: Fixed typo

1 0

You are not allowed to follow up in this post.

Question

Question

Subtraction Formula in Forms 10.0

Answer

Replies

Sign in to reply to this post.