cleanup function

[crimmelcp]

To All:
Does anyone have a function that can read a selected table and return all fields that are NOT used on a form or report?


Charlie Crimmel

[martinwcole]

RE: cleanup function

Charlie, good to see you. If you were only worried about 1 table, you could search for one field at a time using a5doc - but an automated process, that searched all tables would be a horse of another color.

I have a function that that reads all forms and resets passwords, which could be modified for the above, but would take a chunk of time. I would email Cal and Bill Parker, and ask for ideas or functions they may already have for their private use. Also Jim Chapman wrote a neat function that compares two databases and returns differences between the tables.

It would be a very useful function.

[CALocklin]

RE: cleanup function

I love this issue. It's rather like saying, "There are three stacks of hay over there. I want you to tell me if one of them does NOT have a needle in it." The only way to be sure is to check every single piece of hay until you find a needle. If you get through the whole haystack and don't find a needle, then either there isn't one or you missed it. That's the catch - after you've been doing it awhile you get tired and may get careless and miss what you are looking for. That's why you need a machine (computer in this case) to do it for you - it won't get tired.

My AIMS App Analyzer will do it but you are correct - it can take a BIG chunk of time. It all depends on the table and form sizes - and your computer's speed, of course.

The record was a total of 5 hours! There were a number of tables that had somewhere near 1000 fields and a number of layouts that included all, or most, fields on the layouts!

A more normal time for checking field references is probably somewhere around 1/2 - 3/4 hour total. This is much longer than a "normal" search which does not include field references. Of course, the time for any search will depend on the size and complexity of your app but I guarantee it will be faster and more accurate with my App Analyzer than trying to do it 'manually'.

It's a two step process - a "documentation" run followed by a search of the documentation. Both are automated - define the parameters and start the "documentation" run then define the search parameters and start the search run. There is an option to show a report of only those listed as "No Reference Found" after running a search to find all references to fields.

This is one situation where I think the $19 I'm asking is way below the value received.

One Warning: Naming is critical to getting accurate results. If you have a field name like "Address" and the word "Address" also appears as an index name and on buttons, text labels, etc., each of those other objects will appear as a possible hit even if the field itself is never actually used. I keep pushing this issue and some are probably getting tired of hearing it but I can't overstress the improvement in search efficiency you will get by creating unique names. (And, if you aren't searching for index names, field names, layout names, script names, etc. as part of you application development, you are losing even more efficiency because you are either changing things that shouldn't be, or not changing things that should be, or both. Then the app comes back again for even more changes and the customer/user is getting upset because every change causes another problem.) Every name doesn't have to be unique but making the field name something like "St_addr" would be much better because you won't be using that on buttons or labels. "St_addrf" (ending in "f" to indicate a field name) is even better because you will know that it is not a variable name in a script. If you want to read more about my naming recommendations and, more importantly, WHY I recommend them, go to www.aimsdc.net/Tips_Funcs/A5_Naming_Conventions.HTM.

If you think you may be getting bogus hits on any particular field name, you will have to check every "hit" on the list but this will still be much faster than trying to do it 'manually'.

Cal Locklin
www.aimsdc.net