@DbLookup

HCL Domino Domino Forum
1

Help !.

I am trying to get the following formula to work from a button

FIELD Holidays :=@Sum(@DbLookup(“”:“NoCache”;“”:“”;“Tracker”;IDstring ;

4;[PartialMatch]))

The Issue is that “IDstring” which is a string made up from 2 other

fields joined together on the form

ID1 + ID2

ID1 is @Text(@Name([CN];txtName))

ID2 is @Text(@Year(StartDate))

So IDstring is these values as one string which the @dbColumn looks

for eg “FredSmith2008”

I have checked the IDstring values by displaying them in another field

and they look ok eg “FredSmith2008”

But the formula just does not pick them up.

I tried this to confirm that the formula works

FIELD

Holidays :=@Sum(@DbLookup(“”:“NoCache”;“”:“”;“Tracker”;“FredSmith2008” ;

4;[PartialMatch]))

Which it does, so the issue appers to be the string itself. I have

tried @Implode also to convert it to a text string but it still does

not work

Any ideas why ?

Thanks in advance

AMJ

2

Subject: is there perhaps a space involved?

Typically @Name( [CN] ; … ) would return Fred{space}Smith

whereas you have used FredSnith very clearly?

another possible issue is the Tracker View

does it have Show Responses in Hierrachy unset

Over teh years I found having this set in lookup views can cause issues

3

Subject: RE: is there perhaps a space involved?

Hi, thanks for the reply.

I have checked my query string and the value I am looking for is Fred Smith2008 and the value in the column is Fred Smith2008. So there is a space on both strings. The column in the view is a string constructed from 2 other columns.

so i am still stuck.

I have also tried

UniqueIdentifier :=@Text(txtName):@Text(@Year(StartDate));

FIELD Result := @Sum(@DbLookup(“”:“NoCache”;“”:“”;“Tracker”;UniqueIdentifier;3;[FailSilent]));

But this just gets for the first match on the name and returns a result where as I need the name and the year to match.

Thnaks for your reply.

AMJ

4

Subject: Is the problem the returned values are text and not a numbers

I would suggest you break things up a bit to help debug. I think the problem is you’re dealing with text. However, the following code might help:

Make sure the @DbLookUp is getting you want.

list := @DbLookup(“”:“NoCache”;“”:“”;“Tracker”;UniqueIdentifier;3;[FailSilent]);

tmp := @if( @iserror(list); @Prompt([OK];“Error”;“Error”);

                  list := ""; @Prompt([OK];"Empty";"Empty");

                  @elements(list) = 1; @Prompt([OK];"One Value";list);

                  @Prompt([OK];"Values";@Implode(list;",")

);

Field Result := @Sum( @TextToNumber( list ) );

AFAIR, aren’t the values returned from a column of a view always text?

5

Subject: @DbLookup

It’s hard to tell you how to do what you want, because you haven’t said what you want.

Is it possible you meant to use @Elements instead of @Sum?

The problem with this key:

UniqueIdentifier :=@Text(txtName):@Text(@Year(StartDate));

is that the key is a list (“Fred Smith” : “2008”) and the value in the view is a string that is not the same as either value in the list. Instead try:

UniqueIdentifier :=@Text(txtName)+@Text(@Year(StartDate));

That you’re looking for a start date bothers me, though. Where there’s a start date there’s usually an end date. If the end date is not in the same year, how is this supposed to work?