| |
|
|
Adding Formulae to an Excel Template in an Integra Profile
When Integra for Notes creates a default Excel template from the fields in the Selected Field List on the Integra profile, the field names are used to define the column headers as well as the tag names used to name the column. The fields in the selected field list can be populated either directly from a field in the Notes database or can be computed fields using Notes formula language or Lotuscript to define the values.
However in some cases, it is easier to create a formula in the Excel template itself so that when the data is push into the Excel template by Integra, the formula is computed by Excel. an example may be that there are 2 date fields in a Notes database which need to be exported and you would like the show the number of days between the two dates. because date arithmetic is challenging in Notes formula language for the novice, it may be preferable to export the two dates fields and then create a formula in Excel which simply subtracts the two fields.
Entry of a Column Formula
When wishing to have a column in the spreadsheet into which a formula will be placed to calculate the value, it is essential that a computed field is created in the Selected Field List of the Integra profile. No value should e associated with this computed field. Like the other fields in the Selected Field List, it should be placed in order among the other fields to determine the order of the columns in the spreadsheet. In other words, should you wish the computed formula to be created in Excel in the 3rd column, then the computed field should be listed third in the Selected Field List. It is perfectly possible to have a number of such computed fields. Once the Excel action button is selected, Integra offers to create a default Excel template which results in the creation of a column per field from the Integra Profile's Selected Field List, including any computed field.
The example below shows various fields coming from the Integra profile including the field called Years_Operational which is a blank computed field. In the example below, the Years_operational field is the current date less the field called Established. Lotus Notes is notoriously difficult to undertake date Arithmetic and it is easier to build a formula in Excel which subtracts the date field Established from the current date.
So, Where do we put the formula? As the placeholder help in row 5 indicates, formulas should be placed in this row and therefore the formula should in the example above be placed in E5.
In Integra for Notes 2.x, the help also indicates that row blank should be left blank. This is incorrect because when the profile is run, the formula in E5 above will be calculated but the formula will not be replicated down into the rest of column E and does not populate to all the datarows.
When Integra populates the spreadsheet it inserts rows between row 5 and 6 (in the example above which does not have different row offsets) and this odd behaviour is caused by the manner in which Excel inserts rows.
Excel has a strange way of inserting rows. It takes the cell formatting like colors, fonts and borders from the cells in the row above the insertion point (in this case row 5) but takes the formulae from the current row (in this case row 6).
It is due to this inconsistent behaviour that the default spreadsheet template is created by Integra with 2 rows (5 and 6) to allow this insertion to be corrected effected.
As a result, to allow the spreadsheet formula to be replicated down to all the subsequent rows in the column for the data block, when the formula is created in E5, it should also be copied into E6 despite the help place holder in row 6 saying "[ Keep this row blank - Integra will remove it at runtime ]" and the user manual indicating it should be left blank.
The user documentation and the Excel template comments will be corrected in Integra 3.x |
. |
|
FAQ# 0002
|
|
|
| |