A big thing to watch out for when you’re building an application in K2 blackpearl with an RDBMS back end is an understanding of how to treat datetime values.

K2 uses the value scnull to indicate a NULL value. This becomes especially important in SmartObject calls down to, say, stored procedures in your database.

The problem is, you can’t pass a value of scnull into, say, a workflow datafield that’s expecting a datetime value. For that, you have to use an actual datetime value like 1/1/1900.

All of this manipulation can make things pretty hairy when you’re trying to feed a SmartObject and start a workflow at the same time. If your datetime value is on a SmartForm, and you anticipate having to deal with blank or NULL values, you might consider adding a hidden data label or two and expressions to go along with them. That way, you can convert the calendar control value into something useful for the database in one data label, and something useful for data fields in another.

In the second post of this series I showed how you could use XML functions to return your recordset from the database as HTML

row and cell elements (“Make the Database Return Your Recordset as an HTML Table — Using XML”, 8/6/2016). We did this by naming the XML elements after the appropriate HTML elements.

If we stopped at this point, we could create a SmartObject that returns our string of HTML

data to the caller. For example, we can already have the HTML for the rest of the report elsewhere, and this SmartObject simply “plugs in” the data to be displayed. This could be as easy as concatenating strings (say, an HTML report header, the HTML

output from the SmartObject, and an HTML report footer) in a Data Event.

We can go further. By using XSL Transform, we should be able to have the database return our entire HTML report — not just the table.

Continue reading

I emphasized in my previous post the mechanism through which SmartObject data may be accessed within K2 Server Events. The specific example I gave was the case of a reporting feature for which I had been “awarded” responsibility (“Access Your Workflow SmartObjects Through Server Events”, 7/30/2016).

The feature was comprised of a K2 workflow process consisting of a server event that generates the report in HTML, an event that generates a PDF, and and event that persists the PDF in a document management system. As detailed in the previous post, the server event called SmartObjects to retrieve the data for the report, then used a StringBuilder object to create the HTML through a series of for loops before returning the completed HTML string to a process-level data field in the workflow process.

I began to wonder if perhaps the database engine could handle this transformation more efficiently, so I started digging into the stored procedures that support the report.

Continue reading