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)

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.

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

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