Between the Elephant’s Toes

Earlier, I examined how the Adapx CapturX Forms for Excel allowed you to create an Excel form, digitally enable it and import hand written information back into Excel. This solution addresses one of the key problems in workflow automation – getting field completed forms back into an electronic format.  But that is only half of the problem.

In every organization I work with – large and small – data is routinely maintained in Excel. Loading this information into a backend database is a problem that is generally ignored resulting in silos of information.  Our RADE technology seemed to provide the perfect platform to support a generic Excel to database interface.   We set out to solve this problem – and the result is Landor’s newest product FullCircle. 

Although I was involved in the product requirements phase, I was pleasantly surprised with the simplicity of the initial release.

FullCircle is a web based interface that supports three primary functions:

1. 1.  Query information directly from a database table and export the results to Excel with database registration information. The database registration information is critical because it tracks the database, table and fields associated with the downloaded data.
2. 2.  Create and register Excel Forms which can be downloaded by authorized users. This is critical for large organizations since users can download a form on demand and do not have to manually track hundreds of forms.
3. 3. Upload completed Excel files and either update existing records or insert new records

So how does FullCircle perform?

FullCircle Installation

Unlike earlier versions of RADE which required administrators to run the database scripts and setup the virtual directories, FullCircle comes with both a manual scripts and an installer which automates the process. The only manual requirement is to create the database schema in advance on Oracle servers.

Installation procedures are provided for Oracle and SQL Server. The application runs with both Oracle Express and SQL Server Express so there is no extra cost for the database in a small organization which does not already own a commercial database.

FullCircle End User Interface

The FullCircle end user interface is extremely simple. FullCircle assumes three types of users:

1. 1. Administrators configure the application through a web based interface
2. 2. Download users can download data from the database either through a query or by downloading a form
3. 3. Full Control Users have the authority to both download and upload validated Excel files.

Let’s take a quick walk through the FullCircle user interface.

Login Screen

The login screen is typical of most web applications. Administrators create users, provide passwords and assign a user to a user group (download users and full control users).

The group assignment determines which application is loaded after a user logs in.

Application Screen

The application screen gives full control users 4 buttons:

• · Download a form,
• · Upload an Excel File
• · Run a Query and
• · Logout

Note that users with “download only” access will not see the upload button.

The Form Repository

Selecting this button provides a list of forms that have been defined and added to FullCircle.  Users simply select a form which exposes a download button that automatically downloads the form to Excel.  If the form is Capturx enabled, the form is then printed and the Capturx workflow begins.  Otherwise, users can simply fill in the form data directly in Excel.

The RoundTrip Query

The RoundTrip query solves the problem of giving easy users access to data in an environment they’re used to – Microsoft Excel.  In addition to being able to view the data in Excel, they can also edit and add to that data – and still post that back to the appropriate source database in a controlled fashion.

Selecting the RoundTrip Excel Query Button displays a list of queries pre-defined by the administrator.  Queries are created using the web based RADE administrative tools.  From this interface the users can select the query they’re interested in.

Queries can be defined one of two ways by the administrators.  First a strait select statement can be issued.  In this case, the user selects the query and they are immediately taken to the results. 

The second possibility is that the administrator has defined a series of fields as ‘prompts’.  In this situation the user is presented the following dialog.  This dialog is uniquely generated depending on the fields the administrator has selected when building the query.    This dialog will allow the user to enter – or select values as needed.  The following example has five fields, all of which have been associated with lookup tables in the database.  These fields have been “limited” to selecting existing values only.  Other scenarios allow for free text entry, date selections, or lookup values but no limited to the values in the database.

After selecting the conditions, and pressing OK, the results are returned in a tabular report as shown below.  The reports are also created by the administrator – by selecting the fields to include from the appropriate source table.

The FullCircle Export control at the top of the form downloads the selected information into Excel creating two tabs.

The data tab contains the results of the query. A second tab – RADEFullCircleConfig contains some database information as well as the columns that are associated with each field.  This information is used by FullCircle to determine how the data is posted to the database when it is uploaded.

Uploading Data into the Database

I made some simple changes to the Excel data, saved the file and uploaded it through FullCircle.  As mentioned previously, it is possible to control access to the upload component.  This allows your organization to control who can add or update data in the source database.

The process was simple and the results of the upload process were clearly displayed.

Summary

I was very impressed with the first looks at FullCircle. The application did exactly what it was suppose to do. It provided a seamless interface between Excel and the backend database. Users can query either forms or traditional Excel spreadsheets, edit the data in Excel and post the updated information back to the database.  Data is inserted or updated in the appropriate source database table.  No need for temporary intermediate tables or processing the data after the fact.

Now I want to go a bit further.

• · How can I interface this with GIS information to allow field staff to update attribute information in the field?
• · Can I enable a grid style form to be populated in the field using the Adapx digital pen technology?

I received a client request for a couple of new buttons for RADE.

• 1. To add a button that would just run a query without bring up the query dialog
• 2. The other was to allow the user to zoom to a lat/lon coordinate

No problem, I contacted Landor and asked that these two buttons be created for RADE 3.5.

Big problem – I was told that we had agreed that there would not be any more development in RADE 3.5 as that would only delay the release of RADE 4. Some more words were exchanged about impacts, schedules and about sales reps which I won’t elaborate on.

I was also reminded that RADE offered users the ability to develop their own buttons. So I asked for a template on how to develop a custom button to run a user defined query or chart.

Custom Button to Run a RADE Query

The code template was pretty simple – I guess I should not have been whining after all. The basic format is:

   1: var gszTgtQueryBuilder = fnCreateUniqueTarget("QueryBuilder");

   2: function pQuery2003()

   3: {

   4:     var szQueryUrl="querybuilder/queryselect.aspx?A=2003";

   5:     goQueryBuilder = window.open(szQueryUrl, gszTgtQueryBuilder, 'menubar=yes, resizable=yes, scrollbars=yes status=no, toolbar=no, width=300, height=300');

   6: }

Bottom line on the above is this:

• a) Function pQuery2003() – is the new function we are creating that runs the RADE query with a query_id of 2003.
• b) var szQueryUrl="querybuilder/queryselect.aspx?A=2003"; – the A=2003 is a specific parser to indicate that query_id 2003 is to be run

RADE does not expose the query_id to users so I had to take a quick look at the WR_QUERY_NAME table in Toad to find the query_id for the query that I wanted to run.

So, given the above, I accessed the buttons dialog from the RADE administrator and filled in the dialog as shown in the image below:

The above button will run the Places Query from a map based application. The toughest part of the whole exercise was selecting the images I wanted to use with this query.

Configuring a Custom Dashboard

It occurs to me now that I can layout a complete application that simply places the queries that I want in a series of rows on a dashboard – interesting concept.

You can incorporate charts in the same way using the chart id and replacing “Query” in all the above with “Chart”.

Last week I had my first look at Capturx for Autodesk Design Review and was favorably impressed. Capturx Forms for Excel was released on November 10^th and I eagerly awaiting its arrival.

For the past seven years my professional focus has gradually shifted from the integration of CAD and GIS to web based application development tools and enterprise application integration. Workflow automation is critical to any integration project. The best application in the world loses value quickly if the end users do not see immediate benefits.

Capturx Forms for Excel has the potential to address one of the key problems in workflow automation – assisting in moving field collected data transparently to the backend or enterprise database.

Installation

Insert the CD – follow the setup instructions, register the software using they key provided by email and presto I was done…. at least I thought I was until I did the normal Start-Programs-Adapx-Capturx Forms for Microsoft Excel. Where was the program? A user’s manual (sweet), a video file (nice) and some licensing information.

After a few seconds of consternation I realized I only had to start Excel. Sure enough there was the Capturx tab I was expecting.

Getting Started – Creating My First Form

Given my perceived complexity of Capturx Forms for Excel, I actually read the user’s manual. The good news was this was not really required. The interface is absolutely intuitive – open up Excel and start laying out your form.

I added some descriptive text, merged some cells to create larger cells for pen input, used the borders tool to identify the data input fields when printed and pressed the print preview button – OOPS my form ran across a few pages.

Here’s the trick:

1. Set the column width for all columns to a smaller size – I used 0.25”.
2. Do the same for the row height. You basically now have a grid pattern that is 0.25” square.
3. Switch Excel to Page Layout View
4. Layout your form in Page Layout View – to ensure that the entire form stays on a single page.

