There Are Multiple Tables in GP Containing Email Setup and Use Information Let’s Review Them:
Emailing from within Dynamics GP is a game changer and can add a level of automation that is truly impressive. My previous posts have touched on how to set up email functionality and how to utilize email functionality in GP. In this post (Post # 3) we will discuss the table structure behind the setup and use of Emailing in GP.
Many of these tables refer to three small integer values which represent the various products, series’ and modules affected through email setup and use. For your reference, I have included a comprehensive listing of these values at the end of this post.
Let’s look at the main setup and use tables and what information they contain.
I will be avoiding using the physical names of columns to allow new users and non-technical users a better understanding and to allow for ease of reading. However, for a comprehensive listing of the physical and technical table names and columns you may do a search online. My favorite site for this information in Dyndeveloper.com, but there are many others.
SY04900 [Company Level] (Email Options):
This table contains information on how the emailing is setup for each company.
The information in this table includes:
1) Whether to allow the embedding of emailed documents into the message
2) Whether to allow attachments
3) Whether to allow for Docx files to be used and attached
4) Whether to allow for HTML files to be used and attached
5) Whether to allow PDF files to be used and attached
6) Whether to allow XPS files to be used and attached
SY04901 [Company Level] (Email Messages)
This table contains each message that has been created, the message type, the subject line and the body as well as who replies should be sent to, etc.
The information in this table includes:
1) Email Message ID: The message ID users have assigned to the message
2) Email Dictionary ID: Which “Product” it is looking at – for instance 0 is for Dynamics GP, 258 is for Project Accounting, 414 is Human Resources.
- See below for a comprehensive listing
3) Email Series ID:
- See below for a comprehensive listing
4) Email Document ID: A unique Identifier
5) Workflow Type Name: The type of workflow it is assigned to (if any)
6) Email Message Description: The description given to the Email Message ID
7) Email Message Subject
8) Email Message From
9) Email Message Type
- 1 is a standard message
- 2 is a work-flow type message
10) Email Reply To: This is the special reply to that can be used by this message instead of the default
11) Email Message Body
SY04902 [Company] (Email Series Setup)
This table contains information on how each series is setup for emailing . The information here will correspond to the Series setup windows located at Purchasing>Setup>Email Settings and Sales>Setup>Email Settings.
The information in the tables includes:
1) The Email Dictionary ID –
- See below for a comprehensive listing
2) The Email Series ID
- See below for a comprehensive listing
3) The Email_Series_GetMSG_ID: an identifier of the assigned message
4) Email Message From – The email address that the email is sent from
5) Email ACF
6) Email Allow Change Reply To
- 1 = Yes, allow changing the email address for replies at the sending of emails
- 0 = No, do not allow
7) Email Allow Update Entry
- 1 = Yes, allow changes to the “sent from” address at the sending of the email
- 2 = No, do not allow
8) Email Reply To Address from the setup of the Email Message
SY04903 [Company](Email Series Documents)
This table contains further information on the Series Setup. This is the listing of the documents associated to which document type in each series. For instance, if you open the Purchasing>Setup>Email Setup you will see two Document Types: One for Purchase Orders and one for EFT Remittance. The Sales Email Setup has even more Document Types.
The information in this table is as follows:
1) The Email Dictionary ID:
- See below for a comprehensive listing
2) The Email Series ID: This corresponds to the listing of Series ID’s given above
- See below for a comprehensive listing
3) The Module ID:
- See below for a comprehensive listing
4) Email Document ID:
- This number represents a unique identifier for the combination of Series ID and Module.
i. For instance, Series 3 (Sales) emails from Module 9 (Receivables Management) a total of 10 document types. Those fields have the unique identifiers of 1,2,3,4,5,6,7,8,10 to uniquely identify each line.
5) Email Document Enabled:
- You don’t have to enable the sending of any of the document types
i. 1 = Yes
ii. 0 = No
6) Email Message ID
- This is the message ID that corresponds to in table SY04901
SY04904 [Company](Email Card Setup)
Now we are getting granular – as we discussed in Post 1, the setup and use of emailing in GP is a pyramid type of setup. The bulk company-wide setup, then the series setup defaults and then finally at the bottom of the pyramid, each vendor or customer can be setup with individually customized email settings. This is the table that represents those settings. This table will have a listing of all of your customers or vendors whether or not you have setup email or not.
The information contained in this table includes:
1) The email dictionary ID
- This is the product ID from the listing below
2) The Series ID
- This is the series ID from the Series table below
3) The email Card ID: This is either the vendor ID or customer ID in Dynamics GP
- Used in conjunction with the Series ID
- For instance, if the series is 3 (Sales) then the email Card ID is a Customer.
4) Whether to allow embedding of the emailed document into the email or attach the document
5) Whether to allow the attaching of other documents
6) Whether to allow multiple attachments per email
7) The maximum file size for each vendor
8) Whether to allow a different email address based on the document type
SY04905[Company](Email Card Document Assignment)
This table is further information on how each vendor or customer is setup for emailing. This table is very similar to the above table SY04903. It contains all of the same information with the exception of the added Email Card ID (Vendor or Customer) column:
The information contained in this table is:
1) The Email Dictionary ID:
- See below for a comprehensive listing
2) The Email Series ID: This corresponds to the listing of Series ID’s given above
- See below for a comprehensive listing
3) The Module ID:
- See below for a comprehensive listing
4) The email Card ID: Vendor or Customer ID
5) Email Document ID:
- This number represents a unique identifier for the combination of Series ID and Module.
i. For instance, Series 3 (Sales) emails from Module 9 (Receivables Management) a total of 10 document types. Those fields have the unique identifiers of 1,2,3,4,5,6,7,8,10 to uniquely identify each line.
6) Email Document Enabled:
- You don’t have to enable the sending of any of the document types
i. 1 = Yes
ii. 0 = No
7) Email Message ID
- This is the message ID that corresponds to in table SY04901
8) Email Document Format:
- The format that the document will be sent in email as:
i. 1 = .DOCX
ii. 2 = .HTML
iii. 3 = .PDF
iv. 4 = .XPS
SY04906[Company](EmailCardAddress)
This table contains further information on the setup of emailing for individual customers or vendors. As discussed in prior posts, email addresses must be assigned to address ID in Dynamics GP and that address is to be used in the transactions that get emailed.
This table contains the link to those address IDs:
1) Email Dictionary ID:
- See below for a comprehensive listing of product IDs
2) Email Series ID
- See below for a comprehensive listing of series IDs
- Used to determine if the Email Card ID is a vendor or customer
3) Module ID
- See below for a comprehensive listing of module IDs
4) Email Card ID
- The Customer or Vendor ID
5) Email Document ID
- The document ID from above
6) Address Code ID
- Used in conjunction with the email card ID this is the Address ID from the vendor or Customer that contains the email information for this line item
7) Email Card Address
- The address on the email card
8) Email Recipient Type To: the “To” field for the email
9) Email Recipient Type Cc: the “Cc” field for the email
10) Email Recipient Type Bcc: the “Bcc” field for the email
SY04910[Company](Email Details)
Now it’s getting fun! This table contains the information on every document you have sent via email to a customer or vendor.
The information in this table includes:
1) The Module Number
- See below for a comprehensive Listing
- For instance, Module 19 (is Payables Management)
2) The Document Type
- Corresponds to the document type for the transaction from Purchasing or Sales
- For instance, For Module 19 (Payables Management) document type 6 means a payment
3) The Document Number The document number that was emailed
- This is the PO number, Invoice Number or payment number that was sent via email
- Problems can arise if the same document number is used in various modules
- A field other than document number should have been used here – like a Transaction Source that is original to the document
4) The Master ID: The vendor or customer ID
5) The Address Code ID: The vendor or customer address ID that was used to send the email
6) The email message ID that was used in the email sent to the vendor or customer
7) The email subject that is on the email message ID that was sent
8) The email Message From field related to this particular message
9) The Dictionary ID
- This corresponds to the table below with Product ID information
10) The email To Address: To whom at the customer or vendor was the email sent to
11) The email Cc Address: To whom was the email copied to
12) The email Bcc Address: To whom was the email blind compied to
13) The information in the email body
14) The email Reply to address associated to the message sent
SY04915[Company](Email History)
This table contains much the same information as the SY04910. However, this table gets even more granular as it contains the time stamps for every time the email was sent. For instance, if you emailed the same purchase order more than once you will see each time listed here and the date and time that it was sent:
1) The GP User ID who sent the email
2) The Module Number
- See below for a comprehensive Listing
- For instance, Module 19 (is Payables Management)
3) The Document Type
- Corresponds to the document type for the transaction from Purchasing or Sales
- For instance, For Module 19 (Payables Management) document type 6 means a payment
4) The Document Number The document number that was emailed
- This is the PO number, Invoice Number or payment number that was sent via email
- Problems can arise if the same document number is used in various modules
- A field other than document number should have been used here – like a Transaction Source that is original to the document
5) The date the email was sent
6) The time the email was sent (to the second)
7) What appeared in the “Email From” on the email
8) The email subject that is on the email message ID that was sent
9) The Master ID: The vendor or customer ID
10) The dictionary ID: See below for a comprehensive list of product IDs
11) A sequence Number
12) The email Message From field related to this message
13) The email message ID that was used in the email sent to the vendor or customer
14) The email address for replies
15) The email address that “sent” the email
16) The document date for the document sent
17) The email To Address: To whom at the customer or vendor was the email sent to
18) The email Cc Address: To whom was the email copied to
19) The email Bcc Address: To whom was the email blind copied to
20) The information in the email body
21) The email Reply to address associated to the message sent
SY04930[Company](Workflow Lines)
Some workflow emails can have line items associated with them – such as a timecard approval that could have the date, department, pay code, etc. to make approvals easier from email. This table contains the information in email message LINES
1) Email Message ID
2) The line order
3) The field name on the line
4) The Workflow type name
5) The email message type
6) A unique sequence number
CO00105 Table (coAttachEmail):
Though this table contains all of your documents that have attachment, it also contains whether that document can be sent by email. The CP00105 table contains the following:
1) The Series and Type of Window it was attached to
- Such as PM\Payables Transaction\0000000494
i. Meaning it comes from the Payables Module
ii. It is a Payables Transaction
iii. The last digits are the voucher number it is associated with
2) The Record Number it is attached to
- Master Records such as Vendor/Customer/Item number
- Transaction Numbers from any transactions done
- If you attached through the Notes functionality it will include the Note ID Record
3) The filename that was attached, including the file extension
4) The attachment descriptions (This is a field that can be altered In the Document Attachment Window
5) The system assigned Attachment ID
6) The date that you attached the file
7) The time of day that you attached the file
8) The file type (PDF, DOCX) etc
9) Whether or not the attachment is allowed and sent through email
I hope you have found this series useful for understanding setups, use and reporting on emailing in Dynamics GP.
Subscribe to my blog for even more useful knowledge in future posts!!
PRODUCT ID:
Product ID |
Product Name |
0 |
Dynamics |
1 |
Dexterity |
258 |
Project Accounting |
309 |
Fixed Assets |
346 |
Manufacturing |
414 |
Human Resources |
949 |
Field Service |
1042 |
Interfund Management |
1045 |
Revenue Expense Deferrals |
1058 |
Cashbook (Bank Management) |
1150 |
Scheduled Installment |
1157 |
Collections Management |
1235 |
Safe Pay |
1428 |
Electronic Reconcile |
1493 |
SmartList |
1632 |
Cash Flow Management |
1838 |
Technical Service Tools |
1878 |
Excel-Based Budgeting |
1911 |
HRM Solution Series |
2150 |
Payment Document Management |
2199 |
Business Activity Statement |
2277 |
Purchase Order Enhancements |
2416 |
Control Account Management |
2547 |
Enhanced Commitment Management |
2788 |
Enhanced Intrastat |
2992 |
CopierSeries |
3096 |
VAT Daybook |
3104 |
Advanced Security |
3180 |
Analytical Accounting |
3258 |
Encumbrance Management |
3278 |
Report Scheduler |
3830 |
SmartList Builder |
4067 |
ML Checks |
4421 |
Grant Management |
4522 |
Payroll Integration to Payable |
4612 |
Advanced Go Tos |
4621 |
Analysis Cubes for Excel |
4933 |
Certification Manager |
4955 |
Employee Health and Wellness |
5597 |
HITB Report |
5967 |
Fixed Assets Enhancements |
5982 |
Export Financial Data |
6499 |
Dynamics Online |
6831 |
Date Effective Tax Rates |
7131 |
Canadian Payroll |
8811 |
Direct Debits & Refunds |
SERIES ID:
ID |
Series |
2 |
Financial |
3 |
Sales |
4 |
Purchasing |
5 |
Inventory |
6 |
Payroll |
7 |
Project |
10 |
3rd Part |
99 |
All |
MODULE ID:
Module ID |
Module Name |
1 |
General Ledger |
2 |
AFA |
3 |
Multicurrency Management |
4 |
Bank Reconciliation |
5 |
Project Accounting |
6 |
Electronic Banking |
7 |
[Reserved] |
8 |
Fixed Asset Management |
9 |
Receivables Management |
10 |
Invoicing |
11 |
Sales Order Processing |
12 |
Purchase Order Processing |
13 |
Account Level Security |
14 |
Dynamics Process Server |
15 |
National Accounts |
16 |
Collections Management |
17 |
Service Call Management |
18 |
Contract Administration |
19 |
Payables Management |
20 |
Advanced Purchase Order Processing |
21 |
Multidimensional Analysis |
22 |
Intercompany Processing |
23 |
PM |
24 |
Magnetic Media |
25 |
Integration Assistant for Excel |
26 |
Inventory Control |
27 |
Warehouse Management |
28 |
Not sure but might be empty |
29 |
Bill of Materials |
30 |
Cash Flow Management |
31 |
eService Center |
32 |
IBIS: Depot Maintenance |
33 |
IBIS: Returns Management |
34 |
IBIS: Preventative Maintenance |
35 |
US Payroll |
36 |
US Direct Deposit |
37 |
Payroll Connect |
38 |
Explorer |
39 |
.Order Tmp |
40 |
.View Tmp |
41 |
Magnetic Media |
42 |
US 941 Magnetic Media |
43 |
Human Resources |
44 |
Project Tracking |
45 |
Org Structures |
46 |
Business Alerts |
47 |
System Manager |
48 |
Client Server System Manager |
49 |
Integration Manager |
50 |
Dynamics Modifier |
51 |
Dexterity |
52 |
Dynamics Linking Toolkit |
53 |
Payroll Tax Service |
54 |
SM CS+ SQL |
55 |
Integration Manager FRx |
56 |
Dynamics Continuum |
57 |
Customization Site License |
58 |
Requisition Management |
59 |
eCommerce |
60 |
eView |
61 |
eOrder |
62 |
eEmployee |
63 |
.Customer |
64 |
Dynamics SQL Chunk |
65 |
[Reserved] |
66 |
[Reserved] |
67 |
[Reserved] |
68 |
Manufacuturing |
69 |
Safe Pay |
70 |
Electronic Bank Rec |
71 |
RM Electronic Fund Transfer |
72 |
PM Electronic Fund Transfer |
73 |
Small Business Suite |
74 |
Resource Planning |
75 |
Landed Cost |
76 |
Auto PO |
77 |
eTech |
78 |
eService Class |
79 |
eReturns |
80 |
Language: English |
81 |
Language: German |
82 |
Language: French |
83 |
Language: Spanish |
84 |
Language: Portuguese |
85 |
Language: Polish |
86 |
Language: Czech |
87 |
Language: Dutch |
88 |
Language: French Canadian |
89 |
Classic Features |
90 |
GL Transaction Matching |
91 |
Account Rollup Inquiry |
92 |
Revenue/Expense Deferrals |
93 |
Lockbox Processing |
94 |
Refund Checks |
95 |
Customer/Vendor Consolidations |
96 |
Multi-bin |
97 |
Language: Sweden |
98 |
Language: Denmark |
99 |
Language: Norway |
100 |
Language: Finland |
101 |
Language: Estonia |
102 |
Language: Lithuania |
103 |
Language: Latvia |
104 |
Language: China |
105 |
Extended Pricing |
106 |
[Reserved] |
107 |
Cashbook Bank Management |
108 |
Electronic Bank Management |
109 |
Electronic Reconciliation Management |
110 |
Field Level Security |
111 |
Field Level Security Scripting |
112 |
Analytical Accounting |
113 |
Bank One Commercial Card Integration |
114 |
Bank One Direct Paychek Card Integration |
115 |
Available to Promise |
116 |
Business Alerts |
117 |
MS SQL Server Desktop Engine |
118 |
Advanced Distribution |
119 |
Advanced Picking |
120 |
Business Ready Licensing - Advanced Management |
121 |
BP Employee Pay |
122 |
BP Employee Profile |
123 |
BP Recruitment |
124 |
BP Skills and Training |
125 |
BP Time and Attendance |
126 |
BP Key Performance Indicators |
127 |
BP Electronic Doc. Delivery |
128 |
Project Time & Expense |
129 |
SOX Accelerators |
130 |
Demand Planning |
131 |
Grant Management |
132 |
Encumbrance Management |
133 |
Extender |
134 |
SmartList Builder |
135 |
Analysis Basic Cubes |
136 |
Analysis Cubes Library |
137 |
Advanced Analysis Cubes Library |
138 |
CGA Student Edition |
139 |
Education and Evaluation Edition |
140 |
Audit Trails |
141 |
Electronic Signatures |
150 |
Business Portal (GP11 Only) |
*This post is locked for comments