Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 331
  • Last Modified:

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.
0
PaulCutcliffe
Asked:
PaulCutcliffe
  • 5
  • 4
  • 3
1 Solution
 
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
 
PaulCutcliffeAuthor Commented:
p_parths - Excellent, thanks!

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

Thanks!
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now