-
allansbrooksAsked on October 1, 2017 at 7: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.
-
John_BensonReplied on October 2, 2017 at 1: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.
-
allansbrooksReplied on October 3, 2017 at 8: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.
-
John_BensonReplied on October 3, 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.
-
allansbrooksReplied on October 3, 2017 at 11:45 PM
Amazing. Thank you.
-
allansbrooksReplied on October 4, 2017 at 6: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...
-
John_BensonReplied on October 4, 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.
-
allansbrooksReplied on December 6, 2017 at 8:55 PM
Okay, I'm in trouble here. I cannot get the conditional logic field calculation to allow me to enter the specific "shifts" field as a "if" function in my formula. Honestly, I don't understand how you were able to make the calculations take place in the individual fields WITHIN the table, despite watching your guide several times. I have the "input type" in the "input table properties" section set to "numeric text box." I do not know if that is correct, but I need to have some input values that are numeric (for calculations), and some that are txt for information. Is there a way you could assist me by giving me a "step by step" plan for how to access the appropriate calculations? This is so much more difficult than working with numbers/excel, it's very frustrating. Any help would be appreciated.
-
Elton Support Team LeadReplied on December 7, 2017 at 12:49 AM
Numeric text box won't allow you to enter text characters, it can only accept numbers. So if you want to accept both text and numbers, set it to just text box.
Regarding on the performing calculation inside the input table through condition, here's a full guide: https://www.jotform.com/help/414-Calculate-Values-in-the-Input-Table-Through-Condition
If you are still stuck at some point, please let us know so we will try to narrow down the steps for you.
Thanks
-
allansbrooksReplied on December 7, 2017 at 2:19 PM
Finally got it. Thanks for the info!