Caculating page wise sub totals and totals

[Former Member]

Friends,

I am working on an ADOBE form, where i need to calculate subtotals and grand totals for the invoice lines displayed.

I achieved the functionality in the smartforms by defining a different window under the main window(this contains line amounts). For the new window where the subtotal is expected to print i triggerred the event 'Before the end of main window' to print the totals before the lines flow to the next page by auto page break in the main window.

I tried in the forms putting different content area(other than the main content area where table lines are printed) for the subtotals in the layout designer and manipulated using all the pagination options avialable in the table properties, but in vain.

What exatly is the equivalent for the event 'Before the end of main window' as that of smart forms.

Any inputs on this?

regards

Nalinikanth.

[Former Member]

To get the page totals either you can use tablecalcs or you can add the below script in Layout:ready event of that field for which you are assigning the page total value.

[code]Var reccnt = $.table .data.all.length “ to get record count

Var curpag = $layout.page ( ref ( $ ) ) /* current page number

Var tot = 0

For I = 0 upto ( recnt - 1 ) step 1 do

/* gives record page number

Var recpag = $layout.page ( ref ( $.table.data.fieldname ) )

If ( recpag == curpag ) then

Tot = tot + $.table.data.fieldname

Endif

Endfor[/code]

/* assign the value of total to the field

$.rawValue = tot

If you want to display carry forwards then you need to compare the record page number with the previous page number and use the same logic as above.

[Former Member]

Hi Nalinikanth,

assumed you have a table created with subforms like

MyTable

- Header

(text elements)

- Data

(repeating subform with line elements)

you can create a line with subtotal as following:

1. Define a new line below "MyTable" for the subtotals with name e.g. "Subtotals" and insert elements (similar to the ones in "Data").

2. In subform "Data": palette Object -> Pagination -> Overflow Trailer: "Subtotals"

3. In Hierarchy palette, right-click on "Subtotals" -> Insert Script Object

4. name this script object "tableCalcs" and insert following code

/**
******************************************************************************************************
*   Table Calculation Functions
*
*	To invoke these functions:
*
*   syntax:	scriptObjectSOM.obj.functionName(sFieldName, [startPage], [endPage]);
*   example:	tableCalcs.Sum("Field1", 2, 3);
*   notes: 		
*				-The SOM expression of the Script Object may vary upon its position in the Hierarchy.
*				-The start and end page arguments are 0-based numbers or a reference to an XFA object.
*
******************************************************************************************************
*/


/*
DO NOT MODIFY THE CODE BEYOND THIS POINT - 705.20051114114126.253659.250577 - tableCalcs.xfo
*/

var obj = new CalcObject();

function CalcObject()
{
	this.Sum = _doSum;
	this.Average = _doAverage;
	this.Count = _doCount;
}

/** 
*	Calculate the sum of all fields with the same name.
* 
* 	NUMBER Sum(STRING sFieldName, [NUMBER pageNum])
* 	NUMBER Sum(STRING sFieldName, [OBJECT xfaObject])
* 	NUMBER Sum(STRING sFieldName, [NUMBER startPage], [NUMBER endPage])
* 	NUMBER Sum(STRING sFieldName, [OBJECT xfaObject], [OBJECT xfaObject])
*
*	param1:	STRING; Name of field to calculate sum of
*	param2:	NUMBER; Start page for calculation
*	param2:	OBJECT; XFA object on the page calculation will start on
*	param3:	NUMBER; End page for calculation
*	param3:	OBJECT; XFA object on the page calculation will end on
*
*	return: NUMBER; Sum of all fields
*
*/
function _doSum(fieldName)
{
	var fieldSum = null;
	
	var startPage = 0;
	var endPage = 0;
	var lengthOfDecimal = 0;


	if (arguments.length == 2) {
		startPage = _GetPageNum(arguments[1]);
		endPage = startPage;
	} else if (arguments.length == 3){
		startPage = _GetPageNum(arguments[1]);
		endPage = _GetPageNum(arguments[2]);
	} else {
		startPage = 0;
		endPage = xfa.layout.pageCount() - 1;
	}

	if ((startPage < 0) || (endPage < 0) || (startPage > endPage)) 
		throw new PageNumberException(startPage, endPage);

	var curPage = startPage;
	while (curPage <= endPage) {
		// Get the field containers on the current page
		var oFields = xfa.layout.pageContent(curPage, "field");
		var fieldCount = oFields.length;
		// Continue if fields were found on the page
		if (fieldCount > 0) {
			var i = 0;
			for (i=0; i < fieldCount; i++) {
				if (oFields.item(i).name == fieldName) {
					//only count non-empty/numeric fields
					var fieldVal = _GetFieldVal(oFields.item(i));
					if (fieldVal != null) {					
						fieldSum += fieldVal;
					}
					if (_GetLengthOfField(oFields.item(i)) > lengthOfDecimal) {
						lengthOfDecimal = _GetLengthOfField(oFields.item(i));
					}
				}
			}
		}

		curPage++;
	}
	/*
	if (fieldSum == null) 
		throw new FieldNotFoundException(fieldName);
	*/
	if(lengthOfDecimal > 0) {
		return fieldSum.toFixed(lengthOfDecimal);
	} 
	return fieldSum;
}

