Report Script: Filter Overdue Letter with specific age that is due

Introduction

A requirement to show Overdue Letter that is on specific age range in was brought to my attention. I decided to write this tutorial to show how we can manipulate the DataTable in the report.

Attempted to apply DataView.RowFilter on the detail table that is in a DataRelation was unsuccessful. Therefore, in this tutorial will delete the record in the DataTable that is not in the overdue age range.

Task in this tutorial

• Remove detail record, and show documents overdue age between 30-59.
• Calculate the total sum of amount due.

Product

AutoCount Accounting 1.8 / 1.9
Applicable to AutoCount Accounting 2.0 (require to manual modify some code)

[ Download Report Template] (AutoCount Accounting 1.8 / 1.9) Coming soon...

Report Script

Add using directive

1. Add directive at the top of Scripts, if has not already added.
   • using System.Data;

Add function of FilterDueAge

FilterDueAge removes detail table record that is not in the filter range of Age.

private void FilterDueAge(int fromAge, int toAge)
{
	//Get table of documents that are due (Detail table)
	DataTable dtDetail = (__report.DataSource as DataSet).Tables["Detail"];

	//Find document record that the age is not in the age range
	DataRow[] rows = dtDetail.Select(string.Format("Age < {0} OR Age > {1}", fromAge, toAge));

	//Delete document record
	foreach (DataRow row in rows)
	{ row.Delete(); }

	//Above execution may render to debtor has no overdue document,
	//below function is to remove debtor record that has no overdue document.
	RemoveEmptyMaster();
}

Add function of RemoveEmptyMaster

RemoveEmptyMaster function removes master record that has no Overdue document in detail.

private void RemoveEmptyMaster()
{
	//Get Master and Detail tables
	DataTable dtMaster = (__report.DataSource as DataSet).Tables["Master"];
	DataTable dtDetail = (__report.DataSource as DataSet).Tables["Detail"];

	string debtorCode;

	//Delete master record that has no detail (overdue document)
	foreach (DataRow rowM in dtMaster.Rows)
	{
		debtorCode = rowM["AccNo"].ToString();

		//Find DebtorCode in Detail Table
		DataRow[] selectRows = dtDetail.Select(string.Format("DebtorCode='{0}'", debtorCode));
		if (selectRows.Length == 0)
		{
			rowM.Delete();
		}
	}
}

Add function call in Report_BeforePrint event

Before this report is processed, add the function call in the event that is triggered at the beginning.

• Assign value of from age and to age for the function
• If the report is for overdue age that is greater than 60, assign value of 60 and 999.

private void Report_BeforePrint(object sender, System.Drawing.Printing.PrintEventArgs e)
{
	__report = Report as BCE.AutoCount.Report.BaseReport;

	//Filter document that the age is between 30 and 59.
	//The result of Overdue Letter will show document that the due age is 30 to 59.
	FilterDueAge(30, 59);
}

Go to menu

Go to top

Resources For AutoCount Software Developers