A Rev DB App, in 3 Secs or Less - #1
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:
A Learning Tool
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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:
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.