Mass Modify and Account Description

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?