Question

JavaScript to add first 2 cells in the third column of a table, subtract row 3 third column, and display the balance in row 4 third column

Forms

Updated April 28

asked on February 20

I have a Vacation Payout Request process in Laserfiche Forms. On the form is a table to calculate Vacation, Sick, Comp Time Off (CTO) balances. I'm trying to use JavaScript to calculate the Balance Remaining for the Vacation column. In the example below, I added the first 2 cells (50 + 30) and subtracted the third cell (10) to get the fourth cell's expected value, "4. Balance Remaining" (70).

The following JavaScript is not displaying anything in the "4. Balance Remaining" cell. I would like it to display the running balance as numbers are added in the top 3 cells in the Vacation column.

$(document).ready(function() {
  // When Vacation value is changed in the first 3 rows on the 'Leave Credits' table,
  //   update the Vacation balance remaining in row 4
  $(this).getElementById('Field38(1)').onChange = updateVacBal();
  $(this).getElementById('Field38(2)').onChange = updateVacBal();
  $(this).getElementById('Field38(3)').onChange = updateVacBal();
  // Function to perform the desired operation
  function updateVacBal() {
    // Get the values from the specified cells
    var r1c3 = parseFloat($(this).getElementById('Field38(1)').val());
    var r2c3 = parseFloat($(this).getElementById('Field38(2)').val());
    var r3c3 = parseFloat($(this).getElementById('Field38(3)').val());
  
    // Perform the calculation: (Row 1, Column 3) + (Row 2, Column 3) - (Row 3, Column 3)
    var result = r1c3 + r2c3 - r3c3;
  
    // Assign the result to Row 4, Column 3
    $(this).getElementById('Field38(4)').val() = result;
  }
}

0 0

Answer

SELECTED ANSWER

replied on March 5

Yes, using the new designer does make a big difference. Here is code for the new designer:

  function updateVacBal(){
    let r1c3 = parseFloat(LFForm.getFieldValues({fieldId: 102, index: 0}));
    let r2c3 = parseFloat(LFForm.getFieldValues({fieldId: 102, index: 1}));
    let r3c3 = parseFloat(LFForm.getFieldValues({fieldId: 102, index: 2}));
    let r4c3 = parseFloat(LFForm.getFieldValues({fieldId: 102, index: 3}));
    if (r1c3 !== r1c3) {
      // Value is not a number so set it = 0
      r1c3 = 0;
    }
    if (r2c3 !== r2c3) {
      // Value is not a number so set it = 0
      r2c3 = 0;
    }
    if (r3c3 !== r3c3) {
      // Value is not a number so set it = 0
      r3c3 = 0;
    }
    if (r4c3 !== r4c3) {
      // Value is not a number so set it = 0
      r4c3 = 0;
    }
    let result = (r1c3 + r2c3) - r3c3;
    if (result == result) {
      // value is a number so set field value
      if (r4c3 !== result) {
        LFForm.setFieldValues({fieldId: 102, index: 3}, result);
      }
    }
  }

LFForm.onFieldChange(function() {updateVacBal();}, {fieldId: 102});

0 0

Replies

replied on February 20 • Show version history

Hi Mandi,

You need to escape the parentheses and change the way the function is called.

Try this:

$(document).ready(function(){

$('#Field38\\(1\\)').on('change', updateVacBal);

$('#Field38\\(2\\)').on('change', updateVacBal);

$('#Field38\\(3\\)').on('change', updateVacBal);


   function updateVacBal(){

      var r1c3 = parseFloat(('#Field38\\(1\\)').val());

      var r2c3 = parseFloat(('#Field38\\(2\\)').val());

      var r3c3 = parseFloat(('#Field38\\(3\\)').val());
 

     var result = (r1c3 + r2c3) - r3c3;

     $('#Field38\\(4\\)').val(result);

  }

});

0 0

replied on February 24 • Show version history

Thank you for the suggestion, Rick. I copied your code, but I still couldn't get it to work. Not sure how to debug the JavaScript. Field ID changed to 102, so I did change 'Field38' in your code to 'Field102'. Variable name = Vac. Field ID = 102. Does it matter if the field is a Number field, opposed to a text field?

0 0

View 2 previous replies

replied on February 25 • Show version history

Hi Mandi,

Sorry I have a habit of forgetting the dollar signs, try this....although it will get you started as long as they fill in the first 3 rows with a value, you might want to tweak it so it either waits for all 3 values or computes when one of them is left empty. Another option is to have the column default to zero in each row unless someone puts a number in there:

$(document).ready(function(){
  
  
  

$('#Field102\\(1\\)').on('change', updateVacBal);

$('#Field102\\(2\\)').on('change', updateVacBal);

$('#Field102\\(3\\)').on('change', updateVacBal);


   function updateVacBal(){

      var r1c3 = parseFloat($('#Field102\\(1\\)').val());
      

      var r2c3 = parseFloat($('#Field102\\(2\\)').val());

      var r3c3 = parseFloat($('#Field102\\(3\\)').val());
 

     var result = (r1c3 + r2c3) - r3c3;

     $('#Field10\\(4\\)').val(result);

  }
  
  
  
 

});

1 0

replied on February 26 • Show version history

here is a slight change to function provided by @████████ to prevent the Field102(4) from being NaN.

  function updateVacBal(){
    let r1c3 = parseFloat($('#Field102\\(1\\)').val());
    let r2c3 = parseFloat($('#Field102\\(2\\)').val());
    let r3c3 = parseFloat($('#Field102\\(3\\)').val());
    if (r1c3 !== r1c3) {
      // Value is not a number so set it = 0
      r1c3 = 0;
    }
    if (r2c3 !== r2c3) {
      // Value is not a number so set it = 0
      r2c3 = 0;
    }
    if (r3c3 !== r3c3) {
      // Value is not a number so set it = 0
      r3c3 = 0;
    }
    let result = (r1c3 + r2c3) - r3c3;
    if (result == result) {
      // value is a number so set field value
      $('#Field102\\(4\\)').val(result);
    }
  }

1 0

replied on February 26

Thanks for the assist, Bert!

0 0

replied on March 4

Thank you Bert and Rick.

I tried the following without luck:

$(document).ready(function(){

   $('#Field102\\(1\\)').on('change', updateVacBal);
   $('#Field102\\(2\\)').on('change', updateVacBal);
   $('#Field102\\(3\\)').on('change', updateVacBal);

   function updateVacBal(){
     var r1c3 = parseFloat($('#Field102\\(1\\)').val());
     var r2c3 = parseFloat($('#Field102\\(2\\)').val());
     var r3c3 = parseFloat($('#Field102\\(3\\)').val());
     var result = (r1c3 + r2c3) - r3c3;

     $('#Field102\\(4\\)').val(result);
   }
});

Then, I tried the following without luck:

$(document).ready(function(){

   $('#Field102\\(1\\)').on('change', updateVacBal);
   $('#Field102\\(2\\)').on('change', updateVacBal);
   $('#Field102\\(3\\)').on('change', updateVacBal);

  function updateVacBal(){
    let r1c3 = parseFloat($('#Field102\\(1\\)').val());
    let r2c3 = parseFloat($('#Field102\\(2\\)').val());
    let r3c3 = parseFloat($('#Field102\\(3\\)').val());
    if (r1c3 !== r1c3) {
      // Value is not a number so set it = 0
      r1c3 = 0;
    }
    if (r2c3 !== r2c3) {
      // Value is not a number so set it = 0
      r2c3 = 0;
    }
    if (r3c3 !== r3c3) {
      // Value is not a number so set it = 0
      r3c3 = 0;
    }
    let result = (r1c3 + r2c3) - r3c3;
    if (result == result) {
      // value is a number so set field value
      $('#Field102\\(4\\)').val(result);
    }
  }
});

I noticed if I have any JavaScript, even just $(document).ready(function(){});, I see the following error in the F12 console:

ERROR ReferenceError: $ is not defined
at eval (eval at window.init (about:srcdoc:17:33), <anonymous>:3:1)
at window.init (about:srcdoc:21:63)
handleError@vendor.js?v=11.0.2311.50564:1550

I am using the newer Forms Designer. Does that make a difference?

0 0

SELECTED ANSWER

replied on March 5

Yes, using the new designer does make a big difference. Here is code for the new designer:

  function updateVacBal(){
    let r1c3 = parseFloat(LFForm.getFieldValues({fieldId: 102, index: 0}));
    let r2c3 = parseFloat(LFForm.getFieldValues({fieldId: 102, index: 1}));
    let r3c3 = parseFloat(LFForm.getFieldValues({fieldId: 102, index: 2}));
    let r4c3 = parseFloat(LFForm.getFieldValues({fieldId: 102, index: 3}));
    if (r1c3 !== r1c3) {
      // Value is not a number so set it = 0
      r1c3 = 0;
    }
    if (r2c3 !== r2c3) {
      // Value is not a number so set it = 0
      r2c3 = 0;
    }
    if (r3c3 !== r3c3) {
      // Value is not a number so set it = 0
      r3c3 = 0;
    }
    if (r4c3 !== r4c3) {
      // Value is not a number so set it = 0
      r4c3 = 0;
    }
    let result = (r1c3 + r2c3) - r3c3;
    if (result == result) {
      // value is a number so set field value
      if (r4c3 !== result) {
        LFForm.setFieldValues({fieldId: 102, index: 3}, result);
      }
    }
  }

LFForm.onFieldChange(function() {updateVacBal();}, {fieldId: 102});

0 0

replied on April 14

Brilliant, Bert! Thank you!

0 0

replied on April 28 • Show version history

After attending the Advanced JavaScript in the Form Designer class at Empower 2025, I realized I should be putting my table fieldId into a variable, so if the fieldId for my table changes, I can just update one line of code. I tried doing this several different ways, and none of them worked.

This code DOES work, but if the fieldId for the table changes from 102, I have to update several lines of code:

const updateVacBal = async () => {
  
  let VacationAccumulated = LFForm.getFieldValues({fieldId: 102, index: 0});
  let VacationEarned = LFForm.getFieldValues({fieldId: 102, index: 1});
  let VacationTaken = LFForm.getFieldValues({fieldId: 102, index: 2});
  let VacationBalance = LFForm.getFieldValues({fieldId: 102, index: 3});

    if (VacationAccumulated !== VacationAccumulated) {
      // Value is not a number so set it = 0
      VacationAccumulated = 0;
    }
    if (VacationEarned !== VacationEarned) {
      // Value is not a number so set it = 0
      VacationEarned = 0;
    }
    if (VacationTaken !== VacationTaken) {
      // Value is not a number so set it = 0
      VacationTaken = 0;
    }
    if (VacationBalance !== VacationBalance) {
      // Value is not a number so set it = 0
      VacationBalance = 0;
    }
    let result = (VacationAccumulated + VacationEarned) - VacationTaken;
    if (result == result) {
      // value is a number so set field value
      if (VacationBalance !== result) {
        await LFForm.setFieldValues({fieldId: 102, index: 3}, result);
      }
    }
  }

LFForm.onFieldBlur(function() {updateVacBal();}, {fieldId: 102});

This does NOT work, but if the fieldId for my table changes, I would only have to update one line of code (if it worked ):

const formFields = {
  tableFieldId: { fieldId: 102 },
  vacAccum: {fieldId: tableFieldId, index: 0},
  vacEarned: {fieldId: tableFieldId, index: 1},
  vacTaken: {fieldId: tableFieldId, index: 2},
  vacBalance: {fieldId: tableFieldId, index: 3}
};

const updateVacBal = async (formFields) => {
  
  let VacationAccumulated = LFForm.getFieldValues(formFields.vacAccum);
  let VacationEarned = LFForm.getFieldValues(formFields.vacEarned);
  let VacationTaken = LFForm.getFieldValues(formFields.vacTaken);
  let VacationBalance = LFForm.getFieldValues(formFields.vacBalance);

    if (VacationAccumulated !== VacationAccumulated) {
      // Value is not a number so set it = 0
      VacationAccumulated = 0;
    }
    if (VacationEarned !== VacationEarned) {
      // Value is not a number so set it = 0
      VacationEarned = 0;
    }
    if (VacationTaken !== VacationTaken) {
      // Value is not a number so set it = 0
      VacationTaken = 0;
    }
    if (VacationBalance !== VacationBalance) {
      // Value is not a number so set it = 0
      VacationBalance = 0;
    }
    let result = (VacationAccumulated + VacationEarned) - VacationTaken;
    if (result == result) {
      // value is a number so set field value
      if (VacationBalance !== result) {
        await LFForm.setFieldValues(VacationBalance, result);
      }
    }
  }

  LFForm.onFieldBlur(function() {updateVacBal();}, formFields.tableFieldId);

Any tips on getting the table fields assigned to JavaScript variables?

0 0

You are not allowed to follow up in this post.

Question

Question

JavaScript to add first 2 cells in the third column of a table, subtract row 3 third column, and display the balance in row 4 third column

Answer

Replies

Sign in to reply to this post.