revUp - Updates and news for the Revolution community
Issue 90 | April 1st 2010 Contact the Editor | How to Contribute

A Rev DB App, in 3 Secs or Less - #1
Use FmPro Migrator Platinum Edition to unlock the power of SQL Yoga and the Rev Data Grid (and save 50% in April).

by David Simpson

Introduction

This article is Part#1 of a 5 part article series which shows the completely rewritten FileMaker Pro/Microsoft Access Database to Rev conversion feature included within FmPro Migrator Platinum Edition. This new feature is based upon the idea:

Converting a simple FileMaker Pro or Microsoft Access database into a full featured Rev application should be a quick, economical and automated process requiring no manual programming for commonly requested database features. By standard database features I mean having the ability to insert, update, query and delete records within the converted database file. Having a functional graphical interface means the automated conversion of the FileMaker or Access database Forms or Layouts into Rev cards along with Value Lists, Fields and Portals.

The financial advantage of automating the database conversion process can be substantial. IT consulting firms typically quote prices of as much as $1000 per Layout or Form to be converted or re-written as a new graphical front-end application. You can think of this as a ballpark figure which consulting firms may use for quick project estimating purposes. This figure may vary depending upon the complexity of internal business logic required within the application. If you have a small application with only 2 forms, but you have quite a bit of internal business logic then you would expect the price to be higher. If you have hundreds of fields on a single form, with lots of calculated fields providing the business logic, then once again the cost would be higher.

As an example, I sent my standard Asset_Management.fp7 test database for a quote to a company in India, and they quoted me $1200 to convert it into a cakePHP framework web app. So this means that for a typical database, your development costs could cost tens or even hundreds of thousands of dollars at these rates. Within this article, you will see this same database converted into a functional Rev application, all of which was done with no manual coding.

Advantages of Converting to Rev

There are number of distinct advantages to converting desktop databases into the Rev development platform:

  • FileMaker Stand-Alone Files Have Limited Functionality - FileMaker stand-alone files have their networking feature disabled. This is done for financial reasons in order to insure the sale of additional license of FileMaker Pro desktop software. All Rev built applications and revLets have networking capability built in, but you do need to have licensed at least revStudio or revEnterprise for database connectivity features.
  • FileMaker Stand-Alone Files Don't Save PDFs - Due to licensing reasons, FileMaker stand-alone files also do not have PDF output capability. This is not a limitation with Rev built apps if you are using Quartum PDF.
  • FileMaker Licensing Cost - FileMaker Inc. has structured their business model in this manner in order to sustain a healthy revenue stream which funds the development of new product features. They don't charge enough money for their server product in order make up the loss of revenue from giving away the desktop software product. Lets look at this from a financial perspective, FileMaker Server 10 Advanced sold for $3000 and could serve up to 999 simultaneous FileMaker Pro clients having a retail price of $300 each. [FileMaker Server 11 Advanced now supports an unlimited number of clients.] This means that a fully loaded server could provide more than $303,000 in revenue to FileMaker Inc. from software sales. That is a nice chunk of revenue for FileMaker, but maybe you would like to build a desktop app for your own company and keep that money in your own pocket.
  • Microsoft Access Licensing Costs - With Microsoft Access you have a retail cost of $229 each or $499 if you are buying the full Microsoft Office package. But if you are using the Microsoft Access database to connect to a Microsoft SQL Server database, you will also have a cost of $3900 to $25,000 for the server software plus an additional $162 per user for Client Access Licenses. So a 1000 user system would cost $187,000 if you were using SQL Server 2008 Enterprise Edition. If you have a Microsoft SQL Server database connected to the internet, then you have to deal with CPU licensing instead of CAL licensing as well. It can all be rather confusing (and expensive) just looking at all of Microsoft's various licensing scenarios, all of which are designed to lighten your wallet as much as possible. Converting your Microsoft Access database to a Rev built application and using an open source database servers like MySQL, PostgreSQL or Firebird could save you quite a bit of money.
  • Cross Platform Support - Rev can be used to deploy applications to MacOS X, Windows and Linux desktops, as a Web browser plug-in revLet, the server and revMobile apps (supporting: Maemo, Windows Mobile/Windows 7 Mobile, iPhone and iPad).
  • Limited GUI Customization - FileMaker Pro and Microsoft Access databases are optimized as database development environments, but are not optimized for general purposes software development needs. The Rev development platform provides unmatched cross-platform functionality even including the ability to have custom window shapes.

