Accessing Contractor Data from Fire Permit Screen

I had a request from a Fire Module customer who wanted to be able to add Contractor information to a Fire Permit record in a similar fashion used by the Building or Zoning screens. The following script will pull up a browse table of all Contractor names and then place the selected name and license number in the Fire Permit screen User3 field. It uses the left-click option of the User Script button. In the User Scripts screen I added a new item using "Fire" for the Module, "Fireperm" for the Screen, and "Left" for the Action:

if fire2screen.isediting
select alltrim(trim(first_name)+" "+last_name) as name, license;
from contract into cursor tempcont
on key label enter keyboard chr(23)
on key label spacebar keyboard chr(23)
on key label rightmouse keyboard chr(23)
browse normal in window mainzone noedit nodelete;
title "Contractor Records - Press Space Bar to Select"
if lastkey() = 23
replace fireperm.user3 with trim(tempcont.name)+" "+tempcont.license
fire2screen.zp_pageframe1.page2.refresh
endif
on key label spacebar
on key label rightmouse
on key label enter
fire2screen.show
use in iif(used("tempcont"),"tempcont",0)
select fireperm
endif

This is an unusual script request but it illustrates how powerful the scripting option can be.

Linking to Web Geocoding Options in ZP SQL

For ZonePro SQL there are some great mapping and geocoding options available in the Staff web site interface that are not available in the Windows interface. But, you can use a script to launch the key web page directly from the Property Screen in the Windows interface. Then you can use the web site to map the property, find and tag neighboring properties, or get driving directions. This script works great from either the Internet Link button or the User Script button. For this example we'll use the right-click option of the User Script button. In the User Scripts screen we added a new item using "Base" for the Module, "Property" for the Screen, and "Right" for the Action:

GoURL("http://www.zpdemo.zpuser.com/work/geocode/geomain.aspx?passedid="+property.prop_id)

Note that this script is for the ZP Demo web site. You would of course change that part of the URL to point to your own web site.

Setting Dual Deadlines for Expire Dates

I just had a customer ask about another Expire Date scenario. They don't have sliding Expire Dates but they have two deadlines. All permits expire in six months if construction is not started, but once started they have two years to finish. To keep track of this scenario you can use the same tutorials that go with the sliding Expire Dates with just some minor modifications to the initial script. Basically you set up the initial six month expiration deadline by setting Expire Days to 183 for each permit type in Maintenance. Then you only need a script to make the switch to the two year deadline. This is done with a script that uses "Building" as the Module, "Building" as the Screen, and "Close" as the Action.

myanswer = 0
mycode = id_code
select max(last_insp) from detail1;
where detail1.id_code = mycode into array mydate
mylast = mydate(1)
if not empty(mylast) and mylast > (cert_date)
myanswer = messagebox("New expire should be "+dtoc(cert_date +730);
+chr(13)+"Do you want to fix it?",68,"New Expiration Needed")
endif
use in detail1
if myanswer = 6
proceedwithclose = .F.
replace exp_date with cert_date+730
buildscreen.isediting = .T.
buildscreen.refresh
endif

Sliding Expiration Dates

Bradford County wants to institute a policy of always setting the Expire Date six months out from the last completed inspection for a building permit. The idea being that this would make it easy to run a report at any time using the Expire Date to find out which permits were in danger of expiring because of no activity in six months. The script below was designed for the "Close" button option of the Building database. It checks to find the most recent Last Inspection date in the Inspection Details database and then compares that with the current Expire Date. If the gap is less than six months it displays a message saying that the Expire Date should be changed and lists the date it should be changed too. This script uses "Building" as the Module, "Building" as the Screen, and "Close" as the Action.

myanswer = 0
mycode = id_code
select max(last_insp) from detail1;
where detail1.id_code = mycode into array mydate
mylast = mydate(1)
if not empty(mylast) and mylast > (exp_date - 183)
myanswer = messagebox("Last inspection was "+dtoc(mylast);
+chr(13)+"New expire should be "+dtoc(mylast +183);
+chr(13)+"Do you want to fix it?",68,"New Expiration Needed")
endif
use in detail1
if myanswer = 6
proceedwithclose = .F.
replace exp_date with mylast+183
buildscreen.isediting = .T.
buildscreen.refresh
endif

