Integrate with VeraCore using Metl
Do you have a need to get data out of your VeraCore order fulfillment application? Using the lightweight, web-based integration platform Metl, from JumpMind, I’ll show you how to do just that.
Metl gives you the ability to build a flow that uses the web request component to execute a VeraCore dynamic report and then pull that data once the report completes. The JSON output can then easily be parsed and processed in any way you want from there.
In some cases, VeraCore does not allow you to get the data via their dynamic reports but the data might be available through another report from their Automatic Report Scheduling option. Metl can handle this as well, it just requires a slightly different method of pulling the data. Currently these scheduled reports from VeraCore are only able to be generated and emailed, not saved directly to a file system. In this case, you can create a flow where Metl pulls attachments from an email account and then imports these files and parses the data as necessary.
It’s that simple. Below, I am going to show you at a high level a basic example for each case.
1. Getting results from a dynamic report.
Depending on your environment, you may need to get an authorization token from VeraCore in order to run these dynamic reports (as shown in this example). The flow needs to preserve this token value to use in subsequent calls. The token gets added to the context flowVariables map, as shown here.
Place Token in a Flow Parameter
I have a VeraCore Http Resource that is used for both authentication and data pulls. Using the authorization token received, I can issue the call to execute our report. In this example, I call the Order_Status_Summary report. In the Http Headers property you can see I reference our flow variable vcBearerToken as: Authorization: Bearer $(vcBearerToken). To reference parameters in a component you use the $(fieldname) syntax and be sure to check the Parameter replacement checkbox.
A Groovy Script will receive the results of the execution and parse out the report task id assigned. The input to the script will be JSON formatted text messages. In my case I am looking for a node labeled TaskId and Status that I will capture for my later use. These values are then placed in the header of the text message that will be forwarded to the next component(s).
Capture the Report Task Id
(don’t forget to add the necessary imports as well)
The flow has a delay of 1 minute to give VeraCore time to execute the report; otherwise, if I pull too soon, I may get a failure that the report is not done yet. Next, I will pull the report results using this report task ID and feed this to another Groovy Script to parse the final report results, which can then be passed to any type of end-point desired.
Parse the Report Results
As each of the JSON nodes is parsed, I selectively choose fields I am interested in and build a comma separated string of results that will be forwarded to the next component, a text file writer. **One note, for your dynamic reports, take into consideration the number of fields and the time frame the reports run for, if too large, the report may error and possibly not return, so you will need to reduce one or both of these values in the VeraCore dynamic report setup.
For this example, I created another Resource of a Local File System type where I save my report data to a .csv file.
Now I have a flow that connects to VeraCore, runs a dynamic report, reads the results and saves it to a comma separated text file.
2. Getting reports from emailed output.
In this example, I am reading from an email-box and then loading the contents of report attachments into a database table. Using the Email Reader Metl component, set up your Mail Session connection, choose your ‘From’ address or ‘Subject’ to search for and define your target file location to place the attachments.
I am looking in the main INBOX for any un-read messages with a subject containing ‘Product Summary (Automatic Report Generation)’ and then saving any attachment into a folder at /EmailReports/incoming.
The File Poller is used to look for the report files in my chosen attachment target path. The matching filenames found are passed to an Excel File Reader which gets the filename from the incoming message and then imports the file data into a relational model Entity I have defined. In the properties of the Excel Reader I need to specify the Header Lines to Skip of 18 since I know my report data does not start until the 19th row in the file otherwise I would get bad data in my Excel mapping.
Advanced Edit of Read Data from Excel Report
(Defines mapping of Entity model to the Excel column)
In my example, you will notice I included a content router to verify my row of data contains a product id in the specified column. This is because some VeraCore reports include comments at the end including a ‘page x of x’ row, so I need to filter these out. I know my report does not have any values in the Column C on these rows so I route on that column being not null.
The last step I have writes this data to a database table using a basic RDBMS Writer component.
In Conclusion
Reports can be pulled from VeraCore and processed using the Metl integration tool. Both examples provided are more basic than you would want to implement in a production environment. Realistically, for the dynamic reports, you will want to add a Groovy Script that loops to check the status of the report until it receives a completed status of ‘Done’. You can also add timeout logic to prevent infinite looping and a way to trigger an alert to a support team if it doesn’t complete. For the email reports example, you would most likely have a table that differs slightly from the actual file layout with maybe an insert/update time column or maybe need different transformations so you would need to add a Mapping to convert to the different Entity model and a Transformer for any additional logic. This article should at least show that it is possible and can help get you started if you want to try it yourself.
