-
bdaparkingAsked on August 27, 2019 at 9:43 AM
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.
-
Victoria_KReplied on August 27, 2019 at 10:50 AM
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?
-
bdaparkingReplied on August 27, 2019 at 10:57 AM
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.
-
Victoria_KReplied on August 27, 2019 at 11:24 AM
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
-
bdaparkingReplied on August 27, 2019 at 11:31 AM
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?
-
Victoria_KReplied on August 27, 2019 at 11:38 AM
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!
-
bdaparkingReplied on August 27, 2019 at 11:46 AM
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.
-
Victoria_KReplied on August 27, 2019 at 12:13 PM
Thank you for the feedback! If you encounter difficulties with your forms, please let us know and we will gladly take a look.
-
bdaparkingReplied on August 27, 2019 at 11:08 PM
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.
When viewing my input table within the Form Calculation Widget to select which cells I want to use for calculation, the input table's rows do display correctly, with the correct row header names and the correct number of rows. However, the column headers and number of columns are stuck matching the default for the table. Here is a screenshot:
As you can see here, the Form Calculation Widget correctly labels my rows as A-G and allows me to select cells within the additional rows that I added. However, the widget does not show the correct header names for my columns; it still shows the default column headers before I customized the table. Also, it only shows the original default four columns; so it is impossible for me to select any cells within columns 5 or 6.
Here is my form link, in case that is helpful: https://form.jotform.com/92383341546156
-
Kiran Support Team LeadReplied on August 28, 2019 at 1:20 AM
The other question is moved to a separate thread and shall be addressed there shortly.
https://www.jotform.com/answers/1942814
Thanks!
-
bdaparkingReplied on August 28, 2019 at 8:57 AM
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?
-
Victoria_KReplied on August 28, 2019 at 9:35 AM
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.
-
Victoria_KReplied on August 28, 2019 at 1:07 PM
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.
-
bdaparkingReplied on August 28, 2019 at 8:50 PM
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.