Sorry in advance for the long post. Some of this is me working
through the process, some is soliciting for sympathy and some is for
advice if possible.

I was recently hired onto a company to do tech support and as part of
that they are looking to me to help "modernize" their tracking/
reporting system. The existing system was developed several years ago
(10+) using MS Access. Initially it was a simple 3-truck shop. It
has growth to 26 trucks all over the country. As the company grew,
Access hit the wall. So what better way to make it work better is to
use .Net to extend the ability of Access. I've spent some quality
time with the db since starting. Many of the links in .Net are hard
coded to specific fields in the Access db. The entire Access db is
flat and has no normalization to the data. In fact there are multiple
PK and FK fields in the same db to contend with. There are some
fields that are contaminated with additional information, not to
mention extra returns and items that are completely irrelevant to that
particular field of data.

The method of all the field folks accessing and updating the
information goes as follows: The user launches the "application",
which then calls a script, which ftp's out to a central server and
grabs the latest .zip file of the database and downloads it to the
local system, decompresses it and then finishes launching the db. The
user does whatever maintenance/updates they need to do and then closes
the app out, which reverses the logic of the opening process. Once
the data arrives back on the server, there is a script on the server
that watches the file date/time stamp and if it's newer than the
previous go-round, then it has another process where it folds the new
data into a master database, which then replicates back out to all of
the folders for other people to download when they log in. There's no
field locking, and to the best of my knowledge (after conversing w/
the developer) no method for error checking for the newest
information.

I last really used FMP back when it was version 3 or 4 and have always
kept an eye on what it was doing each time a new rev came out, but
never really had much need to use it - until now. I'm working on
getting back up to speed on it. So I'm familiar with it - but I
wouldn't consider myself an expert by any stretch of the imagination.

So, if you've read this far (and aren't on the floor holding your
sides from laughing) here's my question. Is it worth it to try to
build something that can integrate the existing information, or just
start from scratch using FMP? I looked at PHP/MySQL, but I like the
rapid-build/ease-of-use of FMP.

I've looked at several options around this, and they include:
- Migrating the Access db to MSSQL - which I can't do because of the
hard coding in the .Net code, and we would wind up essentially
rebuilding the system anyway using SQL and, ugh, MS Products.
- Setting up a script that imports a csv file that was exported from
the Access sytem into FMP until we are fully off of Access.
- Starting from scratch and migrate what we can when the new interface
is done.

I've got multiple date/time fields that need to be converted,
inventory, schedule tracking dependencies (which actually gets a SQL
dump from Schedule-all into the existing system now - which I would
like to convert over to an ODBC connection)

