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

@if(!@IsDocBeingEdited;@Return(@ThisValue);"");
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 Stephan H Wissel on 03 December 2008 | Comments (2) | categories: Show-N-Tell Thursday

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.

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

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

This avoid @isdocbeingedited formula getting calculated every time.