3. Create Service Instances for External Data Sources; Generate a SmartObject
In this step, you begin creating the data objects for your Expense Claim application. The first two data objects connect to external data sources. The first external data source is an Azure SQL Server database we will use to look up Expense Categories, and the second data source is a Web Service that we will use to work with currency conversion.
The first step when integrating with external systems such as SQL or web services is to create a service instance. The service instance contains the connection configuration such as URL, server name, user name, password, and so forth. You will create a SQL Server service instance to connect to the Azure SQL database, and an Endpoints Web Service, service instance to connect to the currency web service.
Creating a service instance allows K2 to discover the objects (such as the properties and methods) that the data source contains. After you create a service instance, you can create a SmartObject from the service instance that allows K2 to interact with the data source through forms, workflows and rules.
Service Types, Brokers, Instances, and SmartObjects
Service types, service brokers, and service instances are the elements that allow K2 to interact with other systems, and which form the base layer for SmartObjects. The following is a brief overview of each concept:
Service types, service brokers, and service instances are the elements that allow K2 to interact with other systems, and which form the base layer for SmartObjects. The following is a brief overview of each concept:
- Service Type: A service type is a pointer to a broker file for a specific system or data source. Examples include: SQL Server, SharePoint, CRM, and web services. Each service type has an underlying service broker associated with it.
- Service Broker: A file that contains the logic needed to interact with a specific system. Each service type has its own requirements for interacting with the system. For example, what type of authorization will the system allow? What type of data is contained in the system?
- Service Instance: A service instance is a single connection to a data source, and is based on the service type. The service instance uses the requirements defined by the broker to connect to the target data source. For example, you might have an instance of a SQL Server service type. The instance is specific to a single SQL database. If you have multiple databases, you need multiple instances. From the instances, you can then generate SmartObjects.
SmartObjects: The middle layer that allows interaction between a K2 object (form, view, workflow) and the target data source. For example, you have a form bound to a SQL SmartObject. When you submit the form, the SmartObject creates a new record or it updates an existing record in the SQL table. The SmartObject connects K2 to the system via the service layer.
In a shared environment, you only need to create the service instance once. If the service instance already exists and you want to create your own, use a unique identifier of some kind. One example might be to add your name or initials to the service instance name.
- Return to the K2 Management site. Use the table settings below to configure a new Service Instance of the SQL Server Service, service type. This data source provides look-up values for the expense claim categories.The database you will be connecting to is hosted on the internet as a SQL Azure database. This database should be accessible through port 1433, and unless your organization has strict firewall policies in place, it should be available for use anywhere. If you need to host this SQL database internally, you can download a SQL script to recreate the database from the following location: http://help.k2.com/files/8553. Contact a member of your application development team for help, if necessary. You will need to adjust the Service Instance's Database and Server keys and potentially the authentication method to connect to a local version of this SQL Database.
Field Value Notes Service Instance A. Display Name K2 Tutorial Sample SQL Database If you are working in a shared environment, include your initials in the display name to make the name unique and easily identifiable to you. B. Description This is the service instance of the sample database used in the Expense Claim tutorial. C. Service Type SQL Server Service You will connect to a SQL database as the data provider. Service Authentication D. Authentication Mode Static You will provide a static username and password to connect to the sample SQL database. E. User Name K2LearningUser F. Password K2LearningPass Extra (leave blank) Service Keys G. On Different SQL Server true This database exists on a SQL server that is in a physically different location than your K2 system. H. Command Timeout 90 You want to extend the command timeout from the default to cater for high load. I. Database K2Learning This is the name of the target database on the sample SQL server. J. Server uh8ydarb4m.database.windows.net This is the name of the sample SQL server you want to connect to. K. Use Native SQL Execution false You do not want K2 to use native SQL commands for this target database. It exists on a separate server and the K2 service account does not have enough permissions on the target system to perform DDL operations. SmartObjects L. Generate SmartObjects for this Service Instance UNCHECKED You will create the SmartObject in a few minutes. - In the Management menu, expand the Integration node, then click Service Types. You will see current service types displayed in the central pane. Scroll through the service types until you locate SQL Server Service. You can also use the search option instead of scrolling through the pages. (You may see more than one result returned.)
- Select SQL Server Service, then click the New Instance button in the toolbar.
- Use the table below to configure the service instance. There is an image below the table for reference as well. If a setting is not indicated in the table, assume the default value. Click OK when you have entered the configuration settings, and then click OK for the confirmation dialog. (It may take a few seconds for the confirmation dialog box to appear).
Field Value Notes Service Instance A. Display Name K2 Tutorial Sample SQL Database If you are working in a shared environment, include your initials in the display name to make the name unique and easily identifiable to you. B. Description This is the service instance of the sample database used in the Expense Claim tutorial. C. Service Type SQL Server Service You will connect to a SQL database as the data provider. Service Authentication D. Authentication Mode Static You will provide a static username and password to connect to the sample SQL database. E. User Name K2LearningUser F. Password K2LearningPass Extra (leave blank) Service Keys G. On Different SQL Server true This database exists on a SQL server that is in a physically different location than your K2 system. H. Command Timeout 90 You want to extend the command timeout from the default to cater for high load. I. Database K2Learning This is the name of the target database on the sample SQL server. J. Server uh8ydarb4m.database.windows.net This is the name of the sample SQL server you want to connect to. K. Use Native SQL Execution false You do not want K2 to use native SQL commands for this target database. It exists on a separate server and the K2 service account does not have enough permissions on the target system to perform DDL operations. SmartObjects L. Generate SmartObjects for this Service Instance UNCHECKED You will create the SmartObject in a few minutes. 
- In the Management menu, expand the Integration node, then click Service Types. You will see current service types displayed in the central pane. Scroll through the service types until you locate SQL Server Service. You can also use the search option instead of scrolling through the pages. (You may see more than one result returned.)
- Create a second service instance using the Endpoints WebService, service type. Use the table settings below as a guide for the service instance configuration. This service provides two methods. The first method returns a list of currency codes (such as GBP, USD) for you to select when entering your expense claim line item. The second method performs a calculation that converts an amount entered to US Dollars. For example, if you enter a line item amount and select GBP as the currency code, the second method converts the amount and outputs it as USD.In a shared environment, you only need to create the service instance once. If the service instance already exists and you want to create your own, use a unique identifier of some kind. One example might be to add your initials to the service instance name.This web service is for tutorial purposes only. Do not use this web service for production implementations. The web service used by this application also runs on Azure. This web service should be accessible through port 80 and unless your organization has strict firewall policies in place, it should be available for use anywhere. If you need to host the web service internally, you can download the source code for the web service from the following location: http://help.k2.com/files/8554. Contact a member of your application development team for help, if necessary.
Field Value Notes Service Instance A. Display Name K2 Tutorial Currency Web Service If you are working in a shared environment, include your initials in the display name to make the name unique and easily identifiable to you. B. Description This is the service instance of the sample currency web service used in the Expense Claim tutorial. C. Service Type Endpoints WebService Service Authentication D. Authentication Mode ServiceAccount You will use the credentials of the K2 service account to connect to the target web service. When using the service account authentication mode, the service account must have the necessary permissions to interact with the data source. Be aware of this when registering a new service instance in your own environment. Service Keys
There is only one service key value to set in this step (WebService URL).E. WebService URL http://k2learning.azurewebsites.net/ExchangeRates.asmx This is the URL of the target web service you will connect to. F. Generate SmartObjects for this Service Instance UNCHECKED You will manually create a SmartObject in a later step. - Next you will add another service instance which points to the web service used for currency conversion operations. Begin by opening a new browser window to verify you can connect to the following web service:
http://k2learning.azurewebsites.net/ExchangeRates.asmx
You should see a screen similar to the image below. This verifies that your firewall configuration is sufficient for connecting to this web service.
This web service is for tutorial purposes only. Do not use this web service for production implementations. The web service used by this application also runs on Azure. This web service should be accessible through port 80 and unless your organization has strict firewall policies in place, it should be available for use anywhere. If you need to host the web service internally, you can download the source code for the web service from the following location: http://help.k2.com/files/8554. Contact a member of your application development team for help, if necessary. - Returning to the K2 Management site, expand the Integration node, then click Service Types. Scroll down to (or search for) the Endpoints WebService. Select Endpoints WebService, then click the New Instance button in the toolbar.
- Use the table below to configure the service instance. There is an image below the table to use as a reference as well. If a setting is not indicated in the table, assume the default value. Click OK when you have entered the configuration settings. Click OK for the confirmation dialog.
Field Value Notes Service Instance A. Display Name K2 Tutorial Currency Web Service If you are working in a shared environment, include your initials in the display name to make the name unique and easily identifiable to you. B. Description This is the service instance for the sample currency web service used in the Expense Claim tutorial. C. Service Type Endpoints WebService Service Authentication D. Authentication Mode ServiceAccount You will use the credentials of the K2 service account to connect to the target web service. When using the service account authentication mode, the service account must have the necessary permissions to interact with the data source. Be aware of this when registering a new service instance in your own environment. Service Keys
There is only one service key value to set in this step (WebService URL).E. WebService URL http://k2learning.azurewebsites.net/ExchangeRates.asmx This is the URL of the target web service you will connect to. F. Generate SmartObjects for this Service Instance UNCHECKED You will manually create a SmartObject in a later step. 
- Still in the Integration node, click Service Instances. Confirm the two new service instances appear in central pane, K2 Tutorial Sample SQL Database and K2 Tutorial Currency Web Service. You have completed the external data source connections.
- Next you will add another service instance which points to the web service used for currency conversion operations. Begin by opening a new browser window to verify you can connect to the following web service:
- Next, you will generate a SmartObject for the SQL database service instance. The SmartObject acts as the "bridge" for interaction between the SQL data source and K2 artifacts (such as views, forms, workflows).
Generate a SmartObject from the K2 Tutorial Sample SQL Database service instance. Select (only) the [Finance].[ExpenseCategory] table.
- Still in the Integration section, click Service Instances once again. Navigate to (or search for) the K2 Tutorial Sample SQL Database service instance in the central pane. Select the service instance, then click the Generate SmartObjects button in the toolbar.
- On the Generate SmartObjects screen, expand the Tables node. (If the Tables and/or Views nodes are already checked, uncheck them first. You do not want to create SmartObjects from all the tables and views, you will be selecting a single table from which to create the SmartObject.) CHECK the [Finance].[ExpenseCategory] table, then click OK.
- Still in the Integration section, click Service Instances once again. Navigate to (or search for) the K2 Tutorial Sample SQL Database service instance in the central pane. Select the service instance, then click the Generate SmartObjects button in the toolbar.
-
Next, you have two minor adjustments to make to the expense categories SmartObject you just generated. Because you generated the SmartObject from the service instance, K2 saved the SmartObject in a generic category, as the table name [Finance].[ExpenseCategory]. First, you will move the SmartObject to your Expense Claim > SmartObjects category, then you will rename the SmartObject so that it's easier to work with.
Switch to the Categories menu, then edit the [Finance].[ExpenseCategory] SmartObject. Move the SmartObject to the Expense Claim > SmartObjects folder, then rename it to Expense Claim Categories.- Still in the K2 Management site, expand the Categories node. (You can collapse the Integration node if you need to.) Navigate to the [Finance].[ExpenseCategory] SmartObject. (SQL Server Service > K2 Tutorial Sample SQL Database > Tables)
- Select the SmartObject, then click the Design link in the central pane. After K2 Designer launches, click the Edit link in the central pane.
- Switch to the GENERAL tab. Rename the SmartObject
Expense Claim Categories
then move it to the K2 Learning > Expense Claim > SmartObjects category.
- Click FINISH to save and exit the SmartObject. Close this browser window if you already have K2 Designer opened in another tab.
- Still in the K2 Management site, expand the Categories node. (You can collapse the Integration node if you need to.) Navigate to the [Finance].[ExpenseCategory] SmartObject. (SQL Server Service > K2 Tutorial Sample SQL Database > Tables)
- Before moving on to the next tutorial topic, take a minute to test the categories SmartObject. You can easily test SmartObjects from within the K2 Management site. This will confirm your SmartObject is ready for use in other artifacts. You will test the SmartObject by executing a list method that returns all the items from the data source.
From the K2 Management site, execute the Expense Claim Categories SmartObject's list method and confirm you see a list of categories returned. You may need to refresh the Categories node first to find the SmartObject in its new location. The SmartObject should be in the K2 Learning > Expense Claim > SmartObjects category.
- You can execute SmartObjects from within the K2 Management site. Because you just renamed and moved the categories SmartObject, you need to refresh the Categories menu to see the changes. Right-click Categories, then select Refresh Menu.
- Navigate to the Expense Claim Categories SmartObject. It should be in the K2 Learning > Expense Claim > SmartObjects folder.
- Select the Expense Claim Categories SmartObject. Notice the central pane displays the details for the SmartObject. The Properties section displays the properties (think columns in a table) and the Methods section displays the different ways you can interact with data from this SmartObject. This data source allows you to create, delete, list, read, and update its data content. Select the List method, then click Execute.
- Click the Execute button in the lower right corner of the screen. You will see results displayed the same screen, which confirms the connection. This data source provides the drop-down list values for the expense claim categories. Click Done to close the screen.
The Execute SmartObject Method screen opens. Here, you can apply filters before executing the method. In some cases, such as read methods, you must provide input parameters before executing the method. For example, you might provide the ID of a record you want returned. For this test, you just want to confirm the connection, so you don't need any filters.
- You can execute SmartObjects from within the K2 Management site. Because you just renamed and moved the categories SmartObject, you need to refresh the Categories menu to see the changes. Right-click Categories, then select Refresh Menu.
Review
In this step, you began creating the data sources for this application. The first two data sources are external, meaning they do not exist on your K2 server locally. To connect to external data sources, you begin by creating service instances of existing service types. (Service types do not have to be existing, you can in fact, create your own custom service types.) You configured connections to an Azure SQL database and an Endpoints Web Service. Once you have the service instance, you must generate or create a SmartObject. The SmartObject acts as the "bridge" between the data source, and the entities that consume the data (such as views, forms, and workflows). In this exercise, you auto-generated a SmartObject from a table found in the SQL database. In the next exercise, you will create a new SmartObject from scratch using the Endpoints Web Service, service instance.