Solved

@DB Lookup new question

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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…
This article covers general Notes 8.5 troubleshooting information including recreating the Notes\Data folder.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

747 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now