E-mailing to All Contractors at Once (ZPSQL)

A customer asked if there is a way to send and e-mail to all of the contractors at once that have an e-mail address in ZP. While there is no way to do that in ZonePro itself, you can use a script to get a usable list of all the e-mail addresses that you can then paste into your regular e-mail program.

To enter the script go to User Defined Scripts in Maintenance. Select "Contractor" for the Module, leave the Screen at "Contractor" and change the Action to "Left". Then past the following script in and save it:

mycommand = [select distinct email from contract where email > '  ']
dosqlcommand(mycommand,'pickresult')
mylist = ''
select pickresult
go top
scan
mylist = mylist + TRIM(email)+[,]
endscan
mylist = left(mylist,len(mylist)-1)
_cliptext = mylist
strtofile(mylist,'contractor_emails.txt')
use in iif(used('pickresult'),'pickresult',0)
select contract
wait window "Emails copied to Windows Clipboard" nowait

Now go to any Contractor record in the Contractor Screen and left-click on the User Script icon (the book). This creates a comma-separated list of contractor e-mails and saves it to the Windows clipboard so that you can paste it in another mail program. It also saves a text file of this under the file name "contractor_emails.txt." You can edit text file or copy and paste from it also.

Changing the Default Dates in Contractor Docs Screen (ZPSQL)

The Contractor Docs screen displays a Start and End date that can be used for license printing. At the beginning of the year the date range is January to December, but then it automatically switches to July to June later in the year. I had a customer who wanted to keep the dates at January to December no matter what. This is how you do it. Start a script for the "Contractor" Module. Choose "ContDoc" for Screen, and leave the Action at "Screen". The script would be:

contdocs.startday = "January "+STR(YEAR(DATE()),4)
contdocs.endday = "December "+STR(YEAR(DATE()),4)

Changing the Default Sort in a Report Screen (ZPSQL & ZP32)

A customer asked if there was a way to change the default sort choice in their Violations Report Screen so that it always started on "Inspect Date." Looking at the sort choices in that screen I could see that that option was the fourth one in the list. To create the script in the User Defined Script Screen I chose "Zoning" as the Module, "ViiolateRep" as the Screen, and left "Screen" as the action. The actual script I entered was:

viorep.cmbsortby.listitemid = 4
viorep.sort_order = viorep.taglist(4,2)

Now when they enter that screen the default sort is the one they want. This script can be easily modified for other choices in the sorting list by changing the 4's in the script to the desired number. You can also use it for other report screens by substituting the proper screen name for "viorep".

Selectively Hiding the Social Security Number on Contact Screen (ZPSQL)

A customer just asked if there was a way to selectively hide the Social Security number field on the Contact File Screen so that only certain users could see it. The most reasonable way to do this would be by Security Access Level. The script below hides both the number field and its label if the user's security level is over 2. This script was created using "Contact File" for the Module, "Contact" for the Screen, and "Screen" for the Action:

if zpsecure.cfs > 2
if wexist('propscreen')
cfscreen.zp_pageframe1.page2.txtSs_num.visible = .F.
cfscreen.zp_pageframe1.page2.lblSs_num.visible = .F.
else
cfscreen2.zp_pageframe1.page2.txtSs_num.visible = .F.
cfscreen2.zp_pageframe1.page2.lblSs_num.visible = .F.
endif
endif

You'll notice there appear to be two sets of screen references. This is because there are two Contact Screens: one launched from the Main Screen and one from the Property Screen.

It is worth noting you can also use the security setting to block printing of the field in any document by adding "zpsecure.cfs>2" to the Print When section in the approriate field properties via the Document Editor.

Using Login Credentials to Fill Initial Fields (ZPSQL)

Because you have to login to use ZP SQL, the system knows who you are and can add your initials to a field automatically if desired. Your initials are stored in a system variable called ZPSECURE.OWNINIT. So if you want to always have your initials added to the Received By field in the Violations Screen each time you add a new violation record you can do so by creating a script for the "Add" action for the "Violations" screen:

replace comp_recv with zpsecure.owninit.

If you want to prevent anyone from editing that info once it is added automatically, just add another line to your script:

vlscreen.zp_pageframe1.page2.txtComp_recv.enabled = .F.

If you really want to lock down that info you should also create a script for the "Edit" action that repeats that second line of the script.

Referencing Rental Status in Property Screen (ZP32)

If you need to run reports on permits or violations issued to rental properties it can be useful to have the Property database itself indicate that the property is used for rental. The following script is a one-time run script that would be executed from the Database Browser screen script option. It will put the value "RENTAL" in the Property User7 field if said property has any kind of Rental record store.

update property set user7 = "RENTAL" from rental where rental.prop_id = property.prop_id

Now you run reports from any screen and used the Advanced Filter option to check the Property User7 field for "RENTAL" to get only application records issued to rental properties.

Referecing Zoning District Setbacks From Any Screen (ZPSQL)

From the Property Screen you can easily reference the minimum setbacks defined for the assigned Zoning District by clicking on the "Zoning" label to display the Zoning District Maintenance Screen. This option may be even more useful on permit screens where you need to compare the desired setbacks of a project with those allowed. This script uses the Building Screen as an example but the script will work for almost any screen. I'm using the right-click option on the Script button but that is easily changed too. To implement the script start in User Defined Scripts Screen using "Building" as the Module, "Building" as the Screen and "Right" as the Action. Click the New button and then paste in the script below:

myAlias = alias()
 if not empty(property.zoning)
myCommand = [do zcodes_link in maintsql.app with '] + property.zoning +[']
&myCommand
endif
select (myAlias)

Now if you go to any Building Screen and right-click on the Script button the Zoning District Maintenance Screen will pop up showing the minimum setbacks allowed for that property.

Changing the Default Date Range in a Report Screen (ZPSQL & ZP32)

A customer asked if it possible to change the default 30 day date range in a report screen. Using a script you can set the From date to any value you wish. Using the Rental Report Screen as an example the script would be:

rentrep.txtStartdate.value = DATE() - 60

Where the 60 value determines how many days back the range goes. This value is 30 by default but you can reset it using this script to any value you want. This approach should work for any report screen.

Set A Default Date Type in Rental Reports Screen (ZPSQL & ZP32)

By default no date field is set when you enter the Rental Report Screen. You can use a script to change this. A customer just requested a way to make the Enter Date the default date field. To do this you start a new script in the User Defined Scripts Screen using "Housing" as the Module, "RentRep" as the Screen and "Screen" as the Action. The script itself would be:

rentrep.date_type="ent_date"
rentrep.cmbUsedate.value = "Enter Date"

Now when you first enter the Rental Report Screen the Enter Date will be shown as the default date type.

Special Time Clock Script (ZPSQL)

In several of the screens that track inspection you can launch a Time Clock box that lets you fill in the Inspection Time field by selecting a pre-defined value from a list. Recently a customer asked if there was a way to customize the time options offered in the Time Box list. Now you can by creating a special script file that is editable in the User Defined Scripts Screen. The file should be called script_timeclock.txt and the format is simply a series of time values separated by commas.  A example script might be:

AM 08:00,AM 08:30,AM 09:00,AM 09:30,AM 10:00,AM 10:30,AM 11:00,AM 11:30,PM 12:00,PM 12:30,PM 01:00,PM 01:30

Whenever the Time Box is launched it looks to see if the a script file exists before either loading that script or defaulting to it's own list of times. You can create this text file yourself and place it in the main installation folder or download a starting script from our web site at Time_Clock_Script. Either way you can edit the list in the User Defined Scripts Screen to make it suit your needs.

Special Script for Renaming Application Buttons (ZPSQL)

You can add a special script file to your installation folder that allows you to rename the Application Buttons that appear at the bottom of the Property Screen. This feature has existed for a while but I have changed the naming scheme of the files so that now the text can be easily edited in the User Defined Script Screen. By default there are four Application Button categories that each have five pre-defined  buttons. The four groups are Zoning, Building, Housing and General. When the screen switches categories it looks to see if a script file exists for that category and uses the new button names if it is found. Each category has it's own script files. The file names are script_property_btnzoning.txt, script_property_btnbuilding.txt, script_property_btnhousing.txt or script_property_btngeneral.txt. A look at the Zoning button script shows what the text would look like:

%Zoning%Violations%Amend%Non-Conform%Appeals%

You can simply edit the names in between the percentage signs to change the name as it appears on the screen. The format is simple enough that you could create the text file yourself or you can download the set from our website at App Button Scripts. This file contains several files but you only need to copy the one you want to change into your main installation folder. It will then appear as choice in the User Defined Scripts Screen where you can edit it.

Easy Photo and File Linking in the Property Screen

One of the easiest ways I have discovered to link photos or files back to a Property record is to use a script that simply looks for a folder based on the address of the property. I have been talking about this concept a lot lately so it bears writing about again. This approach is completely independent of the Photo Module and instead relies on creating a simple structure of folders to house the files. When the user clicks on the Script Button in the Property screen the script finds the folder that matches the address and launches it in Windows Explorer so that the user can easily see the contents of the folder. Using thumbnail views you can quickly see the contents of photos, PDFs, Word files or whatever. The user can just drag and drop any file they desire into the folder which makes it super easy and quick to populate. The script below has an added feature where it creates the folder for you if it did not previously exist. In this script I use the Street Name followed by the Street Number to name the folders but you could use other Property fields instead. This naming scheme is easy to maintain though and has the big advantage of making sense outside of ZonePro as well.  This script uses the "Base" Module, the "Property" Screen and the "Left" Action.

myfolder = "C:\Photos\" + trim(street)+" "+trim(st_nr)
if directory(myfolder)
shellexecute(0,"open",myfolder,"","",1)
else
mkdir  (myfolder)
shellexecute(0,"open",myfolder,"","",1)
endif

This script shows the base location as "C:\Photos\" but in most cases this would refer to a location on a shared server. There is a Training Video from last year that shows how this works with a simpler version of the script.

Changing Label Colors (ZP SQL & ZP 32)

I have written before about how to use a script to change the text assigned to a field label. Today I had a customer ask if you could also change the color of the label to make it stand out. 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.page1.lblExist_use.Caption = "Warning"

propscreen.zp_pageframe1.page1.lblExist_use.Forecolor = RGB(255,0,0)

The first line changes the label for the Existing Use field on the Property Screen to "Warning." The second line makes that label red. To change the color of a field you enter in three Red/Green/Blue values from 0 to 255. Different values produce the rainbow of colors. You can use a number of web sites to learn the RGB values for any color you desire.

Forcing Final Date Fill Ins (ZP SQL)

A customer recently asked if there was a way to force the filling in of the Final Date field after a "FINAL" inspection was completed in the Building Inspection Details Screen. This script does just that assuming you have an inspection type called "FINAL" and that your result value is "APPROVED."  If those conditions are met and the Final Date field is empty in the Building Screen, the script will fill in today's date and put you in Edit Mode in the Building Screen after you exit the Inspection Details Screen. This script uses "Building" for the Module, "Buildinsp" for the Screen, and "Save" for the Action:

if detail1.detail = "FINAL" and detail1.iresult = "APPROVED" and ISNULL(build1.occ_date)
= messagebox("Be sure to check Final Date in Building Screen",64,"Alert")
CURSORSETPROP('Buffering',4,'build1')
replace build1.occ_date with date()
buildscreen.isediting = .T.
endif

Auto Increment Permit Numbers By Ten (ZP 32 & ZP SQL)

I had a customer ask if there was a way to have the auto numbering feature for permits increment by 10 instead of just one. You can do this by adding a script to the "Add" function of any screen. This script uses "Building" as the Module, "Building" as the Screen, and "Add" as the Action. The script is:

newcert = left(cert, len(trim(cert))-4) +padl(alltrim(str(val(right(trim(cert),4))+9)),4,"0")
replace cert with newcert

This script add an extra 9 to the auto generated new permit number to make it increment by a total of ten. This script will work for any of the permit screens in either ZP 32 or ZP SQL.

Set New Defaults for Max Rows in Reports (ZP SQL)

A customer asked me today if there was a way to set a new default value for the Max Rows field on report screens. The Max Rows value determines the maximum number of records pulled by a SQL query when running a report. It defaults to 100 records which is sufficient for most reports but in their case was not enough for the Building Combo Reports screen. This script sets the value to zero for that screen which removes the restriction on the number of rows a query can pull. The script uses "Building" for the Module, "BldCombo" for the Screen, and "Screen" for the Action.

 rep_combo.max_rows.value = 0

