Variable number of keys for view lookups

HCL Domino Domino Forum
1

I have a computed field on a page with a variable number of values, e.g. “x,y,z”

I want to do a lookup to a view and see how many documents have those values, e.g.

@Text(@Elements(@DbLookup(“”:“Nocache”;“”;view;“x”;2;[FAILSILENT])))

  • “,” +

@Text(@Elements(@DbLookup(“”:“Nocache”;“”;view;“y”;2;[FAILSILENT])))

etc.

(I’m not adding the elements - just showing them in a graph)

Since the first field can contain a different number of variables - what is the best way to do this? Every time I have tried the @While or @For statements I get a return value of true (“1”) instead of being able to get a value I need (to show on the (web) page).

I can do a somewhat dynamic lookup by extracting elements from the computed field using @Subset(list;1) and the like - however, for this, too, I need to know the number of elements …

2

Subject: variable number of keys for view lookups

try ther @For like this:

@For(n := 1; n <= @Elements(VariableKeys); n := n + 1;

FIELD

thisfield:=@If(n=1 ;

@If(@IsError(@DbLookup(“”:“NoCache”;“”:“”;“(viewname)”;@Text(VariableKeys)[n];“value”)) ; “NoValue” ;

@DbLookup(“”:“NoCache”;“”:“”;“(viewname)”;@Text(VariableKeys)[n];“value”)) ;

thisfield:@If(@IsError(@DbLookup(“”:“NoCache”;“”:“”;“(viewname)”;)+@Text(VariableKeys)[n];“value”)) ; “NaValue” ;

@DbLookup(“”:“NoCache”;“”:“”;“(viewname)”;@text(VariableKeys)[n];“value”))

));

thisfield

HTH-ST

3

Subject: RE: variable number of keys for view lookups

I just can’t stand to see a long, inefficient formula.

@DbLookup can accept multiple keys, and it will look up all of them. So the formula

@DbLookup(“”:“NoCache”;“”;“(viewname)”; VariableKeys; 2; [FAILSILENT])

may work just fine – you might not need a looping construct. However, I haven’t tried the [FAILSILENT] option and don’t know for sure that it’ll go ahead and look up the remaining keys if it doesn’t find a match for one. Also, if “” is a legitimate value in the view, you might want to substitute a different value to differentiate a blank value from an invalid key. In that case, here’s another way to do it.

@Transform(VariableKeys; “x”; @IfError(@DbLookup(“”:“NoCache”;“”;“(viewname)”; x; 2); “Invalid Key "” + x + “"”))

Note the following:

To refer to the current database, use “”, not “”:“”.

Never do @DbLookup to determine whether the key is valid, then repeat the @DbLookup to get the actual data – especially not if you’re using NoCache. What a waste. Use @IfError, or store the value in a temp variable.

If there are multiple view rows that match the key, @DbLookup will return all of them, so the number of elements in your return list may not match the number of elements in the key list.

To insert commas between the values, just make the computed field multivalued and select comma as the output separator. Or, you could slap an @Implode(…; “,”) around either formula shown above.

4

Subject: RE: variable number of keys for view lookups

Hi,

How do you define “variablekeys”?

i.e. if I have three keys say ‘x’,‘y’ & ‘z’, then variablekeys will be defined as :

variablekeys:=x+y+z;

is it correct?

Sharad.

5

Subject: RE: variable number of keys for view lookups

No, VariableKeys would be x:y:z (a multivalue list).