One of the neat things about REST APIs is every API is different. The SmartConnect REST Connector is simple and adaptable enough to work with many different APIs. Recently I have been working with the API for Big Commerce which is a popular storefront cloud app. These storefront apps like Big Commerce will have variants and different options for products.
The Scenario
The example I used was a T-Shirt. The shirt has three options, Color, Size, and Availability. Typically, a data source will display the various options like the below screen shot. The SmartConnect REST Connector cannot add child data or array within a single web call. This restriction makes the connector less dynamic than the prebuilt connectors we have. We are working on adding these features in a future release to make this process much easier!
Prerequisites
Import the maps and global variables attached above
Run attached script to create the SQL table and Stored procedures
Product already exists in Big Commerce
Formatting the data
SmartConnect will need all of the Item Options on one line instead of spread out as above. To fix this we can use SQL Stored Procedure(InsertOrUdpateArray) to take all Option_Values with the same Item and Option_Name on one line. The procedure then writes the data to a staging table so SmartConnect can process it. After the Procedure processed the data it looked like the below screen shot. The procedure and associated table currently go to 10 records which means it can handle arrays up to 10 values in a single option, but the number of options is not limited.
Next, I used a map with a Stored Procedure destination to convert the data using the stored procedure. In the attached file this is map “Convert_Array”. I mapped my three columns and let SQL do all the heavy lifting converting it to the correct format.
Setting up the REST Destination
To create the REST Destination the map only needs to be made with one array record included as shown below. We will use another SQL Stored Procedure(CreateRestFields) to take these values and multiply them out so we can have up to 10 values in the array. This can also be done manually preferred. If I ran the map as shown below it will only take the first column in my source so I would only import the color Black, and not the other four colors.
The CreateRestFields Stored Procedure takes a supplied method name and field name and creates 9 copies with a number appended to the beginning so they can be differentiated. In this example I needed to run the procedure twice because I had two values in my array, ‘is_default’ and ‘label’. Once I finished this is what my REST Method Fields looked like.
The arrangement of the fields shown here doesn’t matter, because SmartConnect uses the arrangement in the Method Body to send the data. This step has created all the fields we need for mapping. Next, I created map BIG_COMMERCE_PRODUCT_OPTION with my SQL staging table as the data source.
Dynamically set number of array values
We need to modify the method body for each option. So, in my example I needed to change the body to only use two of the mapped option value sets when using Active option, but to use five when processing the Color option. To do this I used a third and final Stored Procedure(UpdateRestMethodBody).
This procedure will run on every line to make sure the correct method is being used. If this doesn’t run, then there will be blank values in the body which will make the JSON invalid. This stored procedure will take some adapting for other web services, because there isn’t a way for it to know what the end JSON should look like.
Putting it all together
Finally, we can put this all together. The process will take three maps. I have been unable to find a way to restrict it to fewer. We need to update the method body on a document level, but the map has already pulled the document information by the time we get to a premap task so we need to send the data to another map to do the processing of the line so the method body is pulled again. I set it all to run from a middle map called MASTER_MAP. This has a preamp task calling the CONVERT_ARRAY map to take the data and send it to the staging table.
The map has two document level scripts running before calling the map to send the data to the REST Service. The first grabs the Item name and Display Name and stores them in global variables for the child map to use. The second calls the UpdateRestMethodBody procedure to assign the appropriate method.
Next, the actual map that is calling the REST service is called. This map will run the one line and then wait for the master map to change the body and tell it what to do next.
The MASTER_MAP also has a post map task to clear the staging table so it is ready for the next time the map is run.
Have questions? Give me a shout at ethan.sorenson@eonesolutions.com.