💾 Pt. 3 Importing data to WEBCON

In previous post we've worked on importing data using Cyclic Actions, but now it's time to focus on REST API way.

This article is split into three parts:

Example application
Import with cyclic actions
Import with REST API - you are here :)
I thought about this as one topic, but while writing article it just grew...

Create API tokens in WEBCON admin panel

Go to your WEBCON instance at: https://webcon.local/WEBCONBPS/adminpanel/apiApplications and follow those steps: https://developer.webcon.com/docs/registration-and-authentiaction/

You should end it with settings similar to those:

Application type -> App context
Name -> Data Import
Login -> [email protected]
E-mail -> empty
Client ID -> a8494215-ace2-4c77-b84c-f13cbe1b6c2b
Client Secret -> ifUCBb/qU8v88m00sQtQ9HRCW43xPuaQ1/ivozFmCt0=
Access token lifetime -> 4 hours
Permissions -> App.Elements.ReadWrite.All

🔐

Remember that you should keep your Client ID/Secret - Secret. My instance is running on a local virtual machine, and it's used only by me for testing purposes.

After creating API application - you are able to specify it's permissions like it's a regular user in Designer Studio - using it's login. Our scripts will need permissions to Access application, and launching workflow instances.

Permissions for registered API application.

Swagger

Each WEBCON installation comes with working Swagger, which you can use to explore and test API. You can find it under /api endpoint - https://webcon.local/WEBCONBPS/api. It's not great being honest, but:

Here are some examples of what I mean based on http://bps.lumenn.local/api/data/$apiVersion/db/$databaseId/elements endpoint.

This is part of the schema required to set data on new document.

{
  "formFields": [
    {
      "id": 0,
      "guid": "string",
      "type": "Unspecified",
      "svalue": "string",
      "name": "string",
      "formLayout": {
        "editability": "Editable",
        "requiredness": "Mandatory"
      },
      "value": [
        {
          "id": "string",
          "name": "string"
        }
      ],
      "mode": "Standard"
    }
  ],
  "businessEntity": {
    "id": 0,
    "guid": "string"
  }
}

And this is what we actually have to send:

{
    "formFields": [
        {
            "guid": "dd8a11db-e99b-4b8f-ba5a-d4e772235ca1",
            "svalue": "39275#CF/2023/08/05289"
        },
        {
            "guid": "b4a1f00a-be47-45a7-8332-81fbde8b5f71",
            "value": "SO51180"
        }
    ],
    "businessEntity": {
        "guid": "E554D815-F958-463A-B4DD-E2EB29B29FF2"
    },
}

There is a lot of information you don't have to specify, and there is a difference between value, svalue, and

{
	"value": [
		{
			"id": "",
			"name": ""
		}
	]
}

Using value - you won't be able to set content of dropdown fields which are stored as id#name in database - you should use svalue, or value object for that purpose.

While writing this article I had to search for more examples, to get it going. Without them it would be much harder. Check them out:

Powershell

Whole import will be prepared using PowerShell scripts, as It's by default installed on Windows Servers. No need for additional dependencies, although you could easily rewrite it in any other language. To connect with SQL there is a need to install module - SqlServer. You can do it by running this command: Install-Module SqlServer.
I had to specify -AllowClobber parameter, as I already had previous versions installed, and this overwrote them. Whole command looked like this:

Install-Module SqlServer -AllowClobber

First we will need import Products/Customers - same as when using Cyclic Actions, because Sales uses them.

Code is available on GitHub:

We will need GUIDs for this script. IDs could be used too, but they change between environments, and GUIDs do not so the choice is rather easy. You can find them in Designer Studio under cog icon:

Lets start with products, as it's shortest one:

Import-Module sqlserver

# In this section we just declare data required for our API connectcion.
$clientId = "a8494215-ace2-4c77-b84c-f13cbe1b6c2b"
$clientSecret = "ifUCBb/qU8v88m00sQtQ9HRCW43xPuaQ1/ivozFmCt0="
$scopes = "App.Elements.ReadWrite.All"

# This returns access_token, which will be later used for authorization.
$authorization = Invoke-RestMethod `
    -Method Post `
    -Uri "http://bps.lumenn.local/api/oauth2/token" `
    -ContentType "application/x-www-form-urlencoded" `
    -Body "grant_type=client_credentials&client_id=$clientId&client_secret=$clientSecret&scope=$scopes"

$token = $authorization.access_token

# Query returning products from database
$results = Invoke-SqlCmd -Query @"
    SELECT
        ProductKey,
        ProductAlternateKey,
        EnglishProductName,
        ISNULL(ListPrice, 0) AS ListPrice,
        CASE 
            WHEN Status IS NULL THEN 0
            WHEN Status = 'Current' THEN 1
        END AS Active
    FROM 
        AdventureWorksDW2019.dbo.DimProduct
"@ -ServerInstance "localhost\SQLEXPRESS" -Verbose -TrustServerCertificate

# Guids, which define Workflow/Form/Path/BuisnessEntity
$lumennBuisnessEntityGUID = 'E554D815-F958-463A-B4DD-E2EB29B29FF2'
$productWorkflowGUID = '2660ca16-457d-432f-8b43-beb282ab999a'
$productFormGUID = '3d9819ff-573a-4d1a-b424-45652e963079'
$pathActiveGUID = 'c6a440c1-51ce-4aa4-a2f3-39cb691f2e88'
$pathBlockedGUID = 'abc2a33f-5bd3-4ba2-85d4-2b9aae166ae2'

# Guids defining form fields
$formFieldGUIDs = @{
    name = '7ffc9b32-ad57-4939-af60-d1ab29f6c01c';
    price = '669e369c-1546-4560-9794-44d46b697416';
    erpID = '673a9f06-055f-40b9-b6b6-57b4158db863';
    productKey = '8b54d6c3-a340-4909-b435-f62cf0004eb7';
}

# If you have only one db, then it's just 1, if you have more, then you should know how to deal with it :)
$databaseId = 1
# Check what API versions are available on your instance for examplel with Swagger - I'm using latest stable.
$apiVersion = "v5.0"

$i = 1
$errors = New-Object System.Collections.Generic.List[System.Object]

# This is main loop, which will execute for each row returned from sql query - for each row it prepares request body, and invokes REST api.
foreach($row in $results) {
    $requestBody = @{
        workflow = @{
            guid = $productWorkflowGUID;
        }
        formType = @{
            guid = $productFormGUID;
        }
        formFields = @(
            @{
                guid = $formFieldGUIDs.name;
                value = $row.EnglishProductName;
            },
            @{
                guid = $formFieldGUIDs.price;
                value = $row.ListPrice;
            },
            @{
                guid = $formFieldGUIDs.erpID;
                value = $row.ProductAlternateKey;
            },
            @{
                guid = $formFieldGUIDs.productKey;
                value = $row.ProductKey;
            }
        )
        businessEntity = @{
            guid = $lumennBuisnessEntityGUID
        }
    };

    $body = ConvertTo-Json $requestBody -Depth 10

    try {
        $pathGUID = If ($row.Active) {$pathActiveGUID} Else {$pathBlockedGUID}
        $response = Invoke-RestMethod `
                    -Method Post `
                    -Uri "http://bps.lumenn.local/api/data/$apiVersion/db/$databaseId/elements?path=$pathGUID" `
                    -Body $body `
                    -ContentType "application/json" `
                    -Headers @{Authorization = "Bearer $token"}
    }
    catch {
	    # If something goes wrong, we will save row data, to check it later.
        $errors.Add($row)
    }

    Write-Progress -Activity "Import in progress" -Status "$i out of $($results.Length)"
    $i++;
}


if ($errors.Count -gt 0) {
    $errors | Export-Csv -Path "$env:USERPROFILE\Downloads\ProductErrors.csv"
}

Code to import Products

Customers are pretty similar, so no comments here:

Import-Module sqlserver

$clientId = "a8494215-ace2-4c77-b84c-f13cbe1b6c2b"
$clientSecret = "ifUCBb/qU8v88m00sQtQ9HRCW43xPuaQ1/ivozFmCt0="
$scopes = "App.Elements.ReadWrite.All"

$authorization = Invoke-RestMethod `
    -Method Post `
    -Uri "http://bps.lumenn.local/api/oauth2/token" `
    -ContentType "application/x-www-form-urlencoded" `
    -Body "grant_type=client_credentials&client_id=$clientId&client_secret=$clientSecret&scope=$scopes"

$token = $authorization.access_token

$results = Invoke-SqlCmd -Query @"
    SELECT
        CustomerKey,
        FirstName,
        MiddleName,
        LastName,
        BirthDate,
        CASE
        WHEN Gender = 'M' THEN 'Male'
        WHEN Gender = 'F' THEN 'Female'
        END AS Gender
    FROM 
        AdventureWorksDW2019.dbo.DimCustomer
"@ -ServerInstance "localhost\SQLEXPRESS" -Verbose -TrustServerCertificate

$lumennBuisnessEntityGUID = 'E554D815-F958-463A-B4DD-E2EB29B29FF2'
$customerWorkflowGUID = '8a5d448e-f8cd-45ff-a7fd-b3138390d32b'
$customerFormGUID = '3017e844-6313-4ce2-ace0-ae38d913b77b'
$pathGUID = 'a7465986-c850-4222-ae87-d07bb356004c'

$formFieldGUIDs = @{
    firstName = '34a16cc2-eb87-4d86-be05-62f9262cb79e';
    middleName = '1d6dd53d-89d8-4bc3-8f74-fef4f0ad3cd1';
    lastName = '7bc907b6-75ce-4f54-9453-e1ff526505b5';
    birthDate = '70673133-bfe7-452a-981f-4b6d0b2e16db';
    gender = 'b5e53681-e96d-4596-a2c8-260546882ffe';
    customerKey = 'd539357f-6ae7-4d0c-a05a-ba5f3080a650';
}

$databaseId = 1
$apiVersion = "v5.0"

$i = 1
$errors = New-Object System.Collections.Generic.List[System.Object]

foreach($row in $results) {
    $requestBody = @{
            workflow = @{
                guid = "$customerWorkflowGUID"
            }
            formType = @{
                guid = "$customerFormGUID"
            }
            formFields = @(
                @{
                    guid = $formFieldGUIDs.firstName;
                    value = $row.FirstName;
                },
                @{
                    guid = $formFieldGUIDs.middleName;
                    value = $row.MiddleName;
                },
                @{
                    guid = $formFieldGUIDs.lastName;
                    value = $row.LastName;
                },
                @{
                    guid = $formFieldGUIDs.birthDate;
                    value = Get-Date -Date $row.BirthDate -Format "o";
                },
                @{
                    guid = $formFieldGUIDs.gender;
                    value = $row.Gender;
                },
                @{
                    guid = $formFieldGUIDs.customerKey;
                    value = $row.CustomerKey;
                }
            )
            businessEntity = @{
                guid = $lumennBuisnessEntityGUID
            }
    }

    $body = ConvertTo-Json $requestBody -Depth 10

    try {
        $response = Invoke-RestMethod `
                    -Method Post `
                    -Uri "http://bps.lumenn.local/api/data/$apiVersion/db/$databaseId/elements?path=$pathGUID" `
                    -Body $body `
                    -ContentType "application/json" `
                    -Headers @{Authorization = "Bearer $token"}
    }
    catch {
        $errors.Add($row)
    }

    Write-Progress -Activity "Import in progress" -Status "$i out of $($results.Length)"
    $i++;
}

$errors | Export-Csv -Path "$env:USERPROFILE\Downloads\CustomerErrors.csv"

Code to import Customers

In AdventureWorks there are around 18k customers - it took around 1h to import them all using this method.

Only sales are left, so here we go:

Import-Module sqlserver

$clientId = "a8494215-ace2-4c77-b84c-f13cbe1b6c2b"
$clientSecret = "ifUCBb/qU8v88m00sQtQ9HRCW43xPuaQ1/ivozFmCt0="
$scopes = "App.Elements.ReadWrite.All"

$authorization = Invoke-RestMethod `
    -Method Post `
    -Uri "http://bps.lumenn.local/api/oauth2/token" `
    -ContentType "application/x-www-form-urlencoded" `
    -Body "grant_type=client_credentials&client_id=$clientId&client_secret=$clientSecret&scope=$scopes"

$token = $authorization.access_token

# First we query all unique sales from the table
$results = Invoke-SqlCmd -Query @"
    SELECT DISTINCT
        CONCAT(WFD_ID, '#', WFD_Signature) AS Customer, /*Customer*/
        SalesOrderNumber
    FROM 
        AdventureWorksDW2019.dbo.FactInternetSales JOIN
        BPS_Content.dbo.WFElements ON CustomerKey = WFD_AttText4 /*Customer Key*/ AND WFD_DTYPEID = 1003 /*Customer*/
