External Cascading Select Not Working

[bobwright]

Greetings Everyone,

I can create a regular cascading select, but when I convert it to an external cascading select the choices that are in the external_choices tab do not show up in the form. I believe I've followed the guidance provided here (http://xlsform.org/#external) and in the spreadsheet example it references, but I've probably got tunnel vision by now. Perhaps it's a bug? Source spreadsheets attached. I am using Excel 2013 on a Win 7 machine. Any suggestions would be greatly appreciated. 

[JamesTedrick]

What you are seeing is a type mismatch between the the values generated in the first select_one (dpas) and select_one_externals that occurs after the first.  remove the dpa=${dpa} component of the ranges and sections choice filter - from looking at the structure of the data, no ambiguity is created by it's removal, yes?

[bobwright]

Hi James,

Thanks for the response. I looked at the schema and see what you mean. I’m not sure if you meant the ranges and sections filter or the filters for both the ranges and sections, so I tried it both ways. Neither worked. I also tried re-creating the spreadsheet from scratch with no luck.

Bob

type

name

label

choice_filter

select_one dpas

dpa

Deer Permit Area

select_one_external towns

town

Township

dpa=$ select_one_external ranges range Range town=$ select_one_external sections section Section town=$ and range=$ type name label choice_filter select_one dpas dpa Deer Permit Area select_one_external towns town Township dpa=$

select_one_external ranges

range

Range

dpa=$ and town=$ select_one_external sections section Section town=$ and range=$

[JamesTedrick]

Check to see if this version works- it does so on my computer

Edit: Add attachment 

[bobwright]

Attachment?

[JamesTedrick]

Edited message above with the file

[bobwright]

The file that James provided backed worked as both xls and xlsx  files...until I populated them with the real data. Once I did that, the external select no longer worked and editing the spreadsheet back to the original from James did not correct it. Thinking that perhaps there is a bug in Survey123, I used the same spreadsheets (choices and external choices versions) to create and load forms in ODK Collect (https://opendatakit.org/). Same issue. Attached are more complete spreadsheets for our Chronic Wasting Disease surveillance operation this fall, with complete cascading select choice lists (over 5000 records). Putting these choice lists in the "choice" tab works, but it definitely responds slower and I'm worried about devices locking up in the field. So, now that I have a bad case of tunnel vision I'm hoping that someone can point out an obvious overlook for me. Thanks for any help!

As an aside and perhaps a clue, I have been getting the error message below, but cannot document a pattern other than if I close both 123 and the spreadsheet, then open 123 and the form of interest I generally don't get the message.

[tomhughson]

Hi James, rather than raise a new post I thought I'd revive this old thread as my problem is similar. Over time one of my surveys has become too slow to load so I've decided to move the select_one cascade choices to an external_choices tab. While previously the cascading selects were working as expected, moving the choices to the external tab has resulted in the selections for a particular select-one depicted in the image below becoming unavailable. Do I need to make any adjustments to the Choice filter when moving to an External_choices tab? Hoping you may be able to pinpoint for me please where I need to make some adjustments as I can't readily see where the issue is? I've attached the xls form as well. Thanks, Tom

[DougBrowning]

Ok I think I go it.  It seems confused that your field name and list name are the same.  I changed KPI_number to KPI_numberList and it worked.  Note I also had changed the column Mppart to Mppart2 so may need to do both.

For me as a practice I always add List to to my list names.  So MgtPlanList and LPI_numberList, etc so no confusion.  XLS forms really count on all names being unique.

Note I also found some names in there with what I think are opps spaces.  For example RowleyShoals _Environment.  You prob want to remove those.

Hope that helps

[tomhughson]

Hi Doug, can you please send me the working xls so I can see the changes you have made to get it to work? Thanks for looking at this, it's much appreciated. Regards, Tom.