Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / SQL script to mas upda...
Microsoft Dynamics GP (Archived)

SQL script to mas update all customers to have credit limit set to a specific amount

(0) ShareShare
ReportReport
Posted on by Clayton Ayers 115

I am not sure if anyone can help me with this, but I am trying to write a SQL script to update all customers in GP to have a Credit Limit set to an amount = $999,999,999,999.99. Then clicking on the blue arrow next to the Credit Limit amount, I would also like my script to set OR statement to : "Or If The Sum Of 31 - 60 Days and Beyond Exceeds: $1. 

I am trying to update hundreds of customers so going through each customer card is not really an option. I have tried reading through the DexSQL.log file and none of the tables it goes through have any data referring to the Credit Limit settings. I have also looked through the RM tables and many of the SY tables. 

If anyone can help me with this or where I should look please let me know, I am just spinning my wheels. 

*This post is locked for comments

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,730 on at
    RE: SQL script to mas update all customers to have credit limit set to a specific amount

    You could do that with one caveat. If anyone ever went to the customer class ID and change the credit limit type and selected to roll down the change it would overwrite this change. This field is used at the time the AR or SOP document is entered. So nothing historically will change nor anything in a work or open state.

  • Clayton Ayers Profile Picture
    Clayton Ayers 115 on at
    RE: SQL script to mas update all customers to have credit limit set to a specific amount

    Our client has customers with a variety of credit limit types but wants to display the error message that a customer is past due for all customers. Could I just include an update statement in my sql script to change all credit limit types for all customers to credit limit type of 2? Unless credit limit type is referenced in more tables than just RM00101.

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,730 on at
    RE: SQL script to mas update all customers to have credit limit set to a specific amount

    If you have a customer class which are only those with a credit limit this would work. If not, you could write a script to change only those with a credit limit to a certain customer class and use this class to roll down these changes. Let us know if you were able to accomplish this task.

  • Tami Farrelly Profile Picture
    Tami Farrelly 5,080 on at
    RE: SQL script to mas update all customers to have credit limit set to a specific amount

    Update the Customer Class and roll it down through the Customers?

  • Verified answer
    Richard Wheeler Profile Picture
    Richard Wheeler 75,730 on at
    RE: SQL script to mas update all customers to have credit limit set to a specific amount

    RM00101.CRLMAMNT is the credit limit amount. CRLMTPER  is the 'or if the sum of' and CRLMTPAM is the 'beyond exceeds amount'. You will need to convert the drop down values.

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