Any ideas on how to set up a notification to run 30 days before a due date for an item as a reminder to do the work?
Responses (2)
  • Accepted Answer

    Friday, August 24 2018, 10:21 AM - #Permalink
    You should be able to accomplish this with the Escalation option of the Notification.
    For the sake of this example, I will call the resting state "Ready to Work"
    First, set up a notification with the "When" rule as State Is Equal To Ready to Work And Owner Is Equal To (Current User)
    Second, set up the template, subscription while you test, and the fields you want to see in the notification
    Third, in the Escalation section set a termination rule using the Delay Parameters with:
    Enable the Delay checkbox
    in the "Send when interval between:" select the date you will calculate against (ie Due Date) and current date becomes more than 30 days.

    The idea is that as long as the item in question remains in that Ready to Work State, the notification will trigger later; however, if the item transitions as work completed the rule set up no longer applies so the notification will not trigger.

    I have tested with similar notifications,the more than and less than on the delay parameter gave me some confusion, so I hope I have that labeled correctly above. You may want to test I have it labeled correct by making it more than 30 minutes or something.

    I hope that works, I am sure there are other options with app scripts or something, so this may just be one approach.

    Good luck!
    • Mikaela Nilsson
      more than a month ago
      Actually this is how I did set it up but the "start" rule was to common and the reminder too far off in time so the TS_ NOTIFICATIONEVENTS table got many entries and caused other issues.

      I need other suggestion, thanks!
    The reply is currently minimized Show
  • Accepted Answer

    Tuesday, October 23 2018, 07:26 AM - #Permalink
    I have accomplished this by creating a notification that runs a script every day. It doesn't send an email. Instead, the script checks to see if the "Next Calibration Date" is within 30 days. If so, it will update a read-only date field called "Date of Last Notification" with the current date/time. That field change triggers a separate notification that sends the 30 day email.

    Here's the Appscript I use:

    ' Require all vars to be declared before use (and then declare some)
    Option Explicit

    const valOwner = 378
    dim nId, nTableId, objItem, itemRec, dtmLastNotification, dtmNextCalDate, dtmNow

    ' First, get the ts_id of the item in question
    nId = Shell.ItemId

    ' Now get the table id of the item in question
    nTableId = Shell.TableId

    ' Create an object to hold the item
    Set itemRec = Ext.CreateVarRecord(nTableId)

    ' Search for the item
    Call itemRec.Read(nId)

    ' Dim some more Variables
    Dim fldList, fld

    ' Get Date of Last Notification
    Set fldList = itemRec.Fields()
    Set fld = fldList.FindField ( fldLASTNOTIFICATION )
    fld.GetValue dtmLastNotification

    ' Get Next Calibration Date
    Set fldList = itemRec.Fields()
    Set fld = fldList.FindField ( fldNEXTCALDATE )
    fld.GetValue dtmNextCalDate

    ' Calculate current Date/Time
    dtmNow = Ext.DateToDbLong( Now )

    ' Next Calibration Date is within 30 days and Last Notification was 7 or more days ago
    If dtmNextCalDate <= Ext.DateToDbLong( DateAdd( "d", 30, Ext.DBLongToDate ( dtmNow ) ) ) Then
    If dtmLastNotification <= Ext.DateToDbLong( DateAdd( "d", -7, Ext.DBLongToDate ( dtmNow ) ) ) Then

    ' Update Last Notification field to trigger other notification
    Set fld = fldList.FindField ( fldLASTNOTIFICATION )
    fld.SetValue dtmNow

    ' Update Last Modifier field and set the new value
    Set fld = fldList.FindField ( fldLASTMODIFIER )
    fld.SetValue valOwner

    ' Update Last Modified Date field and set the new value
    Set fld = fldList.FindField ( fldLASTMODIFIEDDATE )
    fld.SetValue dtmNow

    dim nRetries

    nRetries = 0

    ' Retry up to 3 times (minutes)
    Do While nRetries < 3
    If itemRec.IsLocked() Then
    nRetries = nRetries + 1
    Call Ext.LogErrorMsg( "Record is currently locked. Attempt: " & nRetries )
    ' Sleep for one minute
    Call Ext.Sleep(60000)
    ' Update record
    Call itemRec.Lock(true)
    Call itemRec.Update()
    Call itemRec.Unlock()
    ' Exit "Do While" loop if successful
    nRetries = 999
    ' Call Ext.LogInfoMsg( "Record Updated by Calibration Notification." )
    End If

    End If
    End If

    ' End of Script

    The 30 day email notice repeats every 7 days unless the first notification running the daily script gets terminated. I don't do this through the notification options in SBM. Instead, the script updates the "Date of Last Notification" field with a new timestamp if the "Next Calibration Date" is still within 30 days and the "Date of Last Notification" is more than 7 days.
    • Don Inghram
      more than a month ago
      Great suggestion. As of 11.4, you can use the SBM scheduler to run the script daily.
    The reply is currently minimized Show
Your Reply

Recent Tweets