Ultimately I want to get the system to a high level of reliability
(which we don't have now), ease of use (which is questionable at this
point of time) and web enabled (which we absolutely don't have now).
I know it can be done w/ FMP, the question is - at what cost to time
and effort. Obviously with enough time and effort anything can be
done, but like anywhere else, the sooner the better.

TIA for any advice or suggestions you might have.

Brian

On 2009-12-01 10:14:57 -0800, ibgarrett <brian (AT) garrett (DOT) net> said:

Sounds like an exciting and headache-inducing project. Congratulations.

Seems like your basic concern is transition/migration between the two
products. Obviously you want to get the web-interface for input up and
running ASAP.

Introducing working data into a solution in development is probably
going to increase your work and the complexity of the migration
tenfold. My recommended course is that you leave the current solution
in place while developing the FM project. Test and train with dummy
data, which can mirror working data but doesn't contaminate it. You can
use insertion of dummy data to test your migration routine. Once all
training is done, and perhaps after running both solutions in parallel
for a week or so, and comparing results, select one go-live date, and
do a final migration.

Do not go back, after that point. You do not want to be in a position
where some working data is in FM, some in Access. The synchronization
will kill you. That way lies insanity.

Hope this helps, some.
--
Lynn Allen
--
www.semiotics.com
Member FBA
FM 10 Certified Developer

On 2009-12-01 11:03:26 -0800, Martin Trautmann <t-use (AT) gmx (DOT) net> said:

There are plenty of professionals who would beg to differ, Martin.

Depending on the amount of data transaction, the actual need for
roll-back, compatibility with other technologies and other factors, FM
may not be the ideal tool. That doesn't mean it's only good for simple
jobs.

PHP now works with FM as a back-end, FM can work directly with SQL
tables, and increasingly, FM has become an excellent middle-ware for
linking disparate technologies and providing a quick and relatively
inexpensive to prototype & build in-house LAN solution with tentacles
all over the place.

If you want a spreadsheet, I suggest a spreadsheet. FM interfaces with
them directly too. Or you can use XML for communication between.

But to build and deploy a useable, reasonably robust solution for the
least amount of programming resources, I suggest that most other
solutions/front ends are much more expensive than FM.

Or so everyone I have encountered who tried to replace a FM solution
has found. After months or years, and thousands of dollars (sometimes
hundreds of thousands) the FM solutions are still chugging along, in
most cases.
--
Lynn Allen
--
www.semiotics.com
Member FBA
FM 10 Certified Developer

ibgarrett wrote:

If you are basically starting from conceptual scratch, also have a look
at 4D. Bit more of a learning curve in some aspects than FM, but offers
a lot of things FM lacks.

On 2009-12-01 16:14:24 -0800, 105 <cortical (AT) internode (DOT) on.net> said:

Or, for that matter, Servoy. SQL backend, db objects stored in tables,
newest versions supposedly as easy as FM to get on the web. I don't
develop in it personally, but I know quite a few FM developers who have
migrated to it and they love it.
--
Lynn Allen
--
www.semiotics.com
Member FBA
FM 10 Certified Developer

On Dec 1, 12:03*pm, Lynn Allen <l... (AT) NOT-semiotics (DOT) com> wrote:
Thanks for the insight Lynn. Sometimes I wonder if I'm headed down
the right road. I just keep telling myself "baby steps, baby steps".
I've almost always avoid programming when I can simply because I want
to bite off the entire chunk of the problem and solve it all at once.
I'm now working on spending more time on smaller steps. I also have
wondered if I start down this path, what happens when it grows bigger
than it can handle. Ultimately when we really get rolling I plan on
having a dedicated FMP Server and serving it up via the web. That
alone should give the project some legs to grow with. Right now I'm
just focusing on a small proof of concept using the Instant Web
Publishing to get the thing off the ground.

Again - I appreciate your reassurance.

Brian

On 2009-12-01 21:57:46 -0800, Martin Trautmann <t-use (AT) gmx (DOT) net> said:

High reliability requires more than backups. If you really want high
reliability you're talking RAIDs and mirrored drives and sometimes even
RAM disks, before you ever get to backups. I've never had a client who
needed that level of reliability, but some developers I know have.

Then you need to talk about UPS's and network housekeeping and keeping
the files available.

That said, FM Server 10 now offers features for creating timestamped
and named backups on whatever schedule you want. Creation of off-site
and separate media backups is left to whatever backup software you want
to use. Newer versions of the software make backups much less intrusive
than they used to be to users, so more frequent backups have a lot less
impact on performance.

As for restoration of backups, if you've got sufficiently recent files,
it can be as simple as copying the files into the proper directory and
starting the server. If your files are not sufficiently recent, that's
not FM's fault. You should backup (and preserve offsite, for disaster
recovery) in direct proportion to how much work you (the business) can
afford to re-create.
I've never seen the need to migrate. With ESS, you can now add SQL
tables directly to the relationship graph, so that more than standard
queries are available. The concept of shadow tables allows one to add
FM-resident fields (and calculations) into the SQL tables to manipulate
the data. Or, if one wants to work directly in FM, imports from SQL are
lightning-fast, even for extremely large numbers of records.
I don't claim anything. You were wanting the spreadsheet-specific
features of multi-cell editing and data movement. I suggest that if
that is a requirement of your solution, that you do it in a program
designed for it. If it's simply viewing the data, FM's table view does
that admirably. With script triggers it's now possible to do amazing
things in FM10, that can mimic a great many of Excel's features, but if
you need a spreadsheet with specific spreadsheet features, use a
spreadsheet.

I don't recommend FM as a page-layout app either. If that's what you
need, move the data into a dedicated app, rather than trying to force
FM into working in ways it's not intended to.

The reason so many people use Excel is that most office solutions start
with a simple need to keep a list of something. Excel is excellent for
this, and easy to use and understand. You can have a list up and
running in Excel as fast as you can type.

Then the needs of the users and the business rules that have to be
applied grow and develop and mutate, and at that point, Excel fails.
This is the point where most independent developers come into a
business and start building the first FM solution for a business. Once
business rules and work process have to be integrated into the data, FM
works admirably. It's not the only tool out there, and sometimes the
data needs do mean that SQL is a better choice. But for in-house
business enterprise needs, FM is usually the cheapest, quickest route
to getting something done.

