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

| 10/29/2010 | 0 Comments

Chris Roehrich - Click for blog homepageA 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 = customerSummaryListiCustomerCount.Key.Id;
                    sLastCustomerName = customerSummaryListiCustomerCount.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 = customerSummaryListiCustomerCount.Key.Id;
                            sLastCustomerName = customerSummaryListiCustomerCount.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, http://opensource.org/licenses/ms-pl.html.)
 

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

Chris

Follow this link:
Retrieve more than 1000 Records using Web Services for Microsoft Dynamics GP 2010

VN:F [1.9.22_1171]
Rating: 10.0/10 (1 vote cast)
VN:F [1.9.22_1171]
Rating: +1 (from 1 vote)
Retrieve more than 1000 Records using Web Services for Microsoft Dynamics GP 2010, 10.0 out of 10 based on 1 rating

Tags: , , , , , , , , , , , , ,

Category: Blogs I Follow

About the Author ()