VBA Snippets: Open a File for Appending

MicrosoftThis post is part of the series on VBA Snippets.

The below VBA script can be used to open a text file for appending and then write a line to the bottom of the file; it could be combined with the example in this eries’ last post on selecting records from a Microsoft Dynamics ODBC connection to out put multiple lines.

The highlighted section is the path of the output file.

Dim objFSO As FileSystemObject
Dim objTextStream As TextStream

Const fsoForAppending = 8
Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objTextStream = objFSO.OpenTextFile(output path, fsoForAppending, True)

'Write line
objTextStream.WriteLine data to output

objTextStream.Close
Set objTextStream = Nothing

Microsoft Dynamics GP 2021 Mid-Year Update Released

Microsoft Dynamics GPThe 2021 Mid-Year update for Microsoft Dynamics GP 2016 and Fall 2020 (18.3) has been released. Full details of the release and download links are available on the Dynamics GP Support and Services Blog.

The key piece of new functionality is support for Group VAT functionality in the VAT 100 window in the 18.3 release (this new functionality has not been rolled back to Dynamics GP 2016).

The below fixes have been made to the Fall 2020 Release only ones noted with GP 2016 have been rolled back to that version):

  • MFA: GP calls MSGraphSignOut of form syEmailObj after every completed e-mail operation.
  • All Workflow escalation email notifications are not being sent as expected when a workflow is escalated.
  • Workflow Approval Email notification being sent instead of Final Approval Email when the step after it does not require approval.
  • A User is not able to receive more than two attachments in E-mail when MFA is enabled.
  • When assigning a Workflow Manager in a multi-domain environment, users from Domain B are unable to save a manager.
  • Failed logins are not properly tracked when using Activity Tracking in Dynamics GP.
  • All 1099 amounts (including less than minimums) print on 1099 incorrectly if any box in 1099 details meets a minimum required value (Fixed on GP 2016).
  • The 1099 NEC needs to work with Project Accounting employee expense and default into the entry window. (Fixed on GP 2016).
  • Remove Multi Currency Fully applied documents from PM HATB (Feature from Fall 2020 resolved).
  • Transfer pricing is not applied when you transfer from a contract to a quote.
  • You may receive an error Get/change operation on table ‘Batch Header’ when two users use the Sales Document look up.
  • You may notice a focus issue on the Purchase Order Entry window with Manufacturing installed and you enter 2 lines to link.
  • You may receive an error message ‘the budget does not exist for this Purchase Order, when you receive against a Project Accounting PO that was has multiple Projects.
  • When you approve Project Timesheets, you may see an error message The formal parameter @emailtemptable was not declared as an OUTPUT parameter.
  • Payables Copy and Paste feature does not work when MC (multi-currency) is not registered.
  • Human Resource VET 4212 report overstates the count if more than one VET box is marked on employee maintenance window.
  • Benefit Self Service Administer View does not recognize the Archive Flag when determining enrollment.
  • Payroll integration to Payables remit to address maybe incorrect when you post to payables.
  • VAT 100 Window Display issue after you install the January 2021.
  • Analytical Accounting CheckLinks updates the aaGLDistID and aaBrowseType incorrectly.
  • Protocol change for Australian ABN look up (Fixed on GP 2016).

lightbulb Important note regarding Dynamics GP 2016

Microsoft have announced that this will be the last update for Dynamics GP 2016; you will not receive a year end update for this version. If you are using Dynamics GP 2016, it is recommended that you start planning to upgrade to the 18.3 release or 18.4 release due in Fall 2021.

The announcement post on the available on the Dynamics GP Support and Services Blog has more details including download links for other languages, or you can download the English language hotfixes using the direct links below (logon to CustomerSource required):

User Has Error Logging Into Microsoft Dynamics GP: The stored procedure smCleanupFilesBeforeLogin…

Microsoft Dynamics GPI’ve been working with a client a lot recently, so when one of their users was not able to log into Microsoft Dynamics GP today, they got in touch.

The problem only affected one user who got the below error message; all other users were unaffected and could log in normally:

Error message

The stored procedure smCleanupFilesBeforeLogin returned the following results: DBMS: 0, Microsoft Dynamics GP: 20505.

This sort of issue is covered in a Microsoft KB article.

The first three possibilities listed, could be ruled out as they would have affected more than just one user; the final option, records stuck in either the SACTIVITY or Batch Activity (SY00800) tables.

I used the below script to check for corrupt records (the highlighted section is the user id to check for):

SELECT * FROM ACTIVITY WHERE userid = 'userid'
SELECT * FROM SY00800 WHERE userid = 'userid'

Once I had confirmed the records were present, I used the below scripts to delete the stuck rows:

DELETE FROM ACTIVITY WHERE userid = 'userid'
DELETE FROM SY00800 WHERE userid = 'userid'

Disable Microsoft Word Dark Mode

