Is there a way to detect an event in Accpac from an external app?

[Cyclops66]

I have written a number of integration apps with Accpac 6 databases, but I have a requirement for an app to process an order as it is created. I haven't done any UI work, so I am wondering what might be the best way to do this? I have read a large number of posts on this site, but nothing that answers my question.

I will be writing in Dotnet, against Accpac 6, SQL back end.

Any suggestions will be greatly appreciated.

 

[Jay Converse]

SQL trigger

 

[Cyclops66]

That is the way I was planning to go, I was wondering if there is a better way.

 

[ramam1]

You could instead poll - every once in a while check to see if new events of the type you like were committed and if so process them. You might need to know that you've processed them - depending on what you need that might be done by a separate table, or if appropriate a custom field on the object.

 

[Cyclops66]

Thanks, ramam1, but the problem I am resolving is a realtime issue. They have a polling solution in place. I am looking for something that will allow me to react as fast as possible to the creation of an order.

 

[Jay Converse]

If you want realtime, then a SQL trigger is your only way.

 

[Cyclops66]

Thanks tuba2007. The only reason I am hesitating and looking for alternatives is that the trigger needs to fire off an order to an external web service, and I am not sure that is the best way to go. What I really wanted (I know I am being demanding!) is a trigger that would fire off a process to extract the order info, generate any XML and messages to external agents and send it on.

But saying that, the trigger can generate the XML and post it to the service. I am just not a SQL guru and would feel more comfortable around a C# coded solution! (There, I admitted it.)

 

[Jay Converse]

Then you need to schedule a C# exe that runs every 10 or 15 minutes, looks for unflagged records in OEORDH, does it's stuff, then flags the records as processed (find an unused field like FOB or TEMPLATE)

 

[dgillz]

http://www.vineyardsoft.com

They have a great tool called knowledgesync. It is not real time like a trigger would be but this is a great tool and you do not have to be a programmer to use it.

Software Training, Implementation, Programming and Support for Macola Progression, Macola ES, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[DjangMan]

The trigger option can extract any of the information that you need from the order records - especially if the trigger is on the header (since the details will be in the database already). You can, in fact, have the trigger run a command line so you could run any sort of external application, too.

 

[philhege]

Also, think about having your trigger be minimally invasive to the transaction process. You may want to move the salient values to a work table apart from the live production table. Do something simple and quick with the production trigger. Then you can have a trigger on the work table that does the heavy lifting.

-----------
With business clients like mine, you'd be better off herding cats.

 

[vbdbcoder]

I have done something silimar to what philhge described.

Use trigger to populate to an external database's table with ORDUNIQ, track the status of what you need to track in that custom table. The C# application reads the custom table every 5 minutes to detect new inserted records by looking at the status field. When the process is done, update the status field in the record to skip the next process. It works fine.

If you really need real time data, you have to use the trigger on the custom table to pull the transactions and format the data the way you need.

 

[Cyclops66]

Thanks everyone for the ideas. We have gone with a combination of them all! A trigger on the orders table will use a stored proc to create the XML and push it to the service as well as start a command line app to write logs, send email, etc.

I have also taken the ideas of philhege to heart, a staging table makes a lot of sense in this issue.

Thanks again.