Can external_choices use more than one lookup column? (and query speed)

[DougBrowning]

I have been testing out external_choices most of the day and it seems that lookups are only on one column. 

For example if my choice filter 1 has Column1=${field1} then ALL my choice filters must use Column1.  If I try Column2= it refuses to work.

Now what is weird is that it MUST be called Column1 forever.  So lets say I change ALL my choice filters to Column2 - no dice.  If I go into the external_choices sheet and rename Column2 to Column1 it all works! Weird.  Is external_choices somehow tied to a specific column name at the beginning and cannot be changed?  Regular choices seems to be able to use other columns but not external_choices.

I have also found that the length of the query makes a big speed difference.  A simple Column1=${field1} is about 2 seconds.  A Column1=${field1} or Column1=${field2} is about 4 seconds - and this continues adding on 2 seconds for each or I add.  My solution to this was to use different Columns as talked about above but it does not work.

The real issue with all of this is I am doing 8 external_choices lookups and they are all hitting a list of 1,300 items.  It was taking 19 seconds to add a repeat with 4 or statements.  It goes down to 4 seconds if there is just a simple query with no or.  If I move then to choices it takes 30+ seconds and the forms loads slow.

I should also note I am using a bit of a trick with external_choices.  I have a calculate that is just text with my lookup value.  So field1 calcs to "Y" then I use Column1=${field1} where Column1 is Y or N.

I did see a post on here on ways to speed up the external_choices but I cannot find it now.

Any hints to help my speed is greatly appreciated.

Thanks

[JamesTedrick]

Hi Doug,

Could you share the form that you are using?  You should be able to (with some limits) have a compound statement in the choice filter.

[DougBrowning]

I can email but not sure I can post due to company policy.  My email is in my profile. 

My question is really not having a compound statement in one filter it is that I want to use more than one column in 2 different choice filters.

Thanks a lot!

[JamesTedrick]

Please feel free to e-mail me at jtedrick AT Esri.com .  Thanks!

[DougBrowning]

Turned out the new list thing was due to a few values in the list have a new line in them - which is very hard to see in excel.  Removed and all worked.

Query speed turned out to be having the Choice Filter lookup field inside the repeat.  So I have all those choice filters layer=${habit_none} - where habit_none is just a calc to Y.  But the habit_none field just happened to be put inside the repeat.  If I simply copy and paste the habit_none field OUTSIDE of the repeat the repeat time drops all the way down to about a second!

So do not have the choice filter use any field that is inside a repeat.

[Anonymous User]

Hi Doug,

Just letting you know that in the latest 3.12 beta builds available on the Survey123 Early Adopter Community we have made significant improvements to large choice lists which improve the survey load time in both Connect and the field app and further improvements to stability and speed of select one and select multiple choice lists once the form is open. From our testing this significantly reduces the time it takes to open a survey with very large choice lists.

On top of this there are now some new ways you can manage your choice lists dynamically and update them without having to republish the survey. Please check out the latest announcement on EAC and test out these new beta features with the latest 3.12 beta builds.

Regards,

Phil.

[DougBrowning]

Thanks Phil.   I just tested quick and the form does seem to load faster.  However the autocomplete search does not seem to work right.  I seem to get all choices no matter what I type in (I typed in moon and for 500+ matches it says but there is just one).  In one case it worked but usually does not.  Note these are still select one externals with no changes to the new style.

At first glance I am not really getting how search() is different than choice filter.  Maybe that I can use text?  Can I set the csv file name to a field that I can calc?  I will have to play with it a bit.

Is it yet possible to dynamically sort the list?  I would love it if the last item picked went to the top of the list, and that this grew over time.  For example if I have 50 repeats the list changes on each repeat load where the order is now repeatchoice3, repeatchoice2, repeatchoice1.  It would be really cool if this then persisted across the form.  Since you are storing this in a DB now you could resort it in the DB as the choices are made.  Any plans on this?

thanks

[DougBrowning]

I tested a older device with the old and new versions but not seeing any speed improvement at all using add itemsets to database.

Posted to the Beta forum.

thanks

[Anonymous User]

Thanks Doug, we will pick up the conversation on EAC and go from there. The limiting factor is that you are using 32 bit Android devices. Unfortunately this is a limiting speed factor, and any improvements made to database speeds are not seen as the device is still running too slow, even with a small list.

Phil.

[DougBrowning]

I was wondering on 32.  The big reason I was excited about this change is getting a little more life out of these Active 2s.  It is over 22 seconds to load the form.

I did try an Active Pro now and that seems to have cut the load time in half!  11 seconds to under 6.  I will try and do some more testing tomorrow.

On my iPad Pro it is so fast it is hard to tell.  I will get some more testers on this.  Thanks!

Any thoughts on the "smart learning" list sort order?  Our old app did this so users really want it back.