Re-constructing details from backups

whonea · Post by **whonea** » Wed Jan 09, 2008 5:03 pm

I inherited a system that has run pretty much haphazardly (and unaudited) since 1995 and have run into a need to reconstruct activities for some long running (and in some cases, long inactive) accounts dealing with special use funds. We have managed to dig up enough backup disks to cover the entire period and - with exception of V6 - have the program disks with which to read them. I have exported all the information into DB2 and the recreation of extended tables went well until somewhere in version 8.5 where I'm seeing small gaps in the year end transitions due to detail deletion by the yearend closing process (fairly simple to resolve) but the V10 table reorganization is causing me some head scratching.

Is there any available documentation on the V10 tables formats and the relations between them?

One comment: I like the reporting, interface, and information available from V10, but the lack of historical detail will likely force us to another system if this recovery process doesn't work well.

Post by **Zorak** » Wed Jan 09, 2008 6:06 pm

There have not been any documents published since version 8.5 to map out the data structure.

In version 8.5, the important tables in Fund Accounting were:

FAACCTS - Stores the 9 digit account numbers
FAMSTR - Stores posted transactions
FAPRIOR - Stores unreconciled transactions from the prior year
FATRANS - Stores the "header" of unposted transactions
FATRANSD - Stores the "detail" of unposted transactions

In version 10, the important tables in Fund Accounting are:

FAACCTS - Stores the 9 digit (01-1000-000) account numbers
FAMAJOR - Stores the 7 digit (1000-000) account numbers
FAMSTR - Stores the "header" of posted transactions
FAMSTRD - Stores the "detail" of posted transactions
FAPRIOR - Stores unreconciled transactions from the prior year
FATRANS - Stores the "header" of unposted transactions
FATRANSD - Stores the "detail" of unposted transactions

What would probably be the easiest thing to do... If you successfully have the information in version 8.5, make a new installation of version 10 and convert the information from version 8.5 into 10.

whonea · Post by **whonea** » Wed Jan 09, 2008 11:33 pm

One very useful piece was the definition of FAPRIOR. That will help a lot as it appears to have sufficient detail to reconstruct the most problematic gaps: missing detail data for transactions made immediately before closing a year. The result of that appears to transfer nearly all the December transactions to faprior before they back up the end-of-year fiugres. Needless to say, I have changed procedures considerably.

One further question, if I may. The famstrd file contains reference to line_no - can you define that line number, please? I have yet to nail that one down nor have I been able to correlate the pr/ap numbers to which these appear to apply. I don't see any relation to the pr/aptrans_no columns in those tables but the date,amount,and employ name seem to permit me to feret out what I need (slowly <g>).

Post by **Zorak** » Thu Jan 10, 2008 11:43 am

FAMSTRD is the details (debits and credits) of the posted transactions in Fund Accounting. So, where each line references the transaction number, which is the unique identifier in FAMSTR, line number is the unique identifier for the FAMSTRD table.

Account detail was previously saved in a long, tab delimited text field in FAMSTR before version 9. Pulling this information out and populating the FAMSTRD table is where it gets tricky...

Again, I have to say, the conversion process does this for you automatically and will save a lot of trouble!

whonea · Post by **whonea** » Thu Jan 10, 2008 3:27 pm

OK, I was looking for linkages that don't explicitly exist.

Actually, pulling the monthly details out of those long strings is straight forward and the V10 tables are a lot easier to work with but I agree - let PC+ do the work where possible. For some reason, FireFox exports the first line of every export trims the leading spaces from the strings string - the rest are consistent - but after that it is a simple fixed width string.

The main reason for all this effort I'm creating for myself is that I am having to work from fragmented data - there are holes in the backup sequences and there is one stretch where the previous owner of the system decided to back up only parts of the data - and made some really Rube Goldberg changes to the COA rather than properly reconciling things.

One of my favorite features of V10 is the ability to back up to a folder. I agree with your warning - backups to the same drive/machine as the running system is a bad thing but backing up to thumb drives makes this probably the best of all choices. Going back to the V6/7 backups is a nightmare. Besides, finding floppy drives is getting harder all the time.

Thanks for your assistance. I think you've got me past my sticking points for now.

whonea · Post by **whonea** » Sun Jan 13, 2008 3:43 pm

There have not been any documents published since version 8.5 to map out the data structure.

In version 8.5, the important tables in Fund Accounting were:
.....

I don't have the 8.5 CD handy - where can I access the version 8.5 file documentation?