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 01 – Employee Data Storage
Generate a database table to store employee information, and populate with data.
Step 02 – Sales Data Storage
Generate a database table to store sales information, and populate with data.
Step 03 – 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 04 – 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 05 – 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 06 – 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 07 – 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 08 – 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.