Need @DbLookup to Return Most Recent Entry

HCL Domino Domino Forum
1

Hi. I need help with the following code:

tDB1 := “DB1.nsf”;

tDB2 := “DB2.nsf”;

tCompanyName := @If(CompanyName = “”; @Return(“”) ; CompanyName) ;

@If (LicenseType = “Type1”; @Unique(@DbLookup(“” : “NoCache” ; @DbName[1] : tDB1 ; “(Stations for Assignment)” ; tCompanyName ; 2)); @If (LicenseType = “Type2”; @Unique(@DbLookup(“” : “NoCache” ; @DbName[1] : tDB2 ; “(Storage-Assignment)” ; tCompanyName ; 2));“”))

This code is used for a field that returns a license number. The companies may have more than 1 license number. (The view shows all of the license numbers that have been issued.) How can I modify this code to return the most current license number?

2

Subject: Need @DbLookup to Return Most Recent Entry

The view must be sorted to show the document with the most recent license number first. Then just use the first element of the resulting list.

3

Subject: RE: Need @DbLookup to Return Most Recent Entry

Harkpabst, how can I make sure that only the 1st element is chosen? Currently it’s returning all documents.

4

Subject: RE: Need @DbLookup to Return Most Recent Entry

@DbLookup returns a list. Assign that to a temporary variable and use only the first element of that list. In general:

_res := @DbLookup(…);

_res[1]

I also noted, that you nested your if statements. That’s not necessary. @If in formula acts more like an @Case. Your complete code could look like this:

tDB1 := “DB1.nsf”;

tDB2 := “DB2.nsf”;

tCompanyName := @If(

CompanyName = ""; 

@Return("");

CompanyName

);

licenseNumbers := @If(

LicenseType = "Type1";

@Unique(@DbLookup("":"NoCache"; @DbName[1]:tDB1; "(Stations for Assignment)"; tCompanyName; 2));

LicenseType = "Type2";

@Unique(@DbLookup("":"NoCache"; @DbName[1]:tDB2; "(Storage-Assignment)"; tCompanyName; 2));

""

);

licenseNumbers[1]

Do you have to consider the case, where there are no license documents yet?

5

Subject: RE: Need @DbLookup to Return Most Recent Entry

Thanks so much! The license number is a mandatory field on its form, so there should always be a license document.

6

Subject: Need @DbLookup to Return Most Recent Entry

Modify the view so that it column that contains both license number and date. Do the @DbLookup, run through the list of results, and select the one with the most recent date, and then parse out the license number.