Converting Flat DB to Tabled DB in 7.0

[weseverett]

I am a non-programmer who has developed a largely flat DB in FMPRO 6.0 (4,000+ fields). I am considering converting to FMPRO 7.0 and wanted to know if there was a technique (once over) to move fields in the Main Table into a different table.

Keep in mind that I have three years of programming in this sucker and a complete redo is out of the question. The program is admittadly designed like a spider web made by a spider on LSD.

I also have to say the entire notion of switch to 7.0 feels a bit scary.

[Fenton]

FMRobot, from http://www.newmillennium.com can move fields from one FileMaker 7 file (or table) to another.

Obviously you're going to make some changes. Otherwise there's little point in converting. For another, some things work differently, some tweaks will need to be made anyway, just to keep it running correctly.

[Lee Smith]

You will have to have developer though, as it requires a DDR to do it.

Lee

[weseverett]

I have attempted a couple of bet conversions to FMPRO 7.0 - the only major issue that has arisen is that I am getting a "Go To Field []" in a couple of my scripts. Once removed they seem to work OK. It seems like complete dumb luck, but it appears that my stupidity of programming everything in a flat database may actually make the conversion go more smoothly - as I don't have to reconstruct a bunch of relationships.

The main issues at this point that are driving my consideration for conversion are 1) I have been scanning pdf files into my DB which has put the 2 GB file size into issue (I have created a number of relational fields to move them offsite) and 2) I have begun to do a few more things with relational DBs and don't want to have to recreate the programming down the road.

Understand that I am an attorney whose clients are predominately folks who have gotten traffic tickets (high volume low margin). I had one programming class in college and have no support from anybody - completely self taught. I have designed the software over the last 3 years to manage my practice and it is fairly detailed (4,000 fields). Moreover, I don't understand many of the technical issues (like what DDR is) and am il- equipped to make an informed decision as th whether a conversion makes sense or whether just staying with FMPRO 6.0 and continuing to move my pdf scans onto relations DBs makes more sense.

I am really struggling with this and appreciate any input.

Thanks,

Wes Everett

[Fenton]

Well, you're right that a simple earlier file that is flat as a pancake, with no complex scripting is likely to need less tweaking. I don't mean to alarm you. I have a strong bias toward correct relational structure, but that doesn't mean flat files won't work. I am always encouraging people to create new files, better, more extendable files, because 7 can do that; but sometimes there is not the time or commitment.

Sorry, I forgot that FMRobit requires Developer, to get the DDR (report of all the objects in the files). There is another product which can move fields between tables, and doesn't seem to require Developer, but I've neither used it nor seen it used.

http://fmpromigrator.com/products/fmpro_migrator/

Of course, if you just want to convert your 4000 fields to one table, then recreate a few of them in another table, you can just do it manually. But 4000 is a lot to deal with.

You are correct that FileMaker 7 will solve most of your pdf size issues, virtually no size limits in 7. There are several newer methods, involving calculation of file paths or embedding entire files which you might want to consider. There are also plug-ins that do things with pdfs you might want to look at, some of which have file management tools.

There is a lot of documentation on the FileMaker site of conversion issues. There is a shorter file that comes with FileMaker 7. Read that at the very least. It will help, especially with windows and record-locking.

[P.S. I still bet that you could move some fields into a few small tables, sometimes letting one field replace many old ones. Whenever I see Field1, Field2, Field3, etc., I know someone has not even learned to use repetitions, which have been here since FileMaker 2.]

[weseverett]

I don't use repititions. Never really understood what they do. I did buy some trainind CDs from New Millennium which seemed to use them, but when I looked at them, couldn't get them to do what I wanted.

There is no doubt my DB is poorly designed - but it does work! Can you advise on "calculation of file paths or embedding entire files..plug ins"

Thanks a bunch,

Wes

Do you think that the flat DB will be easier to convert?

[Wim Decorte]

FM migrator can only create SQL compliant fields. Only FMrobot can create fields with all the options.

Sure it needs FM Developer. But that's pretty much a given for any serious developer, right

[Fenton]

Repeating fields are (were) used in "flatish" files, where you would otherwise be tempted to use fields like: cost1, cost2, cost3, etc.. It is an option in the Storage (where by default there is 1). Calculations can also be repeating, which they obviously would need to be, in order to total a "row" or "line." To include a non-repeating field with repeats in a calculation you use the function Extend(non-repeat).

But a more modern structure (since FileMaker 3) is to use another file (table in 7). Then you just use a field, and it automatically can be in as many records as you need. Calculations are done, from the parent file usually, by using an Aggregate function, such as Sum (relationship::number field). The Aggregate functions were first created to be used with repeating fields, in order to total a "column," so you see the similarity.

Repeating fields have the advantage of being slightly faster, as they are still "stored," whereas calculations using relationships are always "unstored." But there are so many other advantages to using separate tables. The main problem with repeating fields is that you cannot access the data individually for such things as reports. Any kind of complex functionality becomes a pain. By using repeats you are limiting your options for the future (though properly done repeats can be split out into a table later; but that's doing the same work twice).

So it depends what you got as to which to use, and they both can be used, where appropriate. But either beats the pants off: cost1, cost2, cost3.

I agree with Wim. This is what I'd do. Get FileMaker Developer 7 (have it :-), get FMRobot. Convert your file to 7. Then analyze the heck out of it and build a new file properly. Use the converted one in the meantime, if it still works fine.

[P.S. I've seen rumors that FileMaker 8 is coming out, or will be announced fairly soon (August?). That could be crap, but If I was going to buy Developer at this time, I'd call FileMaker and see if they could confirm that the purchase would be good toward 8 (probably they'll be mum, but it's important). The upgrade price for Developer is only $100 off the full price, steep. Developer 7 is great though, and would do all you need.]

[bruceR]

I am a non-programmer who has developed a largely flat DB in FMPRO 6.0 (4,000+ fields). I am considering converting to FMPRO 7.0 and wanted to know if there was a technique (once over) to move fields in the Main Table into a different table.

Keep in mind that I have three years of programming in this sucker and a complete redo is out of the question.

Sounds like you'c be much better off hiring a developer to fix it.

[weseverett]

I would be willing to hire someone to assist and refine it. I would be happy to send anyone a clone of it or one with some of the data included in order to provide a quote.

Thanks,

Wes

[Ron F]

simply put... Filemaker 8 is due out this summer, it will allow you to import tables. you might want to wait and upgrade.