Fields that aren't been used

[Vaughan]

<sigh> The last database that I looked at that had hundreds of fields in it was an invoicing system somebody had built, one that started off just as a personal aid and grew unintentionally into an system the business relies on completely. Unfortunately it was built from Day-1 with a poor data structure.

The hundreds of fields were the designer's way of avoiding the use of related files and records. (The creativity that the designer had used for some of the cludges impressed me enormously. Like the invoice line was calculated by multiplying a qualtity field with a number field, but the number field actually had a text string in it from a value list -- "print processing @ $2.50". It actually worked because FMP cleverly ignored all the non-numerical data in the field and found the 2.50!) All of this was done flat field so the invoice qualtities, items and totals were all hard-coded into the database as separate fields. The crunch came when they tried to get meaningful sales statistics out of the system... it cannot be done because the data is all over the place. They asked "can you spend a few minutes cleaning it up?" and I answered "umm no, it needs a whole re-design, not just a clean-up."

I'd suggest that a system with so many fields should have it's data structure looked at, because it could most certainly be optimised with a related system. The ironic thing is that if done properly, the system actually becomes simpler to use and maintain if the data structure is correct.

[pedrocmart]

Please HELP ME

I'm using a database that have more than 700 fields and this database is constantily modified and some fields are not been used, but i don't know how too identify this unusable fields.

To solve this problem i have to use a plug-in? a script?

Please if someone could help me

Thanks

[Vaughan]

To solve the problem; with 700-odd fields you are going have to use a script or some other automated system.

The script will have to go to the first field, perform a find for "==" and if no record are found, the field is unused. Change to the next field, peform the find, and so on.

You will need a layout with all the fields on it. You also need to know exactly how many fields there are, and which field has tab order 1 on the layout. Create a global text field "unused fields" to hold the field names found and a global number called "count" to count the fields.

Script:

Set Error Capture [on]

Set Field [unused fields, ""]

Set Field [count, 1]

Go to field [tab order 1 on layout]

Loop

Enter Find Mode []

Paste Literal ["=="]

Perform Find []

If [status(CurrentFoundCount) = Status(CurrentRecordCount)]

Paste Result [status (CurrentFieldName) & "

[Ocean West]

If you add noise to the fields in question like ********* or bulletts

Create12_v2.zip

Create12_v2.zip

Create12_v2.zip