Configuring Web Services

What is a Web Service?

A Web Service is a method of communication between two applications or electronic devices over web. You can either use pre-defined web services that come along with the SummitAI application or build Custom Services based on your organization's requirement.

Web Services are of two types:

  1. Simple Object Access Protocol (SOAP)
  2. Representational State Transfer (REST)

SOAP defines a standard communication protocol (set of rules) specification for XML-based message exchange. SOAP uses different transport protocols, such as HTTP and SMTP.

The standard protocol HTTP makes it easier for SOAP model to tunnel across firewalls and proxies without any modifications to the SOAP protocol. SOAP can sometimes be slower than middleware technologies like CORBA or ICE due to its verbose XML format.

REST describes a set of architectural principles by which data can be transmitted over a standardized interface (such as HTTP). REST does not contain an additional messaging layer and focuses on design rules for creating stateless services. A client can access the resource using the unique URL and a representation of the resource is returned. With each new resource representation, the client is said to transfer state.

While accessing RESTful resources with HTTP protocol, the URL of the resource serves as the resource identifier and GET, PUT, DELETE, POST and HEAD are the standard HTTP operations to be performed on that resource.

Provide the Service URL to create a Web Service.


Figure: Edit Service URL


Service URL Example: http://<webserviceurl>/REST/Summit_RESTWCF.svc

Replace <webserviceurl> with base URL.

Web Service Authentication

The default Token-based API authentication is implemented for Web Service from the Sierra HF01 Release onwards. If you upgraded to the Sierra HF01 and want the User ID and Password-based authentication for Web Service, you can enable it. 

Configuration

You can enable the User id and Password-based authentication by adding the following key in the SummitAI web.config file:

<add key="IsAPIKeyDisabled" value="true" />

Note:

If the key is not added in the web.config file or if the value of this key is set as false, by default, the Token-based API based authentication is enabled to log into the SummitAI application.

To preview a Web Service:

  1. Select Admin > Basic > Infrastructure > Web Services.

  2. Select a URL from the drop-down list.
  3. Select a service from the Custom Services list.
  4. Fill in all the mandatory fields and then click SUBMIT. The Output Data is displayed.


    Figure: Web Services Preview page

Field Description

The following table describes the fields on the WEB SERVICES EXPLORER page:

Fields

Description

Custom Services/Predefined

Services

Displays the list of services available. Select a service from the list to extract data from the application through the Web services. Type in the name of the services to search from the list. Click Filters icon to filter the services according to modules.


Figure: Web Services Filter

View Custom Services

Click View Custom Services (highlighted in red color in the above image) to display the list of Custom Services created by the users.

View Predefined Services

Click View Predefined Services (highlighted in green color in the above image) to display list of services already defined in the SUMMIT application.

Refresh

Click to Refresh the list.

URL

Select Input URL format from the drop-down list, either JSON Object or JSON String or XML.
Click EDIT to edit the existing URL. See Screen Shot.


Figure: Edit Service URL

Service URL example: http://<webserviceurl>/REST/Summit_RESTWCF.svc

Replace <webserviceurl> with base URL.

Mandatory Fields

Displays Mandatory Fields for the selected service.

Input Data

Displays Input parameter for the selected URL Type.

Output Data

Displays Output for the selected URL Type.

Search Bar

You can search for the web services, based on their properties. For example, if you specify table in the search bar, all the existing tables will be displayed.

