Tips and Tricks

ZonePro is extremely flexible and there are usually creative ways to handle any special needs a customer has. Listed below are some of the tricks you may find useful. Just click on the question to view the answer. (You can click on the question again to hide the answer.)


REPORTING TRICKS

How to use a prefix to subgroup permit types

Sometimes it is useful to be able to report on groups of permit types at a time. In the Violations database there is a Group field that serves this very function, but the same effect can be achieved using prefixes. This trick was developed to solve a problem for the City of Oakwood. They have certain types of zoning permits that they handle themselves and others that they pass on to the city of Kettering. They needed a way to report on all the permits that pertained to one group or the other. The solution was to put the prefix "OAK-" in front of all the permit types that Oakwood handled, and to put the prefix "KET-" in front of all the permit types that were handled by Kettering. This was done in the Zoning Permit Types section of Maintenance. Two dummy zoning codes were also created that just had the words "OAK-" and "KET-". These two dummy codes were then assigned to two dummy permits. This is just to get them into the zoning permit database so they later appear as choices in the reporting screen. Now, whenever they go to the Zoning Report screen they can use the Permit Type option and select either "OAK-" or "KET-" to see a complete list of all the permit types that were recorded for each respective group.

A simpler version of this technique can be used by most communities. For example, suppose you keep track of in ground pools and above ground pools as separate improvement types, but you sometimes wish to see a report of all pool permits issued. The solution is to set up of your improvement types in the Maintenance Fee Schedule section as "POOL - IN GROUND" and "POOL - ABOVE GROUND". Then create an extra dummy improvement type that is just "POOL". Make sure you create one dummy permit record that has just "POOL" as its permit type and then proceed to issue your other pool permits normally. Now whenever you want to create pool reports you can go to the Zoning Report screen and use the Permit Type option. You will then have the option to report on just the "POOL - ABOVE GROUND" permits or the "POOL - IN GROUND" permits or both at once by selecting the "POOL" improvement type

You can avoid the use of dummy permit types and dummy records by using the Advanced Filter Option in the relevant report screen. For example, you could type TYPEINSP = 'POOL' (ZP32) or TYPEINSP LIKE 'POOL%' (ZPSQL) to get all the pool permits for both "in ground" and "above ground.

How to get a list of expired unused permits

If you want to find out how many zoning or building permits you have that are expired but the project was never completed, you can do so with the Advanced Filter option. From the permit report screen go to the Advanced Filter screen and enter a condition that reads as follows:

(ZP32)  exp_date <= DATE() .AND. EMPTY(occ_date)

(ZPSQL) exp_date <= GETDATE() AND occ_date IS NULL

This filter will locate every permit with an Expiration Date less than the current date and that has an empty Occupy Date. Of course this filter only work if you are properly maintaining the Expire Date and Ocuupy Date fields. (For another take on handling Expire Dates see the Tutorial page.)

How to create a report showing outstanding permits

A customer asked if he could create a report that would list outstanding building permits. The key was to pull permits that did not have an occupy date. To do so, go to the Building Report Screen, choose Occupy Date as your date choice and then delete the starting and ending date ranges so they are blank. Now when you run this report, only permits with an empty Occupy Date will be selected. This technique should work for any report screen and any date criteria. For example, if you want to find out if there were any zoning permits that were never paid for you could go to the Zoning Report Screen, select Date Paid as your date choice and then clear out the starting and ending date ranges.

How to select records from a single zip code

If you want to create a report or mailing labels that only include property records from a single zip code you can do it using the Advanced Filter option. The trick in ZP32 is to use the $ function that searches an entire field for a match of the value you supply. For example, the following filter would only pull property records with the zip code "41011" somewhere in the city, state, zip field:

"41011" $ property.address2

In ZPSQL you would use the % wildcard character to accomplish the same thing. Your Advanced Filter would look like this:

property.address2 LIKE '%41011%'

You can substitute any zip code number into this filter.

PROPERTY SCREEN TRICKS

How to search for a first name with a last name in the Locate Screen

