Advanced Fund
Management Solutions

February 2007
Hi Everyone,

This issue is dedicated to database expressions that pull information from the database. The first is called dbGet (as in get something from the database). It's used in reporting (the Report Designer) and calculations. The second article covers expressions that can help you get the information you want into Financial Report Writer columns. Both articles will help you take the next step up in your report writing! 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

Reporting With the dbGet Function

When using the Reports/Forms Designer to create standard reports, you may have relied on the Field Shortcuts list (that opens when you are in the Design screen) to drag and drop fields on your report. As you've noticed, the Field Shortcuts list has many choices on it that will provide most of the information you will need. For those times when those predetermined choices are not enough, though, you may want to use the dbGet function to extract information directly from your database. This article shows you how the dbGet function works, provides an easy way to find what information is available to the dbGet function, and gives some examples.

First, let's take a look at where the dbGet is used in the Report Designer. The image shown below shows part of the Detail band in the Designer view of one of the standard reports that comes with the system (zA/P Distribution). A field has been dragged into the Detail band, and it has been right clicked with the mouse. The Properties choice has been selected, which opens the Update Control shown below:

The cursor, in the Update Control form below, indicates where we may put the expressions and functions required to bring the correct information into our report. (The word "Field" appears there because a field was dragged into the Detail band.)

Note: The dbGet function is not only used in reports, but may also be found in Utility Billing, and Payroll calculations, for example.

When the dbGet function is used, it must follow a certain syntax (set of rules). It must take the following form:

dbGet(dbTable, dbField, dbInstance, dbDate)

The items shown in red (they are called "arguments") must be replaced by the proper information. dbTable is where you place the name of the database table that contains the information you are trying to retrieve. dbField is where you place the name of the field (piece of information) in the table. The dbInstance argument is where you limit the number of values that will be retrieved (such as twenty invoices, for example). dbDate is used to limit, by date, what will be retrieved (this can be a date range, too). Most of the time you will only use the dbTable and dbField arguments, which makes the dbGet function much easier to use!

About now you are probably thinking "how am I going to know what table and fields I need?" This is where the Help system rescues all of us! Click on the "Help" menu item at the top of your screen and then select "Contents". The following form opens:

In the Search field, type in dbTable:, as shown, then click on the "List Topics" button. The results are shown in the window at the bottom left of the form. Each result will contain the tables and fields for your modules. The first one, highlighted, is for the paid AP billing fields. If you double click on it, the following information is provided:

Above, you can see that our search for dbTable has revealed that the database contains a table called 'Bill'. Below that you see the names of the fields in the table, with the first being called 'APAmountPaid', followed by a description of what the field contains (the amount paid on an invoice, in this case). Use this technique to learn what tables and fields are available to you in any module.

Note: When you opt to create a "Columnar" report in the Report Designer, you will see a list of tables and fields, as well. That is another way to learn what tables and fields are available to you for reporting purposes. When creating a standard report (which is not necessarily columnar in nature), the help files will prove especially useful in learning how to use the dbGet function. Don't forget, the dbGet function is also available for calculations in other modules.

Here are several examples of the dbGet function. The Help files will help you with the dbGet syntax, and whether all of the arguments are required, or not. In addition, at the bottom of the help screen you will see related tables.

dbGet('Pay','Name') - Reports the Payee's name from the 'Pay' table.
dbGet('APPaid','AmountPaid',DATE(02,01,2004),DATE(02,29,2004)) - Reports the amount paid on invoices between the dates specified.
dbGet('Distribution','List','AccountDescription',25) - Shows the first 25 characters of the account descriptions used in a journal entry.
dbGet('ARInvoice','Total','ARTotalPaid') - Reports the total amount paid on all invoices.
dbGet('Address', 'Address', 'Primary') - Reports the primary address.

You can't use just any dbGet in any report. To find out what information can be brought into what type of report, open Help. Search for "accounting components" (use the quotation marks). Choose the module in which you are interested (Accounts Payable, for example). Then open the type of report (such as A/P Accounting Document). At the bottom of the screen that opens you will see Data/Fields (see image below). This shows you what dbGets will work with that report (click on the dbGet for details).