To create Custom Services:

  1. Click CREATE NEW. The WEB SERVICE DESIGNER page is displayed.


    Figure: Web Service Designer page

  2. Type in the Web Service Name and specify the number of records to be displayed.

    Note:

    You can also create SQL Views, SQL Table, and SQL Procedure and modify the created fields.

     Create SQL View


    Figure: Create SQL View Page

     Create SQL Table


    Figure: Create SQL Table Page

    You can add indexes to improve the performance of the tables.

     Create SQL Procedure


    Figure: Create SQL Procedure Page

  3. Click  icon to view, hide, or customize the Web Service properties. 


    Figure: Web Service Properties

    Field Description

    The following table describes the fields on the PAGE PROPERTIES section:

    Fields

    Description

    Module Name

    Select a Module for the Web Service.

    Active

    If selected, the Web Service configuration becomes active.

    User Access

    Type in the name of a user to whom you want to provide access to the Web Service.

    View Only

    If selected, the user will have read-only access to the Web Service and cannot do any modifications to it.

    Report Description

    Specify the description of the Web Service.

  4. Select the check box to select DATA SOURCE from the list. You can scroll down to see all the available Data Sources or type in the Data Source name in the search box to search a particular Data Source.

    Note:

    Create Relationship between the Data Sources if you want to use multiple Data Sources.

     Create Relationship

    To create relationship, select a Data Source. Fill in all the required details and click ADD RELATIONSHIP. A new Relationship is added.


    Figure: Relationship section

    Field Description

    The following table describes the fields under the RELATIONSHIP section:

    Fields

    Description

    Join Type

    Select a Join Type from the list.

    Data Source

    If multiple Data Sources are selected then select a Data Source from the list to create a relationship with it.

    Table Alias

    Select the alias name for the Data Source.

    Join Field

    Select a field from the drop-down list for selected Data Source.

    Foreign Data Source

    Select a Foreign Data Source from the drop-down list to create relationship with the selected Data Source.

    Join Field

    Select a field foreign Data Source from the drop-down list.

     Add Fields to the Web Service

    To add fields to Web Services:

    1. Drag and drop the attributes from the list to FIELDS and FILTERS section to define the mandatory fields and Filter attributes. You can configure the properties of the attributes in the FIELDS and FILTERS section.


      Figure: Fields Section

      Note:

      • Click  icon to copy all the fields.
      • Click  icon to remove all the copied fields.
    2. Upon selecting attributes, PROPERTIES section is displayed (if not displayed, click icon) and configure Properties. Select an attribute on FIELDS/FILTERS column, FIELD/FILTERS PROPERTIES is displayed. If not, click the PROPERTIES icon.


      Figure: Page Properties for Fields

    Field Description

    The following table describes the fields on the PAGE PROPERTIES section:

    Fields

    Description

    Source Name

    Displays the Source Name of the attribute.

    Field Name

    Displays the Field Name.

    Field Alias

    If required, change the Field name.

    Sort

    Select the sort order from the drop-down list.

    Function

    Select the function of the attribute from the drop-down menu.

    Custom Expression

    Specify the custom expression, such as SQL calculations.

    Mandatory

    If selected, the corresponding field becomes mandatory.

  5. Click GENERATE SQL to generate SQL Query. 

     See Screen Shot


    Figure: GENERATE SQL (when Edit Query check box is not selected)


    Figure: GENERATE SQL (when Edit Query check box is selected)

    Note:

    • The string values should be entered within double quotes. Example: ''Username''.
    • Data Manipulation Language (DML) is not allowed in the SQL query. Example: Delete, Truncate, Drop, Comment, Rename, Merge, Grant, Revoke, Commit, Savepoint, Rollback, Exec, and Execute.
    • If you want to pass any parameters, enter # before and after the respective Field name.
  6. Click PREVIEW to view the Output of the ongoing Web Service. 

     See Screen Shot


    Figure: Output Preview pop-up page

  7. Click SAVE. The Web Service is added to the WEB SERVICE PREVIEW list.

Creating Custom Tables in Web Services:

