In Notes 6 the @DBLookup function was improved with an extra parameter with 3 options. These options can only be used when your customers and servers are at least Notes 6. One option that I have been using a lot lately is [RETURNDOCUMENTUNIQUEID]. This option gets the UNID back instead of a column value. Before this you had to have a column in a view that had a value of @text(@documentunqiueid). This could create problems of extra views, meaning slower db and servers, and if the view column was moved the returned value was not a UNID (universal id), which could cause more problems.

This code below asks the user to select an advert by the advert ID using a picklist.

The UNID of the picked document is then retrieved using [ReturnDocumentUniqueID] .

Example code:

r:=@PickList([Custom]:[Single];"";"Adverts By ID";"Pick Advert";"Pick";1);
FIELD AdvertID:=@If(@IsError(r);"";r);
unid:=@DbLookup("";"";"Adverts By ID";AdvertID;1;[ReturnDocumentUniqueID]);
FIELD AdvertUnid:=@If(@IsError(unid);"";unid);
FIELD AuditTrail:=AuditTrail:(@Text(@Now;"T1")+"  "+@Name([CN];@UserName)+"  Advert="+r+")");
@Command([ViewRefreshFields]);
""

NB We are not actually requesting a column to be returned, so I pick the column 1.

In fact if you also use the other new option [FailSilent] you can reduce the code:

r:=@PickList([Custom]:[Single];"""Adverts By ID""Pick Advert""Pick"1);
FIELD AdvertID:=@If(@IsError(r);""r);
unid:=@DbLookup("";"";"Adverts By ID"AdvertID;1;[ReturnDocumentUniqueID]:[FailSilent]);
FIELD AdvertUnid:=unid;
FIELD AuditTrail:=AuditTrail:(@Text(@Now;"T1")+"  "+@Name([CN];@UserName)+"  Advert="+r+")");
@Command([ViewRefreshFields]);
""

The third @Dblookup option is [PartialMatch].

Using this in some operations may be dodgy, as it could return something unexpected or wrong, but I have thought of one use:

If you are searching for first name in a list, you could search for first few letters if the original search fails:

So if your user was searcing for "dave" and the only optin was david, after getting no results you could search for partial match and first 3 characters.