Prepopulate records into a grid for user to select and total

What I would like to do is to export a list of posted check payments from Google Sheets, by creating a URL in Google Sheets with the records as parameters and then display these posted payments in a grid with a checkbox for the user to select which payments he/she wants to deposit.  It would then total all checked payments, for the deposit total.

We would need the following columns in the grid:  1) Payment Serial number (could be hidden to the user but would need to be part of the submission record; 2) date of payment; 3) description; 4) amount; 5) checkbox.  Each row where the checkbox is checked would be added a total field at the bottom, which could either be part of the grid or an independent field.

I am experienced with setting up Jotform URL parameters; so that is not an issue.  And I know how I can create this using all independent fields, but that will require a lot of field and condition setup, as I want the capacity for 40 or 50 payment records.  So I'm wondering if there is some kind of a widget that will allow for prefiling of fields via URL parameter and also allow for conditional totaling of fields inside of it.

Hello!

My apologies, but I am not sure yet if I can understand the task in full. Let us ask some questions to clarify. As far as I understand, you have a sheet with payments information. You need to populate form with that data. Do you need to populate options of a checkbox element for users to select? If this is correct, I'm afraid it would not be possible to make checkbox options dynamic. 

We have a widget, which can populate form from attached excel sheet. Can you please check if it can help in your case? 

Guide: How-to-Use-the-Spreadsheet-to-Form-Widget

Thanks for the quick reply.

No, I don't need a dynamic checkbox option.  I don't need to pre-populate whether the checkbox is checked.  I actually need to populate all the other fields in the row OTHER than the checkbox.  The user filling out the form would determine whether to check the box.

Unfortunately, the Excel sheet widget is not helpful, because it requires the manual update between Excel and Jotform; it doesn't update dynamically as the data in Excel changes.  (Plus I'm using Google Sheets not Excel.)

Again, what I'm wondering if there is some kind of a grid or table widget that will allow for prefiling of its fields via URL parameter and also allow for conditional totaling of fields inside of it.

I see, thank you for clarifying. An Input Table can be prepopulated via URL and also allows calculations via conditions. Please take a look at my test form: www.jotform.com/90722701315953?q3_input3[0][0]=test&q3_input3[0][1]=test1 

Related help guide: How-to-Perform-Form-Calculation-in-the-Matrix-Field

Thank you!  I hadn't found that option.  That appears to be what I need, except I also need a checkbox for each row, that will determine whether the value is included in calculating the total.  Apparently I can't do that with an input table...  Any other ideas?

I'm afraid, input table would be the only 'table' element that can be populated via URL. But, we have recently added new feature to these tables, you can try using Multi Line Columns, checkbox should be there:

Please note that this feature is still new, so we would appreciate if you will provide any feedback for it. 

Thank you!

Thank you, Victoria!  I'll have to play with it, of course, but this looks awesome!  I LOVE Jotform!  You are so quick to respond.  I thought you were going to tell me no dice but you had a solution!  I'm very glad I asked.

Thank you for the feedback! If you encounter difficulties with your forms, please let us know and we will gladly take a look.  

Thank you for requesting feedback for your new input table features.  So I'm playing around with them and do see a bug when using the Form Calculation Widget with the input table, as per the instructions in https://www.jotform.com/help/343-How-to-Perform-Form-Calculation-in-the-Matrix-Field.

The other question is moved to a separate thread and shall be addressed there shortly.

https://www.jotform.com/answers/1942814

Thanks!

So after playing with this, I believe this Input Table does most but not all of what I needed.  This screenshot illustrates what I was trying to do:

So, in other words, if someone checked the box for Row A, the calculated total field would change from 0 to 4554.  If they then also checked the box for Row D, the calculated total field would change to 4682 (with checkboxes A and D checked).

I know it is possible to setup conditional calculations like these by using form conditions, but I can't use form conditions based on cells inside the Input Table, as form conditions treat the Input Table as a single field.

Based on my research, it doesn't appear possible to create an "IF" statement using the Form Calculation Widget.  So there may be no way to do this, other than by using all independent fields, which seems a little overwhelming to setup, given the number of rows I want to support.

So, going back to my original question, we found a table that would allow for prefiling of its fields via URL parameter but unfortunately not allow for conditional totaling of fields inside of it.

Any other thoughts?

Hi again and thank you for providing detailed explanations. Please allow me some time to check this on my test form to see if I can get this working. I will get back to you via this thread when ready.

There could be a solution to this, but a bunch of conditions will be required:

Here is what I did:

I have created fields '1-4' to be filled with '1' (calculation value of Input Table). Then, another set of fields (1 value - 4 value) is being populated conditionally if corresponding field 1-4 is greater than '0'. Total field is set to simply sum up fields (1 value - 4 value). 

Here is a test form: https://www.jotform.com/92395398968987 

And some screenshots: 

Hope this helps.

Thanks.  I do appreciate you coming up with a solution.  I'm finding this a little too complex to be practical for a table with several dozen rows, but thanks again for taking the time to come up with a way to do it.