To Create a New Custom Table:

  1. Select Admin > Basic > Infrastructure > Web Services. The WEB SERVICE EXPLORER page is displayed.
  2. On the WEB SERVICE EXPLORER page, click CREATE NEW. The WEB SERVICE DESIGNER page is displayed.
  3. On the WEB SERVICE DESIGNER page, select Create SQL Table from drop-down list.
  4. Specify the SQL Table Name.


    Figure: WEB SERVICE DESIGNER page: Create SQL Table: SQL Table Name
  5. Click icon in FIELDS section to view FIELD PROPERTIES.


    Figure: WEB SERVICE DESIGNER page: FIELD PROPERTIES
  6. Type in Field Name (or Column Name) and select the Data Type value from drop-down list. For more information about the fields on the FIELD PROPERTIES, see Field Description.

  7. Click ADD NEW FIELD to add a new field (or Column).

    FieldDescription
    Field Name

    Specify the name for the field (or column).

    Data Type

    Select the required data type for the field (or column) from the drop-down list. Following are the available data types:

    • nvarchar: Select the data type as nvarchar to store variable characters (letters and numbers) in the field (or column).
      Note: In the size parameter, specify the maximum column length in characters.
    • int: Select the data type as int to store a whole number (not a fraction) in the field (or column).
    • numeric: Select the data type as numeric to store numbers that have fixed precision and scale.
    • decimal: Select the data type as decimal to store numbers that have fixed scale.  
    • bit: Select the data type as bit to store a single bit with a value of 0,1, or NULL
    • datetime: Select the data type as datatime to store date and time combination in the field (or column).
    • text: Select the data type as text to store text or combinations of text and numbers in the field (or column).
    Auto Identity

    Select this check box to define a field (or column) as auto increment field.

    Note:

    This field is displayed only for int, numeric, and decimal data types.

    Primary Key

    Select this check box to define a field (or column) as Primary Key.

    Note:

    Primary keys should contain unique values and cannot have NULL values.


  8. Click GENERATE SQL to generate the SQL Query.


    Figure: GENERATE SQL (sample screenshot)

  9. Click SAVE to successfully create the SQL Table. Click YES if you want to redirect to the Web Service Explorer page.



 Example 1: Create a Custom Table to Store Location-wise Approver Details.

Scenario: Consider that the user wants to create a Custom Table with the following fields to store Location-wise Approver details.

Column Name

Data Type

Country

varchar

State

varchar

City

varchar

EmailID

varchar

Approver

int

To Create a New Custom Table:

  1. Select Admin > Basic > Infrastructure > Web Services. The WEB SERVICE EXPLORER page is displayed.
  2. On the WEB SERVICE EXPLORER page, click CREATE NEW. The WEB SERVICE DESIGNER page is displayed.
  3. On the WEB SERVICE DESIGNER page, select Create SQL Table from drop-down list.
  4. Specify the SQL Table Name (Ex: Location_Table).


    Figure: WEB SERVICE DESIGNER page: Create SQL Table: SQL Table Name
  5. Click icon in FIELDS section to view FIELD PROPERTIES
  6. Type in Field Name (or Column Name) and select the Data Type value from drop-down list (Refer the above table for column names and data types). 

  7. Click ADD NEW FIELD to add a new field (or Column). For more information about the fields on the FIELD PROPERTIES, see Field Description.


    Figure: WEB SERVICE DESIGNER: FIELD PROPERTIES

    Field Description

    The following table describes the fields on the FIELD PROPERTIES section:

    FieldDescription
    Field Name

    Specify the name for the field (or column).

    Data Type

    Select the required data type for the field (or column) from the drop-down list. Following are the available data types:

    • nvarchar: Select the data type as nvarchar to store variable characters (letters and numbers) in the field (or column).
      Note: In the size parameter, specify the maximum column length in characters.
    • int: Select the data type as int to store a whole number (not a fraction) in the field (or column).
    • numeric: Select the data type as numeric to store numbers that have fixed precision and scale.
    • decimal: Select the data type as decimal to store numbers that have fixed scale.  
    • bit: Select the data type as bit to store a single bit with a value of 0,1, or NULL
    • datetime: Select the data type as datatime to store date and time combination in the field (or column).
    • text: Select the data type as text to store text or combinations of text and numbers in the field (or column).
    Auto IdentitySelect this check box to define a field (or column) as auto increment field.
    Primary Key

    Select this check box to define a field (or column) as Primary Key.

    Note:

    Primary keys should contain unique values and cannot have NULL values.

  8. Click GENERATE SQL to generate the SQL Query.


    Figure: GENERATE SQL (when Edit Query check box is not selected)

  9. Click SAVE to generate a new SQL Table successfully.

    Note:

    To view the Custom Table, type in  select * from Location_Table in the SQL Server Management Studio.

 Example 2: Create a Custom Table to Store Workgroup-wise Approver Details.

