Part 1: Data

Part 1 of this tutorial will extend your knowledge of K2 SmartObjects and how you can define SmartObjects that interact with other systems. SmartObjects are essentially a "middle layer" or "connector" that allows consumers of data (such as forms and workflows) to interact with providers of data (such as SQL databases, Active Directory or SharePoint, among others).

In this part, you will extend the Leave Request SmartObject by adding a field to store approver comments. You will also create a new SmartObject (along with the underlying configuration) to connect to a SQL database. The results returned will be a list of leave types that will automatically populate the Leave Type drop-down list.

This step will demonstrate how you can add additional properties to existing SmartBox SmartObjects. You will add the Approver Comments property to the Leave Request SmartObject. (Later on, you will add this field to the Leave Request item view).

Step 1 Tasks

  1. In K2 Designer, edit the Leave Request SmartObject and add a Memo type property called
    Approver Comments
  1. Launch K2 Designer (Start Button > All Programs > K2 blackpearl > K2 smartforms > K2 Designer).
  2. Expand the All Items > K2 Learning > Leave Request > SmartObjects categories. Right-click the Leave Request SmartObject and select Edit.

  3. On the SmartObject Properties screen, click Add. (A below) For the Name, enter
    Approver Comments
    then select the Memo option from the data Type drop-down. Click OK, then click Finish to save the SmartObject edit.

    4. Adding a new property to a SmartBox SmartObject in K2 Designer is a simple process and the change is published as soon as you Finish the SmartObject Designer. This means that the Approver Comments property you just added is ready for use in your Leave Request item view. Use caution when deleting properties: if you reference a deleted SmartObject property in a rule or workflow for example, that rule or workflow will likely throw an error because it is trying to read a property that does not exist anymore.
STEP 1 REVIEW

In this step, you added a new property to the Leave Request SmartBox SmartObject to hold the approver's comments. Adding a new property to a SmartBox is reflected as soon as you finish the designer, meaning the new property is ready for use in the Leave Request item view that is based off of the SmartBox SmartObject.

In Step 2, you will create a connection called a "service instance" to an external SQL database, then generate a SmartObject for a specific table in that database (leave types). The data from the leave types table will be used to automatically populate the Leave Type drop-down list in the Leave Request item view.

New to K2 blackpearl 4.7 is the K2 Management site. If you are familiar with the K2 Workspace and the SmartObject Services Tester utility, you will find that many of the features and functionality in these tools are now located in the new Management site. In addition to managing processes and process instances, the K2 administrator can create service instances and SmartObjects directly from the Management site. You can also create SmartObjects using K2 Designer, K2 Studio and K2 for Visual Studio.
In a K2 environment, registering a service instance for a particular system only needs to be performed once. If you are using a shared environment (for example, if you are completing this exercise in your own K2 environment), this step only needs to be performed once and may already have been completed by another user. If you prefer to create your own service instances, use a unique identifier for the service instance display names and system names. Otherwise, use the service instance that already exists.
If you are attempting this tutorial from within a fire-walled environment, you may not be able to connect to the target SQL database. In this case, speak to your network administrator to determine if access can be opened to the target SQL database over port 1433 as described in this Microsoft KB article: http://support.microsoft.com/kb/287932, or alternatively you will need to create the database internally in a SQL server in your own environment. You can download a script to create the SQL database from the following location: http://help.k2.com/files/8553 . Please contact your SQL database administrator for more information and help.

Step 2 Tasks

  1. Launch the K2 Management site. (Start > All Programs > K2 blackpearl > K2 Management)
  2. Add a new service instance of the SQL Server Service Type using the table below as a guide for the necessary properties. If a property is not shown in the table below, then assume the default value.
    3. Field Name Setting
    A Display Name Leave Types
    B Description Returns a list of leave types.
      Service Type SQL Server Service (default)
    C Authentication Mode Static
    D User Name
    		 K2LearningUser
    E Password K2LearningPass
    F On Different SQL Server true
    G Command Timeout 90
    H Database K2Learning
    I Server uh8ydarb4m.database.windows.net
    J Use Native SQL Execution false
    K Generate SmartObjects for this Service Instance CHECKED

  3. Confirm the new Leave Types service instance is displayed in the Service Instances pane.
  1. Launch the K2 Management site. (Start > All Programs > K2 blackpearl > K2 Management)

    2. This tutorial touches upon the Integration features found in the K2 Management site. For more detailed information on the K2 Management site, see the tutorial Administering K2, or the K2 blackpearl Product Documentation.
  2. Expand the Integration category, then click Service Types.

  3. The available service types are displayed in the central Service Types pane. You will be adding a service instance of the SQL Server Service Type for this step. To help you locate the correct service type, enter
    sql
    into the search text box, then click the green refresh icon.
    4. The Service Types node contains the available service types (or connectors). Instances of service types are individual connections containing specific configurations. For example, you will add an instance of the SQL Server Service Type and configure it to connect to an external SQL database. By creating the service instance, K2 will discover the properties and methods that define the SQL data source.

  4. At least two service types for SQL are returned: SQL Reporting Service and SQL Server Service. (There may be additional service types depending on your environment.) Click to highlight SQL Server Service, then click the New Instance button.

  5. Use the table below as a guide for configuring the service instance. For additional reference, see the image below the table. Click OK after you have set the fields.
    6. In this step, notice that you will select the option to have K2 automatically generate a SmartObject from the SQL service instance (K). Because you are not customizing the new SmartObject, auto-generating is good choice and will return all of the properties and methods discovered. If you need to customize the SmartObject (for example, selecting which properties you want returned), use a SmartObject design tool such as K2 Studio, K2 Designer or K2 for Visual Studio.
    Field Name Setting
    A Display Name Leave Types
    B Description Returns a list of leave types.
      Service Type SQL Server Service (default)
    C Authentication Mode Static
    D User Name
    		 K2LearningUser
    E Password K2LearningPass
    F On Different SQL Server true
    G Command Timeout 90
    H Database K2Learning
    I Server uh8ydarb4m.database.windows.net
    J Use Native SQL Execution false
    K Generate SmartObjects for this Service Instance CHECKED

  6. You should see a confirmation dialog box. Click OK to close the dialog box.

    7. Take a moment to locate the new service instance you just created using the following steps.

  7. Still in the Integration node, click Service Instances. Scroll down and locate the Leave Types service instance. Notice the name, description and service type correspond to your configuration settings.

