Exchanging data with Peachtree

[DanBrill]

Well I think I've gotten the hang of swapping data between QuickBooks and FileMaker. At least as well as anyone seems to. So for my next trick I'm thinking about integrating a version of my solution with Peachtree as well.

Does anyone have any experience moving data between Peachtree and FileMaker? Is it possible? How does it work? XML? Text files? ODBC? Something else?

Any insights to get me started are appreciated.

Dan

[LaRetta]

Hi Dan!

Well, I'm currently pulling data from Peach and it's quite easy. If the files are large, it'll crash on us - and I must break it into batches, but it's workable. Getting it in to Peach? When you figure it out, please let me know! I'll be replacing Order Entry and Receivables with FM within 3 months and then, for 6-8 months because of my time demands, someone will still need to enter the same orders in Peach for GL. Auditors indicate they will accept just the totals be entered per GL once we've mirrored for three months to verify the data.

I don't look forward to the process, I'm afraid. But it can't be any worse than AccPac! And someone just entering a list of summaries per GL won't be difficult. Now I know who to ask when we come to converting our GL & payroll because you'll have it all figured out by then!

LaRetta

[Ugo DI LUCA]

Dan, LaRetta,

Sorry about my ignorance. What is PeachTree ?

Some posters (or was it just you...) did mentionned it, but I have No idea of what it is, how data is formatted, what'its use.

Just for my own knowledge as a start.

[DanBrill]

Thanks! I'm mainly concerned with pulling data out of an accounting package. My general philosophy is to let GL software do what it is designed to do and not re-invent the wheel within FM. I use FM to fill in the gaps. Specifically, I'm developing for non-profits, and my solution draws contribution data from the GL and matches this to a donor record in FM, where it can be tied to other things like volunteer history, skills, interests, etc. Then we can compare who has given with who volunteers with who has the financial capacity to give (based on their address).

So if getting data out is easy, I'm in luck. How big is big? What will cause a crash? Is it xml? ODBC? Text? Can you pull out invoice data? Deposit data? Is it easy to get the chart of accounts?

I already have the perfect payroll solution. I call ADP and let them worry about it. I hate doing payroll.

Dan

[DanBrill]

Hi Ugo,

Peachtree is an accounting package, usually used by small businesses. It is fairly common in the USA, but I don't know what its international distribution is like. QuickBooks is its chief competitor. I've always used QuickBoks myself, and like it, but I hate telling potential customers that my solution won't work with their general ledger system, and not surprisingly, most don't want to uproot the finance department becasue the development department or volunteer manager wants a new database.

I can't say I know much of anything about Peachtree yet. I suspect I'll be learning over the next few months.

Dan

[LaRetta]

Dan asked ... Can you pull out invoice data? Deposit data? Is it easy to get the chart of accounts?

It can be easily dumped as an ASCII. Every field in PeachTree can be selected in a Query Report from the Main Menu and then just Specify Condition 'Type of Output' and select each field you want. There are 3 file types to choose from but only ASCII doesn't crash on us. Ours is a very old version (1987) of PeachTree and it was never upgraded.

The only module that we couldn't pull was the Customer Master. But, since there were 23,000 customers that had never ordered, Management wanted to purge that db anyway (they use ACT for their REAL customer files). We ended up using our Invoices db (which includes CustomerID (and EVERY field in the Customer module) and I created the Customers db from the last Invoice from each Customer to grab their most current address, phone, etc. Our crash in Customers is probably unique and shouldn't be a problem for you.

Be prepared to correct inconsistent data, as Peach doesn't allow Validation as it should so Users put the State in the Address field, etc. And possibly be prepared to correct years 2000 and 2001. Year 2000 might come through as 1900 and one month in year 2001 might come through as blank. Yep! February 29th - even though they supposedly ran a 'Y2K' fix on the program.

But, sorting Invoices by Invoice # will make those easy to identify and correct.