Once the basic layout was established, the remainder of the Capturx Forms interface was absolutely intuitive. You simply select one of the cells which will be used for data input and apply a format to it.

The Capturx Tab in Excel supports two types of formatting. The standard format shown here permits you to apply a standard Excel type to the cell.

Any cell in “general” format is ignored by Capturx.

Adding a standard format enables the cell as a Capturx cell and the text recognition software will translate the hand written input.

The specialized formatting shown here provides a framework for the text recognition software and improves the overall accuracy of the translation as the software is looking for specific input.

For example State Code automatically applies a state list to the input. Country filters the input based on a list of countries. Email looks for the @ symbol etc.

It was a simple matter to run through each of the fields applying the appropriate formatting parameters.

Pressing the highlight option allowed me to immediately see the fields that were Capturx enabled and the formatting that had been applied.

Printing the form was also a breeze with the Print With Capturx button.

Overall, the interface is completely intuitive.

The Proof is in the Pudding – Would Capturx Really Recognize My Writing?

We had friends from out of state visiting for dinner Sunday afternoon so I printed a number of forms, and enjoyed watching a group of completely non-technical users begin to fill in my survey form.

No instructions were required and all were amazed (myself included) when we plugged the pen into the computer and the forms were automatically uploaded into separate forms.

How did it work? Amazingly well! With the proviso that we had a small sample size of 8 we achieved the following statistics:

• Dates – 100%
• Email addresses – 87% (one email had an extra space – A OL.com instead of AOL.com)
• Street Numbers – 100%
• Free form Text – all needed some editing but overall translation was pretty good.
• State – 100%
• Country – well given that I had labeled this as County and not Country it was amazing how the software interpreted the nearest country from the county that had been entered.

An interesting observation was that Capturx worked much better with hand written script compared to printing.

I don’t think Adapx would ever promise this level accuracy and the users were likely more careful than the average person in the field – but the level of accuracy surprise me.

The Bottom Line on Capturx Forms for Excel

Getting accurate data from the field into the office in a cost effective manner with minimal field staff resistance remains an unsolved problem – Capturx Forms for Excel goes a long way to solving this problem:

1. It is natural – writing on printed forms is a familiar environment
2. It is transparent – attaching the pen to the source computer loads the data automatically
3. Review interface is quick – some interpretation is required but 80% to 90% of the data will already be there and the handwritten data is easily accessible when manual input is required.

If your forms have standard lookup lists, check boxes etc than you can expect close to 100% translation. With free form text you will need manual interpretation.

The Capturx Forms for Excel documentation includes an Appendix which provides an overview of creating customized data input formats. This appears to be one of those gems which will result in even greater data conversion precision – something I will have to look at in more detail.

Suggestion – Perhaps adding a shortcut to Excel on the Capturx Forms for Microsoft Excel tab would eliminate the initial confusion for a new user. As a minimum this step should be added to future Quick Start Instructions or as the last user prompt after installation.

Next Steps – Database Integration

Adapx’s role stops once the data has been returned to Excel.

Darrin and I have been developing and deploying web based solutions that can be configured instead of programmed for selected clients over the past 4 years. The core technology- RADE – supports simultaneous connections to multiple data sources and permits uses to configure reports, queries, charts and web based mapping interfaces. Administrators assign users to user groups and can configure and deploy reporting and mapping applications in a secure environment.

We had developed the requirements and specifications for round tripping data from a database to Excel where users could edit the data in a familiar environment and then post it back to the database.

Capturx Forms for Excel allows us to take this one step further – really full circle – where we can push structured data like a work order from the database, to a form where it can be printed, filled in by field staff, returned to Excel and then posted to the database. We have named this concept FullCircle and are experimenting with the process to allow:

1. Mapping database tables, fields and constraints to Capturx forms

2. Loading Capturx forms into the database where they can be managed and retrieved as an enterprise asset

3. Uploading and populating the source database tables with field captured data

I am looking forward to putting the FullCircle concept through its paces this week with some Oracle based application data.