Forcing Pick Box Use (ZP 32 & ZP SQL)

I recently had a customer ask if there is a way to force users to fill in fields using the Pick Box feature. One way to do this is to disable the field for direct editing by making the field read only. You can do this for selected fields in the screen scripts. The example below makes the Existing Use field read only so the only way it can be filled in is by using the Pick Box option. The script uses "Base" for the Module, "Property" for the Screen, and "Screen" for the Action.

propscreen.zp_pageframe1.page1.txtExist_use.readonly = .T.

You can add as many fields to the same script as needed.

Archiving Previous Owners (ZP 32)

A ZP 32 customer has requested a script to log owner names if they change. She wants to add the old owner's name to the Notes field along with the date the change was made. The script uses "Base" for the Module, "Property" for the Screen, and "Save" for the Action.

if own_fname <> oldval("own_fname") or own_lname <> oldval("own_lname")
oldname = alltrim(trim(oldval("own_fname"))+" "+oldval("own_lname"))
replace notes2 with notes2 + "Old Owner: " + oldname + "  " +dtoc(date())
endif

NOTES
- This script is very sparse but it could be fleshed out easily to copy additional info such as the old owner's address (own_addr1 and own_addr2)
- This script can be easily modified to do the same thing for old occupants simply by changing each "own_" in the script to "occ_".

Cover Photo for Windows Interface (ZP 32 or ZP SQL)

A while back I added a feature for ZP SQL customers where they could easily attach a "cover" photo to any property that would be viewed as part of the Staff web site when that property was viewed. Working with a customer today I explained that that same concept could be brought to the Windows interface for either ZP 32 or ZP SQL. The basic idea is that you have one photo you consider to be the main photo for a given property. I'm calling this photo the cover shot. You can store and display that photo easily in ZonePro using a script. This technique does not even require the Photo Module. Basically you just pick a location to store all such photos. A place on the server is best in a networked environment. Then you save the photos using an agreed upon naming scheme. For this script I am naming the photos after the property ID. Now I can easily create a generic script that will check my folder for the existence of such a photo for any given property and launch my default photo viewer for Windows. I added this script to the left-click action of the Script button on the Property Screen so the setup is "Base" for the Module, "Property" for the Screen and "Left" for the Action:

myfile = [c:\photo\]+property.prop_id + [.jpg]
if file(myfile)
shellexecute(0,"open",myfile,"","",1)
else
wait window "Cannot Find Photo " + myfile nowait
endif

Selecting Any Contact As Applicant (ZP SQL)

A ZP SQL customer just requested an option that would allow them to select any name linked to the Property as an Applicant. In this case they were working in the Planning screen. I devised a script using the left-click Script button that brings up a browse screen showing all Contact names. When the user selects one, that name is automatically copied into the Applicant fields. Notice that the customer also wanted to copy additional Contact information into the User fields. This script only runs when you are editing a record. The script uses "Planning" for the Module, "Planing" for the Screen, and "Left" for the Action:

if planscreen.isediting
select planning
mycommand = [select rtrim(firstname)+' '+rtrim(lastname) as name,;
rtrim(st_nr)+' '+rtrim(street)+' '+rtrim(st_extra) as address1,;
address2,phone1,phone2,email,cftype from vcfsolorep where prop_id = ']+ prop_id +[']
dosqlcommand(mycommand,'mynames')
select mynames
on key label spacebar keyboard chr(23)
on key label rightmouse keyboard chr(23)
on key label enter keyboard chr(23)
browse normal in window mainzone noedit;
title "Select applicant name..."
on key label spacebar
on key label rightmouse
on key label enter
replace planning.app_type with mynames.cftype,;
planning.app_name with mynames.name,;
planning.app_phone with mynames.phone1,;
planning.app_addr1 with mynames.address1,;
planning.app_addr2 with mynames.address2,;
planning.user1 with mynames.phone2,;
planning.user3 with mynames.email
use in iif(used('mynames'),'mynames',0)
select planning
planscreen.refresh
endif