Need Help Using DbLookup where key is multivalue field

Posted on 2011-05-02
Last Modified: 2013-12-18
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?
Question by:onederwomyn
    LVL 10

    Expert Comment

    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

    LVL 46

    Accepted Solution

    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??

    Author Closing Comment

    I used this as a base with a slight modification
    LVL 46

    Expert Comment

    by:Sjef Bosman

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

      In today’s Arena we can’t imagine our lives without Internet as we are highly used to of it. If we consider our life style just for only 2 min we found that face to face communication is swapped by e-communication.  Every Where from Works place to…
    IBM Notes offer Encryption feature using which the user can secure its NSF emails or entire database easily. In this section we will discuss about the process to Encrypt Incoming and Outgoing Mails in depth.
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now