Integra for Notes Banner
  QuickReports Integra4Notes Integra Family of Products



Setting Computed Fields on Integra Imports?


When importing data from an Excel spreadsheet into a Lotus Notes database using Integra for Notes, in addition to being able to import columns of data into Notes fields, computations are able to be undertaken resulting in the computed value or values being placed into Notes fields.

Let us take as an example a spreadsheet which among other fields contains columns titled as follows:
  • Address1
  • Address2
  • City

while the Notes database has one single field called address. And a field on the database called fullname is to be set based on the columns called surname, name and title.

An Integra profile needs to be set to read the 4 fields above then concatenates them with a Carriage Return seperator.

So as to achieve this, three things need to be done:
  1. the Excel columns need to be tagged with the definable name. Let us assume the names shown above will be used as the tag names.
  2. the Integra import profile would need to have a field in the Selected Field List named after each of the tag names above.
  3. the formula callback or the Script Callback in the advanced tab would need to be used to define the values of the computed Notes field called Address

The example below outlines the code to be used to set the fields as outlined above.


statlist := "CB_BFORWRITECOM":"CB_BFORNOTESACTION":"CB_INITIALISE":
        "CB_TERMINATE":"CB_BFORCOMACTION":"CB_BFORREADNOTES";
envstat := @texttonumber(@environment("FormulaCbStatus"));
status := @subset(@subset(statlist;envstat);-1);


@if(
    status="CB_BFORNOTESACTION";
      @Do(
        @SetField("address" ;Address1+@NewLine+Address2+@NewLine+City);
        @SetField("fullname";Surname+" "+Name+" "+title)
      );
    ""
)


Since the fields Address1, Address2, City, Surname, Name and Title would be created on the document in the Notes database as a result of the import process, you may also choose to then delete these "excess" fields after the import is complete. This can be done by adding the following script in the ScriptCallback section of the Advanced Tab.

Const CB_INITIALISE = 3
Const CB_BFORREADNOTES = 6
Const CB_BFORREADCOM = 7
Const CB_BFORWRITECOM = 1
Const CB_AFTERWRITECOM = 9
Const CB_BFORWRITENOTES = 8
Const CB_BFORCOMACTION = 5
Const CB_BFORNOTESACTION = 2
Const CB_TERMINATE = 4

select case stats.cbstatus
case CB_INITIALISE
case CB_BFORREADNOTES
case CB_BFORREADCOM
case CB_BFORWRITECOM
case CB_AFTERWRITECOM
case CB_BFORWRITENOTES
case CB_BFORCOMACTION
case CB_BFORNOTESACTION

    Call stats.impdoc.removeitem("address1")
    Call stats.impdoc.removeitem("address2")
    Call stats.impdoc.removeitem("city")
    Call stats.impdoc.removeitem("surname")

    Call stats.impdoc.removeitem("name")
    Call stats.impdoc.removeitem("title")

case CB_TERMINATE
end select

.




FAQ# 0066
Bookmark and Share

 
QUICK LINKS
 
 

 

   Shireburn Software Ltd. 2008 - 2022