JotForm is a free online form builder which helps you create online forms without writing a single line of code. No sign-up required.
We believe that if one user has a question, there could be more users who may have the same question. This is why many of our support forum threads are public and available to be searched and viewed. If you’d like help immediately, feel free to search for a similar question, or submit your question or concern.
Empty User Contributed Dropdown mapped to Google Sheets through Zapier is returning "N/A" valuesAsked by fulminologyllc on September 10, 2015 at 10:31 PM
The second is that a Zapier mapping of the form data to Google Sheets is returning "N/A" values. I see that this is a common issue with Zapier mappings so I hope we can resolve it quickly.
Our form contains a long roster of selectable names so we've broken up the list into 26 alphabet drop downs. We use a first-letter selector tied to show/hide conditionals for each letter's name list. I'm using Zapier to direct all 26 lists to the same NAME column in our Google Sheet, but the 25 unused drop-downs return "N/A"s.
As an attempted work-around I mapped the sheet to a second sheet with a =SUBSTITUTE(Submissions!B2, "N/A", "", "") formula to correct the extraneous data, but this doubles the amount of data in my worksheet and renders it unusable. You can view the sheet here.
I think N/A is a result of an empty field from your form, particularly the User Contributed Dropdown widget. Especially, you have them mapped into a single column in Google Spreadsheet.
I have now forwarded this thread to our developers so they can check further the cause of this. If there are updates, you should know it in this thread.
For now, I would like to know if you really mean to use the User Contributed Dropdown for everyone to add their own name or to add an option. Note that this widget would allow submitters to add an option in the list. Because if not, I would suggest using our Dropdown field from the Form Tools section. Then you can do it like this:
a. Add a calculation widget in your form
b. Add all the dropdown fields in the equation
c. Mapped this calculation widget in your Zapier-Google Sheets integration.
The idea is to get one field in the integration instead of mapping/adding multiple fields in Zapier. This should work.
For example: http://www.jotformpro.com/form/52528031059956
This works with the default form widget as you stated however I do need the "add additional option" function for this form.
I see that with the Add Drop-Down widget, the calculator always returns a value of 0.
Zapier confirmed today that the N/A values are in the raw data coming from Jotform.
My boss has a more direct question - what solution do you usually offer for companies with an ever-changing list of 200+ employees who need to select their names from a list?
My boss has asked for the completed form by noon - I'm confident you'll have a solution for me!
Thank you once again,
I believe the setup you have is currently the best way for a dynamically changing list. Although the problem is that the values are showing N/A in the Zapier integration.
My colleague has already forwarded this to our developers to have it checked, we will update you on this thread when we hear any news. But I'll also try to look for a possible workaround that you can use.
Thank you Charlie. I appreciate it. I'm curious - are we the first to have a form with a large list of names and the ability to add more?
I would have thought that this would have often arisen with larger companies and I'm wondering what solution has been offered to them in the past?
Thanks in advance for your reply!
Personally, I haven't encountered a Zapier integration from JotForm to Google Spreadsheet, usually the Google spreadsheet is directly integrated on the form.
Although I did encounter other users who wants a dynamic list and the "User Contributed Dropdown" widget usually is advised to them. However, the problem with this one is that blank values are returned to Zapier integration as "N/A".
I'm not sure if my colleague, Welvin, had encountered a similar setup like this.
You're quite right - I'd intended to utilize Google and Jotform's direct integration. The only reason I had to opt for Zapier as a middleman was for the task of mapping 26 fields to a single column in the Google Sheet.
If there is a way to make the calculation widget work with the user-submitted drop-down, I would instantly switch the mapping to a direct communication between Jotform and Google Sheets using the calculation to carry the name data across.
Please ask around to see what solution the JotForm staff usually use for companies with large names lists. If successful we plan to use forms like these for many companies!
Let me know, and thanks again.
Unfortunately, I have tried adding the user-contributed dropdown in the calculation, but it's not working for both text and numeric equations.
The main usage of the user-contributed dropdown is to let your form user to add their own option in the field. We don't have any other tool in the form builder that would let you know do the same in the form.
We have our auto complete widget: http://widgets.jotform.com/widget/autocomp that would provide suggestions as soon as you start typing in the input field.
Another workaround would be to use a regular dropdown then a textbox where people can fill their name if they are not available in the dropdown options. Here's a very simply form showing the output: http://www.jotformpro.com/form/52538261166961 (choose "Other").
We have applied a fix for the N/A bug with Zapier API a couple of months ago—can you please confirm if this fix resolved your issue?