Home Certification 11. Patterns: Key/Value Tracking

11. Patterns: Key/Value Tracking

In the previous section, we used Key/Values to create a correlation of a record between source and target apps. Another use for Key/Values is to report on success or failure information. Generally, a Key/Value group will be used for reporting purposes while another Key/Value group is used to correlate between source and target apps.

We'll create a simpler version of the previous Workflow to illustrate how Key/Values should be used to store sync status and then report on it.

Exercise 14. Key/Value Creation

In this exercise we'll create a simple Workflow that attempts to integrate contacts to a web service and then records the outcome in a Key/Value group.

  1. Add Flat File, connect Start.RunNow → Flat File.

  2. Set Flat File.FlatFileDocument to the value below:

     Id,First Name,Last Name,Email,Phone,Company Name,State
 1,Dennis,Brown,juanjennings@hotmail.com,001-670-283-7481,Pioneer Pharmaceuticals,West Virginia
 2,Joseph,Lucas,sean27@gmail.com,422.631.1017x95303,Ascend Healthcare Partners,New York
 3,Robert,Medina,william51@gmail.com,8737236607,Apex Innovations,South Dakota
 4,Matthew,Flowers,valenzuelarodney@howell-walters.com,758.052.9923,EchoNet Communications,New York
 5,Jacqueline,Carroll,edwardhodge@yahoo.com,203-862-8915x3140,Vertex Construction,Alaska
 6,Diana,Harrison,andrea83@yahoo.com,547.430.8570,GreenLeaf Agriculture,Arkansas
 7,Mary,Jackson,robertsmith@caldwell.info,001-247-971-0674x837,Insight Marketing Solutions,Indiana
 8,Kelly,Richardson,sandra16@johnson.net,001-309-461-5944x9969,Swift Logistics,Pennsylvania
 9,Jay,Taylor,larry55@yahoo.com,001-036-973-5467,Skyline Engineering,New York
 10,Tina,Hill,savannahrussell@glover.com,0018245617,BlueHorizon Travel,Idaho

  3. Set Flat File.ColumnDelimiter to , and Flat File.HasColumnNames to True.'

  4. Add JSON Convert, connect Flat File → JSON Convert.

  5. Set JSON Convert.Action to XmlToJson.

  6. Connect Flat File.XmlDocument to JSON Convert.Xml.

    Run the Workflow to confirm the content is being parsed correctly and to make sample data available.

  7. Add For Each, connect JSON Convert → For Each.

  8. Connect JSON Convert.Json → For Each.SourceDocument.

  9. Set For Each.Path to Document.Row[*] (click the Row element in the tree view that is shown when you focus the Property).

    Run the Workflow to propagate sample data through the For Each Node.

  10. Add Web Request 2 and connect For Each.Item → Web Request 2.

  11. Set Web Request 2.Method to POST and Url to https://zorkco.flowgear.net/contacts?auth-key={auth}

  12. Add Web Request 2.auth, set it to dMFROUiQadVEWaHZe8qTEHM9TieeyGo7PBhY9Ln1TwKCsGu-sfgnJUh4OKIrBZLplNtXKWdcJDdqgHjVScr24Q.

  13. Set Web Request 2.RequestHeaders to Content-Type: application/json.

  14. Connect For Each.Item → RequestBody.

  15. Add Set Key-Value 2, rename to Tag Success, connect Web Request 2 → Tag Success.

  16. Set Tag Success.Group to contact-sync and set Tag Success.Status to Success.

  17. Connect For Each.Item → Tag Success.Key and set the Data Mapping Expression to Id.

  18. Add a second Set Key-Value 2 below Tag Success and rename it to Tag Error, connect Web Request 2.Error (Execution Output) → Tag Error.

  19. Set Tag Error.Group to contact-sync and set Tag Error.Status to Error.

  20. Connect For Each.Item → Tag Error.Key and set the Data Mapping Expression to Id.

  21. Connect Start.Last_Error_Info → Tag Error.Value.

    In the ETL Exercise, we configured a Connection on the Web Request 2 Node to return error information in the ResponseBody Property. For this exercise, we're keeping things a little simpler.

    When the contact record can't be integrated into the web service, the Web Request 2 Node will generate an error and the text of that error is available in the Start.Last_Error_Info Property.

    At this point you should be able to run the Workflow successfully and all contact records should be sync'd to the web service.

    Next, remove the Email value on a few of the contacts and run the Workflow again. For those contacts, the web service call should fail and you should see the Tag Error Node being invoked.

Save and run your Workflow, then click Submit Exercise to grade it.

Exercise 15. Key/Value Reporting

We will now generate a report of the Key/Values that we recorded in the previous exercise.

  1. Add Get Key-Values 2 to a new Workflow and connect Start.RunNow → Get Key-Values 2.

  2. Set Get Key-Values 2.MatchGroup to contact-sync.

  3. Set Get Key-Values 2.Emit to Xml

  4. Add Excel and connect Get Key-Values 2 → Excel.

  5. Set Excel.Action to Create.

  6. Connect Get Key-Values 2.Result → Excel.TableXml.

  7. Add Email Alert, connect Excel → Email Alert.

  8. Set Email Alert.Recipients to your email address.

  9. Set Email Alert.Subject to Your Report.

  10. Set Email Alert.AttachmentName to Report.xlsx.

  11. Connect Excel.ExcelDocument → Email Alert.Attachment.

  12. Add Variable Bar.

  13. Add Variable Bar.Report, set it to type File and set the file extension to xlsx.

  14. Connect Excel.ExcelDocument → Variable Bar.Report.

    Run the Workflow and check that you can download the report from the Report Property of the Start Workflow Log and that you have received the email containing the report in an attachment.

    To keep this exercise simple, we've generated the Excel sheet from the raw Key/Value data. In a real-world scenario, you could use QuickMap to provide additional useful information to the user.

Save and run your Workflow, then click Submit Exercise to grade it.