Adding and Subtracting from Date Field

ZP SQL handles dates differently than ZP 32. In ZP SQL all dates are stored in a long DATETIME format that includes both date information and time information. In ZP 32 all dates were stored a simple DATE format. If you want to perform calculations with dates in your ZP SQL reports or documents, you sometimes will need to convert the long DATETIME format to a simpler DATE format first. This is done with the function TTOD( ) - which stands for "Timestamp to Date." So if I want to add 30 days to the Violation Date my report expression would be: TTOD(vio_date) + 30.

New Conversion Document for ZP 32 Customers

I just posted a new document on the Tutorials page of our web site called "Converting From ZP 32 to ZP SQL." This document is designed to help explain some of the issues existing ZP 32 customers should be aware of when switching to ZP SQL.

Locking Inspection Notes

A feature that carried over from ZonePro 32 to ZP SQL is the option to Lock Notes in the Global Personalization section. This option locks the second Notes field in the selected database screens. The idea being that inspectors could move their notes to the second Notes field without worrying that they could be altered by someone else. The problem with this feature in ZP SQL is that the Building databases no longer store inspection notes in the main Notes fields. Each inspection detail entry has its own Notes field. So now I have extended the Lock Notes option for the Building databases. It now locks the Notes field in the Building Inspection Details screen, but only if the current user does not match up with the initials of the Inspector field for that detail. The original inspector can continue to make changes to that Notes field. The Result field and Inspector field are also locked, but only if the fields already have a valued entered in them, and again, only if the current user is not the original inspector.

Contractor Module Adds Fee Database

In a major redesign of the Contractor Module we have now added a separate database for tracking fees. This allows you to have multiple fees attached to a single contractor record. In prior versions only one fee could be stored in the contractor record and we had a Reset button to copy old fee information into the Notes field before new fee information was added. The flaw with that design is that there is no good way to report on the old fee information. Now you will always be able to report on both the current active fee and any past fees collected from contractors. Existing ZP SQL customers will automatically have the new database added and their existing contractor fees moved. In addition to that major change we have also added a third Phone field to the contractor database as well  as a Notes2 field. We have also expanded the size of the Company field. You will need to download the new program files to take advantage of the new features. A training video has been posted detailing the new fee option.

Photo Module Supports Adds from Main Screen

A new version of the Photo Module is available for download that allows users to add new entries from the Photo Screen off of the Main Screen. Previously you could only view photos from this screen and had to go to the Photo Screen off of the Property Screen to add anything. You can also now use the Batch Loader from both Photo Screens. Users who converted from ZP 32 will recognize that this is more like the way the Photo Module works in that version.

Bracket Change for Custom Alert Messages

You can create your own custom message to insert in the Alert Message Option in ZonePro. In prior versions this was done by putting your message in the Notes field and book-ending your message with the tags: and . Because the angled brackets are associated with HTML web coding I decided it best to switch to curly brackets instead for ZonePro SQL. The feature still works the same as always except now the framing tags should be: {BeginAlert} and {EndAlert}. If you need any assistance finding and converting your existing custom messages please let me know.

Better Navigating & Sorting on Property Screen

Working on the Staff web site I figured out a better way to navigate from record to record in the Property Screen. I just applied the new technique to the Windows interface. After installing the latest Property update you'll see a new set of Navigation buttons. These will be the familiar 4 green arrows to those of you who came from ZP 32. Unlike before, the Navigation buttons are now always available, whether you have used the Locate Screen or not. Also you now have a First and Last button to jump to the beginning or end of the current sort. I have also added a Sort button, which again will be familiar to former ZP 32 users. This opens a screen that lets you change the way the Navigation buttons sort to determine the next or previous records. These changes should make the navigation options more smooth and useful.

SQL Browser Tool Added

