• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 607
  • Last Modified:

Need Help Using DbLookup where key is multivalue field

Hi all.  I need help doing a DbLookup to get a MailSend to work correctly.  Essentially, I have a field containing 3 plants.  1 plant can be chosen, or all 3 plants can be chosen.  The field to choose these plants is a db lookup first column and they just checkmark the ones that apply.

Well, each plant has a list of people that need to be emailed if that plant is chosen.  This is column 2 of the view.  If just one plant is chosen, it is a no brainer and works fine.  However, if 2 or more plants are chosen, the lookup to grab the names from that second column isn't working.  

Here is my code:
@MailSend(@DbLookup( "" : "" ; "" : "" ; "Directors";signofffrom; 2) where signofffrom is the name of the field containing one to three plants.  I am trying to use it as the key thinking it would look up each individually.  I thought wrong, huh?

So, long story short, how can I look up multiple values using DbLookup?
  • 2
1 Solution
I would not try to put all the values into the mail send, instead get the values into a variable first.
If using R7+ then you could put the @dblookup within a @for loop that gets each element of the signofffrom field.

Rough example

Sjef BosmanGroupware ConsultantCommented:
AFAIK it used to work... I'm quite sure I used a similar lookup before, but it was in a much older release. I found out that the lookup stops at the first key not found. So I included a first search to match the keys with the existing keys, and then I did the lookup with the keys found.

In short:

keys:= "tom":"dick":"harry";
keysnotfound:= @Trim(@Replace(keys; @DbColumn(""; ""; "Directors"; 1)));
keysfound:= @Trim(@Replace(keys; keysnotfound; ""));
sendto:= @DbLookup( "" : "" ; "" : "" ; "Directors";keysfound; 2)

Actually, what results do you get??
onederwomynAuthor Commented:
I used this as a base with a slight modification
Sjef BosmanGroupware ConsultantCommented:

Could you let us have a peek at that "slight modification", if it was essential to the case?

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now