How can I do a @DBLookup with a key that spans two columns?

This is annoying - I know this, having done it many times before.

However, I can't remember what to put in my key value so that the key is searched for across the first two columns:

I have a View whose first column contains LookupType & is categorised, the second column contains LookupCode & the third column contains LookupValue.

I am trying to return the value from the third column, based on the value of the first two:

@DbLookup(""; ""; "Lookups"; "LookupType\\" + FieldContainingLookupCode; 3)

I thought I needed to concatenate the two values in the Key string using a backslash, hence I have placed a double backslash (as the first one makes the next character literal, so \\ becomes \ ).

But this doesn't work. Can anyone tell me where I am going wrong?

Thanks.
PaulCutcliffeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Sjef BosmanGroupware ConsultantCommented:
You can't search for values in more than one column, with an @DbLookup. The only thing you can do is to create a hidden lookup-view, with a first column that combines two (or more) field values with a unique separator. You have to do the same when using the lookup.

I used to use "~" as separator, but an @Char(255) is far better.

So, first column:
    sep:= @Char(255);
    field1 + sep + field 2

and in your lookup:
    sep:= @Char(255);
    @DbLookup(""; ""; "Lookups"; "LookupType" + sep + FieldContainingLookupCode; 3)
0
p_parthaCommented:
Key := LookupType:  LookupCode;


@DbLookup(""; ""; "Lookups"; key; 3)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PaulCutcliffeAuthor Commented:
p_parths - Excellent, thanks!

sjef_bosman: p_partha begs to differ, & I'll go with his answer.

Thanks!
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Sjef BosmanGroupware ConsultantCommented:
If this works I'll eat my hat...
0
p_parthaCommented:
Bro,
It will work :)... but pls don't eat your hat.. eat some healthy food :)

Partha
0
Sjef BosmanGroupware ConsultantCommented:
Bro, AFAIK, using a multi-value key will do a multi-document lookup: @DbLookup will find all documents with one of the values mentioned. @DbLookup will NOT use the first value in the first column, the 2nd in the 2nd column, etc.

Or am I sooooo wrong??
0
p_parthaCommented:
Sorry paul, i think i am wrong here. Sjef is right. Please ignore my answer and accept Sjef's answer. I tried and get conflicting results so better stick with sjef's answer

Bro,
YOU are right

Parhta
0
Sjef BosmanGroupware ConsultantCommented:
Want my hat? ;-)
0
PaulCutcliffeAuthor Commented:
I really believed there was a way to do this, & I was convinced that p_partha was correct. So much so I had to so some tests, but of course, you are right!

I now have a nice View the users can look at, with the LookupType Category & the LookupCode seprately, & a hidden View with the first two columns joined together with a | character.

Now how do I Unaccept p_partha's answer & Accept sjef_bosman's instead?
0
p_parthaCommented:
Yes Please, Sorry for the confusion , now am eating my Hat :)
0
PaulCutcliffeAuthor Commented:
p_partha: Strictly speaking, shouldn't you be eating sjef_bosman's hat? :-)
0
Sjef BosmanGroupware ConsultantCommented:
My hats are usually rather sturdy, heavy felt, with a feather. The hat's not the problem usually, but to keep the feather down is something different... For this once, you're excused :-D

Paul, just put a question in the CS TA asking to reopen this question (and add a link of course).
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Lotus IBM

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.