Advanced Fund
Management Solutions

January 2007
Hi Everyone,

You have reporting access to every piece of information in your accounting database (except security data), and one type of report can extract the information you need-The User Report. Learn how it works. Positive Pay is a check security system that you and your bank(s) may appreciate. This month, we set it up and test it within AccuFund. As always, if you have any questions regarding an Accufund® issue, please contact me at (318) 253-8556, or email me at jgarv@afmss.com

Thanks,
Judy Garver

The User Report

The User Report is a category of reports that may be created with the Report/Forms Designer. They are designed to display information from any module: as a columnar report, as a standard report (you drop fields on the design screen), and as a form. This article will describe a General Ledger transaction report in columnar format. Although a simple report, it will show you how much flexibility you have in reporting transaction data.

The category of "User Report" is found on the list of Designer reports, as shown below when you browse your Report/Forms:

A User Report called "Account Activity" has been added, as highlighted.

To add your own report, click on the "Form" button.

The opening form is shown below. The name and description has been filled in.

Note that General Ledger has been selected as the "Component". If you click on the drop down arrow, you will see that you may select any of your installed AccuFund modules! This means you may write reports from your entire database.

The formatting option chosen for this report is "Columnar" (it is grayed out in this image because an existing report is shown- the formatting type cannot be changed once selected). On the left, you will see that there are seven property forms that may be viewed. We'll leave the default formatting information on this form and move to the Tables property form.

From the list of modules, select General Ledger, as shown below:

Double click on "Accounting". This will show the available General Ledger tables on the left, and the selected Accounting tables you have selected on the right, as seen below:

Click on the "Fields" property. Then, expand the GLact table by clicking on its plus sign. Double click on the "Account" field as shown below:

Double click on the "Description" field, also. These two actions will move those fields to the Assigned Fields pane on the right, as shown below:

Since we are creating a columnar report, these two fields will be the first two columns in the final report. Their order can be changed by highlighting an assigned fields and clicking on the Up or Down buttons.

Open the GLtac table and select the "ActivityDate" and "Description" fields. Finally, from the DLdst table, select the "Amount" field. Your assigned fields list should look appear as shown below:

Click on the Grouping property (as shown below) and then double click on the Detail, as highlighted below:

We can now sort the report results by any field we wish. We'll sort by account number and then by activity date (posting date). Open the Report Fields and double click on "GLactAccount and GLtacActivityDate. This will move them to the right pane, as seen below:

Click on the "OK" button. Click on the "OK" button near the top right of the form to save the report.

When you run the report, you should get results similar to those shown below.

We have only touched the surface as far as the User Report's capabilities. You'll want to play with this report, exploring grouping, filtering, etc. You'll find it worth the effort!

Positive Pay

Positive Pay is a method of insuring that the checks presented to your bank, and drawn on your accounts, are legitimate. For this to work your bank must be provided, in advance, information about the Payroll or AP checks you have cut. Each bank may have slightly different information requirements, but it typically includes check number, payee, account number, issue date and amount. When your checks are presented to the bank, the bank compares them to the file you have provided.

There are software packages available that can extract the information from accounting systems and send the information to your bank. You don't need one of them, however, since AccuFund can prepare the file for you! This article shows how you can set up your bank (in AccuFund) to prepare the file in accordance with your bank's requirements.

In this example we will assume that the bank needs your bank account number, the check number, the check date and the check amount. This should be in a comma separated format (in our example). A typical output line of the report should look like this:
12345678,0000106,20051215, 100.00

Where 12345678 is the account number, 0000106 is the check number, 20051215 is December 15, 2005 and 100.00 is the check amount.

To setup the bank, go to Setup/Banks. Highlight the bank, as shown below:

Double click on your bank. The form below opens: Click on the Positive Pay tab, as shown below:

Check the Positive Pay Active box and the bottom part of the form will become active. This is where we will place instructions that will be used to create the file in a format the bank requires. We are not checking the Header 1, Header 2 and Footer boxes because our bank does not want information on the first one or two lines of the file, nor does it need any information at the end of the file (the footer). We only need to provide instructions in the "Detail" area. This is where instructions are placed that will create the body of the file.