If you are trying to locate an owner's name in the Property database and he has a common last name, it is helpful to be able to use the first name too to narrow down the number of hits. For example, you may be looking for Roger Smith but do not want to wade through a hundred Smiths to find him. The trick is to enter the full last name followed by the first couple of letters of the first name into the owner's name search field of the Locate Screen. In ZP32 you put a space between the last name and the first name letters like so: "SMITH RO". In ZPSQL you type in both parts without a space like so: "SMITHRO". This search technique will make quick work out of finding Roger Smith. The same technique works for the occupant field.

How to quickly handle new home additions to the Property database

For communities with new subdivisions going in all the time, dealing with new home permits can be tricky. The problem is that it is often difficult to locate the property record where a new home is being built because the street address is too new. Unless you have the parcel number it is time consuming to figure out which vacant parcel on a given street belongs to the new home. The quickest way to handle these cases is to simply add a new parcel record, enter the information you have on hand and issue the new home permit from there. In order to help you locate this property record again in the future, put the word "Lot" followed by the lot number in the field designated for the parcel number. When you later get the actual parcel number from the county, you can return to this record and put in the real number. At that time you should also sort the database by parcel number and see if there is a duplicate parcel number in the system attached to a vacant lot. If so, this is the property record you needed originally, but now it is redundant. Copy any relevant information from that old record to the new one, and then delete the old record from the system.

This technique is also useful for lot split situations when you know the parent parcel number but not the parcel numbers of all the resulting "children" lots. Use the Add With Copy option (right-clicking the Add button) to create the new property records with a descriptive word in place of the parcel number. You can then create a report of all of these new records by keying in on the descriptive word. As you receive the actual parcel numbers from the county you can then go back to these new records and add that information.

STREET SWEEPER/SQL SWEEPER TRICKS

How to add zoning classifications by subdivision