Practice using the dbGet function in existing reports by adding a field in the design screen of the appropriate report. Through practice you will learn a powerful reporting feature.

Note: If you need to use a dbGet in a report filter, it is recommended that you place the dbGet in a Variable and use the Variable in the filter.

Column Title Expressions

This article discusses some of the expressions that you may use in the Financial Report writer column titles. In column titles, you may want to show report dates, or multiple column titles, or a combination of both. All of this useful information may be displayed using expressions available to you in the report writer. When learning how these expressions work, it is helpful to make a copy of an existing report (one of those provided by AccuFund will work) and then modify the column titles and observe the results when you run the report.

One of the most common requirements is to place more than one title at the top of a column. For example a portion of a combined balance sheet is shown below:

Note that each column has two titles. General Fund, for example, shows up on two lines. The instructions for entering the column titles are shown below:

The second column is shown above. In the Title box is the expression 'General' & nl & 'Fund'. Any text that will appear in the column titles must be enclosed in single quotation marks. Thus, General and Fund are surrounded by single quotes. The ampersand character (&) is used to concatenate (join) text or instructions. In this case, the system will print the word General and then find that nl is immediately following. The characters nl represent a command (since they are not surrounded by single quotes). The command means new line. After printing the word General, the system will go to the next line (similar to the old carriage return/line feed command). Now that we are on the second line, the system finds that the word Fund is surrounded by single quotes and prints that word. This can be continued for multiple lines. 'General' & nl & 'Fund' & nl & 'Actuals' would result in the following column title:

General
Fund
Actuals

The above example involved only text that we provided. And, it is always the same. We may wish to have variable information appear in the column titles, such as the report date (the date the report is run) . With the correct expression, we can instruct the system to bring in certain pre-defined information automatically. To bring in the report date we could use the following expression:

'General' & nl & 'Fund' & nl & ReportDate. Here we are using a pre-defined variable called ReportDate to bring in the report date! Look at the title on column 2 now:

The date came in as a number (75302). This is the Julian date, and it should be expressed in a more familiar format. This brings us to the Format function. For help with the Format function and Date formats, go to Help and click on the Index tab. Type in Result Pictures. Select Date Pictures in the right hand window. There are a number of ways to format the date, such as 2/09/2007, Feb 9, 2007, 9 Feb 07, etc. Each format has its own "picture", or command. Here's how we can format the ReportDate in 2/09/2007 format: Format(ReportDate,@D2). The @D2 is the command for the date format we want. Using 'General' & nl & 'Fund' & nl & Format(ReportDate,@D2) as our column title expression gives us:

The date that you see is also controlled by the column, which, in this case is defined as "Current Period", so the report date will be the period ending date, even if the report is run on February 10th.

If you wanted to just show the year portion of the date, the YEAR function may be used. The expression 'General' & nl & 'Fund' & nl & Year(ReportDate) would give us:

General
Fund
2007

If the column is showing last year's data, the expression 'General' & nl & 'Fund' & nl & Year(ReportDate) - 1 will give us the previous year:

General
Fund
2006

For much more on expressions that may be used in column titles, open Help and go to Advanced Features. There you will Character Functions (under Character Expressions) that are frequently used in column titles. In addition you will find reference to useful related topics (such as Result Pictures under Numeric Expressions) that will help you create your own expressions.

Did You Know...?

Fixed Assets. When setting up Fixed Assets, you have the option of automatically numbering assets as they are acquired, automatically assigning an asset tag number (used for reporting purposes), and automatically assigning a component number (components are portions of an assets). Each number can have its own format. For example, an asset tag could be comprised of six characters, with an alpha character first, followed by 5 numbers. This helps promote consistency in identification.

Human Resources. This component becomes part of Payroll upon its installation and allows you to attach any kind of information you wish to your employees. When you are in the Update Employees window, select the H/R tab. Once setup, you will be able to add such information as what languages the employee speaks, if they have been issued a cell phone, what their e-mail address is, etc.

Inventory. Inventory items may have a preferred vendor attached to them. You may also include a negotiated unit price for the items. When an item is purchased from the preferred vendor, the unit price automatically appears on the purchase order. Once you are setup, you don't have to look up pricing for any of your items.