Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Collection Management ...
Microsoft Dynamics GP (Archived)

Collection Management Query Letters

(0) ShareShare
ReportReport
Posted on by GrapeFruit 1,240

Hi All,

I am trying to optimize the process of running the query letters in our system. I have a few thoughts but not sure if they are possible:

1. is it possible to automate this process and ideally make it happen during out of the business hours since it slows down our system when it runs.

I thought about using a macro to do it during the night, but the problem is there are a few of the letters need to be run and they need to be run on the same day.

2. is it possible to include mutiple individual customer class IDs instead of having them as a range in the Collection Management Build Query Window? If so, we could a least reduce the number of query letters needed to be run.

Thanks.

*This post is locked for comments

  • L Vail Profile Picture
    L Vail 65,271 on at
    Re: Re: Re: Re: Re: Collection Management Query Letters

    Hi SLIU,

    It's possible I guess, but I would still run a profile and Dex SQl log to see what was going on. However, I know that once it works we forge ahead to the next month's business!

    Kind regards,

    Leslie

  • GrapeFruit Profile Picture
    GrapeFruit 1,240 on at
    Re: Re: Re: Re: Collection Management Query Letters

    Tried to run it over night and it worked. Is it because it went into some kind of loop when the customers' collection data got changed during the day in the first test run?

    Thanks for your help Leslie.

  • L Vail Profile Picture
    L Vail 65,271 on at
    Re: Re: Re: Collection Management Query Letters

    Gracious, it took two days to run? How many records are you processing? This might be an infrastructure issue as well.

    The only light I can bring to this is that the CN00100 table is the Collections - Notes table. The zDP_CN00100N procedure is the table operation for Next record.

    If you include the line ScriptDebugger=TRUE in your Dex.ini file and then run the Debug>>Profile Scripts log, what action is taking so long?

    Kind regards,

    Leslie

  • GrapeFruit Profile Picture
    GrapeFruit 1,240 on at
    Re: Re: Collection Management Query Letters

    Tried to run the new query letter 2 days ago. But it still hasnt finished until now. Below are a few lines of queries that were running according to SQL profiler:

     exec ALH.dbo.zDP_CN00100N_7 25,'               ','2006.04.24','11:09:15',104667.00000,13411,'','9999.12.31','23:59:59',-99999999999999.99999,'','1900.01.01','00:00:00',99999999999999.99999
    go
    exec ALH.dbo.zDP_CN00100N_7 25,'               ','2006.04.21','14:42:50',104637.00000,24080,'','9999.12.31','23:59:59',-99999999999999.99999,'','1900.01.01','00:00:00',99999999999999.99999
    go
    exec ALH.dbo.zDP_CN00100N_7 25,'               ','2006.04.20','14:41:25',104599.00000,41946,'','9999.12.31','23:59:59',-99999999999999.99999,'','1900.01.01','00:00:00',99999999999999.99999
    go
    Any ideas?

  • GrapeFruit Profile Picture
    GrapeFruit 1,240 on at
    Re: Re: Collection Management Query Letters

    yes, but the letter needs to be printed before running next one right? So the problem is how the macro can tell if the previous one is finished or not?

  • Sunbeam De Jesus Profile Picture
    Sunbeam De Jesus 3,930 on at
    Re: Collection Management Query Letters

    Q1. I am not sure if I understand it right. I think its possible to create 1 macro in each 'few letters'.

  • GrapeFruit Profile Picture
    GrapeFruit 1,240 on at
    Re: Re: Collection Management Query Letters
    Brilliant. Thank you Leslie for your help AGAIN :-)
  • L Vail Profile Picture
    L Vail 65,271 on at
    Re: Collection Management Query Letters

    With regard to question 2.

    While you cannot put more than one Class ID range in the query, you can use the User Defined 1 field to limit your query. You could put some designation in the User Defined 1 field to use as a customer group and then use that to effectively get customers from many classes (not in the class range) included in the query.

    I have been very pleased with this method. I can move customers from category to category whenever I want.

    Kind regards,

    Leslie

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,235 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans