i have one computed field where i am using dblookup. here multiple values are allowed. the keys come from two fields. one field holds single value and another holds multiple.here the view i am using is categorized on both the fields. how to achieve this?
thanks
Subject: DBLookup
Your description is a little ambiguous, so I’m going to start by saying what I think you meant to ask, then I’ll answer.
So your form contains field key_1 (text, single value) and key_2 (text, multivalue). You also have a computed field that uses @DbLookup to pull information from a view based on key_1 and key_2. The view contains two categorized columns; the first contains the values from key_1, and the second contains the values from key_2.
You want to use @DbLookup to read a value from a third column, but only on those rows where the first column matches key_1 and the second column matches key_2.
Answer: There is no way to do this. You would instead have to lookup in a view that combines key_1 and key_2 into a single column, e.g. using the column formula key_1 + “:” + key_2, and categorizes by that column. Then in your computed field, you could write:
@DbLookup(“”; “”; “viewname”; key_1 + “:” + key_2; columnnumber)
Subject: RE: DBLookup
i exactly meant what you have understood.but the problem with your idea is tht the field key_2 may contain any number of value as per user selection. so at runtime i may not know how many times i have to make a lookup. or is it like i have to extract each value and make a lookup.
what can be done for this problem?
tnanks.
Subject: RE: DBLookup
Extract each and do separate lookups – @For will do that for you:
@For(i := 1; i<= @Count(Field2); i := i + 1;
list := list:@DbLookup(“”;“”;“ViewName”;Field1 + “~” + Field2[i];3;[FailSilent])
}
Subject: RE: DBLookup
Stan’s suggestion will work, though I would have used @Transform instead – the code is simpler. Or, you can just use @DbLookup as I showed in my last message – if you supply multiple keys it will look up and return all the matching results (and if you concatenate a list to a scalar you’ll get a list result). The problem you might run into there, is if not all keys match a document.
Subject: RE: DBLookup
How @Transform can be used to achieve the same?Thanks
Subject: RE: DBLookup
@Transform( Field1 + “~” + Field2;
“x”;
@IfError(@DbLookup(“”;“”;“ViewName”;x;3); @Nothing)
)