Follow

Is there a way to trigger an alert upon the completion of a process?

Summary

It is possible to setup a user defined query that will alert you when a new document is created. You will want to look at updating the "where" clause to set document generation time equal to system time.

In some cases, your database time will not match the server time so you may need to use an adjustment to the Document Creation Time to make it match the server time.

 

Step By Step Instructions

You can use this query to see the current server time and verify whether or not it matches your database time:

SELECT CONVERT(VARCHAR(2),GETDATE(),108)+RIGHT(CONVERT(VARCHAR(5),GETDATE(),108),2)

This will return the current server time on a 24 hour clock. So for example, 0900 would be 9AM and 1600 would be 4PM.

You can then use this query to set up your alert. You will need to replace anything in *'s with your desired data. For example, *table name* would be replaced with OINV to see A/R Invoices:

---

Declare @HourAdj AS INT = *number of hours difference between server and database time*

SELECT *Field1*, *Field2*, ... FROM *table name* T0

WHERE T0.[DocDate] = cast(GetDate() as DATE)

AND CONVERT(VARCHAR(2),GETDATE(),108)+RIGHT(CONVERT(VARCHAR(5),GETDATE(),108),2) >= (T0.[DocTime]+(@HourAdj*100))

AND (T0.[DocTime]+5+(@HourAdj*100)) >= CONVERT(VARCHAR(2),GETDATE(),108)+RIGHT(CONVERT(VARCHAR(5),GETDATE(),108),2)

---

After saving this query, you can use the steps on this page to configure the Alert: http://http://support.orchestrated.com/hc/en-us/articles/206436628support.orchestrated.com/hc/en-us/articles/206436628

The alert should be set to run every 5 minutes.

Example:

In this example, I am looking to be alerted when a new Sales Order is created in Orchestrated.

First, I run the query to verify my server and database time.

My database time is 13:33 (1:33PM), but the server time is 11:33. My database time is 2 hours ahead of the server, so I need to use -2 hours as my time adjustment to make the document time in the query match the server time.

I would like to see the Document Number, Business Partner, and Document Total for each Sales Order that is created. I use the video on this page to determine my Table and field names: httphttp://support.orchestrated.com/hc/en-us/articles/207139297-How-to-write-queries-tables-fields://support.orchestrated.com/hc/en-us/articles/207139297-How-to-write-queries-tables-fields

In this case, I want to use the ORDR table and the DocNum, CardName, and DocTotal fields.

I enter this information into my Query and come up with:

---

Declare @HourAdj AS INT = -2

SELECT DocNum, CardName, DocTotal FROM ORDR T0

WHERE T0.[DocDate] = cast(GetDate() as DATE)

AND CONVERT(VARCHAR(2),GETDATE(),108)+RIGHT(CONVERT(VARCHAR(5),GETDATE(),108),2) >= (T0.[DocTime]+(@HourAdj*100))

AND (T0.[DocTime]+5+(@HourAdj*100)) >= CONVERT(VARCHAR(2),GETDATE(),108)+RIGHT(CONVERT(VARCHAR(5),GETDATE(),108),2)

---

 

 

Version 4.5.1.0

 

 

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments