@DB Lookup new question

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
EvelynLohAsked:
Who is Participating?
 
madheeswarCommented:
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
 
madheeswarCommented:
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
 
EvelynLohAuthor Commented:
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
 
EvelynLohAuthor Commented:
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
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.

All Courses

From novice to tech pro — start learning today.