A Learning Tool

SQL Yoga and Data Grid Manuals

You could spend a few weeks reading Trevor's well-written SQL Yoga and Data Grid manuals, and experimenting with your own database code. You could also carefully analyze the commonly requested features which users need to have available within a typical database application. I know, because I have done this myself. Once I learn an important topic, I like to incorporate the functionality into my software, so that I can move on and learn new things. So now I have a basic foundation of code which I can build upon anytime I need to develop a database front end application. I can just press a button and have FmPro Migrator build my app instantly - and so can you.

And I don't want to forget to mention the tremendously helpful email support which Trevor provided to me as I was figuring it all out too. If you have questions concerning how to accomplish certain tasks with SQL Yoga or the Data Grid, you are likely to find the answers in the sample app I have generated. And because the code is all written in revTalk, you can change any of the features to customize the functionality for your own application's unique needs.

Pre-Migration Preparation

When converting either a FileMaker or Access database, you will need to insure that each table contains a Primary Key column. Not only does SQL Yoga require a Primary Key column, but it is good database design practice too. Ideally, this should be an incrementing numeric column, because this is how FmPro Migrator will convert the column for use within the SQL database. FmPro Migrator recognizes a Primary Key column in a FileMaker table as being a field which has the Unique and Not Empty Validation options checked for the field.

FileMaker Field Validation Options

Since FmPro Migrator gathers field info from Access databases via ODBC, it will be necessary to manually set the PK status within FmPro Migrator. Double-click on the field within the fields list, set the PK by clicking on the key icon, set the Auto-Increment checkbox, the NULL status to NOT NULL and the Unique Status to Unique.

FmPro Migrator Field Details

Note: FileMaker databases allow incrementing fields to be prefixed with letters, but most SQL databases won't allow this feature for auto-increment columns, unless you manually create a trigger to add the text info to an incrementing sequence for instance. By default, FmPro Migrator assumes that the PK column will be an incrementing numeric column so the column will be created this way in the destination database. Having text information within a numeric column will usually cause errors which prevent the transfer of data into the SQL database.

There are additional pre-migration conversion tips documented within PDF documents available on the FmPro Migrator support web page, but this is enough info to get started with the simple example database.

The Conversion Process - FileMaker Pro to Rev

The FmPro Migrator Table Consolidation procedure PDF manual provides a good overview of the process required to capture all of the elements of a FileMaker database which are required for performing a complete migration. This process includes capturing table definitions via the clipboard from FileMaker Pro Advanced, exporting a DDR XML file to gather info about TOs, Relationships, Custom Functions and Custom Value Lists and pasting scripts from the clipboard into FmPro Migrator. The goal with this process is to gather enough info about the original database in order to reproduce the functionality within a Rev stack file.

In addition to gathering the structure info from the FileMaker Pro database, you will want to transfer the data into your favorite SQL database. For this article, I have transferred the data into a SQLite database file which will be located within the same directory as the generated stack file.

I am skipping over a number of the data transfer details, as I have covered these topics in previous articles, webinars, the revLive09 DVDs and in the PDF files on the FmPro Migrator support web page. Within this article, I want to concentrate on the details of generated application stack file and its revTalk code.

FmPro Migrator - Transfer Data

The Conversion Process - Access to Rev

The first step when converting an Access database file is to drag and drop the Access database mdb/accdb file onto FmPro Migrator so that the list of tables and relationships can be gathered from the database file. If you have an Access ODBC driver installed, FmPro Migrator will directly read the contents of the file to gather this info.

