Armanino Blog

Mass Modify and Account Description

November 04, 2008

The mass modify tool in Dynamics GP is a good way to add a new set of GL accounts for a particular segment of your chart of accounts. For example, you could copy a set of accounts for one location and add a new location with the appropriate account number. (I.E. 01-4100-00 can be copied and 02-4100-00 can be added). Mass modify can be found at Cards>>financial>>mass modify.

However, the accounts that were copied will have the same description as the older location. So if you identify the location in the description (phone expense - San Diego) the new accounts will have the old description. If you want to update the new description you can run a query in SQL to update the description. Below is a sample update statement:

UPDATE GL00100 set actdescr = replace(actdescr, 'San Diego', 'Los Angeles') where actnumbr_1 = '2' and actdescr like '%San Diego%'

In non technical terms this query says replace San Diego with Los Angeles where ever my new location (2) has the words San Diego in it. You will have to replace the bolded red fields with your information. My location was listed at the beginning of the GL account (actnumbr_1) but you can change that to be whatever segment you have updated.

I would recommend running a select statement on the GL00100 table first and verify you want to replace everything found in the results. Here's a sample query to do that.

Select * from GL00100 where actnumbr_1 = '2' and actdescr like '%San Diego%'

Once again replace the bolded red fields with your information.

Doing the above procedure saved a client from having to update around 100 accounts manually.

Can you think of any other find and replace uses for SQL?

Stay In Touch

Sign up to stay up-to-date with the latest accounting regulations, best practices, industry news and technology insights to run your business.

Related News & Insights
General Contractor Trends to Consider in 2022 Webinar
Hear from experts how you can better manage your subcontracts — and more.

December 16, 2021 | 10:00 AM - 11:00 AM PT
Women in Nonprofit Leadership Panel
Aspiring nonprofit leaders should hear the insightful stories of these women industry leaders to empower their journeys.

December 14, 2021 | 10:00 AM - 11:00 AM PT
The Evolution of Gaming Companies
From seed-funding to acquisition, position your gaming company for success.

December 9, 2021 | 01:00 PM - 02:00 PM PT