Get Orders Using The BigCommerce API

Slide: Introduction and Objectives

Welcome to the Business Process Automation Platform training videos from Codeless Platforms. Here are the objectives, required tools and approximate running time for this video.

On completion of this training exercise we will;

Use the Web Service Connector tool to connect to the BigCommerce API, get order headers and details, map, transform and convert the BigCommerce data and write the order headers and details to the staging database.

Slide: Prerequisites

If you wish to follow this process in your own environment you will need;

The Staging Database – preferably in the same location as BPA Platform and appropriate Drivers installed for your chosen database.

We are using Microsoft SQL server in this example.

In addition, mapping and data transform files are required as part of this tutorial.

The Staging database and all other assets can be found here: http://trainingresources.codelessplatforms.com

You will need a deployment of BigCommerce for testing. If you do not have access to this
you can sign up for a free trial: https://www.bigcommerce.com/essentials/free-trial/

If required, We have a video that can help with Signing up and the creation of your API key.

The web service connector tool will need to be configured with the BigCommerce API, which we will look at shortly. This will require the following details.

Slide: Call Stored Procedure OLEDB Connection

Before we begin making the task we will need to set up a connection for the call stored procedure tool.

In System, Tools, Data connectors, double click call stored procedure OLEDB, and click add.

Select the appropriate driver, in this case Microsoft OLE DB provider for SQL Server and Click ok.

In the Server drop down we will select local, use trusted connection and in the options select BPA Training DB v3.2 from the list. Click ok.

If successful, you will have the option to name this connection. We will call this B2CStaging v3.2.

Click OK, and then click close.

Slide: Creating a REST Web Service

In System > Tools > Data Connectors, let open the web service connector tool.

Add a new configuration and name it… “BigCommerce tutorial”

Select ‘I do not have a definition file’… Click OK.

Open the ‘Web Service Root’ tab. Under web service URL, type: https://api.bigcommerce.com/stores/ and the Web Service type is ‘Unknown’.

OK, let’s switch to the web browser and lets go to:
https://developer.bigcommerce.com/api-reference/store-management/orders/orders/getallorders

Bottom of the page under send a Test request, click the headers tab, and take Note the headers used.

Lets go back to BPA Platform, and on the ‘Request Headers’ tab replicate the header names and values we just noted.

It should look like this.

We want to ‘Allow mapping’ for X-Auth-Token and we will map these later.

Next, go to the Operations Tab and add a new operation.

This will be a ‘GET’ operation – name it GET_Orders. And under operation type ensure GET is selected. and Click OK –

Go to the URL Tab and select ‘Use the Connection URL’ – notice the URL appear at the bottom.

Lets look back at the BigCommerce documentation
https://developer.bigcommerce.com/api-reference/store-management/orders/orders/getallorders

In the Send Test Request, notice how the URL is made up.

Store name – the store we are going to use

v2/orders – the request we are going to use

OK back in BPA Platform. In the input parameter, Click in the parameter name field, and now type ‘store_hash’ – for now, just give it the parameter name and drag the store_hash parameter to the bottom, at the end of the URL.

Now type: /v2/orders?

Now on the API document web page, look at all the parameters. We won’t use most of these, however, we do want a specific status and also to limit the size of the reply.

This is done using pages and a limit – for example 10 orders per page and then asking for a specific page.

Add the following to the input parameters – these can be anything, but it is good practice to match the parameter in the documentation. Such as; status_id, page, limit.

Back in BPA platform we need to enter the parameters we want to use into the input parameter fields.

In the parameter area now type “status_id”. Back in the URL area at the end, type “status_id=”. Now drag in the staus_id from the input parameters. Followed by “&”.

Then type “page=” and in the parameter area type “page”. drag page from the parameter area followed by by “&”.

Finally type “limit=” and create an input parameter and drag limit at the end of the URL.

The URL should now look like:
https://api.bigcommerce.com/stores/{store_hash}/v2/orders?status_id={status_id}&page={page}&limit={limit}

Ok, In the output data tab, select ‘Output Required’.

The reply from BigCommerce will be in ‘JSON’ format, and in order for BPA Platform to convert this to XML we will need to give it a schema.

Go back to the BigCommerce documentation, and in the ‘Responses’ example section, Copy the entire contents under the application JSON response. This is an example of what will come back from the request later on.

Back in BPA Platform, in the output data tab, Paste this into the schema section.
Ok, click the infer schema from XML or JSON button, and this will convert what you pasted into the schema.

Now press validate schema to check this was OK.

And we can click ok and ok again to save and close this window. Then click close.

Slide: Creating Variables

Navigate to your tasks folder and create a new task. Name it “BigCommerce get orders” and go to the design tab.

Let’s now create the variables we need for this task.

In the browser, environment, with scope set to task, right click variables and select new.

Lets name it “varAcessToken”, then click the details tab.

Ensure type is variant, select parameter, with parameter attributes as In, Out. Then click ok.

We will now repeat this process to creating the following variables.

Slide: Creating the Task

In in browser with tools tab, under execute, drag in a run VBscript step.

Name this step “Set BigCommerce variables”.

Open the main tab. Now go to the browser, environment tab, scope set to task, drag in each variable we just created in the following order.

You now need to populate each of these variables with your store details, API credentials and the following details.

And click ok to close this step.

OK, now add a new web service connector tool.

In the General Tab – name this step, “Get Orders”. We will not use a recordset or XML from another step in this instance.

Go to the Web Service Tab.

Select the new Web Service configuration we created earlier.

Under operations drop down, select get_orders.

In the Mapping Tab, Expand the Web Service folders on the right side of the window so we can see all the parameters.

Open up the ‘Functions’ toolbox on the right and drag in a ‘Fixed/Dynamic’ data function.

In Global Headers folder, drag X-Auth-Token, to the fixed dynamic. Double click to open it.

Next open the value tab. And In the browser, in environment tab, set scope to task, Drag in the ‘varAccessToken’ into the output text area.

This has mapped the variable containing the X-Auth_Token. On the transform function pane, click ok to close.

In functions, drag in 4 more ‘Fixed/Dynamic’ data functions for each of the Headers and each of the remaining Parameters. Then close the functions pane.

Drag store_hash to the fixed/dynamic. Double click to open it And click the Value tab.

In the browser drag varStoreHash to the output text area. Click ok to close the function.

Drag status_id to the fixed/dynamic. Double click to open it and in the Value tab. In the browser drag varStatusID into the output text area. Click ok.

Next drag ‘page’ to the fixed/dynamic. Double click to open it and click the value tab. And drag in the varpage variable from the browser. And click ok.

Lastly drag. ‘limit’ to the fixed/dynamic. Double click to open it and Click the value tab. Drag in the varLimit variable from the browser into the output text area. Then click ok to close this function.

Finally, ok to close this step.

Slide: Getting the Items

Firstly we need to open the browser to this page, as a refence point:

https://developer.bigcommerce.com/api-reference/store-management/orders/order-products/getallorderproducts

Back in BPA Platform. Save and close the current task. Then under data connectors area, under system > tools > data connectors and open the web service configurations.

Double click your configuration.

OK, we are now going to add a second request:

In the Operations tab, click Add. Name it “GET_Order_Products” and ensure operation type is set to GET. Click ok.

The define HTTP Operation window will now appear. In the URL Tab – click ‘use connection URL’ tickbox.

Under input parameters, Create the following Parameters… these are;

store_hash and order_id.

At the bottom, In the URL text area drag down store_hash and type /v2/orders/ and drag down order_id followed by /products.

Click the Output data tab and click output required.– Now go to the BigCommerce API documentation and copy the application JSON response example.

Then back in BPA platform, paste this schema into the text area and then click Infer schema from XML or JSON. Then Validate the schema.

And if it is valid, click ok. Click OK again and click close.

Slide: Transform Data

We have previously looked at transforming the data so this time we will import a predefined transform.

Lets go back to our Task.

Add a Transform Data step.

In Data source, click use a recordset and in the input source dropdown select “Output XML from Get_Orders”.

In the main tab click import, and in the training resources area click BigCommerce Transform structure.XSD file and then open.

Go to the mapping tab and expand the trees.

On the left drag the last “root” sub-node to the “Order” sub-node on the right to create a simple loop.

Next, drag “id” item on the left to “OrderID” on the right.

Open the functions pane, drag in a run VB script function and close the functions pane.

Under billing address sub-node on the left, drag “first name” to the the run VB script function. Then on the left also drag last_name to the run VB script.

On the right, drag FullName to the right of the run VB script function.

Now double click this function, and in the VB Script tab, type the following. Click check syntax and if vaild click ok. ok again to close the function pane.

The VB Script in the mapping merges first name and last name fields from the input data to “fullname” field in the output data.

Now click OK on the middle pane.

We now need to map the following as shown here.

Click ok to close this step.

We will now add a convert XML to Recordset step. In the main tab in data source click the drop down and select Output XML from Transform Data.

In the output recordset configuration, Select ‘All’ fields.

Now click OK to close.

Slide: Getting Order Details from BigCommerce

Lets add another Web Service Connector Tool.

In the General Tab, name it Get Order Products. Then click use a recordset or XML source from another step and in the drop down choose ‘Results from Convert XML to Recordset’ we just created.

In the web service tab, In the drop down select your configuration.

In operations select GET_Order_products.

Then in the Mappings tab expand the trees. And in the functions tab, drag in 2 Fixed/Dynamic functions. And close the pane.

Drag x-auth-Token from the right to the fixed/dynamic function. Then double click it open it. Click the value tab. In the browser environment tab set scope to task, and drag in the VarAccessToken variable into the output text area. Then click ok to close this function.

Then drag Store hash to the remaining fixed/dynamic function. Now double click to open it. And in the value tab, drag the varStoreHash variable into output text area. Click ok to close this function.

Now map Order_Order_id from the Left side to order_id on the right AND also drag Order_Order_id to supplementary reference.

Map the “Row” node from the left side to GET_order_products node on the right – this will automatically create a simple loop.

Click ok to close this step.

Slide: More Transformations

Now let’s Add another transform data for the Order Products.

Let’s name it Transform Order Products. In data source select, use a recordset or XML source from another step, in the drop down select Output XML from Get Order Products.

Click on the Main tab and click the Import button, select BigCommerce transform order products structure XSD file from the training resources and click open.

Now click the mapping tab and expand the trees. And then map the following.

Note the two loops. We can have multiple orders (orderIDs) and Each order can have multiple products (OrderItemIDs). Click ok to close this step.

Now we need to convert the XML into a recordset as we are about push the records into the SQL Staging database.

So lets add a convert XML to recordset step. Append the name “Order Products”

In the main tab, select data source task step, and in the drop down select Output XML from transform order products. Then select all fields. Click ok to close.

Slide: Stored Procedure to Write Order Headers

OK, Lets add a Call Stored Procedure (OLEDB) step. Name this step write order header to staging.

In the general tab click use a recordset and in the drop down select results from convert xml to recordset.

Click the main tab. And in connection used select the connection to the staging database.

In select a procedure, click browse, we will now select a procedure from DBO and find SP_order_header upsert. Select it and click OK.

We can now see the available parameters.

Firstly, locate Web_order_id and from the browser on the environment tab with scope set to this step, drag in order_orderID into the value column.

Now do the same for the following, as shown here.

Click OK to close this step.

Slide: Stored Procedure to Write Order Details

Now we will Create another stored procedure (OLEDB) step for order details.

Name this step “write order products to staging”.

Click use a recordset and in the dropdown select results from convert xml to recordset order products.

This will link the order details XML to the Recordset for order Products as an Input source.

Click the main tab. In connection used ensure your staging database connection is selected. Then in select a procedure click browse. Under DBO, locate sp_order_detail_upsert, select it and click OK.

Then complete the following as shown here.

Click OK to close this step.

Slide: Testing the Task

OK, let’s join up the steps.

We can now save and close the task.

Before we run the task we need to ensure there are new orders in BigCommerce. Let’s add one now.

In the BigCommerce, we need to ensure the new order status is set to “awaiting fulfillment”. Let’s make note of the order number.

Back in BPA Platform…we can now run the task.

Once it has completed, We can now look at the staging database.

Expand databases, b2cstagingdb_v3.2, and select dbo.tbl.order right click and select top 1000 rows.

You can now see the order we created earlier with its matching web order ID. We can also see the web source is BigCommerce as we mapped these fields earlier.

Slide: Summary

In this video we have downloaded sales orders from BigCommerce to a staging database. In a full production example these could be then synchronised with your ERP system.

For more information and other tutorials please visit our website. Thanks for watching.