Topic
  • 1 reply
  • Latest Post - ‏2013-05-30T16:33:12Z by DGawron
JBird
JBird
51 Posts

Pinned topic lookup table using rowloopvar?

‏2013-05-30T12:30:43Z |

I have some data coming back in a data entry datapage from a data service.  I need to add a select drop down on one of the columns to allow the user to change it.  Sounds simple enought right....  My problem is that the values contained in the lookup table for the select can be different for every row in the datapage results.  I need to use another column in the results as a database key to get the correct records into the lookup table instead of one blanket SQL statement.  As far as I know lookup tables are cached and only created one time instead of being executed for each record.  Not an uncommon requirement so I'm hoping someone can provide an easy answer.

 

Thanks!

 

Jay

  • DGawron
    DGawron
    580 Posts

    Re: lookup table using rowloopvar?

    ‏2013-05-30T16:33:12Z  

    Lookup Table is probably the wrong abstraction / builder for this requirement.  Try using a Select builder on that column.  The "Select Data" and "Selected Values" inputs take indirect refs which should allow you to specify a method that returns the row-appropriate XML or CSV based upon the DB key in each row.  To make this perform reasonably well I'd suggest making one DB call per page view to get all of the possible choices across the visible rows and then having the indirect ref method for "Select Data" return a filtered subset that corresponds to the passed-in DB key.

    If the total set of possible choices for that column is relatively small (say a few thousand at most), then I'd also recommend just getting the whole set one time when the provider model is first loaded and caching the set so each page view takes only one DB hit -vs- two.  Lastly, if the total set of possible choices is very large, but you expect the majority of the time only a small subset of choices will be needed, then you can also consider fetching the choices from the DB in the indirect ref method for "Select Data" and cache that method's results so only the first call for each DB key incurs a DB hit.  Which strategy is best for your application is something only you can decide after looking at the data and the expected use patterns.