Processing Complex Repeating Data in K2 – Part 1 of 3: Data Analysis and Storage Design

In this blog we provide a solution for processing complex repeating data in K2, making use of K2s’ underlying workflow events and data types.

Our aim is to solve a real-life problem for example a sales organisation providing contracting sales services to various companies for a limited period, to assist in generating new business and implementing modern and interesting sales campaigns.

The organisation will receive each company’s information in XML format; identify sales leads based on previous campaign results; and capture employee and sales related data accordingly. A report should be generated displaying all employee sales related data, also which employees exceeded their sales targets.

The report should be e-mailed to each individual employee whom is identified as a sales lead, and data should be displayed in proper aligned tabular format.

The “ForEach” workflow activity event will be illustrated, together with applying XML data fields and using data transfer events to process XML information, perform XSLT transformations to manipulate the data and generate the desired HTML.

Part 1 of 3 will focus on analysing and designing the necessary data storage mediums.

 

Step 1 – Employee Data Storage

Generate a database table to store employee information, and populate with data.

 

Step 2 – Sales Data Storage

Generate a database table to store sales information, and populate with data.

 

Step 3 – Data Listing For All Sales Leads

Create a stored procedure to query the Employee data store to fetch all employees identified as sales leads.

Step 4 – Data Listing For All Sales Projections

Create a stored procedure to query both the Employee and Sales data stores to fetch sales projection data and return as XML.

XML data will be easier to manipulate into tabular format to use in the HTML email notification report.

 

 

Step 5 – Data Listing For All Sales Exceeds

Create a stored procedure to query both the Employee and Sales data stores to fetch sales exceed data (sales higher than target of 20,000.00) and return as XML.

XML data will be easier to manipulate for repeating data to use in the HTML email notification report.

 

 

Step 6 – K2 SQL Server Instance

Using the K2 SMO Tester Tool, refresh the SQL database server instance used to host the underlying data storage tables and stored procedures to make them aware in a K2 context.

 

Step 7 – K2 Smart Object for Employees

Using the K2 Designer, create a new smart object to facilitate hosting the necessary database processing methods for Employee information.

 

Step 8 – K2 Smart Objects for Sales

Using the K2 Designer, create a new smart object to facilitate hosting the necessary database processing methods for Sales information.

 

In the first part of this blog, “Data Analysis and Storage Design”, I have illustrated how to analyse the necessary data; design and implement the necessary data storage mediums to host the relevant employee and sales information.

Once the data storage mediums were in place, the K2 Database SQL Server instances could be refreshed which made it possible to create the necessary K2 smart objects to be used to surface the data into the workflow process.

In “Part 2 of 3 – Data Structures and Transformation”, we will look into designing the data structures in XML; generating example data; designing the XSLT transformation operations and HTML data structure to represent the required sales projection information.

 


K2 Five is here! You may have already read all about it but why not join our webinar on the 24th October to see Velocity perform a LIVE DEMO, expertly showcasing how the new and improved features work and how to get the most out of it!

Written by Zach Bester, Senior K2 Consultant at Velocity.

© 2017 Velocity IT Ltd

You may also like

Leave a comment

PODCAST  |  Why you need to establish a K2 Center of Excellence today

Interview with Arno van Rooyen, CEO, Velocity