Software informational articles

Sql: querying microsoft great plains ? overview for folder administrator/developer - software

 

Looks like Microsoft Great Plains becomes more and more popular, in part as of Microsoft muscles at the back it. Now it is embattled to the whole spectrum of horizontal and vertical marketplace clientele. Small companies use Small Commerce Boss (which is based on the same knowledge - Great Plains Ability thesaurus and runtime), Great Plains Average on MSDE is for small to midsize clients, and then Great Plains serves the rest of the marketplace up to big corporations. There are quite a few coverage tools existing and you certainly need to know which one to use for another types of reports.

If you are folder commissioner who is asked to import some data to Great Plains or darning or copy data from one business to a further - read this and you will have the clues on where to look further.

1. Microsoft Great Plains Tables Structure - Launch Great Plains and go to Tools->Resource Description->Tables. Find the table in the apposite series. If you are looking for the customers - it ought to be RM00101 - patron master file.

2. DEX_ROW_ID. This is character article and each Great Plains table has it - this is due to the Great Plains Agility technology. This discourse is never used as a key field - so don't try to link your tables on DEX_ROW_ID. In case if you need to assign the table from one ballet company to an added you ought to use these queries:

select * into GL00100_BAK from TWO. dbo. GL00100
go
alter table GL00100_BAK drop article DEX_ROW_ID
go
insert into GL00100 decide on * from GL00100_BAK
go
drop table GL00100_BAK

The set of queries above will assigning GL00100 (Account Master table) from TWO business into your flow company. Then you need to run Checklinks - refer to GP Blue-collar - in order to reconstruct the rest of the balance master associated tables.

3. Do not alter the table - at times it seems to be nice if you just append duo of extra columns to the table - like in IV00101 - account master file why wouldn't you just add duo of extra descriptions. If you do this - Great Plains Adroitness engine will fail appraisal all your items - due to the fact that DYNAMICS. DIC (main Great Plains Thesaurus file) has exact class of all the tables and Agility uses it for conception and copy into the detail table

4. Feel free to conceive SQL views or stored procs. If you are portion your inner developers to coin Gemstone Gossip - good SQL views are real help to them. Let me give you example, the view below will show work and chronological SOP Invoices - then Precious stone can just use it

create view SOP_WORK_HIST
as
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 the work and past non-voided invoices (SOP Type = 3 stays for invoice)

5. Some darning / unlocking tips:

If you run query above anti DYNAMICS catalog - it will unlock the user, who by accident shut down the cpu exclusive of taking down off Great Plains:

delete Commotion where USERID='JOHN'

Next one will unlock execution batch:

update SY00500 set BCHSTTUS=0 where BACHNUMB = 'JULYINVOICES04'

Happy querying! if you want us to do the job - give us a call 1-866-528-0577! help@albaspectrum. com

Andrew Karasev is Chief Know-how Bureaucrat in Alba Spectrum Technologies - USA all over the country Microsoft CRM, Microsoft Great Plains implementation and customization company, based in Chicago, Boston, New York, San Francisco, Los Angeles, San Diego, Phoenix, Houston, Dallas, Atlanta, and Miami and having locations in numerous states and worldwide (www. albaspectrum. com), he is Dexterity, SQL, VB/C#. Net, Gem Gossip and Microsoft CRM SDK developer.


MORE RESOURCES:
Developed by:
home | site map
goldenarticles.net © 2018