Solved

@DB Lookup new question

Posted on 2008-06-19
4
306 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 50 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Problem "Can you help me recover my changes?  I double-clicked the attachment, made changes, and then hit Save before closing it.  But when I try to re-open it, my changes are missing!"    Solution This solution opens the Outlook Secure Temp Fold…
Article by: Rob
Notes 8.5 Archiving Steps and Tips This article covers setting up a Notes archive, and helps understand some of the menu choices making setting up and maintaining a Notes archive file easier.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

839 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