Pulldata csv column error in Survey 123

[AnuSawant]

I am trying to use pulldata function to populate some of the fields. I am getting error every time when I add new column in the pulldata it is changing first value of the previous question.

e.g. I am trying to populate rating criteria which is depend on the various field added before, (refer to the snapshot of my csv below). So if I choose "ditcheffectiveness = Good" I get "ratingcriteria = 3" which is correct, but when I add new column (or insert a new column) before "ratingcriteria" the next time when I choose "ditcheffectiveness = Good" instead of getting "ratingcriteria = 3"  I get "ratingcriteria = irregular" (which is the value of rockfriction").  It has been showing this error only for first option for the rest it shows correct ratingcriteria value.So to get the correct value either I have to delete ratingcriteria or sometimes both the coulmn and have to add it again. It is annoying as I have few more columns to be added to the csv. 

So my questions are:-

1) Is there any restriction on number of columns can be added to the csv or

2) also the sequence of the columns added into the csv.

3)Do we have to add all the column on the first place in a sequence,they should be.

Thanks,

Anu 

[Anonymous User]

Hi Anu,

The problem is that you have commas (,) in your CSV data. Currently the pulldata() function does not support a comma in the data of the CSV. Therefore you columns of data are being separated in the wrong place and the quotations " are being included in the data returned from the CSV.

We have an open issue for this and hope to support it in the future, I have updated your comments against this issue. In the meantime you will need to remove the commas from your data if you want it to work as expected.

Regards,

Phil.

[AnuSawant]

Thanks Philip for the prompt respond. So if I am not wrong I have to remove comma from my choices list too, as these two are depend on each other.

Cheers,

Anu

[Anonymous User]

Hi Anu,

Yes it is not advisable to use a comma in a choice list value either as it may cause undesirable effects in the app or website when processing values, however commas can be used in the label field, just not the name. Please refer to the type sheet which contains special characters that should not be used, as well as reserved words that should not be used.

Phil.

[AnuSawant]

Sure...

thanks

Anu

[AnuSawant]

Hi Philip,

Need more help. I am trying to count the number of answers captured under each rating criteria (which is calculated by using pull data as shown above.)

e.g. I want to know how many answers has been captured under rating criteria 3 and so on for one site location. I am not sure which function I can use for this, can you please guide me?

Thanks,

Anu

[Anonymous User]

Hi Anu,

Just to be sure, so you want to count the number of times an answer (rating) has been used but across multiple different questions in the survey? Are the questions you want to count in a repeat or only the main part of the survey, or both? How many different rating criteria numbers are there, is it a set list of all questions?

Unfortunately, if I am understanding  your requirement correctly, there is currently no calculation or function to count how many times a number has been used across multiple different answers. However, there are a few ways we could do this manually if the rating criteria is a set number of values, we could use several IF statements and null field types that are hidden to apply the number 1 or 0 where each rating criteria has been used, and then sum those values to get a count. This will only work if you have a small number of possible rating criteria numbers (ie 5), as we have to do this for each value for each question. IF you have 10 questions and 5 rating criteria numbers, that will require added about 50 extra null fields to your survey. They will be hidden so the user will not see them, but they will help to get the functionality you are after.

If you are able to share a copy of your XLS form and CSV data files used in pulldata then I can take a closer look.

Phil.

[AnuSawant]

Hi Philip,

Yes I want to know count of each rating ( rating values are 3,9,27,81) calculated for different questions. Theses questions are not in repeat series, and is a part of main survey.

e.g. If there are 10 questions I want to know how many of them got rating value 3 and ho many of them got rating value 9 and so on. So far I have manged to populate rating criteria automatically by using pull data ...unfortunately (and if I am not wrong )I cant use sum or count function. 

I can email you the details if you can send me your email ID. Unfortunately because of the privacy issue I cant share the details publically.

Thanks,

Anu

[Anonymous User]

I have sent you a private message with email details.

[Anonymous User]

Hi Anu,

Thanks for sending me your survey xls, after having a closer look I realised we don’t need as many extra fields compared to what I mentioned in my previous reply. We only need 4 extra note fields with 13 IF statements in each (one IF statement for each answer). If you then want to combine the counts into one string output summary field, add an extra note field, so 5 fields total.

If you create the note fields with names and calculations, this means they will get added to your feature service as fields. If you do not want to store them as additional fields, you need to either remove the name from these notes (but then we can't use them for concat string) or set the bind::esri:fieldType as null. Alternatively, if you do not want to display them on survey but do want to store them in feature service, you could set them to be hidden fields.

I have emailed you the updated xls file as an example. Below is one of the calculations I have used for rating 3 count:

if(${field01}="3",1,0) + if(${field02}="3",1,0) + if(${field03}="3",1,0) + if(${field04}="3",1,0) + if(${field05}="3",1,0) + if(${field06}="3",1,0) + if(${field07}="3",1,0) + if(${field08}="3",1,0) + if(${field09}="3",1,0) + if(${field10}="3",1,0) + if(${field11}="3",1,0) + if(${field12}="3",1,0) + if(${field13}="3",1,0)

And this is what it should look like in your survey:

Hope this helps and is what you were after.

Regards,

Phil.