/** 
*	Calculate the average of all fields with the same name.
* 
* 	NUMBER Average(STRING sFieldName, [NUMBER pageNum])
* 	NUMBER Average(STRING sFieldName, [OBJECT xfaObject])
* 	NUMBER Average(STRING sFieldName, [NUMBER startPage], [NUMBER endPage])
* 	NUMBER Average(STRING sFieldName, [OBJECT xfaObject], [OBJECT xfaObject])
*
*	param1:	STRING; Name of field to calculate average of
*	param2:	NUMBER; Start page for calculation
*	param2:	OBJECT; XFA object on the page calculation will start on
*	param3:	NUMBER; End page for calculation
*	param3:	OBJECT; XFA object on the page calculation will end on
*
*	return: NUMBER; Average of all fields
*
*/
function _doAverage(fieldName)
{
	var fieldCount = null;
	var fieldSum = null;
	
	var startPage = 0;
	var endPage = 0;
	var lengthOfDecimal = 0;

	if (arguments.length == 2) {
		startPage = _GetPageNum(arguments[1]);
		endPage = startPage;
	} else if (arguments.length == 3){
		startPage = _GetPageNum(arguments[1]);
		endPage = _GetPageNum(arguments[2]);
	} else {
		startPage = 0;
		endPage = xfa.layout.pageCount() - 1;
	}

	if ((startPage < 0) || (endPage < 0) || (startPage > endPage)) 
		throw new PageNumberException(startPage, endPage);

	var curPage = startPage;
	while (curPage <= endPage) {
		// Get the field containers on the current page
		var oFields = xfa.layout.pageContent(curPage, "field");
		var allFields = oFields.length;

		// Continue if fields were found on the page
		if (allFields > 0) {
			var i = 0;
			for (i=0; i < allFields; i++) {
				if (oFields.item(i).name == fieldName) {
					//only count non-empty/numeric fields
					var fieldVal = _GetFieldVal(oFields.item(i));
					if (fieldVal != null) {
						fieldCount ++;
						fieldSum += fieldVal;
					}
					if (_GetLengthOfField(oFields.item(i)) > lengthOfDecimal) {
						lengthOfDecimal = _GetLengthOfField(oFields.item(i));
					}
				}
			}
		}

		curPage++;
	}

	if (fieldCount == null) 
		throw new FieldNotFoundException(fieldName);

	if (fieldCount != null && fieldSum != null && fieldCount > 0) {
		var average = fieldSum / fieldCount;
		if(lengthOfDecimal > 0) {
			return average.toFixed(lengthOfDecimal);
		}
		return average; 
	}
	else {
		return null;
	}
}

