Integra for Notes Banner
  QuickReports Integra4Notes Integra Family of Products



Compute Fields on Importing of Data from Excel

When creating an Integra profile for importing data from an Excel spreadsheet, in addition to importing data from specific columns into fields within the chosen Notes form, a requirement frequently arises to undertake a calculation computing the value of a Notes field based upon some calculation of a number of columns being imported.

An example of this may be two columns in Excel, one called Address1 and the other called Address2 which contain the first 2 lines of a customer's address which are required to be concatenated together and separated by a carriage return. It would be important that the Integra profile contains a field in the Selected Field list called Address1 and another field called Address2 as these will be the recipient of the imported data. As a result, a field called Address1 and another called Address2 will be created on the Notes document.

Then the Integra profile would be set to concatenate the contents of these two fields (address1 and Address2, separated by a carriage return) and to update this value into the field caled Address contained on the Notes form.

After this step, the redundant fields called Address1 and Address2 contained on the form can be removed again.

This is all achieved by putting the following code into the Script Callback sub-tab on the Advanced tab, particularly focusing on the BforWriteNotes event. The BforWriteNotes event is executed as each Notes document is being written into the database.

Const CB_INITIALISE        = 3
Const CB_BFORREADCOM       = 7
Const CB_BFORWRITENOTES    = 2
Const CB_TERMINATE         = 4

select case stats.cbstatus
case CB_INITIALISE
case CB_BFORREADCOM
case CB_BFORWRITENOTES
  stats.impdoc.address = stats.impdoc.address1(0) & chr(13) & chr(10) & stats.impdoc.address2(0)  
' sets value of address to address1 + return + address2

  call stats.impdoc.removeitem("address1")  
' removes the field called address1
  call stats.impdoc.removeitem("address2")  
' removes the field called address2

case CB_TERMINATE
end select


Other scenarios in which this could be used is the setting of a field to a value which is derived as a result of an @DBLookup function either within the same or a different Notes database or even another ODBC datasource.

.




FAQ# 0012
Bookmark and Share

 
QUICK LINKS
 
 

 

   Shireburn Software Ltd. 2008 - 2014