Adding fields corrupts table

[deano]

Hi,

I've got A5 V4.5 and two very simple tables. 15 or so fields and a couple thousand records in each. Easy field rules, few saved operations, couple indexes, no xbasic. Can't get much simpler.

I want to export some of the fields to a simple text file; open in word processor, a little editing, and print it. But I don't want all of each field.

Since A5 won't export calculated fields, I tried to add a new character field, length 80, to each table. Figured I'd do an update to populate it, then export that field. The restructure seemed to go OK, nothing in the Violated table, but when I looked at the result, my memo fields were corrupt. Some were with the wrong record, others were truncated from the front, others were missing, and some were fine.

I worked around it by creating a new, empty, table with same fields plus the new field. Restored the first table from my backup program and appended the records to the new table. Did the update to populate the new field, changed it to a calculated field so it will fill in automatically with any new records, created an export operation, ran it and got what I wanted.

Why couldn't I add a field to the original tables? I believe the usual thing to watch out for in such a case is a corrupt record, but if I append all records to a new table, am I not appending the corrupt record as well? I guess I can't be, because the new table works.

Any ideas? I'm stumped!

Thanks,

Gary

[Tom Cone Jr]

RE: Adding fields corrupts table

Gary,

You might try searching this forum. I vaguely recall others reporting trouble when they tried to insert a new field in the middle of the field list during restructure. If memory serves, the recommendation is to add new fields at the bottom of the list. -- tom

[JerryBrightbill]

RE: Adding fields corrupts table

Gary,

Tom is correct. If you add a field, add it to the bottom of the list. Then run all of your updates. This is especially true if you have used long names for field names. Names longer than 10 characters are saved in the data dictionary in what is essentially a memo field and referenced back to the original field position on the list. If you change the field order, this reference seems to get messed up.

Now if you have added a field and saved the table structure, then you can go back and reorder the table. This seems to work reliably. I usually don't bother, since the order doesn't really matter.

Another similar issue occurs if you have to delete a field. I have found that the only reliable way to delete a field and not have problems, is to move the field to the last in the list, save the new structure, reopen the structure, and then delete the field. I have not had any problems using this method. When I deleted fields from within the list, I did occasionally have corrupted data.

I sometimes add and delete fields frequently during development. Using these rules and keeping the field names to 10 characters, I have not had any problems, even with memo fields.

Jerry

[deano]

RE: Adding fields corrupts table

Tom and Jerry, (sorry!!)

Thanks for the ideas, but ... the new field _was_ added to the bottom of the list. Its name was less than 10 characters, and none of the other field names are more than ten characters. Some are alphanumeric, if that matters?

Gary

[Tom Cone Jr]

RE: Adding fields corrupts table

Gary,

Field names must begin with a letter, and can contain the characters A - Z, 0 - 9 and the underscore character _

If you tried to add a field using a field name that began with a numeral, or contained other non-alpha characters perhaps that explains what happens.

-- tom

[deano]

RE: Adding fields corrupts table

Nope. Field name was "printit". You don't suppose A5 chokes on the word "print" in a field name??

Gary

[Peter.Wayne]

RE: Adding fields corrupts table

Most likely it is a corrupt record, but the easy way to tell
is to create a duplicate, empty table, then restructure the
empty table, and then append, 1/2 the records from the
old table to the new table. If there is no corruption, then
the error is in the second half of the records; if there
is corruption, start over and bring over only 1/4 of the records, etc.

[deano]

RE: Adding fields corrupts table

Thanks, Peter,

I had considered the possibility of a corrupt record, but was put off by the task of digging out the one of 4000. I never thought of bringing in half, then half again, and so on. When I get a chance, I'll give that a try.

When all else fails, try the simple stuff!

Gary