MicrosoftA recent update to Microsoft Word saw the page background change from the white which has been used since forever; I’m assuming that I saw this change as I have office set to use the black theme. However, while I want the window black, I don’t want the page background to be black; I want it to continue being white.

There is a button on the action pane, on the View tab which allows you to toggle the view:

Dark Mode button on the action pane

Continue reading “Disable Microsoft Word Dark Mode”

Upcoming Microsoft Dynamics GP Webinars from ISC Software

ISC Software SolutionsEvery month at ISC Software I present a webinar on Microsoft Dynamics GP and related products. We typically have the next three upcoming monthly webinars I’ll be delivering scheduled.

We run these webinars on a monthly basis, with occasional extra webinars added to the schedule so it is worth checking the Webinar Schedule page every so often.

The upcoming webinars are:

Personalising  Microsoft Dynamics GP
In June is Personalising Microsoft Dynamics GP; Learn how users can personalise their Dynamics GP experience.

Tue, June 15th, 2021 4:00 PM – 4:45 PM BST

In this webinar we’ll take a look at the standard functionality in Dynamics GP which allows users to tailor their homepage to fit their needs and ways they can use this to work smarter, not harder

Register Here

Lesser Used Modules in Dynamics GP
In July is Lesser Used Modules in Dynamics GP; Explore some of the lesser used modules of Microsoft Dynamics GP and how they can improve processes.

Tue, July 20th, 2021 4:00 PM – 4:45 PM BST

This webinar will look at the modules included in the Microsoft Dynamics GP Starter and Extended Packs, picking out some of the modules which can save time but which aren’t as commonly used.

Register Here

Microsoft Dynamics GP Workflow
In August is Microsoft Dynamics GP Workflow; Discover how workflow can be used to require documents and master record changes to be approved, and enforce segregation of duties.

Tue, August 17th, 2021 4:00 PM – 4:45 PM BST

This webinar will look at the workflow approval types available in Dynamics GP and how they can be used to require documents or master record changes, or document submission to be approved. It will then delve further into the types of steps and routings available within workflow.

Register Here

ClassicPress Plugin Development: Create Submenu on Custom Top Level Menu

ClassicPress PluginsThis post is part of the ClassicPress Plugin Development series in which I am going to look at both best practice for developing plugins and how I approach some requirements as well as some of the functions I commonly use.

Once you have added a custom top level menu for your plugin, you can add submenu items. This is done using the add_submenu_page function:

add_submenu_page(string $parent_slug, string $page_title, string $menu_title, string $capability, string $menu_slug, callable $function = '', int $position = null)

Click to show/hide

$parent_slug (string) (Required) The slug name for the parent menu (or the file name of a standard WordPress admin page). $page_title (string) (Required) The text to be displayed in the title tags of the page when the menu is selected. $menu_title (string) (Required) The text to be used for the menu. $capability (string) (Required) The capability required for this menu to be displayed to the user. $menu_slug (string) (Required) The slug name to refer to this menu by. Should be unique for this menu and only include lowercase alphanumeric, dashes, and underscores characters to be compatible with sanitize_key(). $function (callable) (Optional) The function to be called to output the content for this page. Default value: '' $position (int) (Optional) The position in the menu order this item should appear. Default value: null

The below example is extracted from my To Twitter plugin which adds a submenu to the azrcrv-m menu item:

add_action('admin_menu', 'azrcrv_tt_create_admin_menu');

/**
 * Add to menu.
 *
 * @since 1.0.0
 *
 */
function azrcrv_tt_create_admin_menu(){
				
	add_submenu_page(
				'azrcrv-tt'
				,__('Send Tweet', 'to-twitter')
				,__('Send Tweet', 'to-twitter')
				,'manage_options'
				,'azrcrv-tt-smt'
				,'azrcrv_tt_display_send_manual_tweet');
				
}

This will add a second sublevel menu to the custom top level menu which takes the user to a different options page.

To add a custom top level menu to a network admin dashboard, change the admin_menu tag in the add_action function call to network_admin_menu.

Click to show/hide the ClassicPress Plugin Development Series Index

ClassicPress Plugin Development: Create Custom Top Level Menu

ClassicPress PluginsThis post is part of the ClassicPress Plugin Development series in which I am going to look at both best practice for developing plugins and how I approach some requirements as well as some of the functions I commonly use.

While it is most common to add an option spage for a plugin to the Settings or Security top level menu, it is possible to create a custom top level menu.

A top level menu can be created using the add_menu_page function:

add_menu_page(string $page_title, string $menu_title, string $capability, string $menu_slug, callable $function = '', string $icon_url = '', int $position = null)

Click to show/hide

