Hey,
I am probably overthinking this, however I would like help with clarification.
I have an integration I need to build. When certain events occur, say “update tracking” on a shipment, I write out to UD05 with a specific record and some data. These events can occur in multiple places, however I am aggregating all of the event data into this UD05 table.
From there, I need to process each un-processed record by feeding it into an API. I was thinking a Standard Data Directive would be perfect for this, wherein I would monitor the table for changes and then query the table for un-processed records and feed them into the other service, then update the record appropriately.
I cannot seem to get the Standard Directive to recognize table changes to that table, as it’s doing nothing when a record is added. I verified the table is being written to correctly, and I am certainly able to use a Method directive post-processing to see that the record was indeed written from wherever the event occurred to the UD05 table.
I tried a variety of things on my Standard Directive including having no conditions and sending an email via the widget, but it just seems to do nothing.
Yeah, I don’t think I’m going about this the right way when you put it like that haha.
So the writing of events across the system into a single table approach has worked well, but in the previous integration, we would poll the table and process records from it. In the new integration, they want to do process the event as it occurs, instead of polling/reading/writing.
In the new method, I need to see the record has been added, then do something with the record (in this case, to call a web service with the data in my record), then mark the record completed.
In my mind, the way to do this was to look for changes to the UD05 table (where the events are being collected) and then query for un-processed records. Perhaps it is better to do this via post processing?
I’m bound to be missing something here (before I finish my first cup of coffee), and you’ve likely done this already. But I’ll throw this out anyway.
You can look for changes to the record in an in-transaction directive and set an “enable standard directive” or set some context bpm data fields for use in the standard directive.
Do you have functions? I think you have functions, it seems that for this new approach your better method would be to call a function on Post Processing after UD05 is updated to do the web call.
Or better yet bypass UD05 all together and just invoke the function directly from the original transaction (though that would have to be done asynchronously)
CDC does that really well. The downside is, you get the flat table, you can’t like run a BAQ and join on tables. But it’s a trigger which you can pull and then run a BAQ in your external app (if you need custom data).
Hey Joe,
I will take a look at that approach. The advantage of compiling “to-be-processed” events in a table is that it allows me to both have a log of what events were processed, their data, and have the ability to re-try the event if it failed to be processed the first time.
The only sticky thing is that if I both look at the table for changes as a trigger, I need to then grab the records from that same table, process them, and then update the records. I might be getting into a recursive problem.
At any rate, I’ll check it out. This is unfortunately modifying an existing architecture which was purpose-built for polling into more of an event driven model, so my original design might be outdated.
Hey, yes I do make extensive use of functions. I actually even designed a function endpoint to handle the event-processing from the calling application, but it was recently decided that they wanted to have an API endpoint on their end for me to call with event data rather than them calling my API to retrieve/callback with event data.
So, I do like the function approach. I do want to keep a nice clean log of transactions if possible to handle re-tries and history (which is why I went with a table approach first) but as I mentioned above, I do have some concerns if I trigger off UD05 and hen update a UD05 record, I am getting into a recursive issue…
My event processing function is designed to consume a single event record (via a sysrowID) and then update the record with success/fail.
This is currently just handling the update of the UD05 record, so perhaps I could just create a new function to be triggered by the event writing BPMs (NOT UD05 add/update), have that query for to-be-processed records, then call this function for each row it processed.
Perhaps then, a neutral trigger is needed. Events write to log (UD05), trigger to function to go process events, function queries UD05 and processes data and writes back to UD05 rec. This way, we avoid UD05 triggers altogether?
Yes I can write up something later. You can make CDC call your endpoint with json payload or you can have Epicor store it in SQL and there is a REST API Endpoint you can invoke to Pull “Unread” messages. Epicor will give them to you, archive them and delete the archive after x hours.
SELECT * FROM cdc.CaptureLog;
SELECT * FROM cdc.CaptureStatus;
SELECT * FROM cdc.Subscriber;
SELECT * FROM cdc.SubscriberQueue;
Start with
You only need CDC Log Processor running for Pulls and the Sender is only required for pushes.
This (CDC) was introduced to me during a breakout session at insights a couple years ago and I can’t wait to see a Haso tailored session or article soon.
@SAD is it a correct assumption that Epicor CDC has triggers on Entity Framework (Kind of like Data Directive) and it’s not to be confused with SQL Triggers.
I’m trying it this way because it’s already like 99% built out, but I ran into something odd.
I am creating my UD05 records via code inside my event collecting BPMs. Does adding records that way somehow bypass the service layer?
I wouldn’t think so, however I have tried my In-Transaction BPM on UD05 to look for all kinds of rows (added, updated, unchanged) and spit out a message for either true or false. It’s never being triggered on the in-trans directive, however UD05 records are being created just fine.
Example code to create a rec inside an event collector BPM:
//Write new record in UD05
using(var UD05Svc = Ice.Assemblies.ServiceRenderer.GetService<Ice.Contracts.UD05SvcContract>(this.Db))
{
//get new row
var ds = new Ice.Tablesets.UD05Tableset();
UD05Svc.GetaNewUD05(ref ds);
//modify ds
var newUD05Row = ds.UD05.FirstOrDefault();
newUD05Row.Company = "JRF";
newUD05Row.Key1 = DateTime.Now.ToString();
newUD05Row.Key2 = "update_status";
newUD05Row.Key3 = "";
newUD05Row.Key4 = "";
newUD05Row.Key5 = "";
newUD05Row.Date01 = DateTime.Now;
newUD05Row.Character01 = json;
//update
UD05Svc.Update(ref ds);
}
}
In-Trans to enable Std directive (never triggers, true or false):
