Calculating the total sum of mulitple columns in a table

Calculating the total sum of mulitple columns in a table

Postby SWB » Thu Jun 19, 2014 11:34 am

I was wondering if there is an easy way to calculate the total sum of a column in a table. Not simply calculating the total table sum from line items, but independently calculating the sum of individual columns. For example:

Column A = # Packages
Column B = Gross Weight
Column C = Total Volume

I need to calculate the total number of packages, the total weight, and total volume of all line items in the table.

Thanks for any guidance :D 8)
SWB
Participant
 
Posts: 103
Joined: Tue Sep 23, 2008 10:33 am

Re: Calculating the total sum of mulitple columns in a table

Postby DavidCrewe » Fri Jun 20, 2014 1:07 am

Here is a function I use in one of my projects to get a table column sum... You should be able to use or amend for your purposes. Add the function in the project level script, and call it from anywhere.

fldName is the Table field name
cellPos is the column position

The function returns a Decimal, and in the case of an error (such as non-numeric values in cells etc), returns 0. You may need to alter the error handling to suit your specific needs.

Code: Select all
Function getTableColumnTotal(pXDoc As CASCADELib.CscXDocument,fldName As String,cellPos As Integer) As Decimal
   On Error GoTo errHAND

   'return a numeric value as the total of a particular table column
   Dim i As Integer
   getTableColumnTotal=0
   For i=0 To pXDoc.Fields.ItemByName(fldName).Table.Rows.Count-1
      getTableColumnTotal=getTableColumnTotal + CDec(pXDoc.Fields.ItemByName(fldName).Table.Rows(i).Cells(cellPos).Text)
   Next

   Exit Function

errHAND:
   getTableColumnTotal=0

End Function
David Crewe

Check out our set of custom add-ons for Kofax Capture - www.DavidCrewe.com/products.html
DavidCrewe
Participant
 
Posts: 232
Joined: Tue Apr 29, 2008 2:24 am
Location: UK

Re: Calculating the total sum of mulitple columns in a table

Postby SWB » Mon Jun 23, 2014 6:21 am

Thank you David! I will give it a whirl and let you know how it goes.
SWB
Participant
 
Posts: 103
Joined: Tue Sep 23, 2008 10:33 am

Re: Calculating the total sum of mulitple columns in a table

Postby dkekesi » Mon Jun 23, 2014 7:56 am

The problem with CDec function is that it is locale dependent: if you use multiple locales on the validation stations, where decimal separator is once marked with "." other times with ",", then CDec will fail. Instead format the cells using an amount formatter (which tries to be smart about the decimal separator), which will populate the Cell's DoubleValue property with a numeric value. You can safely do arithmetic operations on it, without worrying if contents are numeric or not.
Best Regards,

Daniel Kekesi
DocSoft Hungary
Image
dkekesi
Participant
 
Posts: 2569
Joined: Thu Dec 08, 2005 12:56 am
Location: Budapest, Hungary

Re: Calculating the total sum of mulitple columns in a table

Postby David Wright » Fri Jun 27, 2014 4:28 am

You also have the danger of your script failing on any OCR error.
I recommend using the following code which uses the Default Amount Formatter to convert the table cell values into Double values safely.
It also passes you back a boolean value which tells you if the sum is valid, because if you receive the value 0.00, you don't really know for sure that that is the table sum or not.

Please also avoid using Integer Type as it is 16 bit. You should use Long, which is 32 bit and what KTM uses.
Also avoid Decimal Type and use Double, which is what KTM uses.
The DefaultAmountFormatter is available as a global object. It you want to use another then use Project.FieldFormatters.ItemByName("ABC").FormatTableCell

Code: Select all
Public Function Table_CalculateSumByColumn(Table As CscXDocTable, columnName As String, ByRef bSumIsValid As Boolean) As Double
   Dim r As Long
   'Format each table cell in this column. This will set the .DoubleValue and the .DoubleFormatted parameters on each cell in the column.
   For r =0 To Table.Rows.Count-1
      DefaultAmountFormatter.FormatTableCell(Table.Rows(r).Cells.ItemByName(columnName))
   Next
   Table_CalculateSumByColumn=Table.GetColumnSumByName(columnName,bSumIsValid)
End Function
David Wright
Participant
 
Posts: 18
Joined: Mon Jul 17, 2006 3:34 am
Location: Vienna


Return to Kofax Transformation Modules General Discussion

Who is online

Users browsing this forum: No registered users and 2 guests