| |
|
|
Access data from multiple databases in Integra for Notes using formula language?
Integra for Notes allows data to be accessed from multiple databases and for this data to be combined into one data export stream to an Excel, Word or text file. These multiple databases can also be a combination of Notes and non-Notes databases such as Oracle, DB2, Access, FoxPro etc.
Integra supports access to external databases using either formula language or Lotuscript. This FAQ will address the issue of how to use computed fields in Integra which use the Lotus Notes formula language to access data from other databases.
An essential means in Integra through which data is identified for transfer to Word, Excel or Text files is the Selected Field List. The fields to be exported or to be contained within this selected field list. The fields in the Selected Field List can either be fields found on the Form defined in the Integra profile or could be computed or Formula fields.
Any valid Notes formula can be attached to a computed field. This results for instance in the ability to calculate a value or some form of computational assessment such as Surname + " " + FirstName. A formula can also be defined to lookup a value within the same or an existing database using the standard Notes functions @dbLookup or @DbColumn. For example:
@dblookup("":"";"servername":"database_name";"lookup_view";LinkedFieldName;"NameOfFieldToBeReturned")
Whereas usually, one would either leave the servername and database_name parameters blank (a null string) to indicate that you want to use the current database, since at the time of running the Integra icon the user is moved to the Integra database, the values returned of the current server and database would be the Integra database and not the database that you are trying to access data from. Using programmatic means such as the usual @dbName would not help either for the same reason.
To overcome the problem of creating formulas that have fixed links that need to change when the database location is changed, you must use the replica id of the database Instead of using the servername and databasename, for instance:
@dblookup("":"";"85255CEB:0032AC04";"lookupview";LinkedFieldName;"NameOfFieldToBeReturned")
Alternatively you could create a database profile that contains either two fields for the servername and databasename or a single field storing the replica id of the database you need to access, for instance:
rid := @GetProfileField("LinkedDatabaseReplicaID";"MySetupProfile");
@dblookup("":"";rid;"lookupview";LinkedFieldName;"NameOfFieldToBeReturned")
A more thorough version of the formula is the one below which also caters for the possibility that the LinkedFieldName supplied is not found in the view and provides management of the error message that the user would usually be presented with.
rid := @GetProfileField("LinkedDatabaseReplicaID";"MySetupProfile");
res := @dblookup("":"";rid;"lookupview";LinkedFieldName;"NameOfFieldToBeReturned");
@if(@iserror(res);"";res)
A scenario where this could be used is within a sales application which needs to lookup salesman information from an HR system. Imagine that Integra was required to export sales analysis information from a sales system but is also required to obtain the percentage commission applicable to that salesman from the HR system. Imagine that the replica ID of the HR system was stored in a field called LinkedDatabaseReplicaID within a Profile Document called MySetupProfile stored in the database.
Imagine that the database contained a view called People listing salesman in sorted in order of Salesman code and that the salesman's commission rate was stored in a field called CommissionRate, the following formula should be used in the computed field stored in Selected Field List:
rid := @GetProfileField("LinkedDatabaseReplicaID";"MySetupProfile");
vw :="People" ;
keyfld :=SalesmanCode ;
resfld := "Commission" ;
res :=
@dblookup("":""; rid; vw; keyfld; resfld);
@if(@iserror(res);"";res)
Related FAQ's:
|
. |
|
FAQ# 0001
|
|
|
| |