Scenario: Consider that the user wants to create a Custom Table with the following fields to store Workgroup-wise Approver details.

FieldsData Type
Workgroup

varchar

Workgroup Owner

int

Additional Owner

int

To Create a New Custom Table:

  1. Select Admin > Basic > Infrastructure > Web Services. The WEB SERVICE EXPLORE page is displayed.
  2. On the WEB SERVICE EXPLORE page, click CREATE NEW. The WEB SERVICE DESIGNER page is displayed.
  3. On the WEB SERVICE DESIGNER page, select Create SQL Table from drop-down list.
  4. Specify the SQL Table Name (Ex: Workgroup_Table).


    Figure: WEB SERVICE DESIGNER page: Create SQL Table: SQL Table Name
  5. Click icon in FIELDS section to view FIELD PROPERTIES
  6. Type in Field Name (or Column Name) and select the Data Type value from drop-down list (Refer the above table for column names and data types). 

  7. Click ADD NEW FIELD to add a new field (or Column). For more information about the fields on the FIELD PROPERTIES, see Field Description.


    Figure: WEB SERVICE DESIGNER: FIELD PROPERTIES

    Field Description

    The following table describes the fields on the FIELD PROPERTIES section:

    FieldDescription
    Field Name

    Specify the name for the field (or column).

    Data Type

    Select the required data type for the field (or column) from the drop-down list. Following are the available data types:

    • nvarchar: Select the data type as nvarchar to store variable characters (letters and numbers) in the field (or column).
      Note: In the size parameter, specify the maximum column length in characters.
    • int: Select the data type as int to store a whole number (not a fraction) in the field (or column).
    • numeric: Select the data type as numeric to store numbers that have fixed precision and scale.
    • decimal: Select the data type as decimal to store numbers that have fixed scale.  
    • bit: Select the data type as bit to store a single bit with a value of 0,1, or NULL
    • datetime: Select the data type as datatime to store date and time combination in the field (or column).
    • text: Select the data type as text to store text or combinations of text and numbers in the field (or column).
    Auto IdentitySelect this check box to define a field (or column) as auto increment field.
    Primary Key

    Select this check box to define a field (or column) as Primary Key.

    Note:

    Primary keys should contain unique values and cannot have NULL values.

  8. Click GENERATE SQL to generate the SQL Query.


    Figure: GENERATE SQL (when Edit Query check box is not selected)

  9. Click SAVE to generate a new SQL Table successfully and the following pop-up page is displayed.

  10. Click YES. The WEB SERVICE EXPLORE page displayed.



    Note:

    To view the Custom Table, type in  select * from Workgroup_Table in the SQL Server Management Studio.

Creating Table via Excel in Web Services

You will be able to create a new table in webservices using excel. You can also view or modify the created table. You can view the table along with the records unlike creating table with SQL where you can view only SQL queries in the Web Service Explorer. You can also import and export the records in the excel. 

 Example

ACME requires the app designer, Sam who has no SQL knowledge, to create a table to keep a track of the leaves of all of the employees from an already existing excel sheet named Leave_History_Excel, which has the details about employee's leaves history. Now, Sam has the following requirements:

  • To create a table from an already existing excel sheet
  • To view the created table
  • To edit the created table by adding more records or overriding the existing ones
  • To download only the template of the excel sheet without any records, if required

Following are the columns available in the Leave_History_Excel

  • Leave Type
  • From Date
  • To Date
  • No of Days
  • Status 
  • Remarks
  • Approval Status

For the above requirement, Sam can create the table named Leave_History_Table easily from the Leave_History_Excel excel sheet with the help of Generate Table button available for Create SQL Table drop-down option on the WEB SERVICE DESIGNER page.

Sam can view and edit the table from the View Data and Modify Data which are available on TABLE list page (Admin > WEB SERVICE DESIGNER page > ACTIONS > TABLE LIST)

Sam can import the same records mentioned in the Leave_History_Excel by choosing the option CONTINUE WITH THIS DATA.