There are Sliding Expire Date tutorials in the Tutorial section of our web site for both ZP 32 and ZP SQL. These tutorials cover this topic at much greater length.

Create Copies of Database for use with ArcView

A customer wanted to know how to access ZonePro databases from ArcView. I explained how ArcView chokes on Notes fields and how the ZP Copy program can be used to make copies of the databases minus the fields that ArcView can't handle. The ZP Copy program is really just another way of running scripts and you can do the same thing directly within ZonePro. By way of a demonstration, I wrote a script that runs from the Script button on the Main Screen that makes a copy of the Property database minus the Notes fields. You can then link ArcView to the "property_copy" database. This script uses "Base" as the Module, "MainScreen" as the Screen, and "Right" as the Action.

if file("property.dbf")

use property in 0

select property

copy to property_copy fields except note* fox2x

wait window "Copy Made" nowait

use in property

Note that this script only works with ZP 32.

Link Photos for Contractor Database

The City of Whiting wanted to know if they could use the Photo Module to attach image files to records in the Contractor Screen. While that is not possible, I did devise a script that gives some of the same functionality. This script looks in a designated folder to see if there is a subfolder that matches the name of the contractor company. If it finds such a folder it launches the folder in Windows Explorer. The user can then easily see the contents of the folder be they image files, Word documents, or whatever. Using the Windows thumbnail option with such folders makes them even more informative. While this script uses the Company field as the folder name, almost any field in the contractor database could be used as the matching criteria. This script works well with one of the
Script Button options such as "Left" or "Right". It's worth noting that this script would work just as well in the Property Screen as a poor man's substitute for Photo Module. This script uses the "Contractor" Module, the "Contractor" Screen and the "Left" Action.

myfolder = "c:\photos\" + trim(company)

if directory(myfolder)

shellexecute(0,"open",myfolder,"","",1)

else

wait window "Cannot Find Folder" nowait

endif

Forcing a Field Value Entry

In the Town of Swansboro they want to keep track of both a calculated job cost and the estimated job cost for any building permits. The calculated job cost is used to derive the permit fee and is stored in the Job Cost field. The estimated job cost is provided by the applicant and is stored in the User5 field. Because the estimated job cost is stored on the extra tab, they want to make sure they don't forget to enter it. This script won't allow you to save the record is that field is blank. The script uses "Building" as the Module, "Building" as the Screen and "Save" as the Action:

 if empty(build1.user5)

= messagebox("Estimated Job Cost field is empty!", 16, "Missing Info")

proceedwithsave = .F. 

endif

Notice that the third line makes use of a special script variable "proceedwithsave" to prevent the Save operation from continuing. If you wanted to give a warning but still allow the Save you could emit that line.

Set A Default Permit Type in Other Permits

In Copley Township they use the Other Permits database to track culvert deposit fees. Since it is the only thing they track in that database they wanted an easy way to fill in the Type field consistently every time they add a new record. This script uses "Misc Fee" as the Module, "Other" as the Screen and "Add" as the Action:

replace other.type with "CULVERT DEPOSIT"

You can use this approach to create default values for almost any fields in any database.

Permit Prerequisites Alerts

In the Town of Montreat they have a special consideration whenever they issue a permit to a property with more than one acre of land. They wanted an alert to indicate that a Storm Water & Erosion Control permit was required if the lot being disturbed was more than an acre. This script was added to the "Building" Module, for the "Building" Screen, using "Add" as the Action:

if property.acres > 1

= messagebox("May require Storm Water Permit!", 16, "Large Lot")

endif

Violation Warning for Owners

At Huber Heights they have a particular landlord that wants to be notified immediately whenever one of his properties has a violation. We created a script that displays a message whenever that owner's name is involved. This script uses "Zoning" as the Module, "Violations" as the Screen and "Add" as the Action which means it will run when you click on the User Script Button in the Building Screen.

if property.own_lname = "PARK REALTY"

= messagebox("Call Bob Watson at Park Realty about this!", 16, "Notify")

endif

To use this scrip in ZP SQL I would change it slightly:
 

if propnames.own_lname = "PARK REALTY"

= messagebox("Call Bob Watson at Park Realty about this!", 16, "Notify")

endif

