| |
|
|
How can I export the categories of a categorised view to Excel only?
With a few lines of Lotus Script in the Callback Script field (Advanced tab of the Integra profile) it is possible to export only categories to Excel and ignore the details (documents) within the category.
The first explained method is used for profiles using the "All Documents in View" option with a pre-selected view. In this case it is necessary to enter a formula into the Category field of the Integra profile. To do this, select the Save As... option so that the Category Formula field appears. Enter a formula that references to a field on the Notes document that is used also in the view for the categorisation. This as the order of the view categorisation has to match the categories evaluated for inserting category rows into Excel. Remove the SaveAs... option if it is not to be used for the profile.
As the first field in the Selected Field List add a computed (formula) field called Category and leave the formula set to None. Then add all the other fields (columns) that are to be exported to Excel. When exporting categories only, all other columns can assumed to be fields to export certain totals. The fields selected for the total columns can either be real numeric fields on the selected form or computed fields with a formula entered in the Integra profile. However, the totals itself cannot be calculated by Integra for Notes and therefore this also has to be done in the Callback Script.
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
Dim totals() As Variant
Dim x As Integer
Select Case stats.cbstatus
Case CB_INITIALISE
Redim totals (4) As Variant
stats.uservar = totals
Case CB_BFORREADNOTES
Case CB_BFORREADCOM
Case CB_BFORWRITECOM
' always stop Integra from exporting. Categories are exported from here
export.continue = False
If stats.KeyCnt = 0 Then
' first time set category
stats.uservar(0) = export.Record.FValue(0)
' first time reset total values temporarely stored in stats.uservar
stats.uservar(1) = 0
stats.uservar(2) = 0
stats.uservar(3) = 0
stats.uservar(4) = 0
ElseIf Not stats.Category(stats.KeyCnt-1) = stats.Category(stats.KeyCnt) Then
' change in category. update export values and call writenext method
tmpcat = export.Record.FValue(0)
export.Record.FValue(0) = stats.uservar(0)
export.Record.FValue(1) = stats.uservar(1)
export.Record.FValue(2) = stats.uservar(2)
export.Record.FValue(3) = stats.uservar(3)
export.Record.FValue(4) = stats.uservar(4)
' export category row to Excel
Call export.WriteNext(stats)
' reset current total
stats.uservar(0) = tmpcat
stats.uservar(1) = 0
stats.uservar(2) = 0
stats.uservar(3) = 0
stats.uservar(4) = 0
End If
' add field values to temporary totals.
For x = 1 To 4
If Not Isnumeric(export.Record.FValue(x)) Then
export.Record.FValue(x) = Val(export.Record.FValue(x))
End If
Next
stats.uservar(1) = stats.uservar(1) + export.Record.FValue(1)
stats.uservar(2) = stats.uservar(2) + export.Record.FValue(2)
stats.uservar(3) = stats.uservar(3) + export.Record.FValue(3)
stats.uservar(4) = stats.uservar(4) + export.Record.FValue(4)
If stats.KeyCnt = 0 Then
ElseIf Not stats.Category(stats.KeyCnt-1) = stats.Category(stats.KeyCnt) Then
Elseif stats.KeyCnt+1 = stats.KeyNum Then
' Last document exported
export.Record.FValue(0) = stats.uservar(0)
export.Record.FValue(1) = stats.uservar(1)
export.Record.FValue(2) = stats.uservar(2)
export.Record.FValue(3) = stats.uservar(3)
export.Record.FValue(4) = stats.uservar(4)
' export category row to Excel
Call export.WriteNext(stats)
End If
Case CB_AFTERWRITECOM
Case CB_BFORWRITENOTES
Case CB_BFORCOMACTION
Case CB_BFORNOTESACTION
Case CB_TERMINATE
End Select
End Sub
The second method can be used for profiles using the "Selected Documents in Active View" option. In this case Integra is collecting category information itself (provided the active view is categorised). Thefore we do not need to enter a formula into the Category field of the Integra profile.
Everything else with regards to the Selected Field List explained in the first method is still applicable. Also the Callback Script is very similar. It only differs in one line of code (see line in bold).
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
Dim totals() As Variant
Dim x As Integer
Select Case stats.cbstatus
Case CB_INITIALISE
Redim totals (4) As Variant
Case CB_BFORREADNOTES
Case CB_BFORREADCOM
Case CB_BFORWRITECOM
' always stop Integra from exporting. Categories are exported from here
export.continue = False
If stats.KeyCnt = 0 Then
' first time set category
stats.uservar(0) = export.Record.FValue(0)
' first time reset total values temporarely stored in stats.uservar
stats.uservar(1) = 0
stats.uservar(2) = 0
stats.uservar(3) = 0
stats.uservar(4) = 0
Elseif Not stats.CatStr(stats.KeyCnt-1) = stats.CatStr(stats.KeyCnt) Then
' change in category. update export values and call writenext method
tmpcat = export.Record.FValue(0)
export.Record.FValue(0) = stats.uservar(0)
export.Record.FValue(1) = stats.uservar(1)
export.Record.FValue(2) = stats.uservar(2)
export.Record.FValue(3) = stats.uservar(3)
export.Record.FValue(4) = stats.uservar(4)
' export category row to Excel
Call export.WriteNext(stats)
' reset current total
stats.uservar(0) = tmpcat
stats.uservar(1) = 0
stats.uservar(2) = 0
stats.uservar(3) = 0
stats.uservar(4) = 0
End If
' add field values to temporary totals.
For x = 1 To 4
If Not Isnumeric(export.Record.FValue(x)) Then
export.Record.FValue(x) = Val(export.Record.FValue(x))
End If
Next
stats.uservar(1) = stats.uservar(1) + export.Record.FValue(1)
stats.uservar(2) = stats.uservar(2) + export.Record.FValue(2)
stats.uservar(3) = stats.uservar(3) + export.Record.FValue(3)
stats.uservar(4) = stats.uservar(4) + export.Record.FValue(4)
If stats.KeyCnt = 0 Then
Elseif Not stats.CatStr(stats.KeyCnt-1) = stats.CatStr(stats.KeyCnt) Then
Elseif stats.KeyCnt+1 = stats.KeyNum Then
' Last document exported
export.Record.FValue(0) = stats.uservar(0)
export.Record.FValue(1) = stats.uservar(1)
export.Record.FValue(2) = stats.uservar(2)
export.Record.FValue(3) = stats.uservar(3)
export.Record.FValue(4) = stats.uservar(4)
' export category row to Excel
Call export.WriteNext(stats)
End If
Case CB_AFTERWRITECOM
Case CB_BFORWRITENOTES
Case CB_BFORCOMACTION
Case CB_BFORNOTESACTION
Case CB_TERMINATE
End Select
End Sub
A modification is required to the above examples if a new line is created for each category and the counter is not being re-set, the totals are accumulated and the category is not written.
- In the Script Callback tab, set temporary variables for the fields in the Selected Field List. These need to be placed below the line
tmpcat = export.Record.FValue(0)
e.g. tmpjq = export.Record.FValue(1)
- In the Script Callback tab, restore the temporary values back into the selected field list. This is done by placing the lines of code (e.g. export.Record.FValue(1) = tmpjq) below the block of code which is below the comment "reset current total" and before the "end if" statement.
Having provided the above examples, it must be said that depending on your situation, an alternative method might be more viable. Excel itself actually provides this alternative functionality: the sorting and grouping facilities. You could export all documents and then sort and group in Excel. Which way to go depends largely on how much Notes documents are to be exported. Obviously if we're talking about several hundreds or thousands of documents, processing time becomes an issue and therefore the advanced coding solution with the mentioned modifications would be more appropriate then using the Excel grouping feature. It's important to know that the most time consuming process in an Excel export is the writing to the Excel COM object. Simply reading the Notes documents to calculate totals (skipping most documents for export as only categories are to be exported) doesn't take up much time... |
. |
|
FAQ# 0041
|
|
|
| |