Use ADO to Display Created Date on Customer Maintenance

Here's some weirdness and how to solve it.
If you modify the Customer Maintenance window by dragging out the Created Date field to display on the window, and then create a new customer, the Created Date field will not update, no kidding. I don't know why this happens, it doesn't work this way on the other windows I've checked, but on the Customer Maintenance window, you don't get a new date.
In order to display the Created Date AND have the system update the field when a new record is added is a job for VBA. First, you need to created a local 'Date' field and drag it out onto the window. Create and Link a prompt to the new field. I used 'Date Created' for my prompt.

Add the Customer Maintenance window, the Customer ID and the local Date Created field to your VBA project. Next you will write some VBA code to connect to the database table, look up the value of the Created Date field, and write that value to the date field you created in Modifier. The date will both display to your user and be updated when new records are added.

The scripts I used in my VBA project are below:

-----------------------------------------------------------------------------------------------------------------------------

Option Explicit

Dim cn As New ADODB.Connection

------------------------------------------------------

Private Sub Window_BeforeOpen(OpenVisible As Boolean)

 ' ADO Connection

    Set cn = UserInfoGet.CreateADOConnection

    'set the database to the currently logged in db

    cn.DefaultDatabase = UserInfoGet.IntercompanyID

End Sub

------------------------------------------------------

Private Sub CustomerID_Changed()

    Dim rst As New ADODB.Recordset

    Dim cmd As New ADODB.Command

    Dim sqlstring As String

    If Me.CustomerID <> "" Then

    sqlstring = "select creatddt from rm00101 where CUSTNMBR ='" & Me.CustomerID & "'"

        ' ADO Command

        cmd.ActiveConnection = cn

        ' adCmdText

        cmd.CommandType = 1

        ' Command

        cmd.CommandText = sqlstring

        ' Pass through SQL

        Set rst = cmd.Execute

        If Not (rst.EOF And rst.BOF) Then

          DateCreated = rst!CREATDDT

        End If

        rst.Close

    End If

End Sub

------------------------------------------------------

Private Sub Window_BeforeClose(AbortClose As Boolean)

    ' Close ADO Connection

    cn.Close

    Set cn = Nothing

End Sub

Until next time!
Leslie

Problems with Forms 1099 Names and Addresses

Problems with Forms 1099 Names and Addresses

There is an issue as to which name and tax ID number to use on the Form 1099. If the vendor is a sole proprietor, the IRS states that the individual’s social security number and actual name shall be used.  Their employer ID number (which is what’s used on Forms 941, W-2, 940, etc.) should not be used; neither should their DBA name. The problem is, when printing the Form 1099, GP always uses the Vendor Check Name field from the Vendor Maintenance window as the 1099 name. Often, the vendor check name is a DBA name and not the name of the proprietor. For instance, it’s ‘Joe’s Construction Co.’ and not ‘Joe Smith’. It’s ‘Joe Smith’ that should be used on the Form 1099.

Also, the address that the vendor wants the 1099 to be mailed to is sometimes an altogether different address from the mailing address used for checks. Starting with version 2015 (I think), a new Address ID was added to the Vendor Maintenance window labeled ‘1099’. Unfortunately, the Vendor Check Name is not a field associated with a  Vendor Address ID. Consequently, while the addition of a 1099 address ID specification does solve the address problem, it does not solve the name problem.

One suggestion on how to resolve the name problem, is to use an address ID named ‘1099’ along with a modified report. For those vendors where an alternate name is desired, create a special Address ID named ‘1099’ and place the name you would like to print on the Form 1099 in the Contact field. You would then need to modify the report used to print the 1099s.

To modify the 1099 Laser Miscellaneous report, follow the steps below:

1.     Open the Report Writer application using the menu or by selecting Alt+9.

2.     Click on the Reports toolbar button.

3.     Insert the 1099 Laser Miscellaneous report into the Modified Reports List.

4.     Click on the 1099 Laser Miscellaneous item in the Modified Reports list and select the Open button to open the Report Definition window.

5.     Click on the Tables button to open the Report Table Relationships window.

6.     Click on the 1099s To Print Temporary File table and then click the New button.

7.     Select the PM Vendor Master File table and click OK to add it to the report.

8.     Click on the PM Vendor Master File table and then click the New button.

9.     Slect the PM Address MSTR* table and click OK to add it to the report.

10.   Your Report Table Relationships window should then look like this upon completion:

 

11.   Click the Close button to close the Report Table Relationships window.

12.   Back on the Report Definition window, click on the Layout button and the Report Layout Window will open.

13.   In the Toolbox window, select Calculated Fields and then click the New button to open the Calculated Field Definition window.

