Software Information |
SQL: Querying Microsoft Great Plains - Overview for Database Administrator/Developer
Looks like Microsoft Great Plains becomes more and more popular, partly because of Microsoft muscles behind it. Now it is targeted to the whole spectrum of horizontal and vertical market clientele. Small companies 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 to midsize clients, and then Great Plains serves the rest of the market up to big corporations. There are several reporting tools available and you definitely need to know which one to use for different types of reports. If you are database administrator who is asked to import some data to Great Plains or repair or copy data from one company to another - 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 proper series. If you are looking for the customers - it should be RM00101 - customer master file. 2. DEX_ROW_ID. This is identity column and each Great Plains table has it - this is due to the 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 if you need to transfer the table from one company to another you should use these queries: select * into GL00100_BAK from TWO.dbo.GL00100 The set of queries above will transfer GL00100 (Account Master table) from TWO company into your current company. Then you need to run Checklinks - refer to GP Manual - in order to recreate the rest of the account master related tables. 3. Do not modify the table - sometimes it seems to be nice if you just append couple of extra columns to the table - like in IV00101 - inventory master file why wouldn't you just add couple of additional descriptions. If you do this - Great Plains Dexterity engine will fail reading all your items - due to the fact that DYNAMICS.DIC (main Great Plains Dictionary file) has exact description of all the tables and Dexterity uses it for reading and writing into the specific table 4. Feel free to create SQL views or stored procs. If you are helping your internal developers to create Crystal Reports - good SQL views are real help to them. Let me give you example, the view below will show work and historical SOP Invoices - then Crystal can just use it create view SOP_WORK_HIST The above view will show all the work and historical non-voided invoices (SOP Type = 3 stays for invoice) 5. Some repair / unlocking tips: If you run query above against DYNAMICS database - it will unlock the user, who accidentally shut down the computer without logging off Great Plains: delete ACTIVITY where USERID='JOHN' Next one will unlock hanging 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 Technology Officer in Alba Spectrum Technologies - USA nationwide 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 multiple states and internationally (www.albaspectrum.com), he is Dexterity, SQL, VB/C#.Net, Crystal Reports and Microsoft CRM SDK developer.
MORE RESOURCES: Unable to open RSS Feed $XMLfilename with error HTTP ERROR: 404, exiting |
RELATED ARTICLES
Microsoft CRM Integration With Microsoft Office Documents - Overview For System Integrator Microsoft CRM is CRM application, maintained and supported by Microsoft Business Solutions. Microsoft CRM utilizes majority of the Microsoft technologies: Windows Active Directory, SQL Server, MS Exchange, BizTalk (integration with MS Great Plains, and soon with Navision), Message Queuing, . MultiNational Corporation ERP Implementation - Microsoft Business Solutions Great Plains If you look back to the history, you will see that ERP for large publicly traded company had to be built upon very reliable hardware (more likely non-Intel hardware: mainframe, Sun sparc, etc.), powerful database platform: Oracle, DB2, Ingress, Sybase and reliable OS: UNIX. Instant Messenger Clients If you have been using the Internet for any amount of time you have probably used an instant messenger at some time or another. They allow you to talk to your friends, family and co-workers with the click of a button. Microsoft Great Plains: Interest Calculation Example - Stored Procedure for Crystal Report This is intermediate level SQL scripting article for DB Administrator, Programmer, IT SpecialistOur and Microsoft Business Solutions goal here is to educate database administrator, programmer, software developer to enable them support Microsoft Great Plains for their companies. In our opinion self support is the goal of Microsoft to facilitate implementation of its products: Great Plains, Navision, Solomon, Microsoft CRM. A Simple Guide To Wikis A wiki is an editable text-based website. But you don't need to understand any complex HTML, or anything like that to use a wiki. Linux Vs. Windows This article will not attempt to advocate the use of Linux over Windows or vice versa. I will try to present the differences and similarities between Linux and Windows in a fair manner. Freight Forwarding ERP: Microsoft Business Solutions Great Plains customization & setup - overview Microsoft Great Plains may be recommended for international freight forwarding / transportation company. Great Plains has such features as multicurrency (Euro, Pound, Yuan, Yen, Peso, Real), Spanish, French, Brazilian Portuguese (and if needed Unicode-specific languages such as Chinese, Japanese). CROOK: A Methodology for the Refinement of Forward-Error Correction Table of Contents1) Introduction2) Related Work3) Framework4) Implementation5) Performance Results5.1) Hardware and Software Configuration5. Is Your Family Protected? When you think about Security, what do you think of? We all have one device or another to assist in keeping us safe. We have pad locks and security systems to safe guard our home, alarm systems to protect the equipment in our cars, parental controls on our cable TV and satellite accesses. Popular Fleet Maintenance Programs Some companies that are in need of fleet management may choose to utilize the services of a fleet maintenance program. These programs can generally handle all of you fleet management needs. Anti-Virus Software Anti-virus software is used to find, remove or fix files that are infected with computer viruses. Most anti-virus companies release weekly updates of their databases to help keep you safe. Microsoft Business Solutions VAR/Partner Selection - Overview for IT Director/Manager/Controller Microsoft Great Plains and Microsoft CRM become more and more popular, partly because of Microsoft muscles behind them. Now Great Plains is targeted to the whole spectrum of horizontal and vertical market clientele. Linux - Keyboard Or Mouse Just stress testing one of the latest Linux distributions. Been testing it for about two months. Is Your Small Business Ready For A CRM Software Solution? I have yet to see a business that, sometimes in spite of themselves, didn't benefit from implementing a Customer Relationship Management (CRM) or a simpler Contact Management software solution.Your small business might be the rare exception, but if you can relate to some of the following scenarios, you may be ready to take advantage of the improved and inexpensive CRM systems coming on-stream. 3 Reasons Why Medical Billing Software is Leading the Way Since technology changes so quickly, it is hard to begin by saying "in the old days . . Microsoft CRM Customization Microsoft CRM customization techniques are very diversified and based on the whole spectrum of recent Microsoft technologies. The main terms you should know are: Microsoft CRM SDK 1. Recent Studies Show that 9 out of 10 PCs Are Infected with Spyware Spyware and malware are large problems for Internet users today and can be both annoying and a real threat to you and your computer. There exist a number of different spy and malware of which some are relatively friendly and only spies on your surfing habits, while others like the Trojans can be used to corrupt your hard drive or steal your usernames and passwords. Know-how in Microsoft Publisher For those who still don't know, Microsoft Publisher helps computer users to easily create, customize and publish materials such as flyers, brochures, catalogs, newsletters and websites. It provides tools to create professional and compelling marketing materials that will help you take your business further. Microsoft CRM: Implementation, Customization, Support - Consultant Overview Microsoft Business Solutions CRM is present several years on the US software market plus it makes advances in Europe. We expect substantial number growth of Microsoft CRM implementations across USA. Tools for Customizing Great Plains Microsoft Business Solutions - Great Plains has captured the US market with its integrated business applications for small and mid-size organizations. Its comprehensive accounting and business management capabilities provide businesses with tools to customize various modules of the Great Plains software. |
home | site map | contact us |