The next part of the process is to gather the info about the Forms/Reports within the Access database file. This step requires running the Access DDR Export database file supplied with FmPro Migrator. This file will be written to the output directory automatically once the Access database structure has been read.

Access DDR Export

A folder containing the DatabaseName.txt file along with embedded images will be created as a result of running the Access DDR Export Utility. These files are read and then converted into value lists, scripts and layouts within FmPro Migrator by clicking on the Access to FmPro button on the GUI tab.

Access to FmPro screenshot

Once the conversion into FmPro Migrator has been complete, you will be able to browse thru the list of converted layouts, value lists and scripts which were extracted from the Access database file. At this point in the process, you have a choice to make. Either convert the info directly into a Rev stack file or convert the database into a FileMaker database where you might want to make some changes.

There are some Access database features which can be challenging to convert in an automated manner. For instance, many Access database files use queries to populate individual database form and report fields. FmPro Migrator tries to map each field directly to a field in a database table in order to generate the SQL Yoga code for the database application. So if the field isn't mapped directly to a table, but goes thru a query, FmPro Migrator won't be able to automatically parse the SQL code to make that translation. You could manually write equivalent query code within SQL Yoga, and this could be a good choice for many situations. But if the queries are very simple in their design, you might just re implement the functionality directly within a FileMaker database. Then copy the FileMaker Pro layouts into FmPro Migrator and have the SQL Yoga code generated for you automatically.

Generating the Rev Application Stack

At this point in the conversion process, you will have layouts, value lists, scripts and relationships from your source database stored within FmPro Migrator's SQLite project file. Clicking the Database to Rev conversion button on the GUI tab brings up the Database to Rev Conversion Service window. The number of layouts in your database will be displayed in the window.

The Output File Type menu provides options for generating a stack file to be used as a desktop application, revLet or revMobile app. Selecting the revLet option generates a stack having additional buttons visible for the user and Rev developer to embed or extract a SQLite or Valentina database file from the stack - if one of these database files has been selected for the migration. If you are a consultant, this feature makes it possible to quickly prototype and upload a test file for customer review and approval.

Output File Type Menu

Clicking the Migrate button, generates the new stack file, adds code to the stack to build SQL Yoga objects, and adds new cards to the stack for each layout.

Database to Rev - 2 Layouts - 2 seconds

And as you can see, the whole process took only 2 seconds. In a future article I will explain how to customize FmPro Migrator's conversion process to automatically add your SQL Yoga license key as well as including the copy of the SQL Yoga library stack from your local disk.

Conclusion

The Database to Rev Conversion feature built into FmPro Migrator Platinum Edition enables Rev developers to quickly convert popular database files into functional Rev database front end application stack files. A working application can be created in seconds, without manual coding for commonly requested database features. In future articles, I will review the features built into the generated stack file, show the code which works behind the scenes and show how to substitute your own customized stack for the template stack file.

[Special Offer]

FmPro Migrator Platinum Edition is available for $599 from the RunRev online store, or $399 as an FmPro Migrator Developer Edition upgrade or annual renewal. As a limited time introductory offer until the end of the month of April 2010, .com Solutions Inc. is offering FmPro Migrator Platinum Edition for a 50% discount (new purchase for $299 or upgrade for $199). Use Coupon Code FMPROAPRIL for this special offer. FmPro Migrator Platinum Edition includes license keys for the following features:

  • PHPToRevTalk License Key (Qty = Unlimited)
  • BasicToRevTalk License Key (Qty = Unlimited)
  • AccessToFmPro License Key (Qty = 250)
  • FmProToAccess License Key (Qty = 250)
  • DBToRev License Key (Qty = 250)

Also, Blue Mango Learning Systems is providing a 50% discount for Rev developers who purchase FmPro Migrator Platinum Edition along with SQL Yoga, which is now priced at $99 compared to the regular price of $199 - just put both products in your basket together to apply the discount.

About the Author

David Simpson is the president of .com solutions inc, and a Revolution developer of database applications since 2001.

Main Menu

What's New

Enter and Win an iPad