From ZP Toolbox you can use Street Sweeper (ZP32) or SQL Sweeper (ZPSQL) to assign zoning classifications by street or by subdivision to speed up that process. Here is how: 1) Go into Sweeper; 2) Select the Global Changes option; 3) A dialogue box will open asking you to select the database you wish to work with, choose "property.dbf"; 4) You will notice now that the Lookup Field and Change Field options are enabled. Select "subname" for the Lookup Field and "zoning" for the Change Field; 5) Now you will see two lists in the Current Value and Change To scroll boxes, if you do not see the zoning codes you expected this means these values have not been entered into at least one record of the Property database. You will have to exit the Sweeper Screen and go back into Property Screen to enter the desired values into at least one parcel; 6) If you do see the values you wanted, double click on the subdivision name you wish to work with and then on the zoning classification you wish to assign to that subdivision; 7) When you have made your choices click on the Execute Change button and every property record that has the selected subdivision name will now be assigned the chosen zoning classification. (It should be obvious that the above trick is only useful if you have already assigned subdivision values to the property records you now want to assign zoning codes to. This same trick can be used to assign subdivisions by street; in step 4 choose "street" as the Lookup Field and choose "subname" as the Change Field.

How to select just odd or even street numbers

Sometimes it is useful in the Mailing Label Screen or Street Sweeper to be able to select just odd or even street numbers. You can do this using the Advanced Filter option and a special function that returns the remainder of any division. To select only odd numbered houses you would add the following condition to the Advanced Filter screen:

(ZP32)  MOD(VAL(st_nr),2) <> 0

(ZPSQL)  (CAST(st_nr AS INT) % 2 )<> 0

This formula works by converting the street number to a numeric expression and dividing it by two. If the street number is even the remainder will be zero when dividing by two. Odd numbered street addresses will produce a remainder greater than zero. Note that the fatal flaw with this technique is that the street number must be converted to a numeric expression. If there are letters or other characters in the street number field anywhere in the database this method won't work!

DOCUMENT EDITOR TRICKS

How to easily find field names when modifying documents

One of the stumbling blocks users run into when modifying documents is learning the internal names of the fields they want to add to a report or permit. For example to add the street number to a label you need to know that the internal name for that field is "st_nr." You can't find the internal names by looking at the screens or by guessing. There are, however, several easy ways to find the internal field names.

One way is to right-click on the Browse button of the screen that is relevant to your document. This technique displays a browse table where every column heading shows the internal field name. This is the best technique for Report Screens because these screens often pull fields from multiple databases into a temporary table that is used for the reports.

Another approach is to open a Screen Print document in the Document Editor. The Screen Print documents are designed to print all of the information available on any given database screen, and therefore have the internal names of all the fields as part of the document. They even come with built in descriptions to help you find the right field. When you find the desired field you can either write down the internal field name for use later or you can use the Edit menu to copy the field, open the new document you are creating, and paste the field onto the document. (When pasting fields from one document to another be aware that the program attempts to paste the field at the same relative position is was in the starting document. You will almost always have to find it and move it after pasting.)

If you want a print out of the field names the best approach is to use the Database Browser (ZP32) or SQL Browser (ZPSQL) option in ZP Toolobx. You can select any of the ZonePro databases and print a file layout that lists all of the internal field names.

A final option for ZPSQL users is to look at the ZPSQL Help file. Each application screen has a Fields page and the internal field name is listed as part of the description for each field.

How to easily position objects when modifying documents

When you are trying to get fields or text to line up correctly in the Document Editor the trick is to use the mouse to get close to your objective but use the arrow keys on your keyboard for the fine tuning. The arrow keys move an object one pixel at a time for very fine control. You also want to pay attention to the information in the status bar at the bottom of the document window. Here you will see co-ordinates listing the exact top, bottom, left and right positioning of the selected object. Comparing the co-ordinates of two objects allows you to align these objects perfectly.

How to print envelope addresses from any document screen

Several customers have asked if there is a way to print envelope addresses from one document screen or another. The answer is yes. On the Downloads page is a document created to print envelopes from Violations Document Screen. This document is named "cvl_envelope.frx" and is just a plain page, laid out landscape style, with the address information printed in the middle so that it will print in the proper place on an envelope. This same document can be used from any of the database document screens without any modification. All you need to do is open the document in the Document Editor and save it under a new name that matches the naming scheme of the database you wish to print from. For example, to print this document from the Building document screen, rename the document to "cbd_envelope.frx". (To find out what naming scheme is used by what document screen click on the Help button when you are in the desired document screen and read the text accompanying the Custom print selection option. This will tell you how documents are named so that they are recognized by this screen.)

How to create one document with multiple possible signatures

One customer had created three separate permit documents each with the signature of a different inspector at the bottom, but wanted to know if he could eliminate the extra documents by using the Inspector field. The problem is that the Inspector field only stores initials where as the signature was the inspector's full name printed in a script-style font. The solution is to use the Document Editor to create three separate field boxes at the bottom of the screen. The expression in each field box is the name to print, for example "John W. Smith". Then add code to each Print When button telling the field to only print if the Inspector field equals the inspector's initials. In our example the Inspector field has to equal "JWS" before "John W. Smith" should print on the permit. Next, stach the three field boxes on top of each other so that the signature always print in the same spot. This is okay because only one of the fields will print each time, depending on whose initials are assigned to the permit.

This same technique can be used with any field that stores an abreviated reference such as the Zoning District or Region fields, etc.

How to selectively print subtotals in a report

Reports that group records and print subtotals for each group can quickly become cluttered. One way to clean them up is to hide subtotals when there is only one item listed in a group. To do this you’ll need to open the report you wish to change in the Document Editor. Then follow these steps:

- Go to the Report menu and select “Variables”.
- Type in “itemcount” as the name of a variable.
- Change the Calculate option to “Count”.
- Change the Reset option to the name of the field being used to group the records. Then hit “Okay”.
- In the body of the report find the Group Footer band and double click on each object in the band one at a time. For each one make the changes below.
- In the Report Expression screen click the “Print When” button.
- In the Print When screen check the “Remove line if blank” option.
- In the “Print only when expression is true” box type “itemcount > 1”.

How to print out a document template

Sometimes it is useful to print out a document template that shows which field boxes are on a document, etc. You can do this by using the Document Editor, opening the desired document, use the Edit menu to "Select All" and then "Copy". Now open up your word processor and "Paste". You should see the document layout replicated in the word processor, ready to print.

How to copy word processor documents into the ZonePro Document Editor

If you already have a form letter created in a word processor that you use for code enforcement or other customer interactions, you can move part or all of that letter into a ZonePro document by following these steps. Open the document in your word processor, select and copy the paragraphs you are interested in. Now launch the Paint program that comes with Window. (It can usually be found in the Accessories segment of your Start menu.) Choose the paste option in Paint and you should see the copied text replicated in full. Some versions of Paint automatically resize the canvas so that the copied text fits. In some versions you may have to manually change the canvas size settings. Save the new Paint file with a unique name to the \REPORTS folder. You now have a BMP graphic file that you can add to any ZonePro document. In ZonePro go to the Document Editor, open the desired document, and choose the graphic tool (i.e.., the "Picture/Active X Bound Control"). Use this tool to indicate the space where you wish to place the graphic. This will open a dialog box that lets you point to the BMP file you just created. Stick with the default settings and return to the Document Editor screen. Resize the graphic placeholder box so the entire text is visible.

How to selectively print text based on the number of copies

Sometimes it may be useful to selectively print text on a document based on the number of copies. For example, it is a common practice to print three copies of a permit where copy one is the "Applicant Copy", copy two the "Office Copy", and copy three the "County Copy". To print these labels automatically, place three text blocks on the desired document. One for each of the text examples above. Double-click on text block one (Applicant Copy) and then click on the "Print When" button. In the "Print only when expression is true" box copy and paste the following expression:

IIF(VARTYPE(x) = "N",IIF(x = 1,.T.,0),0)

Repeat this process on text block two except edit the above expression to change the "x=1" portion to "x=2". For text block three change this portion of the expression to "x=3". Even though you have three text blocks, only one will print for each copy. You must use the Copies spinner on the document or report screen for this option to work.

How to selectively preview objects in a document

Sometimes it may be useful to have graphic or text elements in a document that appear when the document is previewed, but not when the document is printed. For example, if you use pre-printed forms, you can scan the form and add that image as a graphic to a ZonePro document template. Adding the scanned form as an image will help you place fields so they print correctly and also make the document more meaningful when you preview it, but you don't want the image to print. To make the image appear selectively, double-click on the image object and then click on the "Print When" button. In the "Print only when expression is true" box copy and paste the following expression:

UPPER(output_type)= "PREVIEW"

The image will now appear when you preview the document, but not when you print it. This trick only works with the Output "Preview" option, not when right-clicking on the Printer button.

How to implement fancy page numbering in reports

If you want to set up fancy page numbering so that it reads something like "Page 1 of 5", create a field box in the Page Footer band of the report and add the following expression:

"Page " + TRANS(_PAGENO) + " of " + TRANS(_PAGETOTAL)

When you add the _PAGETOTAL variable to a report, the report actually runs twice in order to figure out how many pages will be in the total. This will add a very slight delay to the running of reports that use this option but you probably won't notice.

How to selectively print by page number

Sometimes you may want a report that selectively prints different things based on the page number. For example, you may want the text "Continued..." to print at the top of each page but the first. To make an element print only on selected pages you would use the "Print When" option for that element. In the "Print only when expression is true" box your expression might look like this:

_PAGENO > 1

The above expression would ensure that the item printed on every page except the first. The key is the internal variable _PAGENO which keeps track of which page is printing.

If you wanted to print something on the last page only you could tweak this trick so the expression reads:

_PAGENO = _PAGETOTAL

How to add the Street Extra field to location expressions

If you are using the Street Extra field to store the street direction it may be desirable to include this field into a location expression. Adding the Street Extra field after the Street Name field is simple, but adding it between the Street Number and Street Name requires a little more finesse. The trick is to eliminate extra spaces in the output if the Street Extra field is blank. One way to do this is to use an expression like this:

ALLTRIM(TRIM(property.st_nr)+IIF(EMPTY(property.st_extra)," "," "+TRIM(st_extra))+" "+property.street)

How to add the current time to a document

If you want to add the current time to a printed document you can so by entering the following expression in a field box:

TTOC(datetime(),2)

If you want to eliminate the seconds from the time display, you can use the following expression:

LEFT(TTOC(datetime(),2),5) + RIGHT(TTOC(datetime(),2),3)

How to selectively print information from the Notes fields

If can be very handy to selectively print information from the Notes fields. This trick gives you the ability to store virtually any information you want in the Notes field and still be able to print that information selectively on a document or a report. This trick uses a programing function that performs a string extraction. In order for this to work the string (or text) that you wish to select must be enclosed by markers that indicate the beginning and the end of the string. These markers are also just bits of text and they can be anything you like but I find it easier if you stick to a standardized marking method. In this example we'll use <BeginText> as or starting marker and <EndText> as or ending market. I can insert my text statement anywhere in the Notes field as long as it is surrounded by the expected markers. So somewhere in the Notes2 field I'll have this text:

<BeginText> Only print this bit and ignore anything esle in this Notes field. <EndText>

Now in the Document Editor I can create a field box that includes the string extraction function in the expression like so:

STREXTRACT(notes2,"<BeginText>","EndText",1,1)

In my document that field box will print "Only print this bit and ignore anything else in this Notes field." There is no limit to the number of blocks of text you set up to work this way in a Notes field as long as you vary the marker text for each set.

PHOTO MODULE TRICKS

How to use the Photo Module in a peer-to-peer network

Using the Photo Module in a peer-to-peer network can be tricky because: 1) the Photo Module just stores the path to the image file, not the image itself; and 2) that path must be the same for everyone who wants to view the images. This means the path cannot include a "C:" drive because users on a network each have their own C: drive and cannot share a single one.  The trick is to create a virtual drive letter that refers to the folder where the photos are stored. For example, on the the primary PC you could create a virtual drive "P:" that refers to the "C:\Photos" folder. All of the satellite PCs would then map a new network drive to that same "C:\Photos" folder and label that drive "P:". Now whenever someone enters new photos into the Photo Module the path would reference the "P:" drive.

There are several Windows shareware programs that can be used to create virtual drives but the old DOS command seems to do the job the easiest. To use the DOS command for the example above, on the primary PC you would create a batch file with the command "subst p: c:\photos". (You can create a batch file by typing the above command into NotePad and saving the file as "newdrive.bat".) Place the batch file in the root C: folder of the main PC and add it to the StartUp folder so that it executes every time you reboot the computer.

How to move a photo folder and reestablish the links in ZP32

It is important to remember that the Photo Module only stores the path to any linked photos. If you move or rename the folder that the photos are stored in the link is broken. There are times, however, when it is useful to move an entire folder of photos. Rather than reestablishing the correct path one record at a time, you can use the Script option in the Database Browser to change all of the affected records at once. For example, say you have a folder called "D:\myoldphotos\" and you are moving all of those files to a new folder called "E:\newphotohome\". You would use the Database Browser to select the Photo database. Use the Safekeeper icon to make a quick backup. Then click on Run Script button. Your script would look something like this:

SELECT PHOTO
REPLACE FILENAME WITH STRTRAN(FILENAME,"D:\MYBESTPHOTOS\", "E:\NEWPHOTOHOME\") ALL

Execute that script and it will change the pathname of all the photos.

In ZonePro SQL you do not have the option to run such a script yourself. You can however contact ZP Systems and supply the same old path/new path information as listed above.

How to archive old photo links and take them out of ZP32

If you make heavy use of the Photo Module, over time you can amass a huge collection of links to photos, etc. You may decide that you only want to keep links to photos that were added during the last two years. You can use the Script option in the Database Browser to archive the older records. Select the Photo database in the Database Brower. Use the Safekeeper icon to make a quick backup. Then click on the Run Script button. Your script would look something like this:

SELECT PHOTO
COPY TO ARCHIVE_PHOTO.DBF FOR PHOTO_DATE < (DATE( ) - 730)
DELETE FOR PHOTO_DATE < (DATE( ) - 730)

The second line creates a new database called "Archive_Photo.dbf" that only contains records with a Photo Date more than 730 days old. The third line then marks that same group of records for deletion in the Photo database. After you run the scrip use the "Pack Database" button on the Database Browser screen to permanently remove the records you marked for deletion. (Note that the Archive Utility includes an option to archive image records for you!)

How to link a folder in the Photo Module

You know that you can link any kind of file to a property record using the Photo Module, but did you know that you could also link to a Windows folder? If you wanted to link a whole group of files to a property record but not create individual Photo records for each one, the option to link to a folder may be appealing. Go into ZonePro and start a new Photo record attached to the relevant property. Mark the "Not an Image" check box. When you enter the File Path field a naviagation dialog window will pop-up. Link to any file in the desired folder. It doesn't matter which one. When you return to the Image Database Screen edit the path and delete the file name all the way to the last backslash. Now when you use the Camera button you will launch that folder in Windows Explorer.

How to remove all photo records for missing files in ZP32

If you have a lot of records in the Photo Module that no longer point to valid files, either because those files have been moved or deleted, you may want a way to remove these records all at once. You can delete all Photo records with invalid file references by using the Database Browser in ZP Toolbox. Select the Photo database in the Database Brower. Use the Safekeeper icon to make a quick backup. Then click on the Run Script button. Your script would look something like this:

SELECT PHOTO
DELETE FOR NOT FILE(TRIM(filename))

This script may take a few seconds to complete depending on how many records there are in your Photo database. After you run the scrip use the "Pack Database" button on the Database Browser screen to permanently remove the records you marked for deletion.

FEE AND FORMULA TRICKS

How to selectively activate a surcharge fee

We have a customer that wanted to selectively activate the automatic surcharge calculation available in the Building Fee Detail Screen. Small accessory structures did not require a surcharge fee but larger ones did. The following script was added in the Formula Script Screen for the "Accessory Structure" permit type. It checks to see if the user types a "Y" for yes in the Modifier field and then activates the surcharge flag accordingly:

&&SCRIPT
MYFEE = 25
IF DETAIL2.MODIFIER = "Y"
REPLACE DETAIL2.SURCHARGE WITH .T.
ELSE
REPLACE DETAIL2.SURCHARGE WITH .F.
ENDIF
RETURN MYFEE

In this instance the fee does not change regardless of the size but you could easy add extra lines to handle that.

How to differentiate between different surcharge types

Most of our customers in Ohio track a 3% charge on new commercial construction and a 1% charge on new residential construction. When you add the surcharge option to a permit type in the Building fees section in Maintenance the Fee Detail is always listed as simply "SURCHARGE." This doesn't make it easy to tell one type from the other come report time. You can, however, modify the default Fee Detail name without botching the surcharge calculation function by adding text to the end of the default name. So for example, you can rename your surcharge types in Maintenance to be "SURCHARGE 1%" and "SURCHARGE 3%". This makes it very easy to use the Fee Type filter in the Fee Detail Report Screen to just look at one type of surcharge.

How best to handle a second round of fee collections

A customer wanted to know how to handle fees that occur after the main permit fee has been paid. In their city they charge extra fees if the initial inspection fails and a re-inspection is necessary. You cannot simply add the new fee to existing group of fee details because then it looks as if they have already paid the new total amount. The best way to handle this is to create a permit type in maintenance called "Reinspect" . Go to the existing building permit record that has new fees and make a note of the permit number. Now use the add button to create a new permit, but change the number so that it is the same as the original. Do not enter a permit date, because then it would appear as if you issued two permits with the same number. For the Permit Type choose Reinspect . Now in Fee Details you can begin adding the reinspection fees as they occur. Do not fill in the Date Paid field until the final inspections are completed. You may want to add a comment in the Notes field of the original permit record to indicate that a secondary Reinspect record has been created for this record.

TOP