AnsweredAssumed Answered

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

Question asked by dougbrowning on Apr 5, 2018
Latest reply on Aug 14, 2018 by 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

Outcomes