Avoid corruption of linkage??

[Raymond Lyons]

I have yet to find a clear statement of the V5 limits on the # of fields a record can have and the limit to a records length. However, I have a table whose record has 883 fields and whose length is 10,900 according to "properties" and after successfully adding about 10 new fields I could no longer restructure the table, even if it was to delete a field. A5 would begin processing the restructure but would then get stuck and crash.

Having a backup of the shorter table, I am still OK. I assume I am going to have to break the table into 2 linked tables. Other than a auto-incrementing number (essentially using Cal's and others way, not the built in way) there is no field or combination of fields that uniquely identify a record. My concern is the linkages may get messed up, as I have had this happen in the past, and once it happens fixing it is hopeless except for the most recent backup.

On the assumption that I need to break the table into 2 linked tables (that I can use as if they were one), does anyone have any experience (words of wisdom, cautions, recommendation, etc.) they can share on this issue?

Ray Lyons

PS, In posting this I found out what happens if one forgets to put in a subject for these postings. Like do it all over again! That Send Message button needs to do some validating.

[Marc King - A5solutions]

RE: Avoid corruption of linkage??

Ray, A record w/836 fields and field length 10K ??? Not a solution I would consider suitable for any database.

Sounds like a better choice would be a Word doc or PDF. Then just use A5 to manage the stored files.

This will give you much happier result in the long run.

Marc
www.a5solutions.com

[Raymond Lyons]

RE: Avoid corruption of linkage??

>> Not a solution I would consider suitable for any database. Sounds like a better choice would be a Word doc or PDF.

[Marc King - A5solutions]

RE: Avoid corruption of linkage??

Ray, you may be correct. I only question your design model. I'm quite familiar with amortization tables and they are never stored as a record. They are are generated on the fly based on terms and interest rate in reports or as "output" in temporary tables.

If you are trying to input payments or generate invoices these would be individual records grouped by customer.

What are you trying to accomplish that requires 800+ fields per record?

Marc
www.a5solutions.com

[Peter.Greulich]

RE: Avoid corruption of linkage??

Ray,

In v.4 at least:

1,023 fields per record
19,600 bytes per record

Peter

[Raymond Lyons]

RE: Avoid corruption of linkage??

Peter, yeah, I thought that applied to v5 also, but maybe not?

Marc,

No, there are no amortization tables involved at all. Ironically, the major reason there is such a large record is because the previous version of the app (which made use of several linked tables) was too susceptible to corruption from links getting crossed in a network environment. �Too susceptible� in this case meant one time in a year, but that one time was such a disaster that I felt it was worth trying to do away with linkage problems by trying to get as much as possible into one large record. This whole area, by the way, is why I can�t wait for the promises of V6.

As an alternative I have toyed with the idea of having the users enter or modify bunches of data via forms with only variables that would then update individual fields in records in a number of tables. But for reporting and some other processing, these records would still have to be linked in a set. Alas, my paranoia about some of that processing possibly causing linkage corruption made me go with trying the one large record approach before possibly going down other roads.

I was under the impression that I was still more than 100 fields short of A5's record field limit (I thought it was 1023 and much more than my 10k+ size). So it was very disappointing to have things bomb out at around 900 fields, meaning I may have to go back to several linked records after all the work of trying to make one work. I thought maybe others might have some great words of wisdom about how to avoid linkage problems, or maybe a good way to manage two linked records that are essentially one. But I am afraid that in a previous version of this app and in others that I may have already invoked most of the ideas that have been presented here on this subject. Just thought I would see if anyone had any new ideas, or could point me to a good old one. I think I have a pretty good home brewed auto-increment scheme for making a unique record identifier (thanks largely to many others on this board and at Learnalpha.com).

As a point of interest, the database program I import from has at least 10,000 fields for each for each mortgage client. Interestingly, it saves each �record� as a single file, and uses the file name as the way to uniquely identify a �record.� It then has a number of indexes that allow the user to find and open the file needed. For most purposes it never opens anything but one client file plus a number of program support files it needs to be able to work on the one client file. Also, it clearly does not waste space, as dbf does, when a field is empty or partially empty, so file size remains quite small. I assume that when reporting is done on more than one file, it probably opens a file, gets what it needs for a report, closes it and opens another and so on and then generates a report. It�s an interesting approach but I have no idea what database engine it uses except that it clearly is not dbf nor does it use Access or anything else I can recognize.

Ray

[Jim Chapman]

RE: Avoid corruption of linkage??

Hello Ray,

>>the major reason there is such a large record is because the previous version of the app (which made use of several linked tables) was too susceptible to corruption from links getting crossed in a network environment.

[Tom Cone Jr]

RE: Avoid corruption of linkage??

Ray,

Your design will quickly fill the bandwidth on the local network since presumably a lot of fields filled with blank spaces will be shuttling back and forth whenever you do a query or run a report. (This comment assumes that not all 883 fields will be needed in every report, but that all 883 fields will have to be moved across the network even if you want a simple customer list).

-- tom

[Raymond Lyons]

RE: Avoid corruption of linkage??

Jim, this is the sort of words of wisdom (experience) I was looking for.

Thanks.

Ray

[Raymond Lyons]

RE: Avoid corruption of linkage??

Tom,

True, but 1) there isn't a lot of reporting going on during the day, and 2) don't we have the same issue if the vast majority of those fields are spread across a number of records in a number of tables in a set and reports generally have to be done at the set level in order to grab the data the report needs? I don't see much if any difference. Am I missing something?