Sam can add more records or override the existing ones with the option Edit.

Sam can download the only template of Leave_History_Excel without any records with option DOWNLOAD MASTER.

Creating New Table using Excel

The following section provides information on how to create a table using Excel.

To Create a New Table using Excel, perform the following steps:
  1. Select Admin > Basic > Infrastructure > Web Services. The WEB SERVICE EXPLORER page is displayed.


    Figure: WEB SERVICE EXPLORER page


  2. On the WEB SERVICE EXPLORER page, click CREATE NEW. The WEB SERVICE DESIGNER page is displayed. 


    Figure: WEB SERVICE DESIGNER page

  3. On the WEB SERVICE DESIGNER page, select Create SQL Table from the drop-down list.


    Figure: WEB SERVICE DESIGNER page: Create SQL Table

  4. Specify the SQL Table Name.


    Figure: WEB SERVICE DESIGNER page: Create SQL Table: SQL Table Name

  5. Click GENERATE TABLE to create a table using excel import.


    Figure: WEB SERVICE DESIGNER page

    The MASTER IMPORT page is displayed with the following three tabs:

    1. UPLOAD EXCEL
    2. MAP COLUMNS
    3. UPLOAD MASTER


    Figure: MASTER IMPORT page

  6. Specify the required fields on GENERAL tab.


    Figure: GENERAL

  7. Click on the download icon and it will open your file browser window to select the required Excel file from the location.


    Figure: Browser Window

  8. Click SUBMIT to import the selected excel sheet as source.


    Figure: GENERAL

  9. The uploaded excel file will display all of the available sheets inside it and you can choose the required sheet for mapping which needs to be uploaded if the uploaded excel sheet has more than one sheets. Select the required record and click on Select hyperlink. The MAPPING page under the MAP COLUMNS tab is displayed:


    Figure: MAPPING

    Field Description

    The following table describes the fields on the MAPPING page:

    Fields

    Description

    Column Name

    Displays all the columns name mentioned in the uploaded excel sheet.

    For Example in the above screenshot, LeaveHistory Excel displays the columns such as Leave Type, FromDate, ToDate,NoofDays, Status, Remarks and ApprovalStatus.

    Data Type

    Data Type defines which type of value can be provided for the respective column. Respective column input value should be in same data format. For Example: If Data Type for column name NoofDays is defined as an Integer, then the input value in this column should be an integer only.

    Select the required data type for the field (or column) from the drop-down list. Following are the available data types:

    • Number - Select the data type as Number to store numbers that have fixed precision and scale.
    • Integer - Select the data type as Integer to store a whole number (not a fraction) in the field (or column).
    • String - Select the data type as String to store variable characters (letters and numbers) in the field (or column).
    • Bool - Select the data type as Bool to store a single bit with a value of 0,1, or NULL.
    • Date - Select the data type as Date to store a date in the field (or column).

      Note:

      Format of Date must be DD:MM:YYYY

    • Date Time - Select the data type as Date Time to store date and time combination in the field (or column).
    • Time - Select the data type as Time to store a date in the field (or column).

      Note:

      Format of time must be HH:MM:SS

    By default, it displays the data type related to the column name. You may modify it as per the requirement.

    For Example: If the Column Name is mentioned as Priority then by default, the Data Type will be displayed as Number.

    Primary Key

    Select this check box to define a field (or column) as Primary Key.

    Note:

    Primary keys should contain unique values and cannot have NULL values. If Primary Key has duplicate values it will be highlighted during validation checks.

    Mandatory

    Select the Mandatory checkbox to indicate if the associated column is the mandatory column.

    Note:

    If Mandatory Key has null value or left empty then it will be highlighted during validation checks.

    Autogenerate?

    Autogenerate? field is displayed only if Data Type is Number or Integer.

    If Autogenerate? checkbox is selected it indicates that the value for the column will be populated automatically as the records are entered and in the series of the existing series.

    Figure: Autogenerate?

    For example:
    If a new table is created and 5 records are being uploaded. The auto-generate column will get automatically populated from 1 to 5.

    If there is an already existing table with 10 records and 5 more records have to be added. Then the auto-generate column will automatically take the subsequent series i.e. 1 to 10 serial no. already exists. 5 new records will take 11 to 15.

    PREVIOUSClick PREVIOUS button to go back to the previous screen.
    NEXT Click NEXT button to proceed further.
    CANCELClick CANCEL button to cancel the immediate action. It will redirects you to Web Service Designer page.
  10. Click NEXT. The following popup is displayed:


    Figure: Popup page 

  11. The popup page is displayed with two options:
     
    1. CONTINUE WITH THIS DATA - Click this option if you want to continue with the already existing data from the excel.

    2. DOWNLOAD MASTER - Click this option if you want to download the excel template having the defined columns with the configured data types, and add the records in the excel template.

  12. If you click CONTINUE WITH THIS DATA then the PREVIEW page under the UPLOAD MASTER tab is displayed:


    Figure: PREVIEW

    The PREVIEW page displays all of the records which are imported via excel. There will be a few validation checks before you do bulk upload. 

    The following table describes a few validation checks while bulk uploading. 

    SymbolMeaning

    If the data in the field does not match the data type mapped against that field, then it displays the data mismatch.

    For Example: If you have defined Data Type for a column name NoofDays as an integer but the input value is Five (text string) then it's a data mismatch.

    Mandatory columns can not be left blank.

    For Example: If you have defined Data Type for a column name Employee ID as mandatory then this column can not be left blank.

    In Excel data set if a single column which is mentioned as Primary Key has same values then that record is duplicate. 

    If a column is selected as Primary Key, then that column will become a unique identifier. This column can not have duplicate entries and will be identified during validation checks. 

    For Example:  If you have defined Data Type for a column name Employee ID as primary then this column can not have duplicate entries.


    Field Description

    The following table describes the fields on the PREVIEW page:

    Fields

    Description

    Discard

    Select the Discard checkbox to indicate that the selected record will not be included while upload. 

    ColumnDisplays all columns names which are imported via excel.
    PREVIOUSClick PREVIOUS button to go back to the previous screen.
    BULK UPLOADClick BULK UPLOAD button to upload all the records displayed on PREVIEW tab.
    CANCELClick CANCEL button to cancel the immediate action. It will redirects you to Web Service Designer page.
  13. Click BULK UPLOAD. The following popup is displayed with total count of records, count of uploaded records and count of failed records.


    Figure: BULK UPLOAD popup 

    Download failed records - Click Download failed records
    to download the excel with failed records which were failed to upload. You can make corrections to the records and upload again. 
    OK & Proceed - Click OK & Proceed button will direct to the Table List Screen with the table having the records which are uploaded. A sample screenshot is as follows:



Note:

  • Ensure that the Excel file being uploaded is not the Excel file downloaded from the Summit AI Report.
  • Ensure that first row of Excel file to be uploaded is the column name (A1 Cell). Uploading data values should start from the second row onwards (A2 Cell).

     Example

  • To achieve optimal performance, keep number of rows/entries under 2000 for the Excel file being uploaded.
  • In a scenario to update an existing table, ensure that number of columns in Excel file being uploaded, matches the same number of columns in the existing table.
  • Ensure data mapping is done accurately for the respective columns under Mapping tab. Special attention must be given for Date, DateTime and Time data types.
  • Re-upload the Excel file if you get the following error message:
    Failed to get Worksheet Information
    It might be due to Excel file corruption during upload.
  • Ensure the Excel file being uploaded does not have duplicate Primary Key while appending new entries to an existing table with pre-defined Primary Key. Doing so, will prevent data from being uploaded.
  • Ensure that Excel file being uploaded is a simple file without any formatting such as date format, merge cells, cell hyperlink etc.

Various Methods to Import Data into Custom Table

The Administrators can use the following methods to import data into the Custom Table:

  • Import the data form source systems (such as: AD, SAP or HR) via API
  • Backend Import: You can import a file (.csv,.txt, etc.) into your database using SQL Server Management Studio.
  • SQL Query or Stored Procedure

Importing Custom Data into Custom Table Using Stored Procedure:

To Import Data into Custom Table Using Stored Procedure:

  1. On the WEB SERVICE EXPLORE page, click CREATE NEW. The WEB SERVICE DESIGNER page is displayed.
  2. On the WEB SERVICE DESIGNER page, select Create SQL Procedure from drop-down list.
  3. Specify the SQL Stored Procedure Name.
  4. Select Edit Query check box.
  5. Type in the stored procedure to insert new records in a table.

  6. Click SAVE to insert values into table.

 Example 1: Import Custom Data into Custom Table (Location_Table)

Consider that you want to import data into Custom Table (Location_table)

To Import Data Into Custom Table Using Stored Procedure:

  1. On the WEB SERVICE EXPLORE page, click CREATE NEW. The WEB SERVICE DESIGNER page is displayed.
  2. On the WEB SERVICE DESIGNER page, select Create SQL Procedure from drop-down list.
  3. Specify the SQL Stored Procedure Name (Ex: Location_table procedure).
  4. Select Edit Query check box.


    Figure: Edit SQL Query
  5. Type in the Stored Procedure to insert new records in the table.

     Sample Stored Procedure

    CREATE PROCEDURE [dbo].[Location_table procedure]

    AS

    BEGIN

    insert into Location_table(Country,State,City,EmailID,Approver)
    values(''USA'',''Alaska'',''Fairbanks '',''james@xyz.com'',''206'')

    insert into Location_table(Country,State,City, EmailID,Approver)
    values(''USA'',''California'',''Los Angeles'',''michael@xyz.com'',''207'')

    insert into Location_table(Country,State,City,EmailID,Approver) 

    values(''Canada'',''Ontario'',''Toronto'',''david@xyz.com'',''208'')


    END

  6. Click SAVE to insert values into the table.
 Example 2: Import Custom Data into Custom Table (Workgroup_Table)

Consider that you want to import data into Custom Table (Workgroup_Table)

To Import Data Into Custom Table Using Stored Procedure:

  1. On the WEB SERVICE EXPLORE page, click CREATE NEW. The WEB SERVICE DESIGNER page is displayed.
  2. On the WEB SERVICE DESIGNER page, select Create SQL Procedure from drop-down list.
  3. Specify the SQL Stored Procedure Name (EX: Workgroup_Table).
  4. Select Edit Query check box.


    Figure: Edit SQL Query
  5. Type in the Stored Procedure to insert new records into the table.

     Sample Stored Procedure

    CREATE PROCEDURE [dbo].[Workgroup_Table]

    AS

    BEGIN

    insert into Workgroup_Table (Workgroup,[Workgroup Owner],[Additional Owner])
    values(''Server Team'',''4494'',''1'')
    insert into Workgroup_Table (Workgroup,[Workgroup Owner],[Additional Owner])
    values(''Wintel Team'',''4486'',''4488'')
    insert into Workgroup_Table (Workgroup,[Workgroup Owner],[Additional Owner])
    values(''Server Team'',''4494'',''2'')

    END

  6. Click SAVE to insert values into the table.

ACTIONS

This section explains all the icons displayed on the ACTIONS panel of the WEB SERVICE EXPLORER page.

TABLE LIST

Click TABLE LIST to display the list of all tables which are created using Create SQL Table option on Web Service Designer page.


Figure: TABLE list page

Field Description

The following table describes the fields on the TABLE page:

Fields

Description

Table Id

Displays the ID of the table.


Table NameDisplays the name of the table.
No. of RecordsDisplays the number of records of a specific table.
View Data

Click on Download Excel hyperlink to view all of the records of the table. It will download the excel sheet with all record details of the table.

Modify Data

Click Edit hyperlink to modify any record details of the table. It displays the MASTER IMPORT page.

Last UpdatedDisplays the date and time the table was last updated.
Cancel Click Cancel to go back to the Web Service Explorer screen.

To View the Table

