?
Solved

@DB Lookup new question

Posted on 2008-06-19
4
Medium Priority
?
325 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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

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

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…
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…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses

569 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