2

I have an issue with my dropdown field that is populated dynamically by a large query. Since the query returns a very large value when users access the form the form freezes. It's like it's waiting for the dropdown to be populated. In the meantime all the form formatting gets messed up, the dropdown disappear. When I limit my query to a smaller number of records everything is working just fine. This query returns over 26K records. Is there a work around to fix this issue in ColdFusion? I don't believe ColdFusion cannot handle this type of situation. I'm using ColdFusion2016 and back-end Oracle 11g.

Here is the query and the dropdown:

    <cfquery name="xCodes" datasource="#application.DSN#">
     select xcode, desc, xcode || ' ---- ' || desc as FullDesc
     from x_header
     <cfif IsDefined("url.codes")>
        <cfif url.codes EQ "y">
         order by xcode
        <cfelse>
         order by desc
        </cfif>
     </cfif>
    </cfquery>

        <select name="xcode" id="xcode" class="RegSelect" required="TRUE">
          <option value="">--- Select a code ---</option>
             <cfoutput query="xCodes">
             <option value="#xcode#">#FullDesc#</option>
             </cfoutput>
        </select>
James A Mohler
  • 10,562
  • 14
  • 41
  • 65
user3779216
  • 75
  • 1
  • 6
  • 1
    Is it possible the query returns values that are a problem for the generated HTML, like unencoded greater/less-than or ampersands? (26k items seems rather a lot to have to scroll through though...) – Alex Poole Jan 19 '18 at 14:07
  • It doesn't seems there is a value problem because if I wait long enough the dropdown will finally appear but it is too long for users to have to wait especially with deform form on their interface. It is a lot of items to scroll but this is for admin use. We have more than six admins in my office who need to access this values. – user3779216 Jan 19 '18 at 14:18
  • Agreed, 26K items is _way_ too many to scroll through (And is that many actually necessary or can it be filtered?). In Oracle, I believe you can use `ROWNUM` (http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html) to paginate your query to return what you want then cycle the results you need. AJAX is probably your best bet. – Shawn Jan 22 '18 at 20:40
  • You may also want to use Select2. https://stackoverflow.com/questions/32533757/select2-v4-how-to-paginate-results-using-ajax – Shawn Jan 22 '18 at 20:46

1 Answers1

6

I agree with Alex Poole that 26K items in a select is rather large and it seems to me that your browser's rendering engine is struggling to build it, that's why your formatting is wonky until it's finally built.

Here are a couple of suggestions.

  1. Is it possible to break up the values into logical groups and use multiple selects? So your first select will be the logical group values. Once the user selects their choice, the next select will only contain values within that logical group. If no such logical grouping exists, then you can make up your own. For example you can use the 26 letters of the alphabet as your logical group, then your subselect will contain 1000 choices on average.

  2. Another suggestion would be to use a jQuery autocomplete and populate your values via an ajax call. I've done this several times using ColdFusion and Oracle and it works great.

SOS
  • 4,058
  • 1
  • 8
  • 29
Guest
  • 196
  • 3
  • 4
    This is the right answer. This isn't a ColdFusion problem. 26k options in a dropdown is unusable and ridiculous. The better approach, as suggested, would be to use an AJAX search dropdown. – luxdvie Jan 19 '18 at 15:12
  • Thank you! I was thinking about using the logical group but I wasn't sure – user3779216 Jan 19 '18 at 15:19
  • @user3779216 no problem. Please mark as the accepted answer if you're using this solution. – Guest Jan 19 '18 at 15:49