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

Copy and Pasting Fields Between Records (ZP 32 or ZP SQL)

A customer using the Rental Screen wanted to be able to copy information from the existing rental record whenever they added a new one. There is a built-in carry-over option in the Rental Screen if you right-click on the Add button, but that option does not copy all the fields this customer needed. I devised the following script as a way to copy and paste multiple fields at a time between any two records in the same database. This script uses "Housing" for the Module, "Rental" for the Screen and "Left" for the Action:

myaction = inputbox("Enter C to Copy or P to Paste")

if lower(myaction) = "c"

javascript:void(0)
wait window "copying fields..." nowait

public mydata

mydata = createobject("empty")

addproperty(mydata,"user1",rental.user1)

addproperty(mydata,"user2",rental.user2)

addproperty(mydata,"user3",rental.user3)

addproperty(mydata,"user6",rental.user6)

addproperty(mydata,"notes1",rental.notes1)

endif

if lower(myaction) = "p"

wait window "pasting fields..." nowait

if vartype(mydata) = "o"

rescreen.isediting = .t.

gather name mydata memo

release mydata

endif

rescreen.refresh

endif

NOTES
- This script crams both the Copy and Paste actions into one button (the left click Script button) with a prompt to decide which action to take. You could also easily split this into two scripts and put the Copy action in the left click and the Paste action in the right click and drop the prompt code.
- You can revise a script like this to work with any of the ZP databases and with any number of fields.
- Note that the Paste option puts you into Edit Mode, which means you can Cancel the Edit to negate the paste.

Adding Alerts After Completed Inspections

Today I had a customer who wanted to add an alert message to their system after certain types of inspections were completed. This script checks after each Save to see if the Last Inspect date is filled and if the inspection item is a "Foundation". If so it displays a message. This script uses "Building" for the Module, "Buildinsp" for the Screen, and "Save" for the Action:

 if  not empty(last_insp) and detail = "FOUNDATION"
= messagebox("Check for As Built",64,"Alert")
endif

Who Scheduled That?

Had a customer ask if there was a way they could keep track of who scheduled a particular Building Inspection and when. I devised a script that checks to see when the Next Inspection date changes and then replaces the Last Time field with the current users initials and the month and day. This script uses "Building" for the Module, "Buildinsp" for the Screen, and "Save" for the Action:

if insp_date <> oldval("insp_date")
replace last_time with trim(zonepro.owner)+left(dtoc(date()),5)
endif

NOTES
- It is the OLDVAL( ) function that checks to see if the Next Inspection date field has changed during editing.
- This script is not of any use in ZP 32 if you are not using Security.
- For ZP SQL you would change "zonepro.owner" to "zpsecure.ownint".
- There are only a handful of fields available to stick this info into. I chose Last Time because this field should be blank when you are scheduling a new inspection.

Archiving Fields in the Notes

I have a customer who wants to archive old data before they edit and change the value of certain fields. You can easily automate the archiving of various fields into one of the Notes fields by using a script. The example I'm using is archiving the Zoning District and the Existing Use fields in the Property Screen into the Notes1 field. The script copies the fields into the Notes when you click on the Script button while editing a Property record. This script uses "Base" for the Module, "Property" for the Screen, and "Left" for the Action:

if propscreen.isediting = .T.
replace notes1 with iif(empty(notes1),"",alltrim(notes1) +chr(13)+chr(13));
+ "ZONING ARCHIVED: "+dtoc(date()) + chr(13)+;
"Old Zoning: " + alltrim(zoning);
+"  Use: " + alltrim(exist_use)
propscreen.refresh
endif

In ZP 32 the "notes1" field should be referred to as "notes".

A Variation On Locking the Notes Field

There is an option in the Global Options screen that lets you lock the Note2 field is various screens so that no one can alter any text that has been moved into that field using the Append Notes button. Today I worked out a way that you can imitate that same effect on either the Notes1 or Notes2 field with the added benefit of a security level check. With this script, anyone with a security level greater than 1 cannot directly edit the Building Notes1 field. They can still add to the field however by typing into the Notes2 field and then using the Append Notes button to add that text to the existing text in Notes1. Note that the script must also disable the other Append Notes button. This script uses "Building" for the Module, "Building" for the Screen, and "Edit" for the Action:

if vartype(zpsecure) = "O" and val(zpsecure.bds) > 1
buildscreen.zp_pageframe1.page4.edtNotes1.enabled=.F.
buildscreen.zp_pageframe1.page4.notes_buttons1.move1_btn.enabled=.F.
endif

You may want to clean things up by adding another script to the "Save" action that re-enables the text to make it more readable.

For ZP SQL the script would be:

if zpsecure.bds > 1
buildscreen.zp_pageframe1.page3.edtNotes1.enabled=.F.
buildscreen.zp_pageframe1.page3.notes_buttons1.move1_btn.enabled=.F.
endif

You could also change the script to make access dependent on a user's Security Initials so that only one person had access. Do this by changing the first line to:

if zpsecure.ownerinit <> "ZPD"