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
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
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.