Improve company productivity with a Business Account.Sign Up

x
?
Solved

@DB Lookup new question

Posted on 2008-06-19
4
Medium Priority
?
327 Views
Last Modified: 2013-12-18
Hi all,
Am creating a customer form for my new DB so that when user select the company name, all the information of Customer name, Tel, email will appear automatically in the form.
But one company can have a few employees so I manage to list out the employees under the company name so that user can choose the customer name. But how can I use DB lookup for the telephone field so that whoever names are choosen, the correct tel number will list out?
My view looks like this
Company Name    Customer name   Tel               Email
xyz pte ltd             steven                1234567      steven@xyz.com
                              Alvin                   7654321      alvin@xyz.com
                              David                  0949321      david@xyz.com
abc pte ltd             Lily                      1231231      lili@abc.com
                              francis                5677766      francis@abc.com

when user choose xyz in my form, it will list out 3 names in the dialog field "steven, alvin, David" for user to choose the correct customer name. But what is the correct code to use if user choose alvin and telephone number "7654321" will appear?
Currently I try use below code to search for the correct tel number :
tmp:=@DbLookup("";"Singapore":"DHS Machine Reservation.nsf";"Customer Contact";CustName;3);
@If(@IsError(tmp);"";tmp)

But it doesn't work. It appears blank. May I know what is the correct code that I should use?

Thanks a million to all who can help me. :-)

Rgds,
Eve
0
Comment
Question by:EvelynLoh
  • 2
  • 2
4 Comments
 
LVL 19

Expert Comment

by:madheeswar
ID: 21828993
Create a new view with the combination of CompanyName+EmployeeName as the first column and sorted.
Now in the @DbLookup,
tmp:=@DbLookup("";"Singapore":"DHS Machine Reservation.nsf";"newViewName";CompanyName+CustName;3);
@If(@IsError(tmp);"";tmp)

the above should give the result of Telephone no.
0
 

Author Comment

by:EvelynLoh
ID: 21844375
Hi Madheeswar,

THanks for your fast reply. But I have different field name for user to enter the information.
E.g.
Company Name field
Customer Name 1 field    Contact 1 field
Customer Name 2 field    Contact 2 field
Customer Name 3 field    Contact 3 field
Customer Name 4 field     Contact 4 field
Customer Name 5 field     Contact 5 field

So how can I list out all in the view?
Should I enter
@Text(customername)+"~"+@Text(Customer Name 1:Customer Name 2:Customer Name 3:Customer Name 4:Customer Name 5)
for my first column of View? But how should i enter for my 2nd column of view which suppose to display Contact 1, Contact 2, Contact 3, Contact 4, Contact 5???

I try your method, it doesn't work due to I have different field for different names.

Thank you.
0
 
LVL 19

Accepted Solution

by:
madheeswar earned 200 total points
ID: 21844414
I assume customername is the Company Name.
First Column with below code...Sorted and Enable Show multiple values as separate entries.
@Text(customername)+"~"+@Text(Customer Name 1:Customer Name 2:Customer Name 3:Customer Name 4:Customer Name 5)
In the second column with below code, Enable Show multiple values as separate entries.
Contact 1:Contact 2:Contact 3:Contact 4:Contact 5
This should do the trick...

If the above is not successfull, then hard code the key in dblookup and try. it should fetch the value from second column.
tmp:=@DbLookup("";"Singapore":"DHS Machine Reservation.nsf";"newViewName";CompanyName+CustName;3);
@If(@IsError(tmp);"";tmp)
And I assume you are changing the fields names and view names as per your design.
0
 

Author Comment

by:EvelynLoh
ID: 21844527
Hi Madheeswar,

THanks so much for your prompt reply. It's finally ok... I know why... it is because of I didn't choose "Multiple values as separate entries"...
Thank you so much for your help.
Rgds,
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

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.
Sometimes clients can lose connectivity with the Lotus Notes Domino Server, but there's not always an obvious answer as to why it happens.   Read this article to follow one of the first experiences I had with Lotus Notes on a client's machine, my…
In this video I will demonstrate how to set up Nine, which I now consider the best alternative email app to Touchdown.
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…

595 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