Major caution - The UniqueID the Company used was not unique and was never validated. Peach treats case sensitive as unique, so we have two customers with same ID (or 11,000 duplicate Customer IDs). For instance, ALGLOV and AlGLOV and alglov were all different customers! If you end up with that situation, set your right key in your relationships to Storage Option Language ASCII and you can match them unique by case sensitivity (thanks Ray). I actually set them all to ASCII because I was joining so many files in several directions and didn't want to risk missing one. Once they are properly related, you can run replacements to create your new true UniqueIDs through them and delete all the redundant fields.

I suggest you map it all out first and run test exports and store the exported Queries as Reports within Peach; script all your FM imports and cleanup scripts to fix dates, etc. so that, when the time comes, it'll be a matter of working right down a list and then letting FM do most of your work for you. It's amazing what the data will look like in raw form when viewable in FM sorted and isolated. Major problems will jump out at you. Then you'll spend two weeks talking to Management forcing decisions to establish consistency in their new program. And no matter how much you automate it, you will have some 'hand' cleanup work to do on the data.

Feel free to contact me directly, Dan, if any questions come up for you with PeachTree. Oh, and on crashing, I think it's a Files/Buffers (DOS) issue if the files are too large. Depending upon the data, it appears that I can only get .5 mb size files at a time.

I actually love data conversions! Ugo, PeachTree is like Mas90 or AccPac. They are DOS-based Accounting programs still, unfortunately, much used here. Accountants and Auditors love their rigidity.

LaRetta

[DanBrill]

Thanks, LaRetta. You're right -- accountants love Peachtree and real people hate it. Vice versa for QuickBooks because it is so easy to edit.

The Y2K stuff is really scary. Has this been fixed properly in later versions?

ASCII is easy enough to deal with though. Can you initiate the import request from FM, or do you have to initiate an export request from PT?

Thanks for all the great info. Exactly the stuff I need to know.

Dan

[LaRetta]

As far as I know, you must export from Peach and then import into FM. Just to easily view the data, just drag your exported files directly on to your Developer or FMP shortcut. FM will import them for you. But you'll want to use regular imports for the real thing, so your cleanup scripts work (and so you can match fields).

Yeah, I love Quicken. I started using their first version and have used every program they have, ever since (QuickBooks, QuickPay, etc.). I've been considering it, but I'm currently fighting with Auditors over the issue. I'll bet I lose. They are willing to let me create an Accounting package but not use Quicken - because Quicken is too easily modified (which is why Users love it).

LaRetta

[DanBrill]

Yes, but QuickBooks has an audit trail. Data is easy to change (the program data that is, not the audit trail data), but changes leave fingerprints in the audit trail. Plus there are lots of ways to configure the security, so you can really limit who can change what. My auditors have no problems with me using QuickBooks. Tell your auditors I said its OK.

Dan

[LaRetta]

Tell your auditors I said its OK

Oh thanks! That should do it!

Actually, I've run into the same arguments (and refusal to allow QuickBooks) in two other businesses (both non-profits). Maybe it's just Oregonian Auditors, but they can be an old-fashioned bunch, for sure. Well, I won't give up the fight, that's for sure. Thanks Dan!

LaRetta

[Leb i Sol]

Hi Dan!

Peachtree could use a BIG change...hm hm...

but there are ways arround it!

Depends on what data you want to move....PT is more "giving" on export rather than import. You can access data by many ways:

-ODBC proved limited acces but a good "insite" to the structure

u need Prevaise.SQL Workgroup engine that will allow you create a DB=point to the company folder & access to some Table and fields but the nice thing about it is that it will accept SQL Based filtering.

- the other option to data access is to run Reports or Exports from Peachtree which is actually the best way to get to the most of the data.

ALthough PT likes to hide fields ur give an option to export those fiels.

- DynamicDataExchange.....is THE way to get to the fileds that are not avaialble for "viewing" in ODBC and to "silently-synch up" with FM....the catch is= NOT even PeachTree support supports it so ur left on your own. ( I have some documentation and samples on DDE) it is a great tool if you know Excell and DDE/external function in it or VBA

You CAN also make PT run on TS....and you can....well

Let me know what specifically you would like to pull from PT

take care!