STEP 2 REVIEW

In Step 2 you created a new service instance based on the SQL Server Service Type. The new service instance contains connection settings that are specific to an external SQL database. As part of the configuration, you selected the option for K2 to automatically generate SmartObjects for the data objects that were discovered. In the next step, you will test the SmartObject, then rename it and move it to the appropriate Leave Request category.

Before you use the new SmartObject in your item view, you will test it to confirm the connection configuration. Then, just to keep your environment organized, you will rename the SmartObject and move it to the Leave Request > SmartObjects category that was created in the basic tutorial.

Step 3 Tasks

  1. Expand the Categories node, then the SQL Server Service node. Locate the [HumanResources].[LeaveType] table under the Leave Types Service Instance, then execute the List method. Confirm results are returned with two properties shown: LeaveTypeid and LeaveTypeDescription.

  2. Click the Design link to edit the SmartObject. Rename the SmartObject to
    Leave Types
    then move the Leave Types SmartObject to the K2 Learning > Leave Request > SmartObjects category.
  1. Expand the Categories node, then expand the SQL Server Service node. Notice the Leave Types SmartObject. Expand Leave Types, then Tables. There are six tables discovered with this SmartObject.
    2. When the service instance was created, K2 discovered the tables within the SQL database. Each table in return, has properties. Properties can be compared in theory to the columns in a spreadsheet. For example, Name, Department, Email and so forth. Each table will have its own set of properties and those properties may or may not contain actual values. The SmartObject becomes the communication mechanism between your forms and workflows and the values found in the SQL tables. For example, you will use the values found in the [HumanResources].[LeaveType] table as the values for your Leave Type drop-down list on your item view.

  2. Double-dlick the [HumanResources].[LeaveType] table to expose the properties and methods in the central pane. Take a minute to review the following sections:
    • A: Displays the system details such as the system name, date created and created by values.
    • B: Displays the properties: LeaveTypeDescription and LeaveTypeid (think spreadsheet column titles).
    • C: Displays the methods for this table. You will use the List method to return a list of leave types for the Leave Type drop-down list on the item view.
    • D: Keep the default settings here.

    3. Next, you will test the SmartObject connection by executing the List method.

  3. In the Methods pane, click to highlight the List method, then click Execute.

  4. There are no input properties for this test, so click Execute. The Results pane will appear with the values from the [HumanResources].[LeaveType] table. This confirms the external SQL database connection is correct. Exit the Execute SmartObject Method screen.

    5. The last step in this series is to rename, then move the new SmartObject to the Leave Request category that you created using K2 Designer in the basic tutorial. This will keep all of your K2 artifacts organized and easy to locate as you move through the next steps.

  5. Click to highlight the [HumanResources].[LeaveType] table to expose its properties (if they are not already). Click the Design link in the upper right corner of the screen.

  6. You will be redirected to the K2 Designer. (The K2 Designer will open in a new tab on your browser.) Confirm the [HumanResources].[LeaveType] table is highlighted, then click Edit.

  7. Switch to the General settings screen. Change the Name of the SmartObject to
    Leave Types
    then change the Category to the K2 Learning/Leave Request/SmartObjects category. (Your category names may be different if you chose different names in the basic tutorial.) There are no other edits to this screen, so click Finish to republish the SmartObject.

  8. Switch back to the Management site tab in your browser. Right-click the Categories name and select Refresh Menu. Expand the K2 Learning > Leave Request > SmartObjects categories and confirm the Leave Types SmartObject is shown. Notice too, that the Leave Request SmartObject also appears in Management site menu. This is the SmartBox that you created in the basic tutorial.

STEP 3 REVIEW

In this step you confirmed the service instance configuration is working correctly by executing the SmartObject list method. The results returned are the values that are found in the external database (leave type) table. The leave type values will be used to automate the Leave Type drop-down list on the item view. You concluded this step by renaming the SmartObject to a more friendly name, then moving the SmartObject to the appropriate category.

This concludes the data changes you will make for this new version of the Leave Request application. In Part 2: Forms, you will edit the item view and add the two new data sources just created: Approver Comments and Leave Types.