[Last Call] Learn how to a build a cloud-first strategyRegister Now

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

Dblookup and unique

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?
  • 2
1 Solution
Andrea ErcolinoCommented:
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);
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)));""


schmad01Author Commented:
That's the ticket!  Thanks alot!
Andrea ErcolinoCommented:
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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

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