It seems that Microsoft Great Plains is becoming more and more popular, partly because of the Microsoft muscles behind it. It is now aimed at the entire spectrum of horizontal and vertical market customers. Small businesses use Small Business Manager (which is based on the same technology: Great Plains Dexterity dictionary and runtime), Great Plains Standard on MSDE is for small and medium customers, and then Great Plains serves the rest of the market until the big corporations. There are several reporting tools available and you definitely need to know which one to use for the different types of reports.

If you’re the database administrator asked to import some data into Great Plains or to repair or copy data from one company to another, read this and you’ll have clues as to where to look further.

1. Microsoft Great Plains Table Structure: Launch Great Plains and go to Tools->Resource Description->Tables. Find the table in the proper series. If you are looking for customers, it should be RM00101, customer master file.

2.DEX_ROW_ID. This is the identity column and every table in Great Plains has it; this is due to Great Plains Dexterity technology. This column is never used as a key field, so don’t try to link your tables on DEX_ROW_ID. In case you need to transfer the table from one company to another, you should use these queries:

Select * in GL00100_BAK from TWO.dbo.GL00100
go
alter table GL00100_BAK drop column DEX_ROW_ID
go
insert into GL00100 select * from GL00100_BAK
go
drop-down table GL00100_BAK

The above query set will transfer GL00100 (account master table) from TWO companies to your current company. Then you need to run Checklinks (see the GP Manual) to recreate the rest of the account master related tables.

3. Do not modify the table; sometimes it looks good if you just add a couple of extra columns to the table, like in IV00101, inventory master file, why not add a couple of extra descriptions? If you do this, the Great Plains Dexterity engine will not be able to read all of your elements, due to the fact that DYNAMICS.DIC (Great Plains dictionary main file) has an exact description of all tables and is used by Dexterity to read and write to the specific table.

4. Feel free to create SQL views or stored procs. If you’re helping your internal developers build Crystal Reports, good SQL views are a big help to them. Let me give you an example, the view below will show the work and historical SOP invoices; then Crystal can use it.

Create SOP_WORK_HIST View
ace
select SOPNUMBE, CUSTNMBR, CUSTNAME, DOCAMNT from SOP30200 where SOPTYPE=3 and VOIDSTTS=0
Union
select SOPNUMBE, CUSTNMBR, CUSTNAME, DOCAMNT from SOP10100 where SOPTYPE=3 and VOIDSTTS=0

The above view will show all work and historical invoices not voided (SOP Type = 3 stays per invoice)

5. Some repair/unlock tips:

If you run the above query against the DYNAMICS database, it will unlock the user, who accidentally shut down the computer without logging out of Great Plains:

delete ACTIVITY where USERID=’JOHN’

The following will unlock the pendant lot:

update SY00500 set BCHSTTUS = 0 where BACHNUMB = ‘JULYINVOICES04’

Happy consulting! If you want us to do the job, give us a call. 1-866-528-0577! [email protected]