Retrieve more than 1000 Records using Web Services for Microsoft Dynamics GP 2010


Chris Roehrich - Click for blog homepageThis is a reposting of an article Chris Roehrich originally wrote on my Developing for Dynamics GP blog.

A question we have received in the past in Developer Support is how to retrieve more than 1000 records using Web Services.   The maximum number of records a GetList method will return is 1000 records.  This is by design as this value is coded into the call to eConnect and it cannot be changed with a configuration file.

If you have 10,000 customer records in the RM00101 table that have a name that begins with the letter C, you will only receive the first 1000 of them when you call the GetCustomerList method with no criteria.   You can use the following code sample to loop through all the customers that have a name that begins with the letter C.   This is a strategy that was used in the training manual and I have slightly modified it.   You basically keep track of the last customer returned by the GetListCustomer method so you can retrieve the next 1000 after it and so on.

The following code is for a .Net 4.0 Console application that has a Service Reference to the Dynamics GP 2010 Web Service that I have called GPWS and it is also using the native endpoint.  Note when using the native endpoint, you instantiate a DynamicsGPClient.   The rest of the code is pretty much the same as calling the legacy service.

using System;
using System.Collections.Generic;
using System.Text;
using GetCustomerListConsoleApp.GPWS;

namespace GetCustomerListConsoleApp
{
  class Program
  {
  static void Main(string[] args)
    {
      CompanyKey companyKey;
      Context context;

      // Create an instance of the web service
      Console.WriteLine("Initializing Web Service for Dynamics GP 2010...");
      DynamicsGPClient wsDynamicsGP = new DynamicsGPClient();

      // Create a context with which to call the web service
      context = new Context();

      // Specify which company to use (sample company)
      companyKey = new CompanyKey();
      companyKey.Id = -1;

      // Set up the context
      context.OrganizationKey = (OrganizationKey)companyKey;
      context.CultureName = "en-US";

      // Create variables to store the last customer ID and Name returned by the GetCustomerList method.
      string sLastCustomerNumber = "";
      string sLastCustomerName = "";

      // When results are returned by method call, this variable will be set to 1
      int iResultsExist = 0;

      // Create a CustomerCriteria object
      CustomerCriteria customerCriteria = new CustomerCriteria();

      // Create a LikeRestrictionOfstring for Customer Name that starts with a C
      LikeRestrictionOfstring customerNameCriteria = new LikeRestrictionOfstring();
      customerNameCriteria.Like = "C%";

      // Create a LikeRestriction for Customer ID used later
      LikeRestrictionOfstring customerIDCriteria = new LikeRestrictionOfstring();

      // Create CustomerSummary object
      CustomerSummary[] customerSummaryList;

      try
      {
        // Set Customer Name Criteria for first GetCustomerList call
        customerCriteria.Name = customerNameCriteria;

        // Retrieve the list of customer summaries
        customerSummaryList = wsDynamicsGP.GetCustomerList(customerCriteria, context);

        // Create a for loop.
        int iCustomerCount = 0;

        // Create a counter to track number of customers
        int TotalCustomerCount = 0;

        for (iCustomerCount = 0; iCustomerCount < customerSummaryList.Length; iCustomerCount++)
        {
          // Obtain the customer Id and name of last customer returned.
          sLastCustomerNumber = customerSummaryList[iCustomerCount].Key.Id;
          sLastCustomerName = customerSummaryList[iCustomerCount].Name;

          // Display the customer ID and Name
          Console.WriteLine("Customer Number: " + sLastCustomerNumber + " Customer Name: " + sLastCustomerName );
        }

        // Set the total counter
        TotalCustomerCount = iCustomerCount;

        // Check the iCustomerCount to see we need to call the GetCustomerList method again.
        if (iCustomerCount >= 1000)
        {
          // Set iResultsExist = 1 which indicates results are still being returned from the Web service method call
          iResultsExist = 1;
          // While iResultsExist = 1, call the GetCustomerList
          while (iResultsExist == 1)
          {
            /* Add Criteria restriction to return customers where the Customer ID is larger than the last Customer ID returned.*/
            customerIDCriteria.GreaterThan = sLastCustomerNumber;
            customerCriteria.Id = customerIDCriteria;

            //Call GetCustomerList to obtain the next 1000 customers
            customerSummaryList = wsDynamicsGP.GetCustomerList(customerCriteria, context);
            for (iCustomerCount = 0; iCustomerCount < customerSummaryList.Length; ++iCustomerCount)
            {
              //Obtain the Id of the last customer returned.
              sLastCustomerNumber = customerSummaryList[iCustomerCount].Key.Id;
              sLastCustomerName = customerSummaryList[iCustomerCount].Name;
              // Display the number of customers matching the criteria
              Console.WriteLine("Customer Number: " + sLastCustomerNumber + " Customer Name: " + sLastCustomerName);
            }
            // Add to the Total Customer Counter
            TotalCustomerCount = TotalCustomerCount + iCustomerCount;

            // Evaluate whether iCustomerCount is larger than or equal to 1000. If it is, set the iResultsExist and call the GetCustomerList method again.
            if (iCustomerCount >= 1000)
            {
              //Set iResultsExist = 1 and call GetCustomerList again.
              iResultsExist = 1;
            }
            else
            {
              //Set iResultsExist = 0 and exit the while statement
              iResultsExist = 0;
            }
          }
        }
        // Display the total number of Customer that match the criteria
        Console.WriteLine("Total Customer Count is " + TotalCustomerCount);
      }

      catch (Exception ex)
      {
        Console.WriteLine(ex.Message + ex.StackTrace);
      }
      Console.WriteLine("Press Enter to quit");
      Console.ReadLine();
    }
  }
}

// Copyright © Microsoft Corporation.  All Rights Reserved.
// This code released under the terms of the
// Microsoft Public License (MS-PL, <a href="http://opensource.org/licenses/ms-pl.html">http://opensource.org/licenses/ms-pl.html</a>.)

I hope this helps in providing a starting point to retrieving large data sets using Web Services for Dynamics GP 2010.

Chris

This article was originally posted on the Developing for Dynamics GP Blog and has been reposted on http://www.winthropdc.com/blog.

One thought on “Retrieve more than 1000 Records using Web Services for Microsoft Dynamics GP 2010

Please post feedback or comments

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.