Usability - Productivity - Business - The web - Singapore & Twins

Avoid Keyword Lookups in Read Mode

Keyword fields (known to the younger audience as: Radio buttons, checkboxes, combobox, dropdowns etc.) very often are populated by @Formulas. @DBColumn, @DBLookup and @GetProfileField are equally popluar. For best performance you should use @GetProfileField (if that is possible in your application context). Another way to speed things up: prevent the formulas from executing in Read Mode. If for example a formula to populate the options looks like this:
tmp := @DBLookup("Notes":"NoCache";"";"(LookupProjectsBySponsor)";@UserName;2);
@if(@IsError(tmp);"You don't sponsor projects"; tmp)
change it to
tmp := @DBLookup("Notes":"NoCache";"";"(LookupProjectsBySponsor)";@UserName;2);
@if(@IsError(tmp);"You don't sponsor projects"; tmp)
You can refine that and not populate keywords you don't intend to change (based on a status), you would update the first condition then.

Posted by on 03 December 2008 | Comments (2) | categories: Show-N-Tell Thursday


  1. posted by Kevin Pettitt on Wednesday 03 December 2008 AD:
    The "Code Helper" tab on a SuperNTF Keyword or Name List configuration document utilizes a variation of this technique that Andre Guirard had posted about a while back. I'll take a closer look but I think the @ThisValue bit might be an improvement over that approach.

    In any case, one "gotcha" with this technique is when a keyword field uses aliases. In those cases, the lookup is necessary to ensure the "user-friendly" non-alias value displays in read mode.
  2. posted by P.V.Nagarajan on Wednesday 10 December 2008 AD:
    One more addition to this method. If the number of lookup fields are more, we can compute @isdocbeingedited in a separate field (Computed For Display Field) and then use

    tmp := @DBLookup("Notes":"NoCache";"";"(LookupProjectsBySponsor)";@UserName;2);
    @if(@IsError(tmp);"You don't sponsor projects"; tmp)

    This avoid @isdocbeingedited formula getting calculated every time.