How to add calculations on Input Table field?

  • Profile Image
    allansbrooks
    Asked on October 01, 2017 at 07:27 PM

    Hey JotForm.  I am trying to include a table in my form, where guys can enter numerical information.  I would like to have some of the cells automatically calculate totals based on the entered information (essentially I want to include a cell with a formula).  Is this possible on the included "input table" from element?  If not, is it possible to upload a table from Mac Numbers or Excel?  I am including a screen shot of the current input table I created.  I would like to be able to add formulas to calculate hours based on shift numbers, and also to add a column at the bottom to calculate total hours.  Please advise.

  • Profile Image
    John_Benson
    Answered on October 02, 2017 at 01:09 AM

    We have a full guide on how to add calculations on the Input Table field. Here's the guide on How to Perform Form Calculation in the Matrix Field

    If you need assistance regarding on the guide that I have provided, please do not hesitate to ask us. We'll be happy to assist you.

    Thank you.

  • Profile Image
    allansbrooks
    Answered on October 03, 2017 at 08:43 PM

    So, I can see that I can use the Calculation Widget to create a separate calculation outside of the input table, but what about having calculations made inside the table.  For instance, in my table, if someone puts in number of shifts, I would like to be able to have the hrs. column auto fill based on the formula (Shifts x 8 = Hrs.), and then add a row at the bottom where I could calculate total Hrs. by using a SUM function for all of the entries in the Hrs. column.  There are other fields such as Month and Day(s) which are purely informational, but I want to have all this information IN the table, not in a separate form element.  I did not see that this was addressed in the Matrix Field Calculation link.  Please advise.

  • Profile Image
    John_Benson
    Answered on October 03, 2017 at 10:14 PM

    Please check my demo form: https://form.jotform.com/72758335528971

    If you like it, you can clone it and check the conditions settings I have created. Here's a guide on how to copy the demo form: https://www.jotform.com/help/42-How-to-Clone-an-Existing-Form-from-a-URL

    Here are the guides that I have used in the demo form:

    https://www.jotform.com/help/259-How-to-Perform-Form-Calculation-Using-a-Widget

    https://www.jotform.com/help/268-Conditional-Logic-Inserts-Text-Calculation-Into-A-Field

    Here's the demo form in action:

    If you have any question, please contact us again. Thank you.

     

  • Profile Image
    allansbrooks
    Answered on October 03, 2017 at 11:45 PM

    Amazing.  Thank you.


  • Profile Image
    allansbrooks
    Answered on October 04, 2017 at 06:26 PM

    Okay, so I have to confess, I've watched all the videos, and also see that you were able to create the form according to my specifications, but I really need some step by step help.  The individual calculation widget is what you used to calculate the total hrs., but how are you inserting the calculations into the individual cells?  Is there no way for me to simply enter a formula a la a spreadsheet?  The calculation instructions are also confusing to me (e.g. "if 2. Number 1" etc.), and I am unclear how to apply to my issue.  There is no instruction that appears to detail the use of the calculations in a table, but clearly you have been able to do it...

  • Profile Image
    John_Benson
    Answered on October 04, 2017 at 10:02 PM

    I created another short demo form so I can provide you a guide on how I did it. Here's the link to the demo form: https://form.jotform.com/72767709428975

    In the demo form, I added an Input Table field and Form Calculation widget.

    After that, I am going to use this guide to calculate the "Hours" in the Input Table field.

    In the calculation page, if the Input Table is filled then the "Shift" data will be multiplied to 8 and it will pass the value/result to the "Hours" field. Here's a screen animation guide:

    The next step is the Form Calculation widget that will get the Total of the "Hours" in the Table Input field.

    Open the widget settings and click the Add Field button. Add all the "Hours" in the Table Input field. After that, put the "+" between the added fields.

    I also set the field to "Read-only" so the Total Hours field cannot be edited. Here's a screen animation guide:

    Update: I also updated the first demo form: https://form.jotform.com/72758335528971

    I hope that helps. If you have any questions or need clarifications, please contact us again. We'll be happy to assist you.