This script works fine if you have only a few such landlords to track, but would become unwieldy quickly. Another approach would be to key off a value in the Property database that you fill in to indicate you want a message displayed. You could use the USER4 field for example and the value "NOTIFY" to trigger the script. Your script would then look like this for both ZP 32 and ZP SQL:

if property.user4 = "NOTIFY"

= messagebox("Call owner about this!", 16, "Notify")

endif

This option is really just a variation of the built-in Alert Message feature.

Linking Zoning Permits To A Building Permit

This script was designed to enable a customer to keep better tabs on permits in other databases that are somehow associated with a given building permit. The example used here is a Zoning permit that is a required precursor to a Building permit. The objective is to be able to view the details of that Zoning permit from within the Building Screen. I created a script that will browse select fields of the Zoning record from within the Building Screen. This script uses "Building" as the Module, "Building" as the Screen and "Left" as the Action which means it will run when you click on the User Script Button in the Building Screen.

mylink = strextract(build1.notes1,"<BeginLink>","<EndLink>",1,1)
if not empty(mylink) select id_code as id,typeinsp as type,details,cert as permit,cert_date as date from zoning
into cursor tempzoning where id_code = mylink
browse normal in window mainzone noedit nodelete title "Linked Zoning Record"
use in iif(used('zoning'),'zoning',0)
use in iif(used('tempzoning'),'tempzoning',0)
endif
select build1

This script will now display a browse window anytime a Zoning record is linked to a Building record, but you have to define that link. To create a link to the desired Zoning permit in the Notes1 field of the Building record I’m using the technique that is explained in the "Selective Notes Printing 1" video on the Training Videos  section of our web page.

Here are the steps I take:
- Go to the Zoning permit record that you want to link and make a note of the ID Code. (That is the second number in the ID string at the top right of the Main tab.)
- Go to the Building record where you want to implement the link.
- Go to the Notes tab. Hit "Edit", and add this text anywhere in the Notes1 field:

<BeginLink> id_code <EndLink>

- Replace the bit that says id_code with the Zoning id_code you made a note of and save your change.

Now you click on the User Script Button to display a browse table with details about the linked Zoning record. This script technique can be expanded to work with any databases. The link tags are good candidates for using with the Notekeeper feature.

To use this option with ZP SQL the initial script needs some minor changes:

mylink = strextract(build1.notes1,"<BeginLink>","<EndLink>",1,1)
if not empty(mylink)
myCommand=[select id_code as id,typeinsp as type,details,cert as permit,;
cert_date as date from zoning where id_code = '] + mylink +[']
dosqlcommand(myCommand,'tempzoning')
browse normal in window mainzone noedit nodelete title "Linked Zoning Record"
use in iif(used('tempzoning'),'tempzoning',0)
endif
select build1

Enhancing Security For Closed Permits

A customer who uses the Security Module wanted a way to prevent users without the proper access levels from editing building permit records after they had been closed. Only users with a security level of 1 should be allowed to edit those permits. The following script assumes that the Occupy Date field is used to indicate whether a record is closed or not. This script uses "Building" as the Module, "Building" as the Screen and "Edit" as the Action:

if vartype(zpsecure) = "O" and not empty(occ_date) and val(zpsecure.bds) > 1
= messagebox("You cannot edit a finaled permit.",64,"Action Canceled")
buildscreen.isediting = .f.
endif

To use this option with ZP SQL the script needs a slightly altered first line:

if not empty(occ_date) and zpsecure.bds > 1

= messagebox("You cannot edit a finaled permit.",64,"Action Canceled")
buildscreen.isediting = .f.
endif

Preventing Violation Type Editing

The City of Whitehall wanted a way to prevent users from changing the Violation Type of existing violations. This was accomplished with two scripts. Both scripts use "Zoning" as the Module and "Violations" as the Screen. The first script uses "Edit" as the Action and it disables the Violation Type drop down box when editing:

vlscreen.zp_pageframe1.page1.cmbVio_type.enabled = .F.

Another script is need for the “Add” Action to make sure the drop-down box works when you are adding new violations:

vlscreen.zp_pageframe1.page1.cmbVio_type.enabled = .T.

Customizing The Application Group Labels

At the bottom of the Property Screen there is a radio button control we call the Application Group button that lets you switch from Zoning to Building to Housing to General to Custom permit databases. We had a customer request to change the label of "General" to "Site Plan". We did it with a script that used "Base" as the Module, "Property" as the Screen and "Screen" as the Action.

