Dblookup and unique

Posted on 2004-11-19
Last Modified: 2013-12-18
I am trying to automatically fill in address fields based on a company name which is generated from a button which has the following formula:

List1 := @Unique(@DbColumn("":"NoCache";"85256f4e:006706e3 "; "Company Lookup"; 1));
Choice := @Prompt([OKCANCELLIST]; "Customer";"Select the Customer"; List1;

Then (and I will only use 1 address field to save space) I want to fill in the address field. So , this follows the above:

FIELD address := @Unique(@DbLookup("" : "NoCache"; "" ; "Company Lookup"; Choice; 2));
@If(@IsError(address); ""; address);

The problem is that it is still returning 3 or 4 of the same addresses. So, I guess @Unique is not the correct command to use?
Question by:schmad01
    LVL 3

    Expert Comment

    by:Andrea Ercolino
    List1 := @Unique(@DbColumn("":"NoCache";"85256f4e:006706e3"; "Company Lookup"; 1));

    Choice := @Prompt([OKCANCELLIST]; "Customer";"Select the Customer"; ""; List1 );
    @If( Choice = ""; @Return( "" ); "" );

    FIELD address := @DbLookup("" : "NoCache"; "" ; "Company Lookup"; Choice; 2);
    @If(@IsError(address); ""; address);
    LVL 15

    Accepted Solution

    Several problems in your code :

    You don't catch errors in dblookups (that can occur sometimes), your error checking for the address is incorrect, and your @prompt can return multiple values (correct ?).
    Your button code should look like this :

    res := @DbColumn("":"NoCache";"85256f4e:006706e3 "; "Company Lookup"; 1);
    List1 := @if(@iserror(res);"Error getting companies";@trim(@Unique(res)));
    Choice := @Prompt([OKCANCELLIST]; "Customer";"Select the Customer"; @subset(List1;1);List1);
    @if(Choice = "";@return(""); Choice = null; @return("");"");
    res2 := @DbLookup("" : "NoCache"; "" ; "Company Lookup"; Choice; 2);
    FIELD address := @if(@iserror(res2);"More Errors";@trim(@unique(res2)));""



    Author Comment

    That's the ticket!  Thanks alot!
    LVL 3

    Expert Comment

    by:Andrea Ercolino
    Bozzie4> @if(Choice = "";@return(""); Choice = null; @return("");"");
    <<Choice = null>> ? :-)

    I agree with points to Bozzie... because I forgot the first error check and thought the formula was executing in a computed field!

    However I don't think the prompt can return multiple values without [OkCancelListMult]...
    So <<@trim(@unique(res2))>> could be simply <<res2>>. And I think <<@trim(@unique(res))>> could be simply <<@unique(res)>> because it's very uncommon that a "Company Lookup" view lists empty companies (and if it does, should be considered an oversight)

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    For beginners of Lotus Notes user this is important to know about the types of files and their location supported by IBM Notes. Mostly users are unaware about how many file types are created and what their usages are. This Article is fully dedicated…
    Notes Document Link used by IBM Notes is a link file which aids in the sharing of links to documents in email and webpages. The posts describe the importance and steps to create a Lotus Notes NDL file in brief.
    This video discusses moving either the default database or any database to a new volume.
    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…

    730 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

    15 Experts available now in Live!

    Get 1:1 Help Now