The April 2015 Collections Management release included a new feature for building advanced, custom SQL queries. The Collections Management Build Query window is used to create lists of customers that meet specific user-defined criteria. Query results are then saved by a user and each collector can run his or her own queries. Saved customer groups can be used to create call down lists, bulk process actions, etc. Prior to our April 2015 release, customers were limited to a define set of fields and options.
Above is a screen shot of the Build Query feature, as seen in the latest build of Collections Management.
How did the concept of ‘Advanced Queries’ come about?
Our Development Team was frequently asked to add new fields and options to this window based on a plethora of different requirements. While a few fields and options have been added to the window over time, there will always be new criterion customers need to sort by. We’ve found that customers who have these requests typically know exactly what kind of query they want and where the data is.
So, why do customers want this?
Users want to find customers with complex requirements. Example: Find only customers who have a specific value in a SOP user defined field, or invoices that have a specific item or type of item. With the Standard Collection Query, criteria are bound together by “And” logic (i.e. for a customer to be included, they must pass all the criteria).
An example of the Standard Query functionality:
Customers must have a balance over $100.00 AND have not made a payment in 30 days.
However, a user may want to find:
Customers with a balance over $100 OR have any balance with no payments made in 30 days.
With the Standard Query option, you would have to build at least two separate queries, then somehow manage the overlap manually so the same customer doesn’t get contacted twice. However, with SQL, the options are almost endless of how this can be used.
How do ‘Advanced Queries’ work?
This incredible power and flexibility of Advanced Queries (and SQL queries) isn’t for everyone. It takes knowledge of Transact SQL and the Dynamics GP data model to build the query properly. We recommend you use the template provided on the Collections Management Advanced Query window as a starting point in SQL Server Management Studio to refine the query. Once you have it working in SQL Server Management Studio, paste it into the Collections Query window. Since query results are stored per user, there will be some minor editing needed so the current user is set properly when the query is executed. Important Note: Not all customers may have this level of SQL knowledge in house. Please consult your Microsoft VAR for assistance.
Whether you are using Advanced Queries to sort on comprehensive debt collection criteria or customers who have a sales quote expiring in 30 days, take full advantage of this new feature.
