Lewis & Lewis Feasibility Study Report

Lewis & Lewis Feasibility Study Report – February, 2006

Determine the Best Method For Transferring, Storing, and Manipulating Data

Lewis & Lewis Feasibility Study Report

to

Determine the Best Method

For

Transferring, Storing, and Manipulating Data

Diana Bochsler

MyTech, Inc.

February 17, 2006

Summary

This report indicates the steps and decisions and documents generated that were used to study the importing of data from Lewis & Lewis’s scale program at the pit to the database FileMaker Pro. This constituted the majority of the study. However, MyTech, Inc. had hoped to demonstrate the advantage of having a single point access to all of the data required for producing the various reports required for Lewis & Lewis. Since we were unable to obtain a connection between the required application of FileMaker Pro and Crystal Reports, we decided not to spend the time creating screens and reports. David also realized that Lewis & Lewis were satisfied with the status quo obtain from the implementation of security measure and now were only interested in reviewing the import process that was determined from the study.

If in the future, Lewis & Lewis become interested in streamlining their processes, I would recommend the use of a database engine such as Access or MySql, which is better supported by Crystal Reports. The re-structuring of the scale database would be recommended, but not absolutely required. Some reports indicated that there were other processes not captured in this study. These would need to further investigation to obtain a normalized, relational database.

The 2005 Data Merge project and the importing documentation will be included on a CD for Lewis and Lewis.

Introduction

The objective of this report is to determine a better method for the transferring, storing and manipulation of data for MyTech, Inc.’s customer: Lewis & Lewis. The original intent was to develop a business process which provided a single point access for the Lewis & Lewis data using Access, MySql or such database engine; however, Lewis & Lewis expressed their desire to remain with FileMaker Pro 7 and Crystal Report 10. With the desired applications in place, the study set out to establish the best process of transferring data from the pit scale program to FileMaker Pro with Crystal Reports providing the reporting capabilities.

Background

Lewis & Lewis process of handling the scale data had allowed duplication of data, lost data as well as having no security in place for the protection of the data. The 2005 data from the scale was stored in several copies, some duplicating others with overlapping date ranges and different data structures. After merging the various copies into one set of data, it was important to begin to review the entire process. David Brinkeroff, owner of MyTech, Inc. began to address the security and hardware issues. The following lists the solutions that David initiated:

• He placed the computer system on a UPS system.
• A laptop was used to connect to the scale and capture the scale data.
• The Scale program security was tightened to allow only designated personnel to make changes to the data structure and data entry into supporting tables.
• The laptop was taken to the office at the end of the day for updating the tables and to create a CD backup of the data.

The next step was to develop the best method of transfer data from the scale database to FileMaker Pro and to provide single point data access. The manipulation of the data from the scale was being processed by several methods. By having the data from the scale imported into FileMaker Pro, screens and reports could be developed to aid in the manipulation and reporting of the data, eliminating the various copying of the same data to create the required reports, thereby creating a single point access to the data.

Discussion

Importing Data

Importing Data from the Scale Data

The scale program is a Fairbanks DataMaster Plus Data Management System, Model SFW-9700. It uses Paradox database engine for its data collection. The Paradox has the file extension of .db. Paradox and FileMaker Pro do not integrate with each other. Therefore, Excel spreadsheet was used to provide the common link between the two applications.
The data from the scale program is housed in a common file called Working.DB. This file is a combination of the information from several support tables such as Material, Contract, Attendent (spelling according to the table), Vehicle, Customer and the actual scale weight data in System. This field names include the table name such as

Customer->Customer #.

By opening Excel and double clicking on the file Working.DB, the data from the scale backup CD is imported into Excel and saved as Scalexxxxxx.xls (x represent the date of the transfer) ex: Scale010706.xls. The end user should review the data, repair any data that requires fixing, delete rows that have already been imported and save the file in a csv format such as Scale010706.csv. “.csv” is a comma delimited file format.

It should be noted that the deleting of already imported records could be eliminated if the scale Working.DB file could be cleared of data safely after each backup.

This would help to address the problem of duplicating records even though it is a manual process. The best method would be to have a program written that read the scale Working.DB and determined which record was to be imported into FileMaker Pro. However, the cost of having a custom program written is not an option at this time.

Importing the Excel .csv File to FileMaker Pro

FileMaker Pro requires the mapping of its table fields to the fields being imported. The Work.fp7 was created by listing the fields in the same sequence as the import file. Then by opening the Work.fp7 file, choosing File\Import Records\File and the Excel csv file, the import of data into FileMaker is complete.

Supporting Documents for the Import Process

Instruction for Importing Data from the Scale Program to FileMaker Pro.doc is a document that details step by step procedures in performing the data transfer from the scale to Excel to FileMaker Pro. Thisdocument is located in the Appendix B – Supporting Documents as well as on the CD provided by MyTech, Inc to Lewis & Lewis.

FileMaker Pro Data Structure. xls is a document that details the data structure in the Lewis & Lewis FileMaker Pro database. It provides a list of field names and type of field in each table along with key fields noted for supporting the relatingof tables. The sequence of the fields in Work.fp7 table is in the same sequence as the data being imported from the scale. Any other fields that will support other data requirements will be added to the end of the file as well as the other supporting tables: Vehicle, Contract, Material, Attendant, and Customer.

Note: The data from the supporting tables does not generate the records in Work.fp7. The table also is not normalized. This would have required having the Fairbanks serviceman to redo the scale database structure. This was out of the question per Lewis & Lewis. Therefore, the structure of Work.fp7 matches the scale Working.DB.

This document is located in the Appendix B – Supporting Documents as well as on the CD provided by MyTech, Inc to Lewis & Lewis.

ImportLog.xls is a document that was created to provide visual log of the records imported from the scale program to FileMaker Pro. It denotes the date of the import, the Start Date, Time and Ticket No., End Date, Time and Ticket No. along with the number of records imported from the csv file and the number of records imported into FileMaker Pro. A Total Record Count of the csv file and FileMaker Pro is calculated. This helps to remind that the two numbers should match. If not then the import into FileMaker Pro should be deleted and an investigation of why the number of records did not match. This document is located in the Appendix B – Supporting Documents as well as on the CD provided by MyTech, Inc to Lewis & Lewis.

Screens

Initially, I was going to create screens to support the data input and review of data. It was decided to use the automatic data entry screens created by FileMaker Pro. David detected that Lewis and Lewis were more interested in maintaining a status quo that they had reached after the security issues were resolved. They were interested in the import process. It was deemed that if their interest became apparent later, we could create the screens and obtain the information needed to support other processes that a couple of reports indicated.

Reports

Lewis & Lewis use Crystal Report to generate many of their reports required for their business. This is a very robust reporting application and has many built in connection protocols to databases. When I started to set up the link between Crystal Reports and FileMaker Pro database, I was unable to get a connection to work. There was not a direct link protocol; however, both Crystal Reports and FileMaker Pro are ODBC compliant and should connect. David was not familiar with either program, so I called Misty at Lewis & Lewis. She stated she did not know how to create the link and thought perhaps it was not possible. Therefore, no reports were generated in Crystal Report until either a connection can be figured out or another database engine such as Access or MySql is used and better supported by Crystal Reports.

FileMaker Pro has reporting capabilities. It was decided not to spend the time learning how to use the script function for reporting to select various records or create any other reports due to the lack of interest by the customer to make changes at this time.

Conclusion

Utilizing the constraints of using Crystal Reports and FileMaker Pro, the study was able to produce an import process from the scale database to FileMaker Pro. This will satisfy the interest at this time of the customer, Lewis & Lewis along with the final merge of the 2005 records which was not part of the Feasibility Study but was part of the overall project.

MyTech, Inc. will create a CD that includes Excel and FileMaker Pro formats of the 2005 data, and the following documents: FileMaker Pro Data Structure.xls, Import Log.xls, Import Instructions.doc and the document that support the 2005 data merge, Merge Documentation.xls.

Recommendations

By providing the 2005 data merge and the documented import process the desires of our customer, Lewis & Lewis is met.

If we find that they are interested in streamlining their current business processes, I recommend that a different database engine such as Access or MySql be used along with Crystal Reports for creating reports. The current structure could be used, but if a revamping is in process, it would be best to reset the databases in the scale program if allowed by Lewis & Lewis. Further study of all of their reporting needs and data requirements would be required to establish a complete and normalized database from which all reports could be generated.

References:

CrystalTrainingPartners (2006). Forum. Retrieved from

Crystal Reports for .Net (2004). Chapter 1: Introducing Crystal Reports. Retrieved from

DatabaseCorner.Com (2004-2005). Database Software Directory about FileMakerPro. Retrieved from

Fairbanks Datamaster Plus Data Management System (na). SFW -9700 Model. Retrieved from

File Maker Pro Help. Sharing FileMaker Pro data via ODBC or JDBC (Windows).

FileMaker Pro Help. Importing and Exporting Data.

FileMaker Pro Help. Supported Import/Export File Formats.

FileMaker Pro Help. Enabling access to FileMaker Pro via ODBC/JDBC (Windows).

FileMaker Pro Help. Importing Data Into FileMaker Pro.

FileMaker Pro Help. Setting the Import Action and Mapping Fields during Import.

FileMaker Pro Help. Using ODBC and JDBC with FileMaker Pro.

FileMaker, Inc.(1997-2005). FileMaker Pro 7 User's Guide - ODBC and JDBC (pg 76).

Firebridge.com (1997). ODBC. Retrieved from

Microsoft Excel Help. Importing or Exporting Text Files.

StaffKit (2004). Crystal Reports 8 Online Training. Retrieved from

TeachUComp, Inc. (2006). Mastering Crystal Reports Made Easy, Disc 1 Chapter 2 - ODBC. (Tutorial program on CD).

Appendix

FileMaker Pro Data Structure.xls

Import Instructions.doc

Import Log.xls

CD created from MyTech, Inc to Lewis & Lewis.

2005 Data Merge.xls

2005 Data Merge.fp7

Merge Documentation.xls

FileMaker Pro Data Structure.xls

Import Instructions.doc

Import Log.xls

Created by Diana Bochsler. 1of 8
Printed: 01/23/2019 12:31:45 PM