Utility to keep track of Database Flow...

[RemingtonSteel]

I would like to have a sense of Macola's data flow. Does anyone uses a utility to keep track of database that has been used/touched if a particular transaction took place.
Any help is appreciated.

 

[dgillz]

I assume you mean the tables that are affected. Macola has only one database, with multiple tables (files if you are using Pervasive)

In order to answer your question, we really need to know if you are using Pervasive or SQL - and to be honest you should never post a question without this info as well as your version of Macola.

In this case however, I can make a few assumptions. If you are using Pervasive, I know of no such utility. One of many reasons to drop Pervasive like a bad habit.

If you are using MS SQL, I like SQL Delta. MS SQL Profiler can do this, but it is much harder to read, not nearly as user friendly.

If you own Flexibility, you can look at the DRMs to see what tables are affected, but it does not tell you how it is affected.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry

 

[RemingtonSteel]

Thanks for the prompt reply Dave!
We are using Macola 372/SQL Server 2000. Is there any documentation that tells me what affects what/when regarding the Macola tables. I'm very new to this software, so I really don't know where to start because I don't have the resources and training (I don't even know if there's such a training). If you can give me some advice/approach on how to deal with Macola development that's really great.

 

[dgillz]

You can get training from your Macola business partner or from Exact. I do not know of any documentation that tells you waht affects what and when for Macola ES.

Do you have a specific question?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry

 

[RemingtonSteel]

I don't have specific question right now. Thanks a lot!

 

[jdblank]

Dave, I agree, SQL Profiler is a pain to read. What about this SQL Delta? I've never seen it. Does it give you calculations by field? In other words, if we wanted to write a program to mimick a Macola transaction, will Delta kick out that kind of detail?

Many thanks......

JB

 

[dgillz]

JB,

First, my name is Don, Dave is the person who spoke the quote in my signature.

On SQL Delta, it will not give you a formula, but you can track results on a field by field basis, new records added, old records deleted, etc.

Using this to mimick a macola transaction is exactly why I use it. Depending on what it is, I may have to do several tests, looking at before and after data, to really get my arms around what is happening. For example, a backorder in OE does certain things to the Macola database. However if the customer or item is not flagged to accept backorders, a completely different database update takes place - namely it deletes the back order, reduces the qty allocated, etc.

Throw in serial/lot, multibin, confirm pick or ship, forced demand=pull, configured items, kit items, etc. and you could be looking at a lot of work. But SQL Delta will be a great help getting there.

It has a 30 day eval if I recall, you should try it.


Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry

 

[jdblank]

Thanks a lot for the info Don, sorry about the name confusion. I think I'll give it a try.

 

[RemingtonSteel]

Don-
Since you were using SQL Delta, I just would like to confirm how it compares tables. If I add a record a FirstDatabase.FirstTable and compare to SecondDatabase.SecondTable (note they are on different servers but with the same structure) will SQL Delta suppose to detect that? The reason I asked is because I tried adding a record on the first table and refreshed the comparison in SQL Delta and I didn't get a comparison. I'm wondering if SQL Delta doesn't detect the changes on a record level.

Thanks!
Remi

 

[dgillz]

Yes it does. By default it compares structures, but you need to click on an additional button to get it to compare data.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry

 

[dgillz]

Just a followup on SQL Delta. Hopefully this real life story of how I use SQL Delta will help you.

I was tasked to create an application that imports unposted AP transactions into Progression. My gut reaction was that this updates 3 tables, APCTLFIL (last voucher #) and of course APTRXFIL and APTRXDST.

However, these transaction are all subject to 1099s, something I do not usually work with and do not have setup in any of my Macola databases. So I thought it would be prudent to see if any other tables are affected when you process a 1099 AP voucher.

So I took a copy of my database. I then changed one vendor to be subject to 1099s, and entered one AP voucher for that vendor. Finally I ran SQL Delta against the 2 databases, choosing compare structures and compare data.

No structure differences, as expected. I had 2 tables with different data: APVENFIL (the vendor I changed to a 1099 vendor) and APCTLFIL (updated for next voucher #). So this was normal.

The AP Vendor Audit file (APVENADT) had new data (more records) for my modified vendor, a B before change record and a C after change record. Again this was expected.

The APTRXFIL and APTRXDST had new data for my 1099 voucher. I could see by field comparisons that the only difference between a 1099 voucher and a non 1099 voucher was the ap_1099_fg field, which was set to Y and N, respectively.

No other tables were affected. I can now confidently build my application to import AP transactions.


Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry

 

[RemingtonSteel]

Thanks Don! Now I have a slight idea of how to use the SQL Delta.

Cheers!
Eugene