"@ -ServerInstance "localhost\SQLEXPRESS" -Verbose -TrustServerCertificate

$lumennBuisnessEntityGUID = 'E554D815-F958-463A-B4DD-E2EB29B29FF2'
$saleWorkflowGUID = '5525a5f2-a71b-43eb-914d-1d489ca81da5'
$saleFormGUID = '58a2b9af-d569-4fd6-b8cf-d61528a2085f'
$pathGUID = '9ee1e141-d289-4b75-9785-3b8fc9789a09'

$formFieldGUIDs = @{
    customer = 'dd8a11db-e99b-4b8f-ba5a-d4e772235ca1';
    orderNumber = 'b4a1f00a-be47-45a7-8332-81fbde8b5f71';
    orderedItems = @{
        guid = '0175b3a8-0e1d-4f35-b9ff-57e25d6367bf';
        product = '5402c7e5-338e-4ef0-989e-317a6bf537d6';
        quantity = 'c120eb08-4b50-4692-94bb-37dd7c969e24';
        unitPrice = '480df9a1-4309-42c7-aeb3-a5d8380290e8';
    }
}

$databaseId = 1
$apiVersion = "v5.0"

$i = 1
$errors = [System.Collections.ArrayList]::new()

# Each unique sale might have few order lines, so we will send another query, to return specific order lines.
foreach($row in $results) {
    $saleItems = Invoke-SqlCmd -Query @"
    SELECT
        SalesOrderLineNumber,
        CONCAT(WFD_ID, '#', WFD_Signature) AS Product,
        OrderQuantity AS Quantity,
        UnitPrice AS Price
    FROM
        AdventureWorksDW2019.dbo.FactInternetSales JOIN
        BPS_Content.dbo.WFElements ON ProductKey = WFD_AttText7 /*Product Key*/ AND WFD_DTYPEID = 2004 /*Product form*/
    WHERE
        SalesOrderNumber = '$($row.SalesOrderNumber)' COLLATE DATABASE_DEFAULT
    ORDER BY
        SalesOrderLineNumber
"@ -ServerInstance "localhost\SQLEXPRESS" -Verbose -TrustServerCertificate
    
    $rows = [System.Collections.ArrayList]::new()

	# This loop prepares data for item list - it will have as many rows as there are in the database, and each row will have 3 cells/columns.
    foreach($sale in $saleItems) {
        $cells = @(
            @{
                guid = $formFieldGUIDs.orderedItems.product;
                svalue = $sale.Product;
            },
            @{
                guid = $formFieldGUIDs.orderedItems.quantity;
                value = $sale.Quantity;
            },
            @{
                guid = $formFieldGUIDs.orderedItems.unitPrice;
                value = $sale.Price;
            }
        )
        $rows.Add(@{cells = $cells}) > $null
    }

    $listItemsBody = @(
        @{
            guid = $formFieldGUIDs.orderedItems.guid;
            rows = $rows;
        }
    )

    $body = @{
            workflow = @{
                guid = $saleWorkflowGUID
            };
            formType = @{
                guid = $saleFormGUID
            };
            formFields = @(
                @{
                    guid = $formFieldGUIDs.customer;
                    svalue = $row.Customer;
                },
                @{
                    guid = $formFieldGUIDs.orderNumber;
                    value = $row.SalesOrderNumber;
                }
            );
            itemLists = $listItemsBody;
            businessEntity = @{
                guid = $lumennBuisnessEntityGUID;
            }
    }
    $bodyJSON = ConvertTo-Json $body -Depth 10
    try {
        $response = Invoke-RestMethod `
                    -Method Post `
                    -Uri "http://bps.lumenn.local/api/data/$apiVersion/db/$databaseId/elements?path=$pathGUID" `
                    -Body $bodyJSON `
                    -ContentType "application/json" `
                    -Headers @{Authorization = "Bearer $token"}
    }
    catch {
        $errors.Add($row)
    }

    Write-Progress -Activity "Import in progress" -Status "$i out of $($results.Length)"
    $i++;
}

if ($errors.Count -gt 0) {
    $errors | Export-Csv -Path "$env:USERPROFILE\Downloads\SaleErrors.csv"
}

Code to import Sales

There is a little bit of code to make this method work - most of it is creating the request body though. Do you know more methods to import data to WEBCON? Share them in comments!