FormMaker Tutorial

This tutorial will explain how to design a very simple report, then gradually add new features to the same report to show how to create reports. We'll use some of the fields from the PREmp, or Employee Record File.

FIELD NAME	DESCRIPTION
PRE:LastName	Last name
PRE:FirstName	First name
PRE:Address1	Address Line 1
PRE:City	City
PRE:State	State
PRE:Zipcode	Zip Code
PRE:BirthDate	Birth Date
PRE:WTDFedGross	Week to date pay

When FormMaker is selected from the main screen, the following screen comes into view.

This is the FormMaker screen. It keeps track of all the reports that have been designed for this database program. The category names represent all of the custom FormMaker reports and forms available in the accounting system.

Reports that are created are saved as individual files with a file extension of *.RPT in the company data folder. The display above will show detailed information about the reports (date/time of creation, file size). Because FormMaker uses a tree style browse, the branch can easily be expanded or contracted to locate the desired report.

Beneath the browse area are various buttons used to Print the highlighted *.RPT report file, Expand/Contract the reports tree, Add a new report, Change the highlighted report, Delete the highlighted report and Close the FormMaker window.

To create a new report click the Add button. This will display the report formatter screen that is used to create or modify the actual report.

Click HERE for information about the screen layout of the Report Formatter and what the various buttons do.

There are two goals for any report that is created: properly extract information that is needed, and make the report visually pleasing. The first goal is very simple, once it is understood how FormMaker works.

It helps some users to think of a FormMaker as a big circle. Inside the circle is the information that has been put into the accounting system. Using a DETAIL BAND in the report will start at the top of that circle (at the first database record), then loop through all the inputted information until the last database record is reached, returning to the top of the circle once again. A DETAIL BAND is responsible for doing the "grunt work" of accessing the database, retrieving the database information and printing the database field in the area that is designated on the report.

Every report that is created will have a DETAIL BAND in it. While there are many different types of report "bands" that can be used, a DETAIL BAND is mandatory. Without it, the information in the accounting system will never be processed. Before adding a DETAIL band let's go ahead and define some basic information about the report.

Click the main menu option titled FILE, then select REPORT PROPERTIES from the dropdown menu. The following screen will come into view.

The Report Title is the description that will be displayed in the FormMaker window. Give the report a meaningful description that others can understand in this entry field.

The Record Filter entry field allows the user to access the Data Dictionary to assist in creating a filter condition.

Click on the second tab labeled Page layout. On this tab the default settings for the generated report can be overridden. The default settings are for 8.5x11 portrait output, with small margins on all 4 sides of the generated report page. The report orientation can also be changed to landscape by clicking on the Landscape checkbox. For now the default settings will be used.

To save a new report, select File:Save from the menu or click the Save button. The default location is in the subdirectory where the data files for this company are located. Enter the file name that has been chosen for this report.

Now that the filename is input the Save button is clicked to save the report file. Each report that is created will be saved to a DIFFERENT *.RPT file.

Now the Detail Band can be created for this sample report. To recap, the Detail Band performs the data retrieval from the database and inserts it in the desired location in the report. Sometimes it is a good idea to draw the sample report on a piece of paper to help visualize how the report needs to look when it is generated.

Now the work area inside the Report Formatter is completely blank, with only the top menu and toolbar buttons visible. Click on the BAND main menu option, then select NEW, then select DETAIL from the options. Notice that the screen now has a rectangular box with red boxes at each corner and the top, bottom and middle. A ruler bar is displayed to the left of the rectangular box also. The red rectangles allow the user to resize this report band.

Now database fields can be added to the Detail Band. Database fields will be placed inside of FRAMES. A FRAME tells FormMaker how and what to print in the location where the FRAME is located. There are two ways of adding a FRAME to the Detail Band. Select Frame from the main menu, then Create. An empty FRAME is automatically created inside the Detail Band at the top left corner. An easier way is to place the mouse in the area where the database field should be printed, then right click the mouse to display a popup menu; select the menu option titled New Frame. An empty frame will be created in the exact location where the mouse was inside the Detail Band.

The empty frame can be moved now or when the information that will be inside the frame has already been defined. To move the frame with the mouse you left click anywhere inside the frame, and while holding down the left mouse button, drag the rectangle to the desired location. The keyboard can also be used to move the frame by holding down the CTRL key, and use the arrow keys to move the frame. Here is what the screen looks like for now:

To add text to a frame, click inside the CONTENTS entry field in the Frame Properties box. As information is typed, it will be displayed inside the FRAME. If the length of the text is longer than the frame, it can easily be resized by clicking on the right middle red box, and while holding down the left mouse button drag the box to it's new size.

Click on the button with the flashlight inside it to display the Dictionary. When the Dictionary appears onscreen, click the tree expand box directly underneath the Processed Files description. A list of all the database fields is displayed onscreen to choose from:

To select a field, double click on the field name description. For this example the "Last Name " tree choice is double clicked. The dictionary window will disappear, and the FRAME box is now updated:

Notice that an equal sign (=) is displayed in front of the field name (PRE:LastName). Also notice that the inside of the frame is displaying dollar signs ($). This helps the user properly resize the frame size for this database field.

At this point the steps for adding a new frame and populating the frame are repeated for each of the remaining fields in the database. After moving the mouse to the desired location inside the Detail Band, right click the mouse and select New Frame, then select the desired database field. Drag the frame to be to the right of the last added frame. All frames will be located at the top of the Detail Band. Here is what the screen looks like with many Frames added:

The FormMaker features a very easy way of aligning frames in many different ways. Select all the frames to be aligned. Right click to display a popup menu. This menu will display various options to aligning multiple frames. The easiest choice is ALIGN TOP. After selecting it from the menu, all the frames are now aligned in a more pleasing way:

Save the report right now by clicking the Save File button located on the toolbar. It is a good idea to periodically save as a report is being changed. To view how the report will look once it is printed, exit FormMaker. The FormMaker screen is different now:

The newly created report is displayed onscreen, with the filename / date / time / size information. To print the report click the PRINT button. The report will be sent to the screen (print preview). Here is an example output.

The important thing to notice here is the layout of the information to detect problems. Using the above information, these problems can be seen:

There is too much space between each line of information
The street address field is getting chopped off (ex: 12345 South P)
The birth date field is missing since insufficient space existed for it on the Detail Band
The name fields could be formatted a lot more attractively
There is no sorting being done on the database

One of FormMaker's most attractive features is its flexibility. Anything can be changed on the reports. The problems identified above with the first report are very easily fixed. We will be fixing these things in the next tutorial step.

Now it is time to clean up the report and make it more presentable. FRAMES can be moved with either the keyboard OR the mouse. For moving a FRAME in very small increments, the keyboard will be more precise and easier. Use the mouse to select multiple FRAMES (hold down the CTRL key, then left click on each desired frame to select them individually), then use the keyboard to move the selected FRAMES (while holding down the CTRL key, use the arrow keys to move the selected frame(s) up/down/left/right) to the desired location. Also note that the TAB key on the keyboard will select the next FRAME automatically. Pressing Shift-tab will select the previous FRAME.

Reopen the report that was just created in FormMaker by highlighting it inside the screen and clicking the CHANGE button. The Report Formatter screen will come into view once again, with everything just as we saved it.

The first thing to change is the default font. Some fonts used in Windows are more attractive than others. The Verdana font was designed specifically for readability, and makes an excellent choice for printed reports. The font can be changed inside ALL frames in the report by clicking FILE, then select the FONT menu option. The standard Windows font selection screen will come into view, allowing the user to select the font, style and size. Once a font has been selected, click the OK button. All of the FRAMES in the Detail Band will change their font. Be aware that some fonts print larger than other fonts.

Located on the toolbar is a button with a blue "A" inside it. This font button will change the font ONLY for the FRAME that is currently selected (i.e. has the red resizing boxes on the corners). It will NOT change the font used for the entire report.

Click the FRAME that contains the LAST NAME field from the database To display the text as bolded, click the desired frame, click the blue "A" button, select Bold then click OK. The contents of that frame will now be boldfaced. The FRAME may need to be resized because bold type requires more space.

Each FRAME in a report can print in a different font, they can even print in different colors. Try not to use too many different types of fonts in the report, as it can become a distraction and make reading it more difficult. Stick with common fonts that are loaded on most computers, otherwise Windows will substitute the font chosen with a font that could cause printing problems (such as a column bleeding into the next column, improper spacing, etc).

To fix the report problem of a large gap between the printed lines the Detail Band needs to be resized. Left click once on the Detail Band border line to make the resize boxes appear. Select the middle resize box on the bottom of the Detail Band, and drag it upwards until it is flush with the bottom of the FRAMES. The FormMaker will not allow the user to resize the Detail Band smaller than required, so it can only be reduced to a certain point.

Save the report (click the floppy disc on the toolbar), then exit the report (FILE -> EXIT), then test print the report to see how it looks now. Here is what a sample looks like:

Notice that the last name is BOLD, the huge amounts of blank space after each line are much smaller now, and the information on each line is aligned horizontally. The amount of blank space that is printed below a line was also reduced.

This time around we are going to add a Page Header, which will print the title to the new report, and also print column header information at the top of each page. We've already reopened the report into the Report Formatter screen.

A Page Header is another REPORT BAND that is generated at the top of each page. FormMaker keeps track of how much data can be printed on a page and when it's time for the printer to eject the page after printing as much information as it can, the Page Header is created, then printed at the top of the next page. Each page of the report will have the Page Header information printed at the top of each page.

Select BAND from the pulldown menu, then NEW, then PAGE HEADER. A new empty band named Page Header is inserted ABOVE the DETAIL band automatically.

The first order of business is thinking of a title that will be easily understood. For this report title we will use "Employee Database - Sample Company Inc.". Any and all text that is added to a report is inserted into a FRAME, so a new frame must be created inside the new Page Header frame. Right click inside the Page Header band and an empty frame appears. Most reports have a centered title, so here is an easy way of ensuring that the report title is always centered onscreen:

Place the mouse on the right middle resize box on the empty frame
Resize the box until you reach the right side of the report
Make sure the gaps are the same for the left and right sides of the frame
Type the title inside the FRAME characteristics box
Click the center align button on the toolbar
Click the blue "A" for font type. Select ARIAL, BOLD, 12, then OK

The title that will always be properly centered, and slightly larger than the rest of the report to make it stand out.

Now we are going to create headers for each of the fields that are printed. To ensure they are printed at the top of each page, they also need to be located in the Page Header band. Create a new frame inside the Page Header band directly above the leftmost column, then type in the header name (in this case, LAST NAME).

After adding all the frames and their header text, the frames should be resized to be as small as possible. After doing that, align the frames so they are even. Here is an example:

We can see from the above screen that the Page Header band needs to be reduced in height - otherwise the printed output will have a huge gap between the column headers and the database information printed in the Detail Band. Resize the Page Header band (left click-drag the center red resize box until it can't be reduced in height anymore). Here is what it will look like:

The column headers would look better if they were BOLD and UNDERLINED, change each FRAME for the headers. Be sure that no text is lost in the change, resize the FRAME if necessary.

Left align the column headers with the data information that is printed beneath it. An easy way is to first click the header frame, then Ctrl-Leftclick the field frame underneath it inside the Detail Band. Once the two frames are selected click the right mouse button, then select Align Left from the popup menu. Repeat this process for each column until they are left aligned. Remember that two selected objects can be moved in case they overlap other information by holding down the CTRL key, then tap the left or right arrow button to move the frames sideways. The final result now looks like this:

Once satisfied with the results, save the report then print a sample report to the screen to see how it looks.

Remember, FormMaker will print the information it is told to. The user controls how the reports look, so don't be afraid to experiment. Reports will NEVER change information in the accounting system files.

Sorting information on a report is a crucial element that is often overlooked. It is almost impossible to locate specific data without sorting the output in a report. FormMaker allows the user to sort information in whatever way is necessary. Reopen the report so it is displayed in the Report Formatter screen once again.

The FormMaker engine uses a Sorting Band to sort data the way that the user tells it to. To add a Sort Band to a report to enable sorting on a database field (in this example, the last name), select BAND, then NEW, then SORTING BAND. Notice that the cursor changed to a pointed hand. Move the hand until the tip of the finger is on top of the word DETAIL in the detail band, then click the mouse. The report will now look like this:

Notice that above the DETAIL band is a new entry named Sorting Band 1, and a dialogue box titled Sort Expression is displayed below it; FormMaker is asking how the database information should be sorted. Use the dictionary to select the database field as the primary sort. Click the button with the flashlight to display the dictionary onscreen:

We wanted this report to be sorted on the Last Name field of the database, so we expand the "tree" for "Names Database" to display all the field names for the database. Double click on the Last Name field to enter the field name into the entry field for the Sort Expression box:

Notice that the field name is now entered into the entry field; click the SAVE button. The information displayed in the Sorting Band detail is now changed to this:

This allows the user to know how this Sorting Band will be sorting information. Go ahead and test the sort by saving the report, then exit the Report Formatter screen and print a sample report. Here is what the report looks like now:

FormMaker allows the user to manipulate database information before it is generated to a report page Our report so far has two columns that break apart the name (last name, first name). While this is acceptable, we can make it look much better by formatting the database information before it is printed. Instead of printing information inside 2 FRAMES we will instead print a formatted name inside of 1 FRAME. Not only will it look better, but may also take up less space on the report.

Go ahead and open up the report again; the Report Formatter screen should be displayed. Delete the frame for First Name, and also delete the header information for that column (to delete a FRAME you right click it, then select DELETE from the popup menu).

Click the Last Name FRAME inside the DETAIL band; the properties box for that frame should look like this:

On the old report there is a LOT of blank space after the last name. We need a way to clip the empty spaces out of the database field. This is achieved by digging deeper into the FormMaker's bag of tricks, and use a built in function that CLIPs off blank space at the end of a database field. The function called CLIP() does exactly this for us, and here is how to use it.

Go ahead and delete the Contents information inside the Properties box for the frame, making it completely blank. Click the button with the flashlight (this is the Dictionary access button). This will display the DICTIONARY onscreen. Not only does the DICTIONARY maintain the database field information for the accounting system, but is also a centralized place for accessing and using the different built in functions that FormMaker has to offer. This screen shows where the CLIP() function is located inside the DICTIONARY:

Double click the CLIP(string) entry, which inserts it into the FRAME properties:

Obviously we aren't manipulating a database field called (string); this is simply saying that the CLIP() function is used against text strings. This function can not be used on numeric fields. Remove the word "string" from inside the '()' symbols, thus leaving you with = CLIP() inside the contents entry field. Click once inside the '()' characters, then click the DICTIONARY button again to select the Last Name field from the dictionary. Double click the Last Name field, which changes the above screen to look like this:

Now our frame is going to print the contents of the Last Name field from the database, BUT it will remove all the blank spaces from the end of the field!

We've solved one piece of the puzzle; now we need to combine the last name field with a comma character, and a blank space after the comma. To combine two or more strings together we use the Ampersand (&) character. Any text that we want to combine with a database field has to be enclosed in single quotes. Here is what the Contents entry field looks like now:

We have the last name (with all blank spaces removed), and the comma character added directly after it, with a blank space after the comma. Now it's time to add the First Name field. Remember that the first name field can also have blank spaces at the end, so we need to CLIP() that field to. We are combining the First Name field to the existing information, so we also need the Ampersand character to join them together. Type in this information:

Click inside the '()' symbols where the First Name field will be inserted, then click the DICTIONARY button to display the database fields. Here is our revised screen:

The PICTURE for this FRAME needs to be changed as well. Originally it was as long as the Last Name field allowed, which was 20 places. We want to extend this to be as long as all the fields and blank spaces require for the 3 fields. To see how long the database fields are, use the DICTIONARY:

The number after the @S is the length of the field. Based on the above information, we need to extend the frame to 20 + 20 = 40 spaces. It is doubtful that both the last name and first name would fully occupy 20 spaces apiece, so it is up to the user if the picture should be shorter.

Click on the tab titled Picture. The picture is @S20 currently. Type in @S34. If some names are being truncated, increase the length of the picture to accommodate the longer length.

Resize the last name FRAME to accommodate the increased length. Don't forget to change the column header, since it now is the full name instead of just the last name. Once the FRAMES are properly aligned, save the report, then run it to see how it looks. Here is what the sample report looks like now:

In this section we'll describe how to better format other FRAMES including the address fields.

To combine the City/State/Zip code, information we'll need to eliminate the blank spaces after two of the fields (city and state). Insert a comma between the City and State fields, and a blank space before the zip code is printed. This will change the output from: TUCSON AZ 85730

We'll also have to play with the column headers a bit, since there will no longer be 3 individual columns of information.

•Select the City/State/Zip Code FRAMES together and drag them downward to provide more room

•This will insert '=CLIP(string)' inside the Contents entry field. Delete the word 'string' from inside the '()' characters or highlight the word 'string'. Click on the Dictionary button again

•Select the field for City from the Dictionary. The Contents entry field now shows =CLIP(PRE:City)

•Now we need to add a comma character after the CLIPped City field. Change the Contents entry field to show =CLIP(PRE:City) & ', ' &

•Click the Dictionary button again and select the CLIP() function again, then remove the word 'string' from inside the '()' characters.

•Now the Contents entry field shows =CLIP( PRE:city ) & ', ' & CLIP(PRE:state) &

•We need to add a blank space after the State field is printed. After the last Ampersand (&) character add a single space surrounded by quote characters: ' '

•Click on the Dictionary button and select the ZipCode field. This completes cleaning up the City/State/Zipcode information, and our FRAME Contents look like this:

We need to change the Picture to accommodate the combined length of the three fields, plus the comma and space characters between the fields. If the length of the fields is unknown, click on the Dictionary button to retrieve their lengths. For this example we have 30+2+2+1+10 = 45. This does not mean that all 45 spaces will be used, so fewer spaces can be used.

Click the Picture tab on the FRAME properties box. The picture entry field is completely blank:

Click on the "face" icon to display the Picture formatter. See more on Pictures. A popup window of picture types is shown onscreen:

The FRAME that we created for the 2 combined fields contains a String. Choose the String radio button and click NEXT. FormMaker prompts for the maximum length of this string:

The default value is 20 positions. We'll change this to 45, then click OK. Now our Picture entry field in the Frame properties box looks like this:

Notice that the FRAME contents in the DETAIL band have changed to dollar ($) signs. Resize the new FRAME to the correct length, then align it to be lined up horizontally with the other FRAMES in the DETAIL band.

Now we can delete the headers for Zip Code and State (right click the FRAME, then select DELETE from the popup menu to remove that header FRAME). Now we have one header FRAME left for City. We can modify that Header to reflect a more fitting description. We'll need to resize that header FRAME after changing the text, then left align it with the associated FRAME in the DETAIL band so everything lines up.

Delete the original City/State/Zip Code FRAMES that we moved downward inside the DETAIL band, since they are no longer needed.

Now is a good time to discuss the two types of text entry boxes available in FormMaker. When a frame is selected and the frame properties window is opened, there is a pull-down selection box directly below the Contents entry field. The two selections we need to discuss are "Customary Frame" and "Mail Merge"

The Customary Frame allows for combinations of field data and hand-entered text. Below are some examples of how data can be entered in customary frame.

Entered text	Report
=PRE:LastName	Hansen
=CLIP(PRE:LastName) & ', ' & PRE:FirstName	Hansen, Robert
='Employee name is ' & CLIP(PRE:FirstName) & ' ' & PRE:LastName	Employee name is Robert Hansen

There are some limitations on this type of frame. First, large amounts of text will not fit well in this type of box. For example, if you want to include a large paragraph of text with the employee name embedded somewhere in the middle, this style of text frame would not be appropriate.

A Mail Merge Frame acts more like a the mail merge capabilities found in word processors. Here is an example of how data can be entered in a mail merge frame.

Entered Text	Report
This is a large paragraph that outlines the attributes of a specific employee named «PRE:FirstName» «PRE:LastName». He currently lives in «PRE:City», «PRE:State» and has been with the company since «PRE:HireDate».	This is a large paragraph that outlines the attributes of a specific employee named Robert Hansen. He currently lives in Portland, OR and has been with the company since 1/14/1989.

As you can see, the mail merge field is much more flexible for larger quantities of text.

Now that our sample report is looking pretty good, let's explore some tricks that can be used for reports. A common report obstacle is printing information that is sorted more than one time. In this module we are going to change the report from printing in Last Name sequence to printing a report sorted first on the State, then the City, then the Last Name database fields.

Back on PAGE 7 of the tutorial we sorted the report on the Last Name field of the report by creating a Sorting Band. We will still use a Sorting Band to perform the sorting, but we'll be using more of them to perform the sorting that we want for the report.

It is important to figure out the correct sequence of events BEFORE adding Report Sorting bands to a report. Take a good look at the database information and plan on paper how the report to be sorted. This will save time in the long run, and prevent frustration later on. For the example above, we want the report sorted on three different database fields. The most important sort is on the STATE field. Within the STATE information we want it to be sorted by CITY. Within the CITY we want the names sorted alphabetically by LAST NAME.

The first order of business for this example is deleting the existing Sorting Band that was created in our sample report. To delete a Sorting Band, right click on the words Sorting Band, then select Delete Band from the popup menu.

NOTE- If a report that already has a Sorting Band is being modified, and associated Header and Footer bands have already been created for this Sorting Band, DO NOT delete the band. To change the existing sort for a Sorting Band, right click once on that Sorting Band, select PROPERTIES from the popup menu, then modify the field to sort by.

NOTE- We will be adding Sorting Bands in the same sequence as their order of importance. The primary sort on the STATE field is first, then CITY, then LASTNAME. From the menu select BAND, then NEW, then SORTING BAND. The mouse cursor changes to the hand cursor; place it inside the DETAIL band and left click the mouse . Inside the Sort Expression box, click the Dictionary button, then select the STATE field from the Dictionary, then click SAVE.

Repeat the above steps two more times for the CITY, then the LAST NAME database fields, adding a Sorting Band for each field. The final result will look like this:

Continue adding more Sorting Bands if needed. For example, if the report needed to be sorted by Last Name, then the First Name, a fourth Sorting Band could be added with the database field NAM:firstname selected.

It may take some trial and error to get the information sorted exactly as desired. Remember that no damage can be done to the database file(s) using FormMaker, so experiment as much as necessary.

An important point to remember about multiple sorts is that they will slow down the report generation, as more work has to be done to get the data to appear as desired. With small database file(s), there won't be much (if any) of a delay. The delays will occur mostly with larger database file(s).

A database FILTER is a means of extracting only a portion of information that is wanted. For example, if the names of people living in France are desired in an address book why print a list with EVERYONE's names? The list could be huge, and take forever to read through and extract only the information wanted.

A database FILTER can be very simple (ex: Color = 'blue'), or complex (ex: Race = 'Caucasian' AND Income > 25000 AND (Status = 'Single' OR Status = 'Divorced'), it depends on what the situation requires. A FILTER is saved inside the report, so it does not need to be created each time that the report is printed. There are some basic concepts that need to be understand before creating a filter; some knowledge of some of the FormMaker functions will also prove handy for creating filters.

A simple definition of a FILTER could be described as comparing database information against certain required values. Some simple examples could resemble:

The database field starts the filter
An operator symbol that describes what is being done with the database field
A text string or number that the database field is being compared against

The database field name can be retrieved from the Dictionary. The same goes for the different operators that can used. The third part of the filter is up to the user. Remember when comparing database information against textual information, that it must be a string (enclosed in quote symbols); if comparing against a number, don't add the quote symbols.

Report FILTERS are created by selecting FILE from the pulldown menu; select Report Properties from the menu to display the Report Properties screen:

On the bottom of the screen is an entry field for the Record Filter; to the right of the entry field is the Dictionary button. The record filter is entered in this location, and once the SAVE button is clicked the FILTER is saved with the report.

Let's start with a simple filter to show how to use the Dictionary to build the filter for us:

•With the report open, and the Report Properties window visible (as shown in the above screen capture,) click on the Dictionary button to display the list of database fields. We are going to select the CITY field in the dictionary, which inserts it into the entry field above. This completes step one of the filter building process.

•We now need to decide how we are going to compare the City field in the database. For this example we want to print out database entries for employees living in Portland. To compare information we use an OPERATOR, which tells the computer how to manipulate information. After inserting a blank space after the field name we click on the Dictionary button again, and choose the entry titled "Equal To" from the long list of Standard Operators. This inserts an equal sign (=) after the field name, which completes step 2 of the filter.

•Now we have to manually type in how we are going to compare the State database field. We wanted to print only those entries for Florida. We will be comparing "Portland" against the database field

In theory this filter is 100% correct; structurally it IS correct, but there exists a problem. The problem is how the State database field is stored in the database. When the user inputs the City information it is capitalized as "Portland", with the "P" being uppercase and the rest of the word being lowercase. The filter shown above is all uppercase. Because computers do EXACTLY what they are told to do, the result will be that NO database entries will be found, even though there are many entries in this database that have people living in Portland!

The filter statement shown above is structurally correct: we have a database field, an operator and the text string for what it is being compared against. The problem is that we are telling the computer to make sure that the City field EXACTLY matches "PORTLAND". The filter must tell the computer to check for the value of "Portland" which is actually contained in the database.

There are several ways to fix this problem. The easiest way of ensuring that a filter dealing with text strings will always work is to use the upper() function in FormMaker to convert the database information to uppercase temporarily, which will always accurately compare the database information against the text string. Armed with this knowledge we change the filter to look like this:

As mentioned before, there is another way of changing the report filter to make sure that the data printed is correct. We would change the filter to perform two comparisons of the City database field against "PORTLAND" AND "Portland". This works by piecing together two different filters by using a CONDITIONAL OPERATOR out of the FormMaker's dictionary. Here is what the revised filter would look like:

The safest route is to check for all the possible ways that information could be typed in.

As mentioned before, the Dictionary (or this help file) can be used to see what the different functions and operators are that can be used in creating a filter for reports. Some functions will be used more often than others; here is a short list of them:

Clip() - remove blank space from the end of text
Upper() - convert text to all uppercase
Lower() - convert text to all lowercase
Sub() - retrieve a portion of a text string for comparison
Instring() - check for text inside a larger piece of text

We already covered how to compare a database field containing text against a text string. However, there are some more tricks that can spruce up reports. For example, let's assume that a report of all employees with a last name starting with the letter "B" needs to be printed. Use the sub() function to test the value of the first letter of the last name. To be safe, use the upper() function to make sure that the letter was uppercase (it also makes the filter simpler, and it runs a bit faster too). Here is what that filter would look like:

Notice that we used two functions at the same time, one inside the other with the field in the middle.

Here's another good example. A report needs to be printed for any references to SONY brand equipment. Remember that people will not always type in SONY in capital letters. They may have spelled it Sony or sony, so we have to convert the memo field first to UPPER case, then check for the word SONY. Here is what the filter would look like:

Again, we are using a function inside a function (the function UPPER() inside of the function INSTRING())..

A common reporting need is printed graphic image(s) on a report. Most commonly this is used by businesses for company logos, or for customized letterhead for memos. It is also a nice way of sprucing up drab reports into a more eye-catching design. FormMaker makes it very easy to add graphic image file(s) to a report.

Image files are normally placed in the Report Header band, but they can be placed in other Report Bands as desired.

After opening the report into the Report Formatter screen, create a new frame in the desired location. Right click on the new frame to display the Frame Properties window. Underneath the Contents entry field is a dropdown list box. Go ahead and select "Image from file," then click the Dictionary button (looks like a flashlight):

Because the 'Contents - image file name' is checked the Dictionary button works like a standard Windows file selection box instead of displaying the Dictionary. From the popup file selection box, choose the desired graphic image file of these formats:

* NOTE * If the selected image file does not have the path defined, FormMaker will search for the file in this order:

The current folder
The folder where *.Rpt report files are saved
All directories from the PATH environment variable defined in the Autoexec.Bat file

Remember that the placement buttons can be used to define the placement of the image inside the frame (top / middle / bottom aligned). A visible frame can be placed around the image file (on one or more sides). Change the coloring used in the frame as desired.

Knowing how many database records have been selected for printing can be a useful addition to many reports. FormMaker makes it very easy to print this information:

•Create a Report Footer band (from the main menu select BAND, then NEW, then REPORT FOOTER). An empty Report Footer band is made available inside the Report Formatter screen.

•Create an empty frame in the new Report Footer band in the desired location; with the new frame selected click the Dictionary button on the Frame Properties box.

•From the Dictionary popup tree menu select the entry for OTHERS; under this entry open up "Built In Variables", then select "Record Counter". This will insert this information into the Frame Contents: =Record_Counter.

•Manually type in the (=Record_Counter) information without having to go through the Dictionary.

Make this information print a little fancier by adding a frame to the left of where the record counter is printed:

As shown above two frames have been created. The first has the text string "Records Selected" inserted. The second frame contains the built in variable for the Record Counter.

There are times when you want to force a page break in a report to make it more visually appealing, or when you need to physically separate different sections of a report for mailing or workload requirements. The FormMaker makes this very easy and straightforward to accomplish.

The first step is adding a Sorting Band to the report. The first sort (or primary sort) should be on the database field with which the page break will be performed. For example, to separate a report based on the state field, where the start of a new state abbreviation code starts, a new Sorting Band would be created on the state database field. Additional Sorting Bands can be created if desired, but the FIRST Sorting Band needs to be created using the database field that will be be broken into sections on the printed report.

After adding the Sorting Band either a Group Header or Group Footer detail band will be created in the report. Either band will work as desired; the difference is where contents of that report band are printed. A Group Header band is printed at the top of the page; a Group Footer band is printed at the bottom of the report page.

To create a Group Header or Group Footer band, select BAND from the main menu, then NEW, then the desired Group band. The mouse cursor will change to the hand icon. Place the hand icon directly on the first Sorting Band and click the mouse. A new band (Group Header or Group Footer) is created inside the Report Formatter screen.

Right click on the Group report band that is newly created, then select the Band Properties popup menu entry. A popup dialogue window appears with various options available:

FormMaker offers a great amount of control on how to print information in the new Group header/footer report band. By default no setup checkboxes are selected

To see how different settings affect the final output, let's start with a Group Footer band:

As shown above, a frame was created with the text of "City: " entered inside it, with the frame borders enabled on all 4 sides of the frame. This will create a nice box effect around the information when printed. Another frame was created that contains the city database field with no frame border. This frame was then moved INSIDE the first frame, which gives an illusion of one large box surrounding both frames.

* NOTE * Another way of creating a nice box effect around two or more frames that are side by side is done by setting the border to be displayed (use the border buttons that are displayed in the toolbar); for the leftmost frame don't display the right side border; for the second frame don't display the left side border. Alternatively, you could create just one bordered frame with the following contents: = 'City: ' & CLIP(PRE:City).

* NOTE * Using the default report (used throughout this tutorial) and the above Group Footer information, here is how the report output would change based on different settings:

Print Selection, if expression evaluates TRUE: Print only information contained in the new report band when a specific condition evaluates to TRUE. Use the Dictionary to help define the database condition that evaluates to TRUE for this action to occur.

Memo Field: Enter some text you want to show up below the band text on the report.

Explicit Page Overflow Before Band Printing: Print the Group Footer band when the selected database field data changes at the beginning of the next group of information (i.e. ALWAYS print this band when the selected field data changes at the BEGINNING of the next group of information).

Explicit Page Overflow After Band Printing: Print the Group Footer band when the selected database field data changes at the end of that group of information (i.e. ALWAYS print this band when the selected field data changes at the END of that group of information). The next page that prints will contain new database field information.

Print This Band At The Same Page With Prior: This attribute specifies that the Detail, or Group Header/Group Footer structure (contained within a Sorting Band) is ALWAYS printed on the same page as the band immediately preceding it in the Report Formatter screen. This ensures that the structure is never printed on a page by itself, thus eliminating "orphan" bands. An "orphan" band in this case is defined as a Group Footer, or last detail item in a related group of items, that are printed on the following page separated from the rest of it's related items.

Print This Band At The Same Page With Next: This attribute specifies that the Detail, or Group Header/Group Footer structure (contained within a Sorting Band) is ALWAYS printed on the same page as the band immediately following it in the Report Formatter screen. This ensures that the structure is never printed on a page by itself, thus eliminating "orphan" bands. An "orphan" band in this case is defined as a Group Header, or last detail item in a related group of items, that are printed on the previous page separated from the rest of it's related items.

Print After This Band The Text Field: This attribute is used whenever a database field that is a large MEMO type database field, which are commonly used to contain large amounts of text (such as notes) is to be printed. A normal Frame will be unable to print a memo database field within it's defined space. By checking this feature the selected database field will be printed after the report band in its entirety without truncation. If there is insufficient space to print all of the information, the remainder of the field information will continue printing on the next page.

A common addition to many reports (especially financial reports), is printing total fields (such as total sales) or the number of matching database entries for a group of information. This information is normally placed within a Group Footer report band, so it prints at the end of that related group of database information.

For this example let's assume that we want to print the total number of database record(s) for each state. This information will print at the last page of that group of information as a summary; here's how to do it:

•Create a Sorting Band on the CITY database field (if one doesn't already exist)

•Create a Group Footer report band (if one doesn't already exist) on the STATE database field

•Underneath the 'User Variables' section of the dictionary will be an entry labeled 'Gather Totals'. Each of the Group Header / Group Footer report bands that have been defined will have an entry here titled 'Group Totals'. Highlight the entry for the CITY database field:

•A new dialogue box appears to create a new variable based on the selected database field:

As displayed above, there are many different ways of manipulating the information to be stored in the new variable. Here is a breakdown of each item:

Assign Name: Enter the desired name of the variable here. This name MUST be unique. DO NOT use the same name as the database field. An easy way of keeping the name unique is by using a prefix, such as LOC. For example, for the database field PRE:City a good variable name would be LOC:City.
Field/expression to total: Use the Dictionary button to help select the database field(s) that are to manipulated here.
Picture: Define how the variable will be printed on the report by using the Picture dialogue box.
Total Type: Define how the selected database field(s) will be manipulated. From the droplist, select Sum, Average, Count, Minimum, Maximum or Evaluate.
Evaluate On: Define when the variable will be modified; from the droplist select 'At Each Record Retrieve', 'At The End Of The Group', 'At The End Of The Report' or 'At The End Of Every Page'

And here is the modified Group Footer report band to make use of the new variable information:

Printing dollar totals for a group of database information is very common in financial reports (i.e. What is the total year to date pay for all employees in a city?). The concepts described in the previous tutorial page are practically the same, with some slight changes to achieve the desired result:

•Create the Sorting Band on the desired database field (in this case, the CITY database field)

•Create a new variable to contain the dollar total value based on the database field containing the dollar sales (for this example it is the database field PRE:YTDFedGross):

* NOTE * Remember that the variable LOC:CityTotal will be containing the SUM of the PRE:YTDFedGross database field. This means that the variable will most likely be larger (potentially MUCH larger) than the largest database entry. When assigning the Picture for the variable, MAKE SURE to increase its length to accommodate for this situation, otherwise, data contained in the variable may be incorrect due to truncation.

* NOTE * After saving the new variable information, the Dictionary screen will be updated:

The topics covered in the last two sections can also help print a Grand Total on the last page of the report; just a matter of changing some settings for the variable.

•Add a new Report Band of the type Report Footer (click on BAND, then NEW, then Report Footer)

•Create a new frame inside this new report band; click on the Dictionary button

•Inside the Dictionary dialogue window left click the Grand Totals item; this is located under the Gather Totals section of the User Variables section.

•Assign a UNIQUE name for this new variable; select the database field containing the dollar amount that you want added together. Change the picture length to a longer length than what the selected database field is already set to, and add the '$' character to the string. The "Total Type" will be "sum", and it will be evaluated 'At each record retrieve':

•Create a variable in the Dictionary for all 50 state abbreviation codes in the Grand Totals section of the dictionary. The variable would make use of 3 internal functions of the FormMaker: Clip(), Upper() and Choose(). The first two functions are used to convert the State database field into a consistent format for testing with the Choose() function. The Choose() function is used to compare the current database record against the known state abbreviation code. If it matches the database field containing the total sales for this customer, the state is added to the variable (to keep a running total); otherwise it is skipped.

•In the Report Footer report band a new frame is added. The Dictionary button is clicked with the empty frame being highlighted. Under the Grand Totals section of the User Variables a new variable is created by clicking the '+' button.

•For this sample report, a new variable is being created for state abbreviation code "FL" (all customers living in Florida). Here is what the variable information looks like:

•Another trick being used is using the Center attribute for the frame contents for both the text string "Florida" and the Loc:Florida variable. By using the border attributes a table effect is achieved, which makes the summary page look very attractive.

•The pattern of adding a new frame for the state abbreviation code, and another frame for a new variable for that state abbreviation code is repeated. You can elect to stack them any way you desire; the choice is yours! Here is how the report band looks at the end:

As shown above, 4 different states are being tracked, with the total dollar sales printed below. Here is the sample output: