Question

Forms 10 Calculations

Forms

Updated April 28, 2016

asked on December 1, 2015 • Show version history

We are currently writing a form with some calculations that we cannot figure out.

Here is a sample of the Form in question:

What we are looking to accomplish is to fill in the Payment Line 1 Amount field (in the Payments table) with the sum total of the Invoice Line Amount fields that match the Invoice Line Payment Line 1 AND Paid = Yes.

So from the above shot, Payment Line 1 (in the Payments Table) should be 9 since Rows 1-3 (in the Invoice Lines table) are all for Payment Line 1 but Row 2 is set to No.

Payment Line 2 (in the Payments table) should be 17 since rows 4 and 5 (in the Invoice Line table) are set to Payment Line 2 and marked Yes.

We had limited success when using only one criteria (where Paid = Yes). We were able to use:

=SUMIF(Invoice_Lines.Paid,"Yes",Invoice_Lines.Amount) for our Payment Line 1 and it worked in that it displayed the totals of all the amounts but only if Paid was marked as Yes. Now we are trying to add a second criteria (Payment Line = {n}). We did find =SUMIFS as an Excel formula and that seems to work perfectly but after playing, the Forms Help File does not seem to list SUMIFS as a supported function.

Does anyone have any suggesstions as to how we can reach or goal of using multiple criteria in our SUM formula using Forms Calculations. Or would this still need to be done with some custom Java scripting activities. Would be great to use out of the box calculations simply for support going forward and for somewhat for a time crunch.

Thanks!

0 0

Answer

SELECTED ANSWER

replied on December 4, 2015

Hello Chris,

For your case in particular the following JavaScript should work:

$(document).ready(function () {
  $('.cf-table-block.invoice').on('blur','input',update);
  $('.cf-table-block.invoice').on('blur','select',update);
  function update() {
    var amt1 = 0;
    var amt2 = 0;
    var amt3 = 0;
    $('.cf-table-block.invoice tbody tr').each(function () {
      var amt = parseNum($(this).find('.amount input').val());
      if ($(this).find('.paid select').val() == "Yes") {
        switch($(this).find('.line select').val()) {
          case "1":
            amt1 += amt;
            break;
          case "2":
            amt2 += amt;
            break;
          case "3":
            amt3 += amt;
            break;
          default:
            break;
        }
      }
      $('.cf-table-block.payments tbody tr:nth-child(1)').find('.amount input').val(amt1);
      $('.cf-table-block.payments tbody tr:nth-child(2)').find('.amount input').val(amt2);
      $('.cf-table-block.payments tbody tr:nth-child(3)').find('.amount input').val(amt3);
    });
  }
  function parseNum(n) {
    var f = parseFloat(n);
    return isNaN(f) ? 0 : f;
  }
});

This example used only 3 Payment Lines, but of course can be extended for more. I tested on the following form layout, where you can also see the CSS class assignments used (again, can be customized to match your preferences):

Hope this helps!

2 0

replied on December 4, 2015

Thanks James!

0 0

Replies

replied on December 4, 2015

Hi there,

We aware of the feature request for SUMIFS. Please refer to this post for javascript customization https://answers.laserfiche.com/questions/87901/SUMIF

0 0

replied on April 28, 2016

Hi,

SUMIFS is supported in Forms 10.1.

SUMIFS( Reference R ; Reference R1 ; Criterion C1 [ ; Reference R2 ; Criterion C2 ]... )

Thank you.

0 0

You are not allowed to follow up in this post.

Question

Question

Forms 10 Calculations

Answer

Replies

Sign in to reply to this post.