How can I calculate a form field total from all form submissions?

As I am working through the form functions/widgets available, which I have submitted different tickets. This question is a summation of my findings and looking for a way to calculate field totals for all form submissions.

I have looked at every widget and function.  The Guest Registry, Ticket Purchases, Inventory, etc.  While some widgets will calculate the amount available, it is not possible to use calculations in Gift Registry.  Other widgets like Ticket Purchases show amount available, but no count available. So what if someone wants to purchase more than one ticket? That leads us to Inventory. Now this is a viable solution, but is limiting in that we are not able to combine a total count from two or more of the selections.  For example, if I have a Vendor - Member and Vendor - Guest with only 10 tables available combined, I am not able to track for a total of 10 tables. Check boxes - great for calculations, but limited to 

So, my question: Is it possible to use drop down boxes and calculate the total from all submissions; then, use conditional formatting to hide the fields when total count of 10 is reached? 

I believe Inventory and Drop Down boxes will provide the greatest opportunity for an optimal solution.

Please look at my test form for examples:

https://form.jotform.com/61224797422962

What would you recommend is the best approach to achieve the following?

(a) Total ticket sales or meal count for a given selection can be calculated from all submission; then use conditional formatting to hide/disable certain fields when the max value is reached.

(b) Calculate Meal Count and Ticket Count on the current form to do a conditional formatting comparison and disable the submit button if the counts are not equal

(c) Shown as a group. Widgets provide a nice table format. If check boxes can be used to do form and total submission count, is it possible to have boxes grouped tightly like a Widget table?

Thanks,

Okay.  I think the best approach and option with the greatest flexibility is the Inventory Widget.  Although, I do not believe there is the option to combine the total Vendor Table sales (two inventory fields), I am able to successfully calculate meals and tickets and I added a CSS inject to remove the padding so it now looks like one table.

Let me know if there is something I am missing to be able to hide the widget field if total vendor submissions (Member Vendor + Guest Vendor) is greater than 10.

https://form.jotform.com/61224797422962 

Thanks,

I believe what you're trying to achieve will only be possible if we can combine the total selection made on different Inventory widget to be passed to a textbox field, then from the textbox field to another Inventory widget that will hold the total number of selections so it will be recorded. However, upon further testing, values are not being passed from a textbox field to the Inventory widget.

I would really like to apologize, it seems that there is some part of your form process that's missing.

Here's what I only understand:

1. You will use the Inventory widget to list down your items. The maximum total number of items that can be selected for all the Inventory widgets is 10 per submission.

2. You need to get the total number of items purchased on all form submissions.

Are those correct? If not, can you help us understand your concern clearly by giving us the exact step-by-step process on how your form should work, please? Also, would you want to have the total number of items in all form submission shown on the form? Or, it can be shown anywhere else as long as you will be updated?

We will wait for your response. Thank you.

Hi Mike,

Thanks for the response.  To give context - I hold an event quarterly. Total ticket sales (seats) are usually not a problem. The venue can hold up to 200 and I have about 120 registrants per quarter, so I do not need necessarily to monitor total seat count. Depending on the room that we are given, I have to limit the total number of vendor tables available.  However, members and guests are charged different rates, so I need two different line items - Member Vendor and Guest Vendor. The challenge is that I only have a total of 10 vendor spots available for my next event (member vendor + guest vendor tables), thus I am trying to find the best approach to monitor the total vendor table count, then turn off the option with conditional logic or a widget instead of trying to catch it manually.  Last event, we did not have the resources to monitor counts and we oversold vendor spots, which caused a significant issue. 

If I use the Inventory widget, I am not able to combine the two vendor options for total count.

If I use drop down boxes, I do not know how to calculate all form submissions to hide a vendor table option, when a total limit for all form submissions is reached. This would be a perfect solution if the total form total vendor count can be determined, providing the greatest flexibility.

Reservation Options

Member Dinner - $35 (no limit)

Member Vendor Dinner - $35.00

Guest Dinner - $40.00 (no limit)

Guest Vendor Dinner - $60.00 

Event Table (seats 10) - $350.00 (no limit)

The maximum total number of vendor options only (using drop down boxes or Inventory widgets) is 10.

Your assistance is greatly appreciated.

Thanks,

Is there a way to conditionally update/calculate an Inventory Widget item?  For example, if Member Vendor and/or Guest Vendor is selected, then update a Total Vendor widget field?

Once the Vendor Widget Field reaches a limit, hide both Member Vendor and Guest Vendor options?

Alternatively, use condition to calculate total vendor options submitted either using drop boxes or a widget.  

Thanks,

I believe the problem here is how to get the total number of vendors on ALL of your submissions. Currently, the Inventory widget has separate items, they each have their own available quantities and they cannot share it to other widgets.

In your case, The Member Vendor and Guest Vendor are two separate widgets that have their own available quantities. It is not possible to update the widget settings using conditions, they are hard coded. This should be possible if you are using a custom script that uses API. Here's the current idea that I have:

1. First, use this API to check and count the number of submissions that selected a specific vendor (Guest or Member): http://api.jotform.com/docs/#form-id-submissions 

2. You can then have another custom script using API to update your widget settings, that's possible using this API function: http://api.jotform.com/docs/#post-form-id-question-id 

Another much more simpler way is to just merge the "Guest" and "Member" vendor and have an additional option display to select specific vendor type. Here's an example form: https://form.jotform.com/61244460833957. You can clone it to have a version of that form in your account. 

Noticed that there's a general "Vendor option" as an Inventory widget.

When you fill out the widget, additional radio button will show up showing the specific vendor option with the prices I've done that using conditional logic. The calculation is simple, I have multiplied the quantity of the inventory widget with the assigned calculation value of the radio buttons.

With that, the Member and Guest only has a total of 10 tables collectively. 

I hope that gives you an idea on how to proceed.

That will work.

Thanks,

@docketmaster

To better assist you, I have opened a separate thread for your concern. Please refer to this link instead: https://www.jotform.com/answers/1070817. We will address it accordingly.

Thank you.