Find fields not on any layout?? & Records w/ little data?

[jessebSF]

Hey all-

I am in FileMaker 6 on a mac, and my organization's database is a total mess that I would like to clean up. I am wondering whether there is a way to find all fields that are not on a singly layout?

Also, relatedly, I am wondering if there is a way to find records that have data in less than a certain # (say 7) fields?

I would appreciate any suggestions.

Thanks a lot,

Jesse

[bcooney]

Well, I wouldn't assume that just because a field isn't on any layout, it's not needed.

You can find records with no data by entering "=" (no quotes) in a field in find mode. This means, find all records in which this field is empty. By putting "=" in several fields, you'll accomplish your goal.

Are you interested in upgrading to FM9? If so, it's pretty much a rewrite.

[jessebSF]

I wouldn't generally assume that a field that isn't on a layout is unneeded either, but in the case of our 15+ databases developed over 10+yrs, I am positive that there are over 25 fields that nobody even knows what the information they stored was intended to mean. There are also a bunch of fields that have never had any data input into them. I am actually trying to clean up the records in the databse, not the fields. But with more than 100 fields in a single database, it is impossible to know if there is data that we actually need on that record somewhere in the system that we can't even see.

The main reason I am trying to clean our data itself IS because we are going to upgrade our system. We haven't determined whether that will be to FileMaker 9 or another product entirely, but before we can even begin to vet the technologies against our requirements, we still need to get rid of the 5000+ records that need to be deleted (literally!). It is a mess. Any FileMaker pro would be disgusted to see the state of our data storage! :

Thanks,

jesse

[Fenton]

There is no data in the system that you "can't even see," because you can put any field on a developer layout and do a Find on it. Do not rely on the often inadequate layouts used for data entry.

As bcooney says, it is not so much whether a field is on a layout, it is whether a field has data. As she also says, Find for "=" (no quotes) will tell you whether a field is empty in all records. At this point in the game, I'd say it could be deleted.

You can also type "=" in all critical fields, and Find all records which can be considered essentially "blank" (they may have some data, but not enough to make the record usable).

Other than that it's hard to say what is junk and what is not. One thing I do is put a bunch of questionable fields on a table view layout, and get an overview of the data. It lets you see things like "field hasn't had any new data in years." Or, worse, "these two fields are for the same thing, but sometimes they used one, sometimes the other"; or worst, "sometimes they used both, but different data!".

Then there's duplicates.

[jessebSF]

I tried putting all the fields onto a layout, but it was so many fields, it kept hanging FileMaker up- which is why I wanted to find the useless fields.

I didn't even think of a simple = find for all the fields. i guess that is the first thing i wanted, to find all the fields which have no data.

The typing = into all critical fields is also another simple thing that I didn't even think of. Starting with those 2 will probably solve 50% of my cleaning. The duplicates are another story altogether, but that will have to be the next step.

Thanks you both so much!

[Fenton]

I didn't really mean to put ALL the fields on the layout, just the ones that are suspect. Then do a Find with = in one. If none are found, delete the field from the database (or write it down and delete it when no one else is using the file, safest). In other words, use your developer layout as a kind of scratchboard, to put fields to check out. Then, after you've removed the obvious blank or obsolete fields, begin to arrange the fields a little better, to check for data patterns, then duplicates. It's a winnowing process.

Be sure to have a backup of all data before starting. Because if you delete a field then decide you want it back, you will have to get it from the backup.

You should likely consider using another external tool to look at your files. There are a couple commercial solutions, Inspector (which has a free limited version also), and another (can't remember the name). They are not cheap, but they can answer questions like, "Is this field used in any scripts?" (which FileMaker will also tell you when you try to delete it), or "Is this field used in any relationships?", or "Is this relationship used in any scripts?" Things like that. Also just a good overview of what all you've got.

[jessebSF]

Thanks Fenton. You have been really helpful.

Take care,

jesse

[bcooney]

As you see, it's a tedious process. What I do when upgrading another developer's system that I've inherited is to rename fields that are suspect to "xx_field" because this'll make them stand out in scripts. If you delete them, the script will just put "unknown field," and so you'll never know what used to be there. Then, as Fenton suggests, put those fields only on a developer-only layout.