Perform the following steps to view the table:

  1. Navigate to Admin > Web Services. The WEB SERVICE EXPLORER page is displayed:


    Figure: Table List

  2. Click Table List under ACTIONS panel. The TABLE list page is displayed:


    Figure: TABLE list page 

  3. Click Download Excel hyperlink under the View Data column. The following popup is displayed:


    Figure: Download Excel Popup

  4. Click DOWNLOAD EXCEL.


    Figure: Download Excel


    The excel file is downloaded at the bottom-left of the page.  

To Modify the Table

Perform the following steps to edit the table:

  1. Navigate to  Admin > Web Services. The WEB SERVICE EXPLORER page is displayed:


    Figure: WEB SERVICE EXPLORER

  2. Click Table List under ACTIONS panel. The TABLE list page is displayed:


    Figure: TABLE list page

  3. Click Edit hyperlink under the Modify Data column. The following popup is displayed:


    Figure: Popup Page

  4. Click on the one of the following options:

    Override the table records - Select this option to replace all the table records with new records when updated excel is uploaded. By this, records can be deleted or edited, or new records can also be added.

    Add new records -Select this option to add new records to the already existing records. The new records will be appended at the end of the table.

    The MASTER IMPORT page is displayed:


    Figure: MASTER IMPORT

    Field Description

    The following table describes the fields on the MASTER IMPORT page:

    Fields

    Description

    Table Name

    Displays the name of the table whose records are getting modified.

    Mode

    Displays the mode based on the selection in the above popup. 

    Override - Displays the Mode as Override if OVERRIDE THE TABLE RECORDS is selected in the popup. A sample screenshot is as follows:

    Add new record - Displays the Mode as Add new record if ADD NEW RECORDS is selected in the popup. A sample screenshot is as follows:


    Source

    Download the excel which needs to be modified. 

    Download Excel

    Click Download Excel to download to excel sheet with all previous records.

    DOWNLOAD MASTER

    Click DOWNLOAD MASTER to download excel template without any records. It will only display the column names with appropriate validations associated with columns.

  5. Click on Download icon to browse the excel file which needs to be uploaded. A sample screenshot is as follows:

    Note:

    Only the Excel file can be uploaded and data types and column names can not be changed while editing the table.


    Figure: Download excel file

  6. Click SUBMIT. You can view the imported sheets under GENERAL tab. A sample screenshot is as follows:


    Figure: Imported Excel Sheets

  7. Click Select hyperlink and it moves to the MAPPING tab. A sample screenshot is as follows:


    Figure: Import - Mapping

    The following table describes the fields on the MAPPING tab.

    Fields

    Description

    Destination

    Displays the name of the destination columns

    Data TypeDisplays the data type of the Destination column.
    Source

    Select the required column from the drop-down list. Available options are as per the imported excel.

  8. Click Next. The PREVIEW tab is displayed.

    i. Add New RecordsThe records will be imported as per the Primary Key. If Primary Key matches, then as per the Primary Key, the new records gets replaced with the previous records. If new Primary Keys are fetched, then the new records will be entered at the end of already existing records.

    ii. Override the Table Records:
    The imported records override the already existing records i.e. all the existing records will be deleted and only new imported records are available in the table.


    Figure: Import - PREVIEW

You can discard the records and do the validation checks before Bulk Upload.

FILTERS

This section explains all the icons displayed on the FILTERS section. FILTERS provide easy and simplified search criteria to search for a table based on Table Name or Table ID from a list of tables. Click on FILTERS, the following popup page is displayed.

Figure: FILTERS popup page

Note:

FILTERS are applicable only to Table Id and Table Name field.

Field Description

The following table describes the fields on the FILTER popup page:

Fields

Description

Field

The fields related to the Table List are populated in the drop-down list. Select the required option. Available options are as follows:

  • Table ID
  • Table Name
Operator

Based on the selected field value, the operators are displayed. Select the required operator from the drop-down list. 

Available options for the TABLE ID field are as follows:

  • >=
  • <=
  • >
  • <
  • IN
  • Between

Available options for the TABLE Name field are as follows:

  • Contains
ValuesSpecify the respective values in Values field based on the selected Field type and Operator.
APPLYClick APPLY to save and to apply the above mentioned filters.
CLEARClick CLEAR to cancel any selected filters.