Change Acct Description using SQL 3

[barbola]

I would like to update our account descriptions. When we mass copy from one division to another, the account description in the new division has the old division name.

IE. SALES - DIVISION ONE
But I want the new account to be SALES - DIVISION TWO.

Can I use a SQL statement to update GL00100 and then run checklinks on the Financial tables or can anyone tell me if this will make a mess of history?


Thanks!
Barb E.

 

[winthropdc]

Barb

You should be able to update the Account Description (ACTDESCR) field in the GL_Account_MSTR (GL00100) with SQL without any issues. It is the Account Index and sometimes the Account Number field with is used in relationships to other tables.

Running Checklinks (after a backup) can find issues, but probably will not be necessary for this change.

David Musgrave [MSFT]
Senior Development Consultant
Escalation Engineer
MBS Support - Asia Pacific

Microsoft Business Solutions

http://www.microsoft.com/BusinessSolutions

Any views contained within are my personal views and
not necessarily Microsoft Business Solutions policy.
This posting is provided "AS IS" with no warranties,
and confers no rights.

 

[klewis10367]

To add another point...if your are using FRx Report writer you will probably need to rebuild your index and might want to check the frl_acct_code table also in the company that you changed the info in.

 

[barbola]

Thanks for your valuable information. I will make sure I have a good backup before attempting this.

Thanks!
Barb E.

 

[barbola]

I went into the Test database to try this. Does anyone know the SQL to search and replace part of a field?

I did a search on here under the SQL forum, but man, the code looks whacky and not sure it should be that complicated - but I've been wrong b4!



Thanks!
Barb E.

 

[jymm]

check out the REPLACE function in the BOL

it should look something like
UPDATE GL00100
SET ACTDESCR = replace(ACTDESCR,'JYMM','BARBOLA')

which should find all occurances of JYMM in the ACTDESCR field and put BARBOLA in it's place

of course I also always scan the database for the field to make sure I am getting each occurance of that field in each table... you need that code?

 

[jymm]

sorry - I was flying out of here on Friday - this sql will find a field name anywhere in the DB - I have found so many times that GP duplicates data (that I was not expecting), so this comes in handy

select * from information_schema.columns where table_catalog='specific_company_db_name' and column_name = 'field_you_are_looking_for'

so if I was running for Test & the item class
select * from information_schema.columns where table_catalog='TEST' and column_name = 'ITMCLSCD'

 

[barbola]

OK thanks. I did find out how to do it using replace. So your 'answer on the fly' from Friday was great.

thanks.

Thanks!
Barb E.