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