Although the Database Browser tool was carried over from ZP 32 to ZP SQL, it is of limited use in the ZP SQL environment because it cannot browse SQL databases. Now we have added a SQL Browser tool that gives you a way to directly view the contents of any of the databases in your SQL account. This tool can be very useful for troubleshooting purposes. Like the Database Browser, it also includes an option for printing the file layouts of databases. This is a great resource for learning the field names you need when customizing documents and reports in ZonePro SQL. One of the major differences between the SQL Browser and the Database Browser tool is that the SQL Browser does not allow any editing of the data. This is a limitation that we just have to live with because the data is internet-based. Still this new tool will be a welcome addition to the Toolbox Module arsenal.

Advanced Filter Issues

Just made a minor change to the Advanced Filter screen which is accessed from the report screens. It no longer adds periods to the AND and OR statements when it adds them into a filter expression. That syntax works in ZP 32 but not ZP SQL. We're finding that you also have to be more careful with OR statements in ZP SQL Advanced Filter expressions. If you create a filter expression that includes an OR statement it is usually best to enclose your entire filter expression in parenthesis. One last note, if you are converting Advanced Filters from ZP 32 to ZP SQL keep in mind that the same field name changes that apply to documents also apply to filters.

Changing Owner Names Video

As I expected, some former ZP 32 users are having trouble adjusting to the Contact File database in ZP SQL. In ZP SQL names are no longer stored in the property database. Rather, all names are stored in the Contact File database. This means the process of changing the owner name for an existing property is different in ZP SQL. I just posted a new Training Video on our web site to walk through how it is done. We've had a couple of cases where customer errantly tried to edit the existing Contact File name rather than add a new one. To help curb this  I also added an alert message to the Contact File screen if you attempt to edit the Last Name of a contact record there. In general you would never change that field unless the person (or company) legally changes their name. That doesn't happen too often.

Checking For Blank Dates

In report expressions, advanced filters, etc. customers often want to check for blank dates an take an action. Only do this if the date is filled, for example. In ZP 32 part of the expression to check dates might look like this:

NOT EMPTY(insp_date)

That expression does not have the same results in ZP SQL because the databases handle dates differently. In SQL there never is a "blank" or empty date field. Rather it is set to "null" to show that it does not have a specific value yet. You can still get the same results in your expressions by  adding an extra function to convert nulls to blanks. The new expression would look like this:

NOT EMPTY(NVL(insp_date,""))

There are several other ways to do this as well but this approach works well when converting from ZP 32.

Special Pick Box Feature Aids in Variable Fees

This past week I converted the City of Fostoria from ZP 32 to ZP SQL. As it turns out they are the first case I have converted that had used the variable fees option for Zoning permits in ZP 32. That feature was dropped in ZP SQL because I reasoned that formula scripts could handle those fees just as well, and as far as the fee calculation that is true. What I didn't account for, however, was the way the old variable fee feature let you pick fee choices from a list of options. I wanted that same ease of use in ZP SQL. The solution I came up with is to add a Special Pick Box feature to ZP SQL. This is a Pick Box screen that can be called from any script. You simply add a line of code to the script that calls the Pick Box and passed it the list to use as choices. It returns the selected item which you can then copy to any available field. I have added a new tutorial to the ZP SQL Documents section of the Tutorial page in our web site that shows how I used this feature in Fostoria's case. The newest version of the ZP SQL demo also uses this option for the "Sewer" Zoning permit type.

Document Section on Tutorials Page

A new "ZP SQL Document" section has been added to the Tutorials page on our web site. The initial posting include several documents that were already in existence. One of the documents goes over the field differences between ZP 32 and ZP SQL to help converting customers move over documents and reports from the old system to the new one. Two of the documents are overviews written for the beta testers of the Contractor Request option and the on-line photo storage option. More will follow, of course.

Sliding Expire Date Tutorial

I just added a second "Sliding Expiration Date" tutorial to our web site (on the Tutorial page, of course). Although the process is exactly the same in both ZP 32 and ZP SQL, the scripts are different. This will true anytime you have a script that pulls data from some database other than the currently active one. For the main Sliding Expiration Date script data is pulled from the inspection database to compare the latest inspection date with the Expire Date. As a side note, I'm mulling over several options that would build the Sliding Expiration Date option into ZP SQL so that you wouldn't need scripts at all. We'll see...

Web Based Help

This really is a no-brainer and yet it took me a while to think of it. ZonePro SQL has moved all of its help files to the internet. When you click on the little yellow ? button in any ZP SQL screen, your default web browser is launched to display the relevant Help topic. This is an obvious way to go since you need internet access to use ZP SQL anyway. Have a central repository for the Help files means fewer files that customers need to manage. It also makes it easier to update the Help files incrementally rather than waiting for a new release.

Report Screen Speed Features

As I have mentioned (over and over) speed was a major concern when designing ZP SQL. All of the report screens were modified to add two new features that limit the amount of data requested from the internet. The first is a simple field that limits the maximum number of rows pulled down for a report. By default reports are limited to 100 records. You can edit this value to grab more records. Entering a 0 (zero) in the box effectively turns the feature off. The second new feature is a check box which limits the number of fields that are pulled by each report screen. Because report screens typically include data from both the property database and an application database, there are potentially many fields that could be pulled for every query. Most of the reports, however, include only a fraction of the fields available. So we devised a system whereby a text file holds a list of all the fields each report screen should pull. These lists are shipped with ZP SQL and are initially limited to only those fields included in the default reports. There is a screen in Maintenance where you can edit these lists of fields and add more fields as needed. This system works great but as we soon discovered, speed is not nearly the limiting factor we thought it might be. So now there is an option to turn off field limiting feature in the Personalization Options screen.

Super-Sized ID Codes

Okay, this is not the most exciting change in the world, but another difference between ZP SQL and ZP 32 is that the property ID and all of the ID Codes in the system have been expanded to 8 characters. This does not change the way the program works but it does make it easier to identify where a record came from just by the ID. In ZP 32 all of theID are prefaced by a single letter. In ZP SQL each ID is prefaced by two letters. The Contact File gives a good idea of why this is improvement. In ZP 32, all Contact File records are prefaced with an "X." Why? Because "C" was already taken by the Contractor database. In ZP SQL, the Contact File records are all prefaced with "CF" and the Contractor records are prefaced with "CT." That, to me at least, makes sense.

Contractor Details Database

Another difference between ZP 32 and ZP SQL can be found in the Building Module. In ZP 32 each building screen has spaces for five contractors per permit. In ZP SLQ the contractor info is no longer stored with the permit screen but instead is tracked through its own Contractor Details Screen. Just by the name you should figure that the Contractor Details Screen works similarly to the Fee Details and Inspections Details sections of the Building Module, and you'd be right. With the contractor info stored in its own screen you are no longer limited to five contractors per permit. You can have as many as you like.

Property Deletion Follow-Up

As a follow-up to the deletion post of a few days ago I wanted to mention that there is also another change to the way deletions work from the Property Screen. Unlike in ZP 32, in ZP SQL you cannot delete a property record if it has any other records linked to it. Any other words, you must delete any attached permits, violations, photos, etc., before you can delete a property record. You must also first delete any names attached to a property in the Contact File before you can delete the property. This is basically a safety measure. Typically when ever we've had customers contact us about missing permits, the culprit turns out to be a property record that was accidentally deleted. Because deletions are easier to execute in ZP SQL, we decided to make it harder to delete a property record if it has anything attached.

Notes For Building Inspections

In ZP 32 the Building Inspection Details Screen displays a Note field, but is actually just borrowing the Notes field from the main permit. In ZP SQL each Inspection Detail has its own Note field. When you print the Inspection Details or the Building Ticket any notes associated with a particular Inspection Detail print directly under that detail item. This new approach has been very well received by the customers using ZP SQL. In fact they'd like to see this feature extended to the detail screens for Housing and Fire. We'll see...