I began my journey in the computer aided drafting and design (CADD) industry using AutoCAD 1.2 and a Victor 9000. At the time we were trying to automate the drafting process to improve engineering efficiency. The push to move from pen and pager to digital drafting began.

Twenty five years later I have gone full circle with the Adapx Capturx for Autodesk Design Review. I am back to writing on paper but with a twist – a digital pen that tracks paper based writing and markups and automatically associates these with the source document.

So – what led me here and what was my initial experience with this revolutionary technology?

Field Markups Remain a Problem!

Getting information from the field back into the office remains a problem.

I recall a story of a roll of paper drawings, found behind the back seat of a foreman’s pickup truck after he retired, being dumped on a draftsman’s desk. It contained thousands of markups of the power network that had never been updated in the CADD system.

Everything has been tried from notebooks to PDA’s to mobile devices both in a standalone and web enabled environments. They just don’t work in the field!  The sun is too bright, the form factor is too small, the fingers are too fat or field staff are too resistant – yet this problem has to be solved to maximize overall efficiency.

Digital Pen Technology – A Solution?

Kevin Faus – a long time Autodesk associate has recently joined Adapx as their south east sales representative. Kevin gave me an overview of Capturx and I was intrigued enough to order Capturx for Autodesk Design Review (ADR).

ADR is a free yet very powerful product from Autodesk that allows DWF files generated by AutoCAD to be marked up and then returned to designers for incorporation into the source drawing.  Below is a summary of my experience with this revolutionary technology.

Concepts

Without getting too technical, the Adapx pen contains an infrared sensor that tracks each individual movement on a piece of paper. But – the paper must first be printed with a digital “snowflake”. This snowflake is a unique series of light gray dots which uniquely identifies each piece of paper.

If you check out digital pens at Costco for example, you will notice that each pen is sold with a set of notepads. This is critical because each page in these notepads has been pre-printed with a snowflake. The pens will not work with standard paper.

When you purchase a solution from Adapx you receive a license for approximately 6,000 8”x11” pages or 2000 E size plots. When you run out of “snowflake” you must refill your supply via an on line purchase. At about 7 cents a page or 20 cents for an E size plot – this is very reasonable.

Getting Started – You Need a 4 Color Laser Printer/Plotter

I have been suffering with an ink jet printer for as long as I can remember. The net impact of which is I don’t use a lot of paper. After careful research, I was able to purchase an Okidata C6150 laser printer on-line for under $500. Pen or no pen – I can at least justify printing documents now and am getting great photographs printed in seconds.

Printers supported by Adapx can be found on the supported printers page on the Adapx web site.

Installation Process

The package from Adapx was delivered by courier within 3 days. It came with the pen and two boxes – one for Microsoft One Note and one for ADR. All went on the shelf for a week waiting for the printer and a few free hours.

All and all, installation was a snap – provided that you do READ the four page Quick Start guide with one small exception – Step 5 – “Activate your Digital Pen requires you to print a DWF file through CapturX, mark it with the pen and then dock the pen to the PC.”

This assumes that a user knows how to print a DWF file – sounds easy. I personally had not worked with DWF files although I understood the concept.

So here are the unwritten words:

1. With AutoCAD Map your can either plot to a DWF or Publish as DWF from the File Menu

a. If you want to simply plot a DWF, select the DWF6ePlot.pc3 driver as the plotter. This allows you to save the DWF file to a folder on your computer.

b. If you select publish to DWF from the more plotting options– do not accept the default of 3D DWF as I did. CapturX does not work with 3D DWF files.

2. From the Start – Programs – Adapx item – select the Capturx Control Center to bring up the application shown here.

3. From this program you can load and then plot the DWF file using the Files and Print tabs. This process adds the snowflake to the paper.

4. Mark up the plot with the digital pen.

5. Dock the pen and the markups are returned to the DWF file seamlessly

Undocumented Tip

Selecting the Print Palette Control prints a markup template that can be used with DWF files along with very good instructions.

The Pen Manager Console

Once installed the Adapx Pen Manager icon appears on the Windows Quick Launch toolbar.

Double clicking the icon exposes the Pen Manager as shown to the right.

The Pen Manager is intuitive and easy to use. Its role does not become apparent, however, until you attach the pen.

Downloading Information from the Pen

Plugging the pen into its docking station causes the Pen Manager Console to be activated and you are provided options for downloading the information

On completion, the Capturx Control Center is automatically activated where you can select the downloaded information and immediately view it in ADR. Overall – a very easy and intuitive interface but documentation would be helpful.

Viewing Markups into AutoCAD

Adapx’s job is over once the markups from the pen have been associated with the original DWF file and it does this very nicely.  But I wanted to review the markups in AutoCAD Map. A quick search through the Help file gave me the commands to do this:

1. DWFATTACH – allows you to attach a DWF file in the same way you would any XREF or similar file. But the markups are not visible which caused me some consternation.

2. MARKUP – displays all markups associated with the DWF file.

My Use Case

In my case, I was attempting to associate work orders to field assets. I wanted an easy way for field staff to identify which manholes and hydrants were inspected as part of a specific work order.

I simply wrote the work order numbers on the page in different colors and then circled the assets in that color. Different techniques could be employed of course. I also tested callouts to identify field observations.

The markups appeared in AutoCAD Map as expected. It was a simple process to select the entities in AutoCAD and then update the attached database with the work order number.

The Bottom Line on Adapx CapturX for Autodesk Design Review

Overall, this was the smoothest interface for field data collection that I have seen.

· Markups are stored with the source document and can be processed by office staff at any time.

· Paper backups are available when that is important.

Some suggestions for Adapx:

1. Improve the documentation particularly on the initial DWF plot generation.

2. It would be nice if the ADR palette could be added to paper space and be on the same page. I tried this as an experiment but the commands appear to be tied to the palette snowflake.

3. Consider partnering with a national printing firm for those customers who are hesitant to invest in a large format laser plotter for D and E size plots.

The Rogers Pass double tracking project and the associated Mount Macdonald Tunnel was the largest engineering project undertaken in North America at the time.  The engineering and survey division at CP Rail had a major problem with construction surveys.

Survey crews had captured the original ground cross sections.  The double tracking project had been designed and construction was underway.  Basically, CP Rail was cutting a new rail line through the Canadian Rockies lowering the rails by 91 meters.

This is no simple task when you consider the topography of the area as shown in the picture to the right.  Rod men were often scaling cliffs or hanging off cliffs to get the required survey data.

After contractor blasting, CP Rail survey staff would generate new cross-sections to calculate pay quantities… but if there was a discrepancy in the cross-sections, the difference in volume essentially went to the contractor.

Huge dollars were involved and a more efficient way of identifying and rectifying discrepancies was required.

At the time, all survey data was captured in field books. Survey crews not only captured the survey data but then manually reduced the notes and manually drafted cross sections – a time consuming process that could lead to one or more weeks passing between the actual survey and the production of the cross-sections.

The problem was further compounded by the physical effort it took to get to the construction site and set up for a new survey in rugged mountainous territory.

The first total station survey equipment was being introduced at the time.

Geodimeter was an early industry leader. “The Geodimeter 140 (1981) introduced automatic electronic angle measuring, or what is known today as a total station. It also brought dual-axis compensation with automatic collimation and tilt-axis error correction. In 1981, Geodimeter offered the first Tracklight, a green-white-red light system that informed the rod operator whether he or she was on line, left of line or right of line.”

I had met with the local sales rep for Geodimeter and we believed hat the combination of the Geodimeter total station, AutoCAD, a personal computer, a plotter and some customized software to automate cross-section generation would improve survey efficiency and minimize turnaround time for cross section generation.

We jointly made a presentation to CP Rail resulting in AESL receiving a contract to perform the system integration. I was the project manager… and so the journey began.

In 1983/84 Western Canada was in the midst of the crisis created by the Canadian National Energy Program (NEP). The bottom line was that dozens of oil development projects were cancelled – the boom days were over.

AESL was only one of many engineering firms in Alberta that was in crisis. Stan Lawrence, AESL’s CFO took control to save the company. The Intergraph system was sold; the computer division was collapsed to the group maintaining the financial system; I had to lay off most of my staff or find them positions in other offices. Similar actions were taken in every department as the company moved into survival mode.

Ron Salmon was the manager of the computer services division at AESL. Ron’s position was being eliminated and I was about to lose my development team. I recommended that Ron consider setting up his own business and becoming an AutoCAD dealer.

Stan Lawrence agreed that AESL would sub-contract the development work for the CP Rail project to Ron.

With those decisions made, Rosal Systems became the first AutoCAD dealer in Alberta and one of the first in Canada.

The epiphany occurred in the spring of 1983 when a long forgotten sales rep appeared in my office with a Victor 9000 and a license of AutoCAD 1.2.

At the time I was the Department Head of water resources at AESL (Associated Engineering) in Edmonton Alberta. I had relocated the family from the warmer but gloomy suburbs of Toronto to the sunny but admittedly crisp suburbs of Edmonton in 1979 to join AESL . My attraction – AESL was the first engineering firm in Canada to acquire a CAD system from M&S Computing – later renamed to Intergraph).

Alberta was in the midst of the 1970’s oil boom and was an engineer’s dream with major project following major project. The CAD system was revolutionary but was largely an electronic pencil. It had some major benefits but was extremely difficult to justify economically. Basically it had to run 24/7 to cover capital and operating costs.

The system was configured with 4 workstations in Edmonton connected via a high speed (9600 BAUD) connection to 2 workstations in Regina. I can’t recollect the exact architecture of those systems but local work must have been cached in memory because whenever one of the 6 users had to perform a “regen” – everyone went for coffee. This was your classic closed system – you sent in requests and drawings appeared but there was no engineering design capability.

In an attempt to demonstrate how CAD could be used as part of engineering design, I had assembled a ragtag crew of rebels into my department and had assumed fiscal responsibility for one of the workstations. We were managing the pre-design phase of a regional sewer system and I wanted an automated system that would allow me to examine pipeline re-routes in close to real time. With 63 numeric layers and no attributes, we were marginally successful in evaluating the impact on pipeline length; automated profile generation was still a tedious manual process.

The Victor 9000 sat on my desk. It had an amazing 800×400 screen resolution, two 5 ¼ inch floppy drives, an 8088 chip and a stunning 128 KB of memory – even better it could be mine for around $5,000. As a bonus AutoCAD offered unlimited named layers.

AutoCAD 1.2 also had an import function using a text file in a DXF format – documentation was a bit scarce but I was able to provide the specifications to the AESL programming team and within a day, we had the Intergraph data imported into AutoCAD.

The implications had my head spinning (driven home by the realization that I had just recently approved a $250,000 upgrade to the Intergraph system to upgrade to an early VAX system).

It was time to put AutoCAD and the Victor 9000 to a test.

One of the biggest problems we were facing at the time was uploading survey data from the field to the Intergraph System. AESL programming staff had programmed what was affectionately call the “brick” – used primarily for store inventory purposes – to collect field survey notes. The information was transmitted by phone back to the office every night and used to automatically draft the survey data in the Intergraph system…. But field crews could not review their data independently – minor survey input errors caused conflicts with the engineers who were using the drafted documents for design purposes. Allowing field crews to review the data in the field, make corrections, create their own drawing and add notes before the data was transmitted made a lot of sense.

So I wrote up a spec for the system and submitted it to computer services for a quote. Clearly computer services had bigger fish to fry because the quote came back at over $100,000 and the project was killed by the executive review committee.

Never one to take no for an answer, I went looking for a client with the vision to support the project – and so the journey began.

Having worked with Autodesk from the early days (AutoCAD 1.2) as a customer, developer, reseller, employee and consultant over a period of 25 years, I thought it would be interesting to share these experiences.

Between The Elephant’s Toes is a chronology of this experience. It describes my personal experiences from the early years of computer aided drafting and design, digital mapping, the evolution of geographic information systems (GIS) to current web based application development.

This story will be of interest to those who:

• have worked with Autodesk as an employee, developer or reseller and wish to collaborate with their own insights and experiences
• want an insight into Autodesk’s history from a partner’s perspective
• are starting their own software company. Hopefully you will glean some small nugget that will assist you from my personal experiences
• have an interest in the history and evolution of GIS.

Certainly I would love to hear from friends, business partners and acquaintances that I have made over the years.  Incorporating your perspectives and anecdotes will only add to the value of this collaborative effort.