Many companies decide to use FM as a prototype tool, a
proof-of-concept, with the idea of rebuilding it in SQL once the
prototype is done. Then once it's built they find FM is doing fine, and
the cost of the SQL solution (and the resultant inflexibility of the
final product) is insupportable from a business viewpoint.

For companies who need flexible, easily changed or enhanced solutions,
FM can be ideal. SQL, not so much.
The one that comes immediately to mind is:

http://www.shakeout.org/

This was a project aimed at an Oct 15, 2009 statewide (in California)
earthquake preparedness drill. The site is still up and still
apparently taking registrations for next year.

I am personally acquainted with the developer. The entire backend is
FileMaker, and he said the site took nearly 7 MILLION registrations in
the months leading up to the drill. Yes, Million.

--
Lynn Allen
--
www.semiotics.com
Member FBA
FM 10 Certified Developer

On 2009-12-02 12:44:35 -0800, Martin Trautmann <t-usenet (AT) gmx (DOT) net> said:

As far as I know, the data is entirely within FM. The office uses a
LAN-based FM interface to manage it, and the web pages seem to be
generated by php, once you click one of the REGISTER buttons. I have
not seen what the in-office interface looks like.

Hmm. Good thought to ask the developer to present a case study for
FMDiSC. (Filemaker Developers in Southern California, our developer's
group.)
--
Lynn Allen
--
www.semiotics.com
Member FBA
FM 10 Certified Developer

On 2009-12-02 12:44:35 -0800, Martin Trautmann <t-usenet (AT) gmx (DOT) net> said:

SQL import is blazing fast. Trust me, it amazes developers too. Some
have reported being able to import as many as a million records in
something like 30 seconds.

As for FM files not showing they are broken, the Recover function has
changed in major ways in FM10. It's now possible to incrementally test
a file, and a Recover log is written that identifies objects that don't
meet the testing criteria of the program. We just had a presentation by
a FM sales engineer at the last FMDiSC meeting on this. Recover still
does not fix a file. There are suspicions that coming versions will be
able to perhaps rebuild a file from a DDR. These may or may not be
correct.

As for restoring from Backup, with a sufficiently recent file, there's
no import. Say a file is backed up at noon. At 1pm, all users report
problems. One simply closes the server, copies the noon backup files,
which goes as fast as the OS copies, and starts the server again.
You've lost an hour of work, but that's usually not a killer.

Now...the backup files may be corrupt. At this point, though, the users
are up and running and working. The administrator can take their time
testing the backup files for soundness, and if necessary, retreating
back through versions until a solid file is found. Then working data
can be imported during downtime, and provided to the users at a later
date.

Worst case, one has to retreat to the Golden Masters, which are files
kept on the developer machine, and only sent UP to the server, never
coming back down. This is one reason working on live files is a bad
idea, it means you don't have those virgin GM files to deploy if all
your backups are hosed. Some people do work in the live files, but
duplicate all their work in the GM files just in case.

Stay tuned, though. Who knows what will happen next?
--
Lynn Allen
--
www.semiotics.com
Member FBA
FM 10 Certified Developer

On 2009-12-02 23:44:08 -0800, Martin Trautmann <t-usenet (AT) gmx (DOT) net> said:

Err. I use FM Server's scheduled backups. From experience, a 1GB file
takes about 20 seconds to back up. Several files totalling about 20GB
take about 5 minutes. These backups go into specific folders on the
server machine, or onto mounted drives. Users notice almost no effect
during backups.

Then the (separate) backup software is used to move the FM backups to
separate media, or online services, or whatever is used to take offsite
backups.

One can run rolling backups to overwrite daily or weekly backups,
depending on how long a history you want to maintain. So you can
control how much space your backups take.

You dont' explain what a "professional" backup strategy would be, but
it appears you're not familiar with the features of FM Server and what
it offers. Often I do development work on hosted files on a VPS (not
working files, these are the masters) just to gain the ability to run
backups frequently without having to close the files. Works great, even
when backups run while I'm editing schema.

Of course this works for files hosted on FM Server. Non-hosted, single
user files must be closed. During development, I do my backups by
closing files and zipping them. This takes about 30 seconds for 5G
files.

Complaining that FM isn't "professional" when you don't seem to know
about or use the features available is like someone complaining that
their piano is broken when they only use the black keys.

--
Lynn Allen
--
www.semiotics.com
Member FBA
FM 10 Certified Developer