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
Sage Transform VIP Experience
Live Event
VIP luncheon for top finance executives attending Sage Intacct 2022.

October 12, 2022 | 09:30 AM - 10:45 AM PT
Women in Life Sciences: Women’s Health & Wellness
Spill the Tea With Delphine O’Rourke on the State of Women’s Health

October 11, 2022 | 02:00 PM - 03:30 PM PT
ASC 842: Lessons Learned From Early Adopters
Get the scoop from early adopters who have implemented ASC 842.

October 6, 2022 | 09:00 AM - 10:00 AM PT