Make a table calculation and integrate with Google Spreadsheet

Hi Guys

im looking at integrating a test making sheet  in to my form but not sure if its possible.

it would be a list of incidents each incident as a penalty value for each time the operator made the fault.

example from below, operator dismounts incorrectly once this would show a mark in colom Faults x penalty = Award - but you could have more than one fault so the faults coloum would need to increase = - if needed 

You can do this with Jotform's input table make a calculation on the table itself. The result can be integrated to Google Sheet.

Here's the guide:

1). Make input table calculation https://www.jotform.com/help/343-How-to-Perform-Form-Calculation-in-the-Input-Table-Field

2). Integrate form with Google Sheet https://www.jotform.com/help/228-How-to-Integrate-Forms-with-Google-Sheets

Let us know if you have any questions or if you need further assistance. 

hi jed

not sure if it would work or how to start

please have a look at the mockup attached of how i need it to look and work

thanks 

You can try the multi type column field type for the input table as explained here: https://www.jotform.com/help/567-Input-Table-s-New-Type-Multi-Type-Columns 

The Fault column can be setup as a drop down option and others as text boxes.

The heading in between the rows can also be added - it will need minor customization using CSS.

Do try this and let us know if you face any difficulties.

Hi Gerard

thanks for info, getting there 

just a couple of things

i need to fix the value in each cell in one of the coloums "Penalty"

so when i select option from coloum= "faults"
coloum "award" will be the multiplication of coloum "penalty"

if that make sence

colin

You can calculate values in the Input Table through conditions. In order to do this, you can check and follow this guide:

https://www.jotform.com/help/414-Calculate-Values-in-the-Input-Table-Through-Condition

This should allow you to make the calculations in the Input Table.

I have moved your second question to a separate ticket here:

https://www.jotform.com/answers/2349765-Also-I-need-to-reduce-the-size-of-the-section-collapse-properties

I will reply to that ticket shortly.

i understand the calculations but im trying to avoid having to type the value in the "penalty"field

so what i would like is the i select the value from the "faults" field this would then multiply  by the fixed value in "penalty" and display the result in the "award" field.

You should assign calculation values to the "Penalty" fields. Unfortunately, you can not do that if they are textboxes, since this will allow your users to type a value.

I would suggest you to switch the "Penalty" column to Single Choice column. So, your respondents will have to just confirm and click on it. Then you can assign calculation values to this Single Choice column. Please refer to the attachment.

Your conditions should look like this:

Let us know if you need any further assistance.

looks like im going to be unable to use for tests.

because they may do more than one fault on more than one occasion per test the max would be 3 time per fault 

could not do that we a single choice button 

if you look at the test sheet below there at the fault field there is a (- 0 +) if they press the plus or minus this increases the number of faults   

might make it work your way 

if i could work out the calculations per fault description  (example)

Mounts / dismounts incorrectly                      (1*5 = 5)

Limbs / body outside confines of truck           (1*3 = 3) 

Fails to check all around                                (2*3 = 6)

Fails to look in the direction of travel               (1*1 = 1)

Fails to use appropriate safety device          

the points range is 1,3,5 depending on severity and a max of 3 fault per field "Mounts / dismounts incorrectly"

The idea is to use a Dropdown for the Faults column.

Here is a form example:

https://form.jotform.com/201477148886063

You can clone the form to check it in the editor.

yep that what i needed thanks, work out the calculation 

is there any css code we can enter so we get the number in the centre of box instead of left 

is there any css code we can enter so we get the number in the centre of box instead of left 

Please try adding the following CSS code to the form.

.form-matrix-values .form-textbox {

    text-align: center;

}

Thanks!