propscreen.app_option.option4.caption = “SitePlan”

Adding Items To Drop-Down Lists

At the City of Sandusky they wanted the option of “Court” to appear in the Billing Status drop-down box on the Rental Screen. Turns out you can use a script to add new options to the fixed list drop-down boxes in ZonePro. To accomplish this we used "Housing" as the Module, "Rental" as the Screen, and "Screen" as the Action.

rescreen.zp_pageframe1.page1.cmbBstatus.rowsource =;
rescreen.zp_pageframe1.page1.cmbBstatus.rowsource + ",Court"
rescreen.zp_pageframe1.page1.cmbBstatus.requery

Changing the Default Document

By default the document screen for any database always has the top document option selected when the screen is launched. We had a customer who wanted to default to the Nuisance Letter option instead of the Violation Letter when the Violation Documents screen launched. To do this we created a script that used "Zoning" as the Module, "ViolateDoc" as the Screen, and "Screen" as the Action. The script is:

vdocs.doc_select.value = 4

Preventing Permit Number Alterations

A customer contacted me who was frustrated with employees who kept screwing up ZonePro's auto numbering feature for permit numbers by manually changing the suggested numbers to their own liking. This example prevents anyone from altering the permit numbers that ZonePro assigns to each new permit. This script uses "Zoning" as the Module, "Zoning" as the Screen, and "Screen" as the Action. The script is:

znscreen.zp_pageframe1.page1.txtcert.readonly = .T.

If you use a script like this and have the year as part of your permit numbering scheme, you'll have to disable the script at the start of each year in order to switchover to the new year.

Preventing Fee Editing

This script will prevent anyone from editing the fee amount once the fee has been paid. This example uses the Zoning database and keys in on the Date Paid field as the deciding factor.  I use "Zoning" as the Module, "Zoning" as the Screen, and "Edit" as the Action. The script is:

IF NOT EMPTY(date_paid)
znscreen.zp_pageframe1.page1.txtPer_fee.enabled = .F.
ELSE
znscreen.zp_pageframe1.page1.txtPer_fee.enabled = .T.
ENDIF

The second part of the script enables the Fee field again if you navigate to another record where the condition no longer holds true.

Changing Field Labels

One of the most popular uses for User Defined Scripts in ZonePro is for changing the screen labels in front of fields. In this example I'm selecting "Base" as the Module, "Property" as the Screen, and "Screen" as the Action and my script is:

propscreen.zp_pageframe1.page2.lblFlood.Caption = "School"

This will change the "Flood" label on the Extra tab of the Property screen to now read "School." To create a script like this you need to know several things that typically require our involvement. You need to know that the internal name for the Property screen is "propscreen" and that the Extra tab is referenced as "zp_pageframe1.page2". Once you know that you can theoretically change the label for any field on that tab. The trickier bit is knowing that the Flood label is called "lblFlood".  Usually the label for a field is the same as the internal field name prefixed by "lbl", but unfortunately we were not always consistent in our naming habits. A quick e-mail to us though will get you the answers to the screen and label names you want to change.

Using Picasa for On-line Photo Storage

This is a repeat topic I recently entered in the ZonePro Journal blog. The idea is to use an on-line service such as Picasa for storing photos and then using a script to launch the photos. In this scenario I envision a customer creating a separate on-line photo album for each property. The name you give to the album will then be part of the URL that is used to later launch that page of photos from the ZonePro property screen. Whatever you name the album, you need to store that name in a field in the Property database so you can access it via a script and launch the site. Alternatively you could name each album after the Property ID so you don't have to waste a field, but then the album name would be meaningless outside the context of ZonePro. In my example I created an album in Picasa and named it after the address: LOWELL584. I then stored the value "LOWELL584#" in the User3 field of the Property database. Note the # sign. Picasa adds that when it creates its URLs but it may not be necessary. The rest of the URL is always a constant for my Picasa account. Your account would be different, of course. I'm going to use the User Script button to launch the web page and create a script that checks to make sure a value exists in User3 before attempting to launch the web page. To set up the script I use "Base" for the Module, "Property" for the Screen, and "Left" for the Action:

IF NOT EMPTY(property.user3)

