How do I use @DbColumn correctly?

Brian Olivier

unread,

Jan 26, 1996, 3:00:00 AM1/26/96

to

Hello,

In my company we already use a database with information about
our customers. Want I want is to use this information in another
application.
Via @DbColumn or @DbLookup I want to look up the name of the
customer in the first database. Automatically notes should then
paste the corresponding address and phone numbers in the
documents.

How can you make Notes do this automatically?. The other option I
have, is to just copy over all the data to the new database, but
that is the last thing I want to do.

All help is welcome.

Regards,

Brian

aav...@pel.com

unread,

Jan 29, 1996, 3:00:00 AM1/29/96

to

Hi, Brian!

You will need to use an @DbLookup or @DbColumn on each field that you want to
populate. The bad news is that the performance will be pretty bad if you have
a lot of these. I recommend that you make the fields "Computed When Composed"
or Editable (with the Db functions as the default) if possible, so the lookups
will only be done once. If you have to make them Computed, use a formula that
will NOT do the lookups if the document is only being read:

@If(!@IsNewDoc & @IsDocBeingLoaded; @Unavailable; @DbColumn(~~~))

with appropriate tests for null values in the lookup key.

_________ Hope this helps!
/ \
/ \ I must be a mushroom.
| | They keep me in the dark
^^^^^^^|^^|^^^^^^ and feed me lots of B.S.!
/ \
||\||/ | \\|| /||||/||\ || -- Ann Avila

In article <4eajaa$m8n$1...@mhadf.production.compuserve.com>,

Glenn A Thibert

unread,

Jan 29, 1996, 3:00:00 AM1/29/96

to

Taken from Brian Olivier's log...

>In my company we already use a database with information about
>our customers. Want I want is to use this information in another
>application.
>Via @DbColumn or @DbLookup I want to look up the name of the
>customer in the first database. Automatically notes should then
>paste the corresponding address and phone numbers in the
>documents.
>
>How can you make Notes do this automatically?. The other option I
>have, is to just copy over all the data to the new database, but
>that is the last thing I want to do.

here is a snippet of code we use in one of our db's to do this exact thing.
It's in a button on the form. Hope it helps.

REM "SELECT CUSTOMER";
VarCustomer := "";
VarLocation := "";
VarLocations := "";
VarCustomers := @DbColumn("Notes" : "Cache"; "062561DB:00760B46"; "(KWSites)";
2);
@If(@IsError(VarCustomers); ""; @Set("VarCustomer"; @Prompt([OKCANCELLIST];
"Select Customer"; "Select the customer(location)."; @If(Customer = "";
@Subset(VarCustomers; 1); Customer); VarCustomers)));
@SetField("Customer"; @Left(VarCustomer; "("));
@SetField("Location"; @Left(@Right(VarCustomer; "("); ")"))

ih...@c2.hinet.net

unread,

Jan 30, 1996, 3:00:00 AM1/30/96

to

Brian Olivier <10012...@CompuServe.COM> wrote:

>Hello,

>In my company we already use a database with information about
>our customers. Want I want is to use this information in another
>application.
>Via @DbColumn or @DbLookup I want to look up the name of the
>customer in the first database. Automatically notes should then
>paste the corresponding address and phone numbers in the
>documents.

You can create a computed-when-composed field, say Address, in your
second database with a formula:

@dblookup("notes":"nocache";firstservername:firstdbname;yourview;keyname;"Address")

assuming that your first database has a view(yourview) with customer
names in its first column.

You may also need to tools-refresh-alldocs your docs in second
database if there are already some docs.

Any further discussions are welcome.

James
>>>>>>>>>>>> James Hou <<<<<<<<<<<<
>
> 886-2-8830909 Ext 584
> A Real Taiwanese
> Chinese Name: I-Lang Hou
>
>>>>>>>>>>>>>>><<<<<<<<<<<<<<<<<<<<<<

Michael Hart

unread,

Feb 1, 1996, 3:00:00 AM2/1/96

to

aav...@pel.com wrote:

>Hi, Brian!

>You will need to use an @DbLookup or @DbColumn on each field that you want to
>populate. The bad news is that the performance will be pretty bad if you have
>a lot of these. I recommend that you make the fields "Computed When Composed"
>or Editable (with the Db functions as the default) if possible, so the lookups
>will only be done once. If you have to make them Computed, use a formula that
>will NOT do the lookups if the document is only being read:

That's one solution. WHATEVER you do, DO NOT use multiple @Dbxxx to
populate fields, whether at compose time or during read/edit. The
better solution is to plan ahead of time what data you're going to
need to pull from a particular form, and save it in a "lookup field"
when the source doc is saved. Then, you only need to do one @DbLookup
to pull _all_ the needed data in from that doc.

Example:
Form A has fields a-z on it.

Form B has fields aa-zz on it. Fields aa,gg,mm,nn,oo, and tt need to
be populated with the values from a particular Form A, such that
inheritance is not an option. Rather than do 6 @DbLookups, we put a
computed field 'Lookup' on Form A with the formula:
@If(
@IsDocBeingSaved;
a + "!#!" + g+ "!#!" + m + "!#!" + n+ "!#!" + o+ "!#!" + t ;
Lookup
)

The result of this is that all the data can now be retrieved with a
single lookup. And, the data in the computed field 'Lookup' is
refreshed everytime the Form A is edited and saved.

Once the data is retrieved, you simply parse it out into it's elements
and use what you need, discarding the rest. Here's one way you might
do it:
theLookUp := @DbLookup( whatever );
Var1 := @Word( theLookUp ; "!#!" ; 1 ) ;
Var2 := @Word( theLookUp ; "!#!" ; 2 ) ;
Var3 := @Word( theLookUp ; "!#!" ; 4 ) ;
Var4 := @Word( theLookUp ; "!#!" ; 5 ) ;

Here, we're ignoring whatever came in in positions 3 and 6.
The parsing example above is not always the best way to do it, but
it's one way.