Retrieve more than 1000 Records from Microsoft Dynamics GP customer

Here is the trick for MS Dynamics GP programming. The first thing, there is no any paging facility provide in Microsoft GP. And Microsoft GP API calls return 1000 record, but what if you want to load all the data. Let’s we take one example for deeply understand.

 

Suppose,
If you have 10,000 customer records in the RM00101 table,
you will only receive the first 1000 of them when you call the GetCustomerList method with no criteria,
now, what to do if you want to receive remaining data.

 

Here is the sample C# code for Customer List which helps you to get data more than 1000 in dynamics GP.

bool HasRecordGraterThen1000 = true;
bool AddCustomeridCriteria = false;

// Create variables to store the last customer ID and Name returned by the GetCustomerList method.
string strLastCustomerNumber = “”;
string strLastCustomerName = “”;
CustomerCriteria customerCriteria;

while (HasRecordGraterThen1000)
{
HasRecordGraterThen1000 = false;
// Create the bank criteria object
customerCriteria = new CustomerCriteria();
if (AddCustomeridCriteria)
{
AddCustomeridCriteria = false;
customerIDCriteria.GreaterThan = strLastCustomerNumber;
customerCriteria.Id = customerIDCriteria;
}
var customerSummaryList = wsDynamicsGP.GetCustomerList(customerCriteria, context);
if (customerSummaryList.Count() >= 1000)
{
HasRecordGraterThen1000 = true;
}

//Here you can process the data
if (HasRecordGraterThen1000)
{
// Obtain the customer Id and name of last customer returned.
strLastCustomerNumber = customerSummaryList[customerSummaryList.Length – 1].Key.Id;
strLastCustomerName = customerSummaryList[customerSummaryList.Length – 1].Name;
AddCustomeridCriteria = true;
}
}

What does above code do ? Let’s understand the code

 

First of all,  there are variables declared with a default value. Now the while comes which will continue until it found HasRecordGraterThen1000 = true.

 

Now, within while we first set HasRecordGraterThen1000 to false, we assume that there is less then 1000 record.

 

For the first time, AddCustomeridCriteria is set to false so it will skip that if portion. Now API call will execute and will take data from Microsoft GP customer.

 

Next, if customerSummaryList has more than 1000 record we set variable HasRecordGraterThen1000 to true. as it more than 1000 now we set strLastCustomerNumber and AddCustomeridCriteria.

 

However, HasRecordGraterThen1000 is set to true it will execute while loop again and it will go in first if condition and set customerIDCriteria.
And as it goes further it will load next record and skip first 1000 record.

 

Finally, This method is usefull in Microsoft GP Item List / Product List wsDynamicsGP.GetItemList,
Microsoft GP Price List wsDynamicsGP.GetPricingList.

 

Note: This method will not work for Sales Order because by default sales order is order by Order No, and this order is not arranged in a proper manner because it saves all data in the same table.

 

I hope you liked my tiny help for .Net programming. Here is my other blog post for challenges in Microsoft GP.
By | 2017-07-03T16:45:56+00:00 January 20th, 2017|Asp.Net, Asp.Net MVC, Microsoft Dynamics GP|