$page_title (string) (Required) The text to be displayed in the title tags of the page when the menu is selected. $menu_title (string) (Required) The text to be used for the menu. $capability (string) (Required) The capability required for this menu to be displayed to the user. $menu_slug (string) (Required) The slug name to refer to this menu by. Should be unique for this menu page and only include lowercase alphanumeric, dashes, and underscores characters to be compatible with sanitize_key(). $function (callable) (Optional) The function to be called to output the content for this page. Default value: '' $icon_url (string) (Optional) The URL to the icon to be used for this menu.
  • Pass a base64-encoded SVG using a data URI, which will be coloured to match the color scheme. This should begin with 'data:image/svg+xml;base64,'.
  • Pass the name of a Dashicons helper class to use a font icon, e.g. 'dashicons-chart-pie'.
  • Pass 'none' to leave div.wp-menu-image empty so an icon can be added via CSS.
  • Default value: ''

Below is an example of a custom top level menu from my To Twitter plugin:

add_action('admin_menu', 'azrcrv_tt_create_admin_menu');

/**
 * Add to menu.
 *
 * @since 1.0.0
 *
 */
function azrcrv_tt_create_admin_menu(){

    add_menu_page(
				__('To Twitter', 'to-twitter')
				,__('To Twitter','to-twitter')
				,'manage_options'
				,'azrcrv-tt'
				,'azrcrv_tt_display_options'
				,'dashicons-twitter'
				, 50);
				
}

The top level menu automatically has a sublevel menu of the same name added; I’ll show how to rename this in the next post of this series.

To add a custom top level menu to a network admin dashboard, change the admin_menu tag in the add_action function call to network_admin_menu.

Click to show/hide the ClassicPress Plugin Development Series Index

Recent ISC Software Webinar: Business Intelligence with Microsoft Dynamics GP

ISC Software SolutionsIn our most recent webinar, we took a look at Business Intelligence with Microsoft Dynamics GP. In this webinar, we covered how business intelligence can be used with Microsoft Dynamics GP. If you want to catch up on this, or any other, webinar, you can do so here.

  1. Introduction
  2. What is Business Intelligence?
  3. What is Jet Analytics?
  4. Reporting options with Jet Analytics
  5. What is Power BI?
  6. Reporting options with Power BI
  7. Conclusion

Introduction ^

I think this webinar was the first one in which we didn’t even open Microsoft Dynamics GP. This because this webinar focused on how business intelligence can be used with Microsoft Dynamics GP. We focused on two business intelligence products which can be used to quickly build required dashboards and reporting for business intelligence.

The two products we looked at are complimentary and can be used both together or independently. They are Jet Analytics and PowerBI

Continue reading “Recent ISC Software Webinar: Business Intelligence with Microsoft Dynamics GP”

VBA Snippets: Select Records from Microsoft Dynamics ODBC Connection

MicrosoftThis post is part of the series on VBA Snippets.

In yesterdays post, I covered adding an ODBC connection to Microsoft Dynamics GP VBA for use n windows or reports. The below is an example of a SQL query using the ODBC connection.

SOPType and SopNUmber (highlighted) are fields from a window added to the VBA.

This example retries a list of fields from the Sales Transaction Amounts Work (SOP10200) table.

Dim objRS As ADODB.RecordSet
Set objRS = New ADODB.RecordSet
Set objRS.ActiveConnection = madoConn
sSQL = "SELECT * FROM SOP10200 WHERE SOPTYPE = " & SOPType & " AND SOPNUMBE = '" & SOPNumber & "'"
objRS.Source = sSQL
objRS.Open

If objRS.State = adStateOpen Then
	If Not (objRS.BOF Or objRS.EOF) Then
		objRS.MoveFirst
		
		Do While Not objRS.EOF
			' your code goes here; reference fields using objRS.fields("fieldname"))
		
			objRS.MoveNext
		Loop
	End If
	objRS.Close
End If
Set objRS = Nothing

VBA Snippets: Adding an SQL ODBC Connection in Microsoft Dynamics GP

MicrosoftThis post is part of the series on VBA Snippets.

There is an ADO connection available to VBA within Microsoft Dynamics GP which you can use, but there are some steps you need to follow to use it.

The first step is to declare the variable which will hold the connection.

Private madoConn AS ADODB.Connection

Then you need to create the connection which this example does using a Connect subroutine:

Private Sub Connect()
	If madoConn.State <> adStateOpen Then 
		Set madoConn = UserInfoGet.CreateADOConnection
		madoConn.DefaultDatabase = UserInfoGet.IntercompanyID
	End If
End Sub

It checks if the connection is already open and, if not, uses the UserInfoGet object which holds the connection detail exposed in Dynamics GP; I am also using the same object to set the default database property.

Once connected you can use the connection to execute SQL queries; I’ll show some examples of this in later posts.

When you’re finished with the connection, you can close and destroy the connection:

Private Sub Disconnect()
    If madoConn.State = adStateOpen Then madoConn.Close
    Set madoConn = Nothing
End Sub