GoURL("http://picasaweb.google.com/zonepro32/"+property.user3)

ENDIF

Auto Number License Field in ZP32

In the previous post I showed how to auto number the License field in the Contractor screen with a script. That script was for ZP SQL. Some time earlier I had written a script for a customer who wanted to do the same thing in ZP 32. Their number scheme for the License field looks like this: "2010-0001". Again this script uses the User Script button and only works when you are editing. As before, this scripts uses "Contractor" as the Module, "Contractor" as the Screen, and "Left" as the Action. Here is the script:

IF CONTRACT.BROWSE_BTN.ENABLED = .F.
SELECT MAX(LICENSE) FROM CONTRACT INTO ARRAY MYLAST
MYPREFIX = STR(YEAR(DATE()),4)+"-"
MYNEWID = VAL(SUBSTR(MYLAST(1),6))+1
REPLACE LICENSE WITH MYPREFIX +
PADL(ALLTRIM(STR(MYNEWID)),4,"0")
CONTRACT.ZP_PAGEFRAME1.PAGE1.TXTLICENSE.REFRESH
ENDIF

Auto Numbering License in Contractor Screen

Yesterday I talked to a ZP SQL customer about automatically bumping up the number in the License field of the Contractor Screen in a fashion similar to Permit Number fields. I had already covered this topic for ZP 32 but the code is slightly different for ZP SQL. In this case the customer uses a numbering scheme like "C10-001" where 10 represents 2010. The script I created used the User Script button. While editing, clicking on the User Script button automatically fills in the next higher number in the License field. This scripts uses "Contractor" as the Module, "Contractor" as the Screen, and "Left" as the Action. Here is the script:

IF contscreen.isediting = .T.
myCommand = [SELECT MAX(license) AS maxnum FROM contract]
DoSqlCommand(myCommand,'myLicense')
myLast = VAL(RIGHT(TRIM(myLicense.maxnum),3))+1
myNext =  LEFT(myLicense.maxnum,4)+PADL(ALLTRIM(STR(MYLast)),3,"0")
USE IN IIF(USED('myLicense'),'myLicense',0)
SELECT contract
REPLACE license WITH myNext
contscreen.ZP_PAGEFRAME1.PAGE1.TXTLICENSE.REFRESH
ENDIF

Action Options for User Scripts

When you create user defined scripts in ZonePro one of the important setup options is identifying the "Action" that the script is connected to. Basically this determines when the script runs. There are three main Action options. If you use the Screen action then the script runs as the screen is launched. If you want something to happen before the screen is displayed, this is Action option you want. You can also attach scripts to several common control buttons such as the Add, Close, Delete, Edit or Save buttons. Attaching a script to those buttons means the script will run when that button is clicked.
The third script option is to attach the script to the User Script button (the open book icon). This button appears on almost every screen in ZonePro. This button provides three opportunities for scripts depending on whether you click on it with the Left, Right, or Middle mouse buttons.

How to Add User Scripts

All of the scripts on this site are user defined scripts. These scripts can be added to your ZonePro system by copying the script text from any post and pasting it into the User Defined Scripts screen in either ZP 32 or ZP SQL. The User Defined Script screen is accessed from the first tab in the Maintenance Options section of ZonePro. When you create a new script in the User Defined Script screen you first indicate which Module and Screen the script is assigned to and then the Action that the script follows. All of these selections are made via drop-down boxes at the top of the User Defined Script screen. Each post should tell you the Module, Screen, and Action that the script was written for. You would duplicate these choices in the User Defined Scripts screen, click on the "New" button, and then paste the script you copied into the main scroll box on the screen. Use the Windows hot-keys and to copy and paste. The last step is to simply hit the "Save Script" button and you have just added new functionality to your ZonePro system.

Yet Another Blog!

I know, I know. How many blogs does one company need? At least three, apparently. The reason for this blog is a little different. Over the past couple of years I have attempted to keep track of ZonePro user script examples in PDF documents on our web site. The problem there is that it is hard to find the one you are looking for when you know you've written a script but can't remember when. The advantage of the blog format is that you can use labels to help locate topics. This will make it much easier to search for a script. On the sidebar of this page you'll see a list of labels. You can click on any label of interest and see every entry that has a matching label assigned. If I do this right, the blog format should be a great way of cataloging user scripts.