How do I create a DBLOOKUP to another notes database

[collenr123]

Hi,

I am new to all this developer stuff, learning fats however... I have 2 db's, one is an estimate system and the others a customer db. I want to lookup from my estimate db a name that is placed in my customer db. At the moment both of them are on LOCAL and in the same directory.

Thanks

 

[AmazingDominik]

Hi,

From the Notes Designer help:

There are several ways to specify the server : database parameter:
1/
To perform the lookup on the current database (the same database in which the formula is being evaluated), specify "" as the entire argument to the function. "" means the local Domino directory where you are executing.

2/
To perform a lookup on a local database, use "" for the server name and specify the database name explicitly, such as "":"DATABASE.NSF."

3/
To perform a lookup (from the workstation) on a Domino database that resides on a server, include the server plus the path and file name as a text list, as in "SERVER":"DATABASE.NSF."

If there are multiple copies of the database located on various Domino servers, using the database replica ID in place of both the server and database name lets you access a replica copy of that database without having to specify either the server name or the database name. For example, if you use "85255CEB:0032AC04" (a database replica ID, found in the database InfoBox) as the database name, Lotus Domino uses a replica of the database to retrieve the information.
Lotus Domino searches for replicas in this order, using the first replica it encounters:
Workspace

If there is one replica on your workspace, Lotus Domino uses it.

If there are multiple, stacked replicas on your workspace, Lotus Domino uses the replica on top of the stack.
If there are multiple, unstacked replicas on your workspace, Lotus Domino looks for an icon matching your current server and uses that. If none of the icons matches your current server, Lotus Domino uses the icon that was added to your workspace first.
Current server
Locally (your hard disk)
Once a replica is located, it's added to your workspace to save time on future lookups.


I suggest you just use the help function on this command to see all the options.

If you work locally: specify "" as the server name,a nd then specify the other database name. Later you will have to change this as you move to a server.



Kind regards,

Dominik Malfait
dominik@amazingit.com

http://www.amazingit.com

 

[collenr123]

Hi,

Sorry, I am a bit stupid when it comes to this, I have not done this before, so therefore your comments is a bit greek to me...

I have db 1 called Estimate System on my Notes server under LocalDBS\Estimate System directory, file name is EIE.nsf.

I have db2 called T&D on my Notes server under LocalDBS\Customer Contact directory, file name is Customer Contact1.nsf (This is an address book of our customers)

I want to call a customer from db2 onto db 1 using dblookup and from there want to display the other fields, but it is that section I'm having a problem with

Thanks

 

[ABOzIT]

Create a view (call it custlookup) in db2 that shows the customer name in column 1 and then the other info you want in the other columns. Make column 1 a sorted column.

Create a dialog list field (call it dlCustomer) on your form in db1 and put the following formula into the "Choices" box making sure you specify "Use formula for choices".

@DbColumn("":"NoCache";"85255CEB:0032AC04";"custlookup";1)

Note: The long number in the middle of the formula is the replica ID from db2. Get this from the "information" tab on the properties box for the database.

When you click the helper button in the dialog list in the form you will get the list of customers that are displayed in column 1 of the custlookup view.

For each other piece of information you want to get for this customer, create a new field on your form in db1 and use the @dblookup function using the value in the dlCustomer field as your "key" as follows

@DbLookup("":"NoCache";"85255CEB:0032AC04";"custlookup";dlCustomer;columnNumber)

Note: ColumnNumber will be the column in the custlookup view that contains the information you want for the particular customer.

Hope this helps

 

[daleflannery]

The easy way to get the replica id is to use the menu option:
File \ Database \ Design Synopsis ...
Then tick the checkbox for Replication and click OK
Then you can just swipe over the replica id with your right mouse button. A lot easier than copying it by hand and less prone to error too!

Find me @ onlinecorporatesoftware.com

 

[collenr123]

Guys, thank you, this did it for me, both of you...I found this to work perfectly ABOzIT and DaleFlannery method of checking ID is excellent as I did make a mistake and followed your synopsis and got the right ID by copied and paste.

thanks again and kind regards

Collen

 

[collenr123]

Ok, I have run into a problem with the lookup...

I now have 5 records in my Contacts database... when I go to my other database and click on the list box, I can see my contacts names there, but problem comes in when I want to display other information based on this selection, I have put the following formula in the other fields, but it 's putting ALL 5 records information into 1 field...

THIS IS THE FORMULA IN THE LIST BOX
@DbColumn("":"NoCache";"42256D73:0029F684";"Contacts";1)

THIS IS THE FORMULA IN THE OTHER FIELDS
a := @If( Customer_Name != "" ; @DbLookup("":"NoCache";"42256D73:0029F684";"Contacts";2) ; "" );
@If( @IsError( a ) ; "Lookup Failed" ; a )

What have I done wrong?

 

[daleflannery]

DBLookup returns a list of values that satify the given key criteria.

Maybe there is an error in the execution, for example maybe Customer_Name contains a comma eg: "Smith, John" and so the key field appears to @DbLookup as "Smith":" John" and returns all values matching "Smith" - This is all fantasy guesswork on my part.

Use this code to examine the returned value in both a and in Customer_Name.

@Prompt( [OK] ; "Customer_Name" ; @Implode( "\"" + @Text( Customer_Name ) + "\"" ; " : " ) ) ;
@Prompt( [OK] ; "a" ; @Implode( "\"" + @Text( a ) + "\"" ; " : " ) ) ;

Find me @ onlinecorporatesoftware.com

 

[collenr123]

I am now not too sure, maybe I did not explain properly:

I have on my form in DB 1 the following fields:

Customer_Name | List box with
@DbColumn("":"NoCache";"42256D73:0029F684";"Contacts";1)

I also have the following in DB 1

Telephone | TEXT field | COMPUTED FOR DISPLAY
a := @If( Customer_Name != "" ; @DbLookup("":"NoCache";"42256D73:0029F684";"Contacts";2) ; "" );
@If( @IsError( a ) ; "Lookup Failed" ; a )

FAX | TEXT field | COMPUTED FOR DISPLAY
b := @If( Customer_Name != "" ; @DbLookup("":"NoCache";"42256D73:0029F684";"Contacts";3) ; "" );
@If( @IsError( b ) ; "Lookup Failed" ; b )

EMAIL | TEXT field |COMPUTED FOR DISPLAY
c := @If( Customer_Name != "" ; @DbLookup("":"NoCache";"42256D73:0029F684";"Contacts";4) ; "" );
@If( @IsError( c ) ; "Lookup Failed" ; c )

This works 100% if there's ONLY 1 record in my contacts database! It will display my contact Customer name and I am able to select it, it then populates the rest of the fields, But as soon as I add another record to my contacts db 2 then it puts all the information next to each other seperated by a comma, here's how it looks if I have 2 Contacts

Customer Name : ABCD Company, QWERTY Company
Telephone: 123-12345,567-12345
Fax: 123-12345,567-12345
Email: name@name.com,name@hotmail.com

It should ONLY display 1 record!

Customer Name : ABCD Company
Telephone: 123-12345
Fax: 123-12345
Email: name@name.com


I also looked at the filed and the display, there are no commas that seperates them, there's no "Smith":" John" type of information, it's plain input, normal text, no fancy stuff!

Hope all the above makes sense!

Thanks

 

[daleflannery]

To lookup the telephone you will do this:
@DbLookup( "Notes":"NoCache" ; "42256D73:0029F684" ; "Contacts" ; Customer_Name ; 2 ) ;

Hope this helps.

Dale

Also a persons name is not always unique. A convenient way of avoiding this is to use some abstract key value that you can be sure is unique. One way of doing this is to have keyword values that are "First Last|DocumentUNID", that way the selected value appears as "First Last" but actually is the document unique id which can be used as a key into a view. But that, as they say, is another story.


Find me @ onlinecorporatesoftware.com

 

[collenr123]

Thanks, your explanation did the trick for me... it's working now finally!

Thanks again for your patience, it's I'm sure sometimes difficult to walk people through stuff which they know nothing off!

regards

Collen