SQL: Querying Microsoft Excellent Plains – Overview for Database Administrator/Developer

Seems to be like Microsoft Excellent Plains turns into more and extra common, partly since of Microsoft muscle mass powering it. Now it is specific to the total spectrum of horizontal and vertical current market clientele. Modest companies use Modest Enterprise Supervisor (which is dependent on the very same technologies – Excellent Plains Dexterity dictionary and runtime), Great Plains Standard on MSDE is for small to midsize customers, and then Fantastic Plains serves the relaxation of the sector up to significant corporations. There are quite a few reporting resources accessible and you definitely have to have to know which just one to use for various styles of reviews.

If you are databases administrator who is questioned to import some information to Good Plains or mend or copy info from one particular firm to yet another – read through this and you will have the clues on where to look even more.

1. Microsoft Great Plains Tables Framework – Start Wonderful Plains and go to Instruments->Resource Description->Tables. Discover the desk in the good sequence. If you are seeking for the shoppers – it should really be RM00101 – purchaser grasp file.

2. DEX_ROW_ID. This is id column and every single Excellent Plains table has it – this is thanks to the Fantastic Plains Dexterity engineering. This column is in no way applied as a critical subject – so never consider to website link your tables on DEX_ROW_ID. In circumstance if you have to have to transfer the table from one enterprise to one more you need to use these queries:

Decide on * into GL00100_BAK from TWO.dbo.GL00100
go
change table GL00100_BAK drop column DEX_ROW_ID
go
insert into GL00100 select * from GL00100_BAK
go
fall table GL00100_BAK

The set of queries above will transfer GL00100 (Account Master table) from TWO firm into your existing business. Then you require to operate Checklinks – refer to GP Guide – in get to recreate the rest of the account learn related tables.

3. Do not modify the table – at times it appears to be to be good if you just append pair of extra columns to the desk – like in IV00101 – inventory grasp file why would not you just add couple of additional descriptions. If you do this – Great Plains Dexterity engine will fall short examining all your things – because of to the fact that DYNAMICS.DIC (principal Good Plains Dictionary file) has actual description of all the tables and Dexterity makes use of it for studying and creating into the distinct table

4. Sense totally free to create SQL sights or stored procs. If you are serving to your internal builders to generate Crystal Reviews – great SQL views are real assistance to them. Let me give you instance, the watch underneath will display function and historical SOP Invoices – then Crystal can just use it

Generate view SOP_Perform_HIST
as
find SOPNUMBE, CUSTNMBR, CUSTNAME, DOCAMNT from SOP30200 the place SOPTYPE=3 and VOIDSTTS=
union
pick out SOPNUMBE, CUSTNMBR, CUSTNAME, DOCAMNT from SOP10100 in which SOPTYPE=3 and VOIDSTTS=

The previously mentioned perspective will demonstrate all the operate and historical non-voided invoices (SOP Kind = 3 stays for invoice)

5. Some repair / unlocking strategies:

If you run question higher than in opposition to DYNAMICS database – it will unlock the consumer, who unintentionally shut down the laptop with no logging off Excellent Plains:

delete Action in which USERID=’JOHN’

Up coming a single will unlock hanging batch:

update SY00500 set BCHSTTUS= where BACHNUMB = ‘JULYINVOICES04’

Pleased querying! If you want us to do the job – give us a contact 1-866-528-0577! [email protected]