The expression shown in the Detail section of the form is repeated here:
'12345678' & ',' & RegisterReference & ',' & Format(RegisterActivityDate,@D12) & ',' & Format(RegisterAmount*-1,@N-9.2)

We will break down these instructions into bite size portions and explain them. The ‘12345678' portion is the bank account number. It is hard coded to appear on every line of the output. The single quotes tell the system to print what is between the quotes. The ampersand (&) concatenates (or "pulls together") the bank account number with whatever follows. In this case, what follows is a comma. Placing the comma between single quotes causes the comma to be printed. There will be no space between the account number and the comma. (If we wanted a space, we could use ‘ ,' instead.)

Next there is another ampersand. This means that immediately after the comma there will be another piece of information. RegisterReference means that the Reference will be pulled from the bank Register table. The check number is contained in this Reference (which is a database field). This is a shorthand way of telling the system what Table and Field it should pull. Another ampersand and comma follow.

Our next piece of information is the check date. This is the ActivityDate that is contained in the Register table (hence RegisterActivityDate). There is a problem with the date, however. It's stored in Julian format which will be a number such as 74000. We have to convert (format) that date into something that humans can read. We are using a Format command to do the conversion. The Format command is given the RegisterActivityDate between parentheses, followed by the instruction to present the date in @D12 format. The @D12 instruction is explained in the Advanced Features section of the Help file (press the function 1 key to get there). So, the Format (RegisterActivityDate,@D12) portion of our instructions tells the system to present the date in the YYYYMMDD format our bank needs. Another ampersand, comma and ampersand follow.

The last piece of information is the check amount. The database stores the amounts in a format that looks like this for one hundred dollars –100. The check amount is typically a minus amount (that's accounting for you!). We don't want to see the minus sign, and, we would like to see pennies, even if they are zero (one hundred dollars should look like this: 100.00). To get the pennies, we need to format the check amount. Format(RegisterAmount*-1,@N-9.2) will do everything we need. The RegisterAmount is the check amount. The *-1 multiplies the amount by a minus one). Numeric formatting is done with @N commands. In this case @N-9.2 tells the system to format the amount as a number with up to nine digits to the left of the decimal and two digits to the right.

With the above expression, we are telling the system to combine (concatenate) four pieces of information on each line of the file output. Click on the "OK" button to save this work. All we have to do now is run our special Positive Pay report.

To create the Positive Pay file, go to Browse Paid Bills. Highlight the appropriate check run (this will normally be your most recent one because you want to get the file to the bank soon after you cut checks). Click on the "Pos Pay" button at the bottom center of the form. The form is shown below:

Once you click on the "Pos Pay" button, a dialog box, below, will open and ask for a file name and where you want to store it. Upon clicking the "OK" button, the file is immediately created in the directory you have specified.

Below, the file has been opened for your inspection. The file would then be sent to the bank in accordance with procedures you have established with them.

If you require additional check security, talk to your bank and give the Positive Pay feature a try!

Note: Your bank may require information that is different than in our sample. The Register table does contain a few other fields (information) that you could use. Contact us for information on additional fields and how to identify them in the database.

Did You Know...?

Financial Report. When creating a financial report, you have the option of page breaking by element. This feature is found on the Report Options tab. Choose "By Element". If you wish to page break every time the fund changes, choose the fund element. You may page break by more than one segment of your account. For example, you could break by the fund and department segments. Your page break can also have an description taken from the segment's classification.

Accounts Payable. You may import a list of checks, that have cleared your bank, into the reconciliation feature. With the Browse Reconciliations form open, you will see an import button. It opens an import wizard similar to all the other import wizards in AccuFund. The file you get from the bank may be comma separated, fixed field, or Excel worksheet format.

Inventory. The inventory system tracks internal inventory issues (to your departments, for example) and inventory items that are sold to your customers. In the case of your departments, when an item is issued to them, the department is expensed. When selling to customers, the inventory item is expensed to the appropriate cost of goods sold account. Preferred vendors and part numbers are stored, which makes reorder much easier.