14.   Fill in the window using the following settings.

a.     Name: Alt Check Name.

b.     Result Type: String.

c.     Expression Type: Conditional.

d.     Click the Fields Tab.

                                       i.    Fields tab Resources: PMVendor Master File.

                                      ii.    Fields tab Field: Vendor Address Code – 1099.

                                     iii.    Click the Add button.

                                      iv.    Click the equals (=) button in the Operators section.

e.     Click the Constants tab.

                                       i.    Constants tab Type: String.

                                      ii.    Cnstants tab Constant: 1099.

                                     iii.    Click the Add button.

f.     Click into the True Case section.

                                       i.    Select the Fields tab.

1.     Fields tab Resources: PM Address MSTR.

2.     Fields Tab Field: Vendor Contact.

3.     Click the Add button.

g.     Click into the False Case section.

                                       i.    Select the Fields tab.

1.     Fields tab Resources: 1099s To Print Temporary File.

2.     Fields tab Field: Vendor Check Name.

3.     Click the Add button.

4.     Your completed calculated field will look substantially similar to this:

 

h.     Click the OK button.

15.   On the Report Layout, remove the existing Vendor Check Name field from the report and place the new Alt Check Name calculated field in its place. Upon completion, your Report Layout window will look substantially similar to this:

 

16.   Close the Report Layout window and click the Save button on the dialog box presented.

17.   Click OK on the Report Definition window and then return to Microsoft Dynamics GP using the File menu item.

18.   Grant security to the Modified Report using the Alternate/Modified Forms and Reports window.

Now when you print your Forms 1099 the name field will display the Alt Check Name field instead of the Vendor Check Name field if your 1099 Address Code is equal to ‘1099’. One drawback from this approach is that you’d need to be sure to modify the report each year to allow for changes that may be made as part of the year-end update. The year-end update does not update the layout of modified reports.

 

Please Select a Company - But I already have!!

This is a quick ‘when things go wrong’ article that solves an annoying issue. If you launch GP and everything is fine EXCEPT the dialog asking you to select a company never goes away, you have this problem.

 

If you click the OK button, the dialog closes and all is well with the world. If you are experiencing this issue, you need to do a little file surgery in SQL. The problem is in the SY01402 table within the DYNAMICS database. What’s happening is that the system is trying to launch a ‘remembered’ company that doesn’t exist in your current installation. This could come up if you move your system to a different server and do not bring all of the companies over. 

To solve the problem, you need to edit the SY01402 table. Filter the table so that it shows you records where the syDefaultType field is equal to 70. This is the Remembered Company entry. The value in the SYUSERDFSTR field refers to the COMPANYID of the ‘remembered’ company. The only thing you need to do is either delete the record or change the value in the SYUSERDFSTR field to a company ID that exists. You can find valid company IDs in the SY01500 table in the DYNAMICS database.

Until next time, Live the Dream!
Leslie

Technorati Tags: installation,please select a company

Setting up E-mail in Dynamics GP

This post is an attempt to walk through all that was necessary for setting up e-mail for GP. The version I used was 2015. Neither Workflow nor the Purchase Order entry screen, has been addressed. I simply ran out of time.

 
Before we get started, please note that you cannot be using a MAPI server with 64-bit Office; GP email will not work with that configuration. I’ve been told it works if you are using Exchange Server or Exchange 365, but I do not have those specifics. Bottom line - if you want to run GP e-mail with MAPI, you need to be running 32-bit Office. This is true as of 8-3-2016, but times change.

 
System Setup – Done only once for the whole system
Administration | Setup | System | System Preferences

Here, you indicate:

 
1. The mail Server Type, for COMPANY this is MAPI

2. You can protect the .docx documents sent as attachments with a password, such that the recipient cannot open the document in Word without the password. You can also select whether that password is Auto generated by the System or is a specific Password on all .docx files.

Security Setup – Done only once for the whole system. 

Administration | Setup | System | User Security

To provide security access for the Sales series, add the following windows to task TRX_Sales_001 (or any task you choose). Type is Windows, Series - Sales. Check on Sales E-mail Setup and Sales E-mail Detail Entry.

Whole Company Email Settings – Done once per Company.
Administration | Setup | Company | E-mail Settings

In this window you specify:

1. If you want to allow embedding documents in the message body and/or sending documents as attachments.

2. Also, you indicate the allowed formats for sending documents. At COMPANY, we limited the formats to .pdf and .docx only.

3. Click on Sales Series to setup defaults for the Sales Series for this company. In this window you enable which document types you want to be able to E-mail from the Sales Series.

4. You can also access this window from Sales | Setup | E-mail Settings

5. Enable checkbox: Checking this box will automatically check all of the boxes for you. You can then uncheck the ones you don’t want.

6. Message ID: Once a document is enabled, you can identify the default message you want to appear when that specific document type is E-mailed.

7. Have Replies Sent To: If you leave this field blank, the reply will go back to the message sender. Otherwise, enter or select an address that a customer can use to send a reply E-mail.

8. Allow Changing ‘Reply To’ Address at Entry: Marking this box will allow the user to change the default ‘Reply To’ address before they E-mail the sales document. If you mark this option, you can edit the reply-to address in the Sales E-mail Detail Entry window or in the Receivables E-mail Detail Entry window (for a credit memo). The Sales E-mail Detail Entry window is discussed later.

9. Allow Update of E-mail at Entry: Mark this box to change the E-mail message ID when entering the sales document. You can edit the message in the Sales E-mail Detail Entry window or in the Receivables E-mail Detail Entry window. If you don't mark this option, you won't be able to edit the subject or message body.

10. Click on Purchasing Series in the Company E-mail Setup window to setup defaults for the Purchasing Series for this company. In this window, you enable which document types you want to be able to E-mail from the Purchasing Series.

11. You can also access this window from Purchasing | Setup | E-mail Settings

12. Enable checkbox: Checking this box will automatically check all of the boxes for you. You can then uncheck the ones you don’t want.

13. Message ID: Once a document is enabled, you can identify the default message you want to appear when that specific document type is E-mailed.

14. Have Replies Sent To: If you leave this field blank, the reply will go back to the message sender. Otherwise, enter or select an address that a customer can use to send a reply E-mail.

15. Allow Changing ‘Reply To’ Address at Entry: Marking this box will allow the user to change the default ‘Reply To’ address before they send the purchasing document. If you mark this option, you can edit the reply-to address in the Purchasing E-mail Detail Entry window or in the Payables E-mail Detail Entry window (for a remittance advice). The Purchasing E-mail Detail Entry window is not discussed later.

16. Allow Update of E-mail at Entry: Mark this box to change the E-mail message of the message ID when entering the sales document. You can edit the message in the Purchasing E-mail Detail Entry window or in the Payables E-mail Detail Entry window. If you don't mark this option, you won't be able to edit the subject or message body.

Whole Company Email Message Setup – Done once per Company.
Administration | Setup | Company | E-mail Message Setup

The Message Setup window defines what appears as the Subject and in the Body of a message E-mailed with a document attached to it.

1. Message ID: Enter an ID to identify the message. It’s a good idea to somehow indicate the associated Doc. Type in the ID. This is a 25-character field.

2. Message Type: Select Standard if you are creating messages for documents that you can send to customers or vendors. The other options are for Workflow. Workflow is beyond the scope of this document.

3. Series: Select the series in which you want this message to appear. The series available depends upon the message type. By selecting All, the message is available for all of the available series. You can’t select a document type or add fields to customize the message if Standard is the message type and All is assigned as the series.

4. Doc. Type: Select the type of Document that you want to assign to a message. This field is required when you select Standard as the Message Type and Sales or Purchasing as the series. If you use the Copy Email Message window to copy message information from an existing message ID to a new message ID, the Document Type is not copied to the new message, so beware.

5. Subject: Type how you want the E-mail subject line to read. You can insert fields here so that document specific information, such as document number and customer name from the document are displayed. Many fields are available. Select where you want the field to appear in the subject line and then select the field.

6. Body: This is where you type how you want the body of your message to read. You can insert fields here so that document specific information, such as the document number and customer name. Many fields are available; select where you want the field to appear in the subject line and then select the field.

7. Field: Select the field that you want to place in the message body or subject line. The fields available in the list depend on the document type you selected. Fields are not available if you select Standard as the message type and All as the series. The message shown in the screenshot below includes the {%SOP Number%}, {%Customer Name%}, {%Document Date%} and {%Customer PO Number%} fields.

8. Additional Fields checkbox: Marking this box will display additional fields in the Field list that you can embed for the selected document type. I was surprised at all of the fields available.

9. Have Replies Sent to: If you leave this field blank, the reply will go back to the message sender. Otherwise, enter or select an address that a customer or vendor can use to send a reply E-mail. For example, assume you have entered sales@company.com as the Reply To address. If you send a document in an E-mail to a customer, the customer receives the E-mail from Joe@company.com. When the customer replies to the E-mail, sales@company.com is used in the "To" field. The address you entered in the Sales E-mail Setup window, or the Purchasing E-mail Setup window is the default Reply To address. The Sales and Purchasing E-mail setup windows are covered later.


 

Customer E-mail Accounts – Done once per Customer

Sales | Cards | Addresses | Pull up the customer and address ID and then click the World Button
This is the Customer Address Maintenance window with the World Button identified.

 

The window opened by the World button is the Internet Information window. It’s on this window that you should enter your customer E-mail addresses. Enter the addresses on the To, CC or Bcc lines. If you have multiple addresses, enter them all and separate each with a semicolon. You’ll be able to choose the proper address(es) at the document level. The list of addresses to choose from comes from here. Note that the E-mail address is tied to the Customer’s Address ID, not the Customer ID.



Customer E-mail Options – Done once per Customer.

Sales | Setup | Customer | E-mail Button (in lower RH corner of Customer window)

Use the Customer E-mail Options window to select which documents you want to E-mail to the customer, the document’s message ID, and document format. You can either send documents as attachments or embed documents in the message body. If you send documents as attachments, you can select to send multiple attachments to the customer per document type and set the maximum file size of the document. COMPANY is set up to allow multiple documents to be attached to an E-mail.
If you have marked the Allow Update of E-mail at Entry option in the Sales E-mail Setup window, you can zoom back on the message ID, and edit the subject line and body of the message. We will be setting up COMPANY so that you can update the address at document entry.

1. Email Address based on Doc Type: If you choose the Enable radio button, an ellipses button will appear at the end of the row for each document type. With this button, you can select the default E-mail accounts to use for each document type. You can change these defaults at the transaction level if you wish. We’ll discuss how to change it at the transaction level next. The ellipses button will list each E-mail address you have set up on the Internet Information window for that particular customer’s Address ID. Each E-mail address will appear on a separate line in the window.

2. Send Forms as E-mail: Mark the checkbox next to the document types you want to enable for this customer. Only the document types you enabled on the Sales E-mail Setup window will be available to mark. Click the ellipses button to select the E-mail addresses for each specific document type.

 

The ellipses button launches the Email Address Based On Doc Type window. Listed in this window are the addresses you entered on the Internet Information window for a specific Customer ID and Address ID combination. The window below is for the Sales Order window. In the Apply To Other Doc. Type frame, you can select other document types to which you want to apply the selected E-mail addresses. These E-mail addresses will be copied over to the other document types and will appear on their Email Address Based On Doc Type window automatically.

Changing the E-mail address at the time of SOP EntrySales | Transactions | Sales Transaction Entry | E-mail Detail Entry Button
The location of the E-mail Detail Entry button on the Sales Transaction Entry window is identified on the screenshot below:

Use the E-mail Detail Entry Button to open the Sales E-mail Detail Entry window. Use the Sales E-mail Detail Entry window to update a customer's E-mail information. The changes you enter in this window will affect only the current document. To make permanent changes to the customer record for E-mail settings, make them using the Customer E-mail Options window.
 

How to E-mail the SOP Document - 1

Sales | Transactions | Sales Transaction Entry | E-mail Button on Ribbon Bar
Click on the E-mail button in the toolbar to E-mail the document directly from the Sales Transaction Sales Transaction Entry window:

How to E-mail the SOP Document - 2

Sales | Transactions | Sales Batches | Print Button on Ribbon Bar

Click the Print button on the toolbar to open the Sales Document Print Options window. On this window there are two checkboxes that you can use to indicate whether the document should be printed or e-mailed or both:

How to E-mail the SOP Document - 3Sales | Transactions | Sales Batches | Print Button on Ribbon Bar
Click on the Print button on the toolbar to open the Sales Document Print Options window. On this window there are two checkboxes that you can use to indicate whether the document should be printed or E-mailed or both (see screenshot of window above). The difference is that by accessing it from the batch window allows you to E-mail an entire batch of transactions. However, when I tried to E-mail a batch of SOP transactions, nothing E-mailed. Most likely this was just a glitch that will be fixed up later. (perhaps I missed a Box To Check along the way)

I didn’t cover the POP side of e-mail, because my focus at this time is SOP. Please feel free to leave, comments, suggestions, or point out errors!

Live the dream!
Leslie

Making a field required on a scrolling window

I learned something today. This issue came from the Dynamics GP Community Forum. A poster was using Modifier to try and make the Posting Description field in the lower scrolling window of the Analysis Posting Detail screen  a required field. She did everything right. Changed the field property to Required, modified the Alternate/Modified Forms and Report information and restarted Dynamics GP. Still, the 'Required' property was not being enforced. What's up?
What was up is that Dynamics didn't check that row to make sure all required fields were completed. It doesn't have to check because none of fields are required by Dynamics GP out of the box. So, if you're trying to make a scrolling window field required and it doesn't seem to work, make sure there is at least one required field on the scrolling line.
Enjoy!
Leslie