Dynamics GP Customer Email Information

Published: 09th May 2011
Views: N/A

If you are programming integration to this Corporate ERP application as developer you may pose legitimate question about where customer email record is stored. If you know how access table structure via user interface, the first natural way is to guess that email related fields should be on one of the tables in the customer master table cluster in the company database, probably associated with individual address. However architecture of this application found another way of storing internet related information. Let’s review the details:

1. Emails are stored in one table, serving various types of the master objects: vendors, employee and customers are one of the examples. This is why it is not associated with customer master cluster and rather categorized as Company level table. Table ID is SY01200 and its name – Internet Addresses , it is located in the company database (not in system database Dynamics). First field is Master Type, where it is either customer, employee, vendor or another master record type. As email and other internet related info is associated with Address record, this is the third field from the top. Email is typically mapped to INet1 field. In GP 2010 there are several new fields added for explicit Mail To, CC To, BCC To, as customer SOP and RM documents could be emailed directly from the user interface

2. Could we automatically update all the customer addresses emails? If you think that you would like to add these records via SQL Insert statement instead of doing it in Integration Manager, we recommend you just insert one address record with email and other web related fields, see how it was recorded in SY01200, and then design your Insert statement by imitation

3. Are Internet Fields available for earlier versions? Yes, to our knowledge it was introduced maybe ten or even more years ago, when internet and emailing was catching up. We explored the situation with versions 2010/11.0, 10.0, 9.0, 8.0, 7.5. In version 7.5 SY01200 table is available also on Pervasive SQL 2000/Btrieve and Ctree versions. We believe that this table is also available for versions 7.0, 6.0, 5.5, 5.0 and maybe for 4.0

4. How do we access SY01200 table on Pervasive SQL 2000 or Ctree database platforms? Pervasive SQL and its predecessor Btrieve as well as Ctree have ODBC drivers (you can install the driver from CD #2, or #1 depending on the version). Btrieve/Pervasive requires so-called DDF files (Data Definition File), which could be generated by dragging and dropping GenDDF.Set file on Dynamics.exe. This procedure should generate Field.DDF, Index.DDF and File.DDF. If you are not a programmer you can use Microsoft Access to connect to Great Plains tables (use ODBC linked tables, see documentation for your version of MS Access). If you are software developer, consider ADO or ADO.Net to create OLEDB or direct ODBC connection via System DSN ODBC. Known tricks – you should copy DDF files to your company database folder

5. Table Structure description via user interface. If you are software coder it might be beneficial for you to have direct access to table descriptions. Please, open Great Plains user workstation, then in menu Dynamics GP -> Tools -> Resource Descriptions -> Tables. Now let us give you navigation hints. If you are looking for SY01200 table, pick product Dynamics GP (or Great Plains for older versions), switch series to Company, sort by table physical name (not required, we just use it in our example) and scroll down to locate table SY01200

6. Great Plains DOS version. As far as we know internet information is not supported in this archaic version of GP. Consider upgrading all the way to Dynamics GP 2010/11.0. GPA for DOS, Windows and Mac was available for purchasing and implementation in earlier 1990th and it was hosted in Btrieve database. You could migrate your GPA data to 32 bit Windows 2008 or 2003 and your can mount it on Pervasive SQL 2000 Server. We do not recommend any customizations for Great Plains Accounting, just keep your version alive, as you have to battle various technical and technology related issues, such as incompatibility with Windows 64 bit

7. Small Business Financials 9.0, 8.0, 7.5, or 7.0 (for versions 7.5 and 7.0 this small business ERP was known as Small Business Manager). Emaling fields should be available for you as SBF is stripped down version of Great Plains (where certain functionality was disabled via Microsoft Dexterity in Dynamics.Dic). We recommend you migrate your SBF to Dynamics GP 2010/11.0 Business Ready Business Essential licensing

8. Please call us 1-866-304-3265, 1-269-605-4904. help@efaru.com We have local presence in Chicagoland, Southern California, West Michigan, Houston and Dallas areas of Texas. We serve customers USA, Canada, Mexico nationwide and internationally via web sessions and phone conferences (Skype is welcomed). Our consultants speak English, Spanish, Portuguese, Russian, Chinese

Andrew Karasev is Great Plains Certified Master, MVP, help@efaru.com 1-866-304-3265, 1-269-605-4904. He is also the initiator of eFaru project http://www.efaru.com and founder of Alba Spectrum information space

Report this article Ask About This Article

More to Explore