When you consider customization on the database extension level to Microsoft Dynamics GP, you should think about core architecture Microsoft Dexterity or former Great Plains Software Dexterity. The specific is seen in such nuances as DEX_ROW_ID, for example
So, if you plan to hook up your custom tables in GP workstation interface, you should initially create (and design) you custom tables in GP Dexterity IDE. It this is not required, you can create your custom tables directly in SQL and then link them with GP tables via SQL scripts (in this case you will not be able to produce the link in dex)
Updating. If you plan to update GP tables from SQL stored procedures, please be very conservative and try to get help from professional GP Dex SQL developer. GP application business logic is complex and if you think you can update just one table without destroying internal links you may be too optimistic
Selecting. Select statement is OK, as it doesnt update GP tables records
Placing custom tables into DYNAMICS or Company db. This is allowed, if you come from MS CRM world, where all custom objects should be placed into custom database please, know that in the case of GP you can place your custom objects into both system and entity DB
Integrations Design. This is the most popular GP SQL scripting direction integration with legacy databases, Microsoft Retail Management System (MS RMS), integration with heterogeneous database platforms, such as Oracle, Pervasive SQL, Ctree/Faircom, Sybase. When you develop integration, you should try to imitate GP table distribution records and if you integration logic goes beyond simple imitation you should consider getting the help from GP developer who has access to DYNAMICS.DIC with source code (sanscript dex code not stripped out)
eOne Extender. If you plan to deploy the Enterprise version, purchased directly from eOne, you should consider reviewing its table structure and how you can address new tables in SQL scripting
Alba Spectrum Group http://www.albaspectrum.comhelp@albaspectrum.com 1-866-528-0577, 1-630-961-5918, serving GP development clients in USA, Canada, Australia, New Zealand, Palau, Cayman Islands, Germany, France. Local service is available in Houston and Dallas area: Katy, Richmond, Sugar Land, Rosenberg, Galveston; Chicago: Naperville, Aurora, Plainfield, Bolingbrook, Romeoville, Joliet, DeKalb, Dixon, Rochelle, Romeoville, Downers Grove, Lisle, Wheaton, Glen Ellyn, Batavia, Elgin Geneva, Rockford. With reasonable travel we can serve you in Ottawa, LaSalle, Seneca, Peoria, Vandalia, Springfield, Sterling, Rock Falls, Galena, Normal, Bloomington, St. Luis, MO.