/** 
*	Calculate the number of fields with the same name.
* 
* 	NUMBER Count(STRING sFieldName, [NUMBER pageNum])
* 	NUMBER Count(STRING sFieldName, [OBJECT xfaObject])
* 	NUMBER Count(STRING sFieldName, [NUMBER startPage], [NUMBER endPage])
* 	NUMBER Count(STRING sFieldName, [OBJECT xfaObject], [OBJECT xfaObject])
*
*	param1:	STRING; Name of field to calculate number of
*	param2:	NUMBER; Start page for calculation
*	param2:	OBJECT; XFA object on the page calculation will start on
*	param3:	NUMBER; End page for calculation
*	param3:	OBJECT; XFA object on the page calculation will end on
*
*	return: NUMBER; Number of fields found
*
*/
function _doCount(fieldName)
{
	var fieldCount = null;
	
	var startPage = 0;

	var endPage = 0;

	if (arguments.length == 2) {
		startPage = _GetPageNum(arguments[1]);
		endPage = startPage;
	} else if (arguments.length == 3){
		startPage = _GetPageNum(arguments[1]);
		endPage = _GetPageNum(arguments[2]);
	} else {
		startPage = 0;
		endPage = xfa.layout.pageCount() - 1;
	}

	if ((startPage < 0) || (endPage < 0) || (startPage > endPage)) 
		throw new PageNumberException(startPage, endPage);

	var curPage = startPage;
	while (curPage <= endPage) {
		// Get the field containers on the current page
		var oFields = xfa.layout.pageContent(curPage, "field");
		var allFields = oFields.length;

		// Continue if fields were found on the page
		if (allFields > 0) {
			var i = 0;
			for (i=0; i < allFields; i++) {
				if (oFields.item(i).name == fieldName) {
					//only count non-empty/numeric fields
					var fieldVal = _GetFieldVal(oFields.item(i));
					if (fieldVal != null) {
						fieldCount ++;
					}
				}
			}
		}

		curPage++;
	}
	
	if (fieldCount == null) 
		throw new FieldNotFoundException(fieldName);

	return fieldCount;
}

function _GetFieldVal(obj)
{
	var fieldVal = obj.rawValue;
	if (typeof fieldVal != "number") {					
		if (typeof fieldVal == "string" && fieldVal.length > 0) {
			fieldVal = parseFloat(fieldVal);
			if (isNaN(fieldVal))
				fieldVal = null;
		} else {
			fieldVal = null;
		}
	}
	return fieldVal;
}

function _GetPageNum(val)
{
	var pageNum = -1;
	if (typeof val == "number") {					
		pageNum = val;
	}
	else if (typeof val == "object") {
		if (val != null) {			
			pageNum = xfa.layout.page(val) - 1
		}
	}
	return pageNum;

}

function _GetLengthOfField(obj)
{
	var fieldVal = obj.rawValue;
	if (fieldVal != null) {
		if (typeof fieldVal == "number") {
		 fieldVal = new String(fieldVal);
		}
		var index = fieldVal.indexOf(".");
		if (index > -1) {
			return fieldVal.length - index - 1;
		}
	}
	return 0;
}


function getExceptionString() { return this.message; }

function FieldNotFoundException(fieldName)
{
	this.message = "Calculation failed: No non-empty fields found called '" + fieldName + "'.";
	this.name = "FieldNotFoundException";
	this.toString = getExceptionString;
}

function PageNumberException(startPage, endPage)
{
	this.message = "Calculation failed: Incorrect page references (start page = " + startPage + ", end page = " + endPage + ").";
	this.name = "PageNumberException";
	this.toString = getExceptionString;

// END OF DO NOT MODIFY	
}

5. In the subtotal fields, add script for event "calculate":

this.rawValue = this.parent.variables.tableCalcs.obj.Sum("AMOUNT", 0, this);

where "AMOUNT" is the name of the referenced field.

HTH & best regards,

Carlo

[Former Member]

Drag & drop the table into the layout.

Goto the hierarchy and select the row and change the row type to 'Body type' in Object tab.

Select the row and hit Ctrl-D to create a new row. Change the row type to Footer.

Look for the option which says 'Repeat Footer for each page' and check it