The Attivo Blog

We all know that Event Manager is a terrific data-entry slave for businesses. Need Table 1: column A totaled and copied into Table 2:column B?  No problem. Need Table 3: column B tallied and copied into Table 3:column C? Cake. Need Table 1: column A to somehow related all the way across to Table 3: column C? Might be a little messy in SQL, but Event Manager can handle it with relative ease. But sometimes our linking between tables forces Event Manager into the position of needing to separate rows that later we need to be totaled, or vice-versa.  The quick solution to this would be to populate a free-field on each individual row with that total amount; but it's easy to see that so many "writes" are unnecessary and can cause confusion later on.  Instead, it is much more beneficial to split up the single event into 2 or more separate queries. One of The Attivo Group's customers recently needed to implement an Order Acknowledgement that would be automatically distributed when certain criteria were met.  Event Manager satisfied that initial requirement easily with a simple Order Acknowledgement Event provided with the software.  Then the customer recognized that the canned event contained Total Amounts on the order that deducted back-ordered items. While this logic makes sense to many businesses; this customer preferred to include the total amount of all order lines on the acknowledgement.  When initially contacted about this task, I explored the idea of combining the total process into the existing query, but found that I was always forced to convert linked tables from outer joins to inner joins, thereby removing my ability to display multiple rows on the acknowledgement itself.  With that idea shelved, I split my task in two. First I wrote a query to total all the order lines at the line level, and store that total on the order header.

SELECT sum(line.qty_ordered*line.unit_price) AS "total", header.ord_no, header.ID FROM (line LEFT OUTER JOIN header ON line.ord_no = header.ord_no)   GROUP BY header.ord_no, header.ID

Then the triggered event stored the "total" above in a free field at the header level, virtually next to the "incorrect" amount. 

update header set freefieldX={total} where ID={ID}

Finally, I then added an additional filter to my separate Order Acknowledgement query/event that made it ONLY query orders that had a filled in freefieldX.

SELECT line.id, line.ord_no, line.(...),header.freefieldX, header.ord_no, header.(...), etc... FROM (line LEFT OUTER JOIN header ON line.ord_no = header.ord_no)  WHERE header.freefieldX <> 0 AND header.freefieldX is not null, etc...

Notice I did not group the resulting rows together this time, as I no longer needed them to be totaled; this was already done in the previous event!  I then let the second Order Acknowledgement run as designed, with the single exception of replacing the default TOTAL AMOUNT with the amount stored in freefieldX. While this nearly completes the event's multi-step procedure, there's two small steps needed to be absolutely sure that query/event #1 always runs before query/event #2.  First, I make sure the queries/events are on the same schedule in the system.   Then I set the priority of the first event to be higher than the priority of the second event. These steps prevent event #2 from waiting until the next scheduled time before processing - thus allowing event #2 to process immediately after event #1 completes.