K2 SmartForms comes with a multitude of controls used to design Online Forms business users use. Sometimes the K2 SmartForm controls’ functionality need to be extended. Fortunately with a little bit of tweaking and this article, we can add null value “All” functionality to dropdown controls to get them to send null values to data sources that are used to retrieve K2 reporting data.
K2 Automated Testing Software
BenchQA allows full test automation of K2, including fully automated K2 SmartForms and K2 Workflow testing. It promotes test driven development for K2 and ensures continued quality assurance for K2 solutions. Easily apply changes to test cases to accommodate changes to K2 apps and ensure all apps are regression tested to avoid defects and assure continuous quality.
When it comes to reporting, it often uses Stored Procedures as the data source for the K2 blackpearl reports. This data is typically filtered by sending a parameter to the Stored Procedure query that is selected from a K2 SmartForm dropdown list. Even when we want to exclude a filter data from the dropdownlist, the Stored Procedure might always require a parameter to be sent through, albeit a null. As mentioned, a common way of doing this is by sending through null values. In this blog I will show you how to add a null value “All” text record in your dropdown lists which can serve the purpose of sending a null value to your stored procedures.
Figure 1 – Example Report to show use of All in filters
To start off with, I have added a dropdown to my K2 SmartForm View and populated the dropdown with data from a Stored Procedure.
Figure 2 – A Drop-down that displays an empty row by default
Figure 3 – Stored procedure used to populate the Drop-down list
By default, K2 allows for the selection of an Empty row. This could be useful in many scenarios, but in this example we will be adding our own null record (the “All” row). Therefore I will uncheck the “Allow empty rows” checkbox in the properties box of the dropdown control.
Figure 4 – Properties box of the Drop-down control
What we are left with is a dropdown list that automatically selects the first record. This is important to know, because when we add the “All” option, we’d like it to be selected by default. It is therefore important that the “All” option always be the first option in the dropdown list.
Figure 5 – Drop-down with no Empty Row
My next step is to alter the stored procedure that I use to select the data to populate the dropdown list with. I will create an “All” record and then use the UNION statement to combine this dummy null value record with the real data from the database.
Figure 6 – Updated Stored procedure to include All Record
Now looking at the dropdown list, you will notice that the “All” record is in place. For now, the work on the dropdown list is done.
Figure 7 – Drop-down list displaying the newly added All record
By running the K2 blackpearl report again, we can see from the output in the SQL trace that the null value corresponding to the “All” dropdown is passed in correctly and correct data is returned.
Figure 8 – Drop-down list sending null value to K2 report data query
In all, a great tip working with K2 SmartForms dropdown lists and passing native filter criteria values into SQL for the rendering of K2 SmartForms reports.
About K2 SmartForms
SmartForms is enterprise, browser based online forms technology developed by K2 that offers customers rapid drag-and-drop online form design and publish capabilities without writing code. The technology is changing the way organisations create online forms, paving the way for easy, no-code reusable enterprise forms without IT involvement.
About Velocity IT
Velocity IT has been involved with K2 since 2000. Out intimate knowledge of the product makes us a valuable asset for any successful K2 blackpearl and K2 SmartForm project. Read more about our services and solution at velocity-it.com