Once again, many of us needed to get away from the dbf structure a long tiome ago.

Ray

[Marc King - A5solutions]

RE: Avoid corruption of linkage??

Ray Wrote >>>Interestingly, it saves each �record� as a single file, and uses the file name as the way to uniquely identify a �record.�

Ray That is my point exactly. I think you may be misinterpreting the output data as fields when in reality they are documents assembled for export.

One of my clients is a very large brokerage firm with over $10M per month in mortgage loans. I have worked on both the Myers and Point Brokerage Management systems. These are the two leaders of automated loan processing in the brokerage business.

While they have dozens of loan types and associated addendum info, including import web services for realtime lending rate input, none of the forms consist of more than 30 or 40 input fields and other disclosures are only single field memo type input.

They are both custom relational ASP mySQL web projects. If you are having coruption of your linking I would not fault A5. I would rather look at your "homegrown auto increment" script and your overall design approach.

Both Myers and Point are considered highly versatile yet in reality they are quite simple basic relational models. If you look at your relational model and find it problematic a flat file is not the solution. I think you may want to simplify your design and get back to A5's built in features. They work quite well for me on a number of high traffic networks.

Just my oppinion :)

Good Luck

Marc
www.a5solutions.com

[Raymond Lyons]

RE: Avoid corruption of linkage??

Well, the program I referred to is the Calyx Point program which started out life as a DOS program and is almost certainly not the Point Brokerage Management sytem you mentioned. And the documents I import from simple text files exported from Point, but the Point data files contain all the data for the loan in question, and that can have a humongous number of fields and has nothing to do with a ASP mySQL web project since it is a standalone program that I can and do run entirely unconnected to the Internet.

But that is all beside the point. You say "I would rather look at your "homegrown auto increment" script and your overall design approach." I didn't make myself clear enough on this: When the app experienced the problem it was using A5's built-in
auto-increment function, and I am pretty sure that was at least part of what led to the disaster. My "homegrown" one is what I am using now, and it will never be known if using it would have prevented the problem in the old version of the app.

And my app is not 100% flat file. The table with the large record still has to be part of a small set in order for some entry and reports to be accomplished. But now let's suppose far more tables all linked in a set. Yes, there would be a fewer fields in some of the records, but it would not be a huge reduction. And there would be at least several linked reocords that would have to be accessed to. for example, run a report.

Bottom line is that to do much of anything you would still have to access a large number of fields whether they be in one record or 12 linked records. So I don't see how having them in one record is such a sin, especailly since record linking through aut0-increment fields is clearly a problem with A5 operating in a network environment. Maybe you are are the exception to the rule of never haveing such problems, and maybe I should have you build my apps so I can retire, but you have failed to convince me that trying the road I have gone down is such a bad idea.

Ray

[Marc King - A5solutions]

RE: Avoid corruption of linkage??

Except that on a network a table with 800 fields will be locked to all other users when user1 is editing field1 which will shut down the dozens of other forms based on the table needing entries at other workstations.

Marc
www.a5solutions.com

[Raymond Lyons]

RE: Avoid corruption of linkage??

Ah, but in the part of the mortgage game I deal with (and for mortgage data using Point, for that matter) two users almost never have reason to access the same record, much less edit it. And editing a field locks the record, not the entire table, doesn't it? My God, if it was the latter nothing would be possible with A5!

Ray

[JohnZaleski]

RE: Avoid corruption of linkage??

I've been developing commercial apps since 1980 using a variety of development tools.The ugliest problems I have ever encountered have been when I have used a tool or operating system at or close to some kind of limit.They are many times inconsistent and difficult to troubleshoot.
A normal function like adding new fields is giving you a problem. For me that would be all it takes. I'd break up the record.
As for caution using xbasic to create a unique linking field, "do not count on the unique field rule".There is a multi-user flaw involved.Instead use some code in the "cansave" event of the form used to add or edit the record.There is a thread about this and I think a small article in the Newsletter.