Govern 10.8 to OpenForms 6.0 Upgrade

Upgrade from Govern 10.8 to OpenForms 6.0

Upgrading to OpenForms version 6.0 or greater

Overview

As Govern has undergone a major revamp it is a good time for business re-engineering. Prior to upgrading to 6.1, a review of customer processes and requirements is recommended.
More than likely needs have changed, not to mention technology, hardware and software that evolved.

NOT A REGULAR UPDATE *** Structure changes and new features that can bring significant benefits to users if reviewed and analysed beforehand.

See also Database Standards (100-Standards) | System Requirements (100-SysReq)

Configuration Changes

DATABASE Cleanup

To improve management, the reference database information is now recorded in a XML file and created automatically when GNA is first run. Indexes have been standardized (see standards).

Database column types, lengths and/or nullable properties, etc. when not in compliance with the ReferenceXML need to be cleaned-up.
Database Indexes should be deleted and recreated at each version upgrade. Please note that CUSTOM indexes will need to be recreated manually after a delete ALL!

NOTES:

Before starting the process, we highly recommend to review the CUSTOMIZED indexes to make sure they are still needed and that they do not create performance issues.
To make it possible to change field types in design mode when there is data in the column, you need to turn OFF the PREVENT DESIGN CHANGE option in MS SQL SERVER and make sure to turn it back ON afterwards!!!

CLEAN UP TIPS:

Delete the indexes first (ALL if you have reviewed custom indexes and can recreate them) than rerun the verify database. Invalid indexes can cause multiple errors.
Unused tables (ex: Boat, Aircraft, MV…) or empty tables (no records) should be deleted (quicker) as they will be created correctly at the next initialize.
Before making changes to the column types via the design mode, you need to turn off the option that ‘Prevents design changes in MSSQL SERVER‘. Remember to turn in back ON afterwards.

DOCUMENTATION

Detailed information on creating the windows environment, the upgrade process and examples of data corrections made to to an upgraded database.

700-upgrade108_to_60.pdf.
[700-upgrade108_to_60 documentation]

GOVERN STRUCTURE

The basic structure of Govern has changed. The profile is now the cornerstone of the setup and the security is setup directly in the OpenForms Designer.
The same table is used for USERS and Groups can be imported into ROLES in GNA. Note: Create the desired Structure on paper first to see if doing so will be effective.

FORMS
All forms need to be created using the new OpenForms DESIGNER.

PROFILES

Profile links need to be created for Batch Programs, Reports, Forms, Accounts Receivable, etc.

REPORTS
Report defined in the database (configured) are verified to make sure the SSRS or Crystal report exists in the standard or the custom folders defined.
Note: The user will be asked to delete invalid definitions the first time. Make sure report folders are setup correctly.

Step by Step

Update 10.8 to v6.0+ and create a deployment …

Restore Database to MS SQL Server
Create Deployment/Connection Key
Download & Install Binaries
Install GNA
GNA Utilities – Verify Database
Correct Errors — see MS SQL SERVER Option to allow
GNA Utilities – Verify Database
Review Users
Review Departments
GNA Import/Export – Import ….
GNA Import/Export – Import Forms
GNA Verify database (Always after an import)
GNA System Registry for Reports
GNA Create Profile (Forms, Reports, Batch Process (testbatch) ….
GSM Synchronize
GSM Import Departments (If desired)
GSM Review Roles
GSM Review Security (Applications, Forms, etc.)
Open Govern and review the options
Search, send to the treeview and open a form

Model Updater

IMPORTANT NOTE:
Model Updater has not been re-engineered for version 6.0. It is on the roadmap.

Troubleshooting

At any point, if there are problems, Govern VALIDATOR should be executed,

100-upgrade-GFWtoOF

(0 votes, average: 0.00 out of 5)
You need to be a registered member to rate this.

100-upgrade-OFto61

Upgrade to Govern 6.1

Overview

This page is intended for customers, business analysts and technical services to provide tools and tips when upgrading to OpenForms 6.1 from a prior OpenForms version or from Govern for Windows. Version 6.1 is the first OpenForms version that is no longer backward compatible with Govern for Windows 10.8. It is an important upgrade as there are structure changes and core functionality changes so it is important to be well prepared.

NOTE: Everyone’s COLLABORATION is needed to improve, optimize the process of Upgrading to 6.1 (major milestone release). HOW? By sharing knowledge, experience or tips and tricks that can be of interest to the next person that will perform an upgrade. This can be sent via email (object: UPGRADE …), or by sending a note to Harris Govern R&D…
IF or WHEN?
– R&D … You think that a software change may impact the upgrade process
– PS … You experienced problems or singularities during an upgrade that could be of interest to others …
Thank you in advance for your cooperation.

Before Getting Started

Read More...Read Less...

It is highly recommended that a complete backup of the database prior to copying or upgrading be performed.
Because of the structure changes and core functionality changes, it will be important to review and assess the work that will be needed.

Things to consider

Formulas and Logical Expressions …. are replaced with Expressions. Formulas will be converted, but not all of them may work. Logical Expressions will need to be rewritten.
Queries will need to be reviewed to make sure they still work or to implement the new features (setup by jurisdiction, year, etc.)
Conversions will need to be performed that will impact reporting and queries
Reports will need to be reviewed
Vt and System tables used in reports will need to be flagged so that a view will be created for reports
New Table structure BUSINESS RULES

Database Changes needed prior to upgrade
A new Business Rule for Table Naming is enforced and Invalid table names should be fixed prior to the upgrade.

Table name must start with a LETTER
Table name cannot contain non ALPHA-NUMERIC characters except for the underscore ‘_’ character
- See a Script Example that checks User Validation Tables (where the problem is most likely to appear) for INVALID characters.
- Tip: With the select tool set to on, you can copy text from PDF documents that can be pasted in a SQL Query Window

Tables and Data Manipulation

A copy of all MA PARM tables will be created automatically when executing the Database Verification. Ex ma_parm_income copied to ma_parm_income_backup

Upgrading

Deployment

Download and deploy 6.1 Binaries
Note: Client installation of Crystal Report for .Net is not mandatory if already installed

Database Verification

The first time you open Govern Administrator (GNA), a message will prompt the user that the version of the database is not the same and that a database verification is needed.
Please note that it will need to be run twice because of changes that cannot be processed in one step only. This error message will be displayed the first time: The VT_USER records cannot be verified because the SIMULATION column is still in the VT_USER table. Click START to rerun the process and remove the error.

System and User Tables

Before release 6.1, system and user validation tables were each a distinct table in the database, recorded under VT_USER_xxxx or VT_SY….
These tables have been merged into a single table for USER and SYSTEM.

User Added Fields
Upgrading from Govern for Windows … need to validate if any tools
Upgrading from Govern OF 4.7.5, 5.1, 5.2 …. ‘need to be handled manually‘

Conversion (GNA)
A new menu item was added in GNA to convert or merge tables

Batch Programs need to be recreated
Batch Programs standard (verify if it can be added) and custom need to be recreated.

Troubleshooting

PC PARCEL

2 fields have been changed to nullable in this table in 6.1. If you get errors during the Verify Database on VIRTUAL_PARCEL_FLAG or NON_PARCEL_FLAG, it might be that a non-supported customer index exists on this table. If this is the case, than you need to manually drop this index prior to running the Verify Database

Unverified Notes

When you come upon information that might be of interest, create a note here. It does not have to be pretty or formatted …

100-upgrade-OFto61

(0 votes, average: 0.00 out of 5)
You need to be a registered member to rate this.

100-Installation-Hybrid108

Govern 6.0 HYBRID Installation (Govern for Windows)

[The content on this page is under review]

Overview
Quick Reference guide for the installation of Govern for Windows 10.8 Components.
Download requirements and installation steps are covered here.

Requirements

When installing from scratch, the 10.8 REFERENCE DATABASE & BINARIES must be used as a starting point.
Download FULL SERVICE PACK 10.8 and password file.

MS ACCESS is REQUIRED suggested as Govern for Windows uses Microsoft Attach Tables Methodology for improved client/server performance.

Installation

Read More...Read Less...

100-(ui)-updata-[v10.8].png

STEP 1 – System Installation packs

Unzip pack and perform SETUP – to be verified with Richard Support

STEP 2 – Create ODBC Drivers Client connections

For each OpenForms Govern Environment (Production, Staging, Training, etc.)

STEP 3 – Setup Organization Licenses INI files

Create a .INI file that will be configured with an Organization Name and Serial Number.
Below is a sample .ini entry…

‘—————————– START —————————–
[General]
organization name=MyCompany
serial number=123456-78910111213-141516
primary activation key=
secondary activation key=

SQL=sqlserver
GLOBAL_USR_ADM=
GLOBAL_PASSWORD_ADM=XXXXXXX
USE_ACTIVE_DIRECTORY=no

PASSWORD_DOUBLE_CHECK=no
password=XXXXXXXXX
password suffix=
userid prefix=
User=CITY
Login Web Service URL=

[Database]
DSN=GOV_PSTRAIN
DB=GOV_PSTRAIN
Local Name=
DatabaseName=MYSERVERGovernDatabaseGOV_PSTRAIN_MYSERVER_SQL.mdb

[Upgrade]
Grant Name List=upgduser
‘—————————– STOP —————————–

STEP 4 – Run UPDATA – Database UPGRADE

Run the Govern for Windows Updata.exe application.

STEP 5 – Run UPDATA – Create … ATTACH TABLES

If you need more info on installing Govern for Windows, click here for the Getting Started in 10.8 manual.

STEP 6 – OpenForms Installation

OpenForms Initial Setup needs to be performed after installing Govern for Windows. After the Govern for Windows installation, OpenForms Database Verification needs to be performed.

STEP 7- OpenForms Update

Database Verification in GNA
Synchronize in GSM

Advanced SQL Queries

The prior sections contained general rules that are to be followed when creating SQL queries. The following are specific examples of issues with advanced queries, and how they may be resolved.

A. Double Brackets Workaround

Database entries in Govern can have three (3) states, TRUE, FALSE, and NULL. The NULL is placed in a field as a placeholder when no data is entered. TRUE is equal to a value of -1, FALSE is equal to zero (0), and the NULL should equate with zero (0).

A NULL value is treated differently from other values due to its special nature. NULL and 0 cannot be compared because they are not equivalent.
The following example demonstrates how the NULL can be handled when it is encountered in the database.

In the following query…

Read More...Read Less...

Query Fragment

SELECT PC_PARCEL.P_ID, PC_PARCEL.TAX_MAP, PC_ADDRESS.FORMATED_ADDRESS, PC_PARCEL.EFFECTIVE_YEAR, PC_PARCEL.INACTIVE_YEAR

FROM PC_PARCEL left outer join PC_ADDRESS on PC_PARCEL.P_ID=PC_ADDRESS.P_ID AND PC_ADDRESS.LOC_SEQ=0

WHERE (PC_ADDRESS.CIVIC IS NULL OR (PC_ADDRESS.CIVIC>=@pcfhouseno_0 AND PC_ADDRESS.CIVIC<=@pcthouseno_1))
AND (ISNULL(PC_ADDRESS.STREET,”) = ISNULL(@pcstreetname_2PC_ADDRESS.STREET,”))
AND (ISNULL(PC_ADDRESS.SUFFIX,”) = ISNULL(@pcstreettype_0PC_ADDRESS.SUFFIX,”))

In the above query, the SQL ISNULL() function is used, so that when searching specified columns in the table, should a NULL character be encountered, the ISNULL() function will equate it with zero (0). In the above example a modification in the form of double square brackets “[[ ]]”, have been added to the query. This modification has been made for the DAB. When When the DAB encounters double-brackets it will immediately use the data type of the TABLE.COLUMN combination that are within the brackets to declare the leftmost variable that is outside of the brackets, in our case the ISNULL.

B. NULL Characters Alternate Method

As stated in the previous example, the database entry can have three (3) states, TRUE, FALSE, or NULL. A check box in a user interface can have two (2) states, TRUE (-1), or FALSE (0). We would need to code our query to equate a NULL with a zero (0). The following example demonstrates an alternate method of handling the NULL character.

Query Fragment

AND(PC_PARCEL.NON_PARCEL_FLAG=@pcnonparcel_0 OR ( 0=@pcnonparcel_0 AND PC_PARCEL.NON_PARCEL_FLAG IS NULL))

The above statement is written to handle the NULL by equating NON_PARCEL_FLAG with NULL.

The above example statement can also be recreated with the double brackets method. The statement would be as follows:

AND (PC_PARCEL.NON_PARCEL_FLAG=@pcnonparcel_0PC_PARCEL.NON_PARCEL_FLAG,”)

C. Linking Interface Selections

We would like to present the user with a search option that will include inactive parcels. Unfortunately in the database there is no field in the database that will tell you that a parcel is inactive. To be able to link something that does not exist in the database to something that does, and get a TRUE or FALSE value that we can then manipulate.

The PC Inactive Parcel field does not exist in the database but we want the user to use the check box, it will have to be linked to one that is of the same type. i.e. integer, float, text, small int, etc.

Create a dummy condition

This is a condition that will always return a value of TRUE, this also declares a variable based upon a column.

AND (PC_PARCEL.NON_PARCEL_FLAG=@pcinactiveparce_0 OR 1=1)

To declare the pcInactiveParcel object, we will link it to a field that is of the same type as that required by the checkbox, i.e PC_NON_PARCEL_FLAG. The DAB will now be able declare pcInactiveParcel as valid.

The conditions are now set for the two states of the checkbox option…

AND (( 0=@pcinactiveparce_0 AND PC_PARCEL.EFFECTIVE_YEAR<= year id AND

PC_PARCEL.INACTIVE_YEAR > year id )

OR (-1 = @pcinactiveparce_0 ))

If the Include Inactive Parcel option is not checked, i.e. FALSE (0), or Include Inactive Parcel is checked, TRUE(-1).

Following the above steps, we are able to declare a variable based upon a column. Based upon the fact that the variable does not exist, the Data Access Block (DAB) is being directed not to look directly at the column, but rather it is being told what to expect.

Best Practices for SQL Queries

Specify Table and Field Names

When creating SQL queries, it is good practice to specify both the table and the field names in your WHERE query statement.

EXAMPLE 1

The Original query below would function in Govern for Windows, but would result in an error in Govern.NET. In order for it to function
in Govern.NET it is necessary to include the table name in the WHERE statement. This was done in the Corrected example by using a “.” as a
separator.

Read More...Read Less...

Original

select count(*)
from pm_lk_permit_name,na_company_info
where key_id = permit id
and pm_lk_permit_name.na_id = na_company_info.na_id
and link_type = ‘contr’
and cont_type = ‘SI’
and (in_exp_date < current date or in_exp_date is null)

Corrected

select count(*)
from pm_lk_permit_name,na_company_info
where pm_lk_permit_name.key_id = permit id
and pm_lk_permit_name.na_id = na_company_info.na_id
and pm_lk_permit_name.link_type = ‘contr’
and na_company_info .cont_type = ‘SI’
and (na_company_info.in_exp_date < current date or
na_company_info.in_exp_date is null)

Specifying the table name in the WHERE statement is a good practice to follow when building SQL queries.

EXAMPLE 2

Another example of a query is the following. This is a SELECT query for PRIMARY_LAND

Original
SELECT DISTINCT
I.PRIMARY_LAND
FROM
PC_LEGAL_INFO I LEFT JOIN
PC_AREA A ON A.P_ID = I.P_ID
WHERE
I.P_ID = Parcel ID AND
I.YEAR_ID = Year ID AND
A.FROZEN_ID = Frozen IDCorrected
SELECT PC_LEGAL_INFO.PRIMARY_LAND FROM PC_LEGAL_INFO WHERE
PC_LEGAL_INFO.P_ID = Parcel ID AND PC_LEGAL_INFO.YEAR_ID =
Year ID AND PC_LEGAL_INFO.FROZEN_ID = Frozen ID

Results in the Treeview

Controlling the appearance of Results in the Treeview

One feature of the Govern.NET application is the users ability to control the appearance of results in the Treeview. This feature will appeal to users that perform queries that require that they display a Tax Map as opposed to a Parcel.

FOR EXAMPLE:

In the following query, the Tax Map Number (‘TAX_MAP’) is being replaced with the Roll Number (‘ROLL NO’).

SAMPLE LISTING:

Name: ParcelDesc001
SELECT
CASE
WHEN A.NON_PARCEL_FLAG = -1 AND NON_PARCEL_ID > 0 THEN ‘PREMISE ID: ‘ ||
CAST(A.NON_PARCEL_ID AS VARCHAR(30)) || ‘ ‘ || B.CURRENT_TENANT
WHEN A.NON_PARCEL_FLAG = -1 AND NON_PARCEL_ID IS NULL THEN ‘P ID: ‘ || CAST(A.P_ID
AS VARCHAR(30))
ELSE ‘ROLL NO: ‘ || A.TAX_MAP
END
FROM PC_PARCEL A LEFT JOIN V_PC_OCCUPANT_CURRENT_TENANT B ON A.P_ID = B.P_ID
WHERE A.P_ID = 261362 AND ROWNUM = 1

When the above sample code is incorporated in the query, results with Tax Map numbers will be displayed with the Roll Number.

Standard Batch Process – Report Scheduler

General

The Report Scheduler is used to schedule a report that is not associated with a batch process but needs to be run on a regular basis. You may, for example, want to run a report every night that does not require any data from a batch process.

Process description: Report Scheduler
Process name: co_NoProcess

Specification

Prerequisites

Batch Process Definition is created and linked to profile
SSRS or Crystal Report is configured
The same report can be used in multiple batch process definitions

Configuration

Create a batch definition process and select report to be executed

- When you add the report to the batch process definition, you need to define or update the Export Format Type
- PDF, CSV, RPT, etc.
Add newly created batch process definition to profile

Tools and Tips

Before getting started

Take down the report code as it will need to be specified (report selection window does not show description)

Troubleshooting

Execute the report stand-alone before setting it up with a batch process definition

100-bp-ScheduleReport

(0 votes, average: 0.00 out of 5)
You need to be a registered member to rate this.

100-bp-TestBatch

Standard Batch Process – Test Batch

General

The Test Batch is used to test the batch execution and configuration, the batch scheduler service, etc.
It creates an Audit Batch ID, generate errors every 1000 records, etc. and records the information in the Audit Batch Table.

Process Description: Test Batch Process
Process Name: co_TestProcess

Specification

Prerequisites

Batch Process Definition is created with the process name
Batch Process Definition is linked to a profile

Features

Creates error messages for email notification
Creates attachment

Tools and Tips

Try first in Govern

First use the Test Batch Process in Govern as additional information for errors is provided in the process window (stack trace)

Schedule periodically

To validate the service is running

Schedule at specific times

After system maintenance, to validate service is up and running
After system upgrades, installation, change in reporting or notifications, etc.

Troubleshooting

Any information related to troubleshooting will be presented in this area. Currently there are no troubleshooting entries.

100-bp-TestBatch

(0 votes, average: 0.00 out of 5)
You need to be a registered member to rate this.

Upgrade from Govern 10.8 to OpenForms 6.0

Overview

Configuration Changes

DATABASE Cleanup

GOVERN STRUCTURE

PROFILES

Step by Step

Model Updater

Troubleshooting

Upgrade to Govern 6.1

Overview

Before Getting Started

Upgrading

Deployment

Database Verification

System and User Tables

Troubleshooting

PC PARCEL

Unverified Notes

Govern 6.0 HYBRID Installation (Govern for Windows)

Requirements

Installation

STEP 1 – System Installation packs

STEP 2 – Create ODBC Drivers Client connections

STEP 3 – Setup Organization Licenses INI files

STEP 4 – Run UPDATA – Database UPGRADE

STEP 5 – Run UPDATA – Create … ATTACH TABLES

STEP 6 – OpenForms Installation

STEP 7- OpenForms Update

Related Links

Advanced SQL Queries

A. Double Brackets Workaround

Query Fragment

B. NULL Characters Alternate Method

Query Fragment

C. Linking Interface Selections

Create a dummy condition

Related Topics

Best Practices for SQL Queries

Specify Table and Field Names

EXAMPLE 1

EXAMPLE 2

Results in the Treeview

Controlling the appearance of Results in the Treeview

FOR EXAMPLE:

SAMPLE LISTING:

Related Topics

Standard Batch Process – Report Scheduler

General

Specification

Configuration

Tools and Tips

Troubleshooting

Standard Batch Process – Test Batch

General

Specification

Prerequisites

Features

Tools and Tips

Troubleshooting