EONE BLOG

Tech Tuesday : How to Handle Paging with Shopify in SmartConnect


If you have connected to Shopify using SmartConnect and have more than 250 records to query (Customers, Products, Orders, etc.) in your Shopify account you have likely run into the Shopify API limit.  Shopify limits each call to 250 records and breaks them out into pages.  To handle this, we need to run a map for every 250 records using a different page and map for each run.  To start, make sure you have Shopify setup using the REST Connector in SmartConnect.  If you need assistance with this please look at this article. We also need to set up a couple global variables and a default SQL connection to the SmartConnect database.

Create two global variables.

  1. GBL_SHOPIFYPRODUCTPAGES with a blank default value.
  2. GBL_SHOPIFYPRODUCTSCURRENTPAGE with 1 as the default value.


Create a SQL Default Connection


Once you have the connector, variables, and default SQL connection setup you will need to modify the URL for the Get All Products method to take the paging into consideration.  The new URL will be: https://{Store_Prefix}.myshopify.com/admin/products.json?limit=250&page=GBL_SHOPIFYPRODUCTSCURRENTPAGE

In this method we are defining the max record count and which page we want the data from using a global variable.  We will populate the GBL_SHOPIFYPRODUCTSCURRENTPAGE in a script below.

Now that we have the connector and variables setup, we need to setup our maps and get the real page count so we know how many times to run our maps.  We need two maps to handle the paging.  The first map will handle the odd pages and the second map will handle the even pages.  The maps will alternate runs until there are no pages remaining to process.

Map one will use your Get All Products method from the REST connector as a source.  Add a Run Script Task that will run before the map.  This script will get how many pages of products are in your Shopify system.  You will need to update the Authorization to your token and update the endpoint to include your store prefix.

Dim header As New System.Net.WebHeaderCollection() ‘create header

header.Add(“Authorization: Basic YOUR_TOKEN”) ‘add your token to header

Dim sUrl As String = “https://YOUR_STORE_PREFIX.myshopify.com/admin/products/count.json” ‘endpoint to call – count of all products

Dim request As System.Net.HttpWebRequest = TryCast(System.Net.WebRequest.Create(New Uri(sUrl)), System.Net.HttpWebRequest)

request.Headers = header

request.Accept = “application/json”

request.Method = “GET”

Dim response As System.Net.WebResponse = request.GetResponse()

Dim dataStream As Stream = response.GetResponseStream()

Dim reader As New StreamReader(dataStream)

Dim responseFromServer As String = reader.ReadToEnd()

‘Parse json page response

responseFromServer = responseFromServer.Substring(9)

responseFromServer = responseFromServer.Trim(“}”.ToCharArray())

Dim totalpages as Integer

totalpages = responseFromServer/250 ‘get total pages

GBL_SHOPIFYPRODUCTPAGES = totalpages ‘set GBL_SHOPIFYPRODUCTPAGES variable to current pages

We also need to add a Map Post Task if the map succeeds.  This script will update the current page variable so the next map will process data from page two and so on as the loop continues until we run out of pages to process.

Dim connection As SqlConnection = New SqlConnection(_SQLDEF_SMARTCONNECTDB)

‘check to see if the current page is less than the total pages and increment

If GBL_SHOPIFYPRODUCTSCURRENTPAGE < GBL_SHOPIFYPRODUCTPAGES Then

GBL_SHOPIFYPRODUCTSCURRENTPAGE = GBL_SHOPIFYPRODUCTSCURRENTPAGE + 1

‘update the global variables accordingly for next map run

Using connection

connection.Open()

Dim cmd2 As SqlCommand = New SqlCommand(String.Format(” UPDATE GlobalVariable  SET Value =” & GBL_SHOPIFYPRODUCTSCURRENTPAGE & “WHERE [Name] = ‘GBL_SHOPIFYPRODUCTSCURRENTPAGE'”), connection)

cmd2.ExecuteNonQuery()

connection.Close()

End Using

return true

Else

GBL_SHOPIFYPRODUCTSCURRENTPAGE = 1 ‘current page is not greater than products page, set back to 1

Using connection

connection.Open()

Dim cmd2 As SqlCommand = New SqlCommand(String.Format(” UPDATE GlobalVariable  SET Value =” & GBL_SHOPIFYPRODUCTSCURRENTPAGE & “WHERE [Name] = ‘GBL_SHOPIFYPRODUCTSCURRENTPAGE'”), connection)

cmd2.ExecuteNonQuery()

connection.Close()

End Using

return false

End if


This map is ready to be saved and duplicated.

Make a duplicate of the first map.  On the duplicated map, remove the map pretask because we don’t need to get the page count again.  Add a second Map Post Task of Run Map, choose the first map we created, and mark the Use variables from parent map. 

On the first map, we need to do the same to run the second map.   Map one will run map two, and then map two will run map one and so on until all the pages of products have been processed.

Shopify has endpoints that have counts for many of their resources, so you can apply this setup to any of them to page through your records.  If you are connecting to a web service that does not provide a count endpoint, you can apply the same type of map looping, but the maps will stop running when the source returns no more data.

Leave a Comment




RECENT POSTS


SmartConnect replaces Integration Manager for Concur to Microsoft Dynamics GP integrations
Upcoming Webinar: Complex NAV and Microsoft Dynamics 365 Business Central Integrations Made Simple
How to Plan an Integration
eXtreme365 Dubrovnik Recap
Tech Tuesday : What Data Can SmartConnect Integrate in Dynamics GP (eConnect)

POPULAR POSTS


2016 SmartConnect Integration Bootcamps
Tech Tues: Automatically refreshing Pivot Tables in Excel Refreshable Reports
Happy Thanksgiving from the eOne team!
New Releases of Extender and SmartList Builder
2017 SmartConnect Integration Bootcamps

CATEGORIES

ARCHIVES

open all | close all

TAGS

#fargo CRM Demo Dynamics Dynamics 365 dynamics crm Dynamics GP Dynamics NAV Econnect Employee Spotlight eone eOne News eOne Spotlight ERP Events Excel Excel Report Builder Extender Extender Enterprise GP GPUG integration integration manager Meet the Team Microsoft Microsoft dynamics crm Microsoft Dynamics GP ODBC Office Relationships partners release SalesForce SalesForce.com scripting SmartConnect SmartList SmartList Builder SmartList Designer SmartView SQL Support Tech Tuesday Templates training Zendesk

Integrate & Automate without Any Code.

SmartList Data has Never Been Faster.

The Easiest Way to Report on GP Data.