The OneStream Community is temporarily frozen until June 29th due to the ongoing maintenance. Please read the blog post here to learn more.

Forum Discussion

Jesse_DC's avatar
Jesse_DC
New Contributor II
2 years ago

Workflow Validate Intersection Send Via Email

Hi there,

I have a Workflow Import process that runs automatically via Task Scheduler. My question is, is there a way to capture the Invalid Intersections then send it via email? The idea is to get alerted via email without opening the application that the Validation failed.

Regards

Jesse

Workflow

4 Replies

  • kenostrovsky's avatar
    kenostrovsky
    New Contributor III
    2 years ago

    Hello Jesse, 

    There is a new tool that has been released on the marketplace called Data Import Schedule Manager. it schedules data loads as well as email notifications. You have a lot of flexibility in defining what the email looks like. 

  • Keyser_Soze's avatar
    Keyser_Soze
    Contributor
    2 years ago

    Hello there,

    I did something very similar. I was able to retrieve all the Transformation/Validation rejects, add a 'ByPass' rule to the concerned members in their transfo groups then retransform the source file.

    Here is the part related to the validation errors, you should insert it in an "Event Handler" business Rule [Transformation Event handler]:

    If args.OperationName = BREventOperationType.Transformation.ValIntersect.FinalizeValidateIntersect Then

    '*********** Before Routine ************'
  
   Dim wfPk As WorkflowUnitPk = BRApi.Workflow.General.GetWorkflowUnitPk(si, mywfClusterPk)
   Dim wfInfo As WorkflowInfo = BRApi.Workflow.Status.GetWorkflowStatus(si, wfPk)	
  If wfInfo.HasErrors() Then
	Dim errorDict As New Dictionary(Of String, List(Of List(Of String)))
	Dim dtValidation As DataTable = Nothing
	Dim sqlValidation As New Text.StringBuilder
						
	sqlValidation.Append("Select * ")					
	sqlValidation.Append("From StageToFinanceValidationError ")
	sqlValidation.Append("Where StageToFinanceValidationError.WorkflowProfileKey = '" & wfPk.ProfileKey.ToString & "' ")
	sqlValidation.Append("And StageToFinanceValidationError.WorkflowTimeKey = '" & wfPk.TimeKey.ToString & "' ")
	sqlValidation.Append("And StageToFinanceValidationError.WorkflowScenarioKey = '" & wfPk.ScenarioKey.ToString & "' ")
						
	Using dbConnApp As DBConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)								
		dtValidation = BRAPi.Database.ExecuteSqlUsingReader(dbConnApp, sqlValidation.ToString, False)	
		Dim sb_ValErrorsReport As New Text.StringBuilder()						
		For Each drVal As DataRow In dtValidation.Rows
			Dim strValMsg = drVal("ValidationmessagesXml")'.ToString()
			Dim validationMsgList As DataCellValidationMsgList = XmlObject.ReadXmlString(si, GetType(DataCEllValidationMsgList), strValMsg, True)									
			If Not validationMsgList.Messages Is Nothing And validationMsgList.Messages.Count <> 0 Then
				Dim errmessage As String = validationMsgList.Messages(0).ErrorMsg
				Dim errdetails As List(Of String) = validationMsgList.Messages(0).StringParams.ToList()
				
				errmessage = string.Format(errmessage, errdetails.ToArray())						
				If Not errorDict.Keys.Contains(errmessage) Then	
					Dim templist As New List(Of List(Of String))
				        templist.Add(errdetails)
					errorDict.Add(errmessage, templist)
				Else										
					errorDict(errmessage).Add(errdetails)
				End If
			End If
		Next		
	End Using						
End If

     '*********** After Routine ************'

     

    The result is a Dictionary with 'Error Messages' as Keys and a List(of List(of String)) as Values holding per Dimension. You can then 'Flip-Flop' this dictionary in the format you wish 



    • Jesse_DC's avatar
      Jesse_DC
      New Contributor II
      2 years ago

      Hi Keyser_Soze 

      Could you pls provide some details on how can I implement these codes properly.

      Cheers