Query not pulling in all customers with balances in Collection Management using Microsoft Dynamics GP


If you build a query in Collections Management, you may notice that all customers with overdue balances are not pulling into the query. Even if you filter only on Customer ID, the query still does not pull in a particular customer that has an overdue balance.


The "Statement To" Address ID is blank on the Customer Maintenance card in Receivables Management. 


Fill in the "Statement To" Address ID on the Customer Maintenance card and then the customer will pull into the query in Collections Management. To do this, follow these steps:

1. On the Cards menu, point to Sales, and click on Customer.

2. Select an Address ID for the Statement To field.

3. Save.

4. Test the query again and now this customer will pull in. To build a query, click on Transactions, point to Sales and click on Build Query. Select the query filters as desired, and click on Query.

More Information

In SQL, the "Statement To" Address ID is stored in the STADDRCD field in the RM Customer MSTR table (RM00101). You can run the below script against the company database in a query window in SQL Server Management Studio to view all the active customers that are missing an address ID in this field:

select * from RM00101 where STADDRCD = '' and INACTIVE = 0 

After identifying the active customers that have a blank Statement To Address ID, then use the steps in the Resolution section above to select an appropriate Address Id in the Statement To field for each customer. 

Need more help?

Expand your skills
Explore Training
Get new features first
Join Microsoft Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.