Excel Snippets: Preserve Decimals in Result of Formula

Microsoft ExcelI might not post many Excel snippets, but I’m collecting them into a small Excel Snippets series to make them easy to find.

I do far more manipulation of data in SQL scripts than I do in Excel, but sometimes need to accomplish something in Excel for clients. This time I was working with a client using Excel to generate a Microsoft Dynamics GP macro to update some costs.

We realised when testing that the decimal places were being lost by Excel so the macro contained 100 for £100 instead of the 100.00 which is needed for the macro to run successfully. I tired a couple of things and eventually found that TEXT can be used to retain the decimals.

The example, below, also includes a ROUND to reduce the result of a calculation to only two decimals.


Excel Snippets: Series Index

Microsoft ExcelMy knowledge of using Excel is somewhat patchy; there are some things I can do easily and other simple things which I find troublesome. While I might not post many Excel snippets, I’m collecting them into a small series to make them easy to find again in future.

I’ve already been doing similar posts with Network Shell Snippets, PowerShell Snippets and VBA Snippets. The series index will automatically update as posts go live, but if you’re reading a syndicated version, you’ll need to check back to the original post

Excel Snippets
Get first day and last day of month using an Excel formula
Substitute Function
Preserve Decimals in Result of Formula
AND/OR Operators
Generate Row Numbers
Calculate Difference Between Dates
Add n Months to Current Date
Formula to Calculate Monthly Value of a Mortgage with Monthly Interest
Pad Numbers with Leading Zeroes
Offset Formula to Calculate Range on Number
Get First and Last Dates of Calendar Year
Get First and Last Dates of UK Tax Year
Formula to Zero Pad to 6 Digits

Update Web Services Server Location in All Companies in Microsoft Dynamics GP

Microsoft Dynamics GPI have a few clients with many company in Microsoft Dynamics GP (and one with well over 100) so doing upgrades or live to test backups can require a fair few, potentially time consuming, changes to data. I’ve posted scripts to update email addresses on test in bulk before as well as a few other variations. One recent one which has come up a couple of times, is the web services server location.

In the Workflow Setup window (Administration area page » Setup » System » Workflow Setup) is a field for the Web Services Server Location; this is the server where the web services have been installed and will be different for a standalone test server to live. Rather than have to move between several dozen companies changing this setting one at a time, the following script can be run to make the change in all companies.

The first highlighted section is the new server location and the second the current one:

Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */
DECLARE @SQL NVARCHAR(MAX) SET @SQL = STUFF(( SELECT CHAR(13) + 'UPDATE WF SET Web_Service_Server = ''test.example.co.uk'' FROM ' + INTERID + '.dbo.WF00100 AS WF WHERE Web_Service_Server = ''live.example.co.uk''' FROM DYNAMICS.dbo.SY01500 FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '') EXEC sys.sp_executesql @SQL

The second element is there to make sure the script is only run in companies in which the workflow setup has been completed.

ClassicPress Plugin Development: To Use Namepsaces or Not

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.

Namespaces are a standard feature of PHP, post version 5.2. A namespace is a way of addressing the problem of isolation. For example, if you have a function called load_cs in one part of your code, then you cannot use the same name elsewhere. For ClassicPress this also means that having that function in one plugin, you cannot have the same name in another plugin.

To avoid this problem with ClassicPress, the common approach is to prefix function names (and class names) with a developer and plugin specific prefix; I typically use azrcrv_{nn}_ where the highlighted section is one to four letters to represent the specific plugin (e.g. e for Events, uam for Update Admin Menu or smtp for SMTP).

John Alarcon, Code Potent, uses namespaces in all of his plugins so does not need to use developer and plugin specific prefixes; instead he uses a developer and plugin name namespace. For example, the PHP Error Log Viewer uses the following namespace declaration:

// Declare the namespace.
namespace CodePotent\PhpErrorLogViewer;

I started developing plugins for WordPress (back in 2013) before namespaces were introduced to PHP and have never adopted them. I rewrote my WordPress plugins specifically for ClassicPress in 2019 after I migrated all of my sites, but did not adopt namespaces. Partly this was to maintain some backward compatibility as some of the plugins have functions which are intended to be called from outside the plugin (such as URL Shortener which is typically called to display the shortlink in a theme) and partly because at the time I didn’t especially see the benefit.

As time has passed and I’ve created more plugins, I am thinking that was a mistake and I should have introduced namespaces when I did the ClassicPress rewrite; if I make this change now to existing plugins, this would, under semver, be a breaking change necessitating a major version number increase.

If you are starting off developing for ClassicPress, I would encourage you to seriously consider using namespaces in your development of plugins.

Click to show/hide the ClassicPress Plugin Development Series Index

ClassicPress Plugin Development: Coding Standards

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.

In the last post, I covered using semantic versioning or (semver) when developing plugins for ClassicPress.

When developing plugins, ClassicPress largely uses the same coding standards as WordPress:

From the coding standard links above, I do follow the ones for HTML and CSS pretty much 100%, but the PHP one has a few items for which I take a slightly different approach. The idea behind coding standards is to make code readable to other people, so I largely take the view that if there is part of the standard you don’t like, then as long as you’re consistent in how you break the standard, code will still be readable.

The above opinion on adhering to coding standards is in reference to greenfield development; if contributing to an existing plugin, code to the standards used for that plugin. If contributing to core, then adhere to the standards as written.

Click to show/hide the ClassicPress Plugin Development Series Index

Black Boxes Printing Instead of Images on Standard Reports in Microsoft Dynamics GP Now Fixed

Microsoft Dynamics GPThere was a recent issue with the Report Writer reports printing a black box instead of an image. This problem was the result introduced by a Windows update and quickly acknowledged as an issue by Microsoft.

This is an example of how the standard report looks when the bug is present:

Black box showing on Report Writer report

The Dynamics GP Support and Services Blog posted about the problem on the 12th March and have posted regular updates on the progress of the investigation.

The latest update on the 22nd was that a new update had been released which fixed the problem for legacy OS’s like Windows 8 and Windows Server 2012 which follows current versions having working updates available from the 19th.

Web Services for Microsoft Dynamics GP Fall 2020 Release Upgrade Error

Microsoft Dynamics GPWe’ve just upgraded another client to Microsoft Dynamics GP Fall 2020 Release; everything during the upgrade went smoothly until we came to upgrading the web services. The test environment was created by replicating the live servers into a sandbox environment.

I was pretty much expecting to encounter the problem we did as we were moving from using a SQL Server security store to an Active Directory security store. The error received was:

Security object error upgrading Web Services

A loader exception has occurred.
Loader Errors:
- Microsoft.Dynamics.Security.InvalidSecurityContextException: Microsoft.Dynamics.Security.NonExistentSecurityObjectException : The security object does not exist.  Key = 25cc1a21-2cc4-4b13-a1c8-eea186fb688a
   at Microsoft.Dynamics.Security.ConcreteValidator.ValidateContext(SecurityContext context)
   at Microsoft.Dynamics.Security.SecurityService.Get(SecurityContext context, Key key)
   at Microsoft.Dynamics.Security.AzManTaskServiceImplementation.GetTask(SecurityContext context, TaskKey taskKey)
   at Microsoft.Dynamics.GP.GPSecurityMetadataSystemLoader.PersistTaskForAction(Action action, Task task, Keyword keyword)
   at Microsoft.Dynamics.GP.GPSecurityMetadataSystemLoader.PerformActionOnKeywords(Action action)
   at Microsoft.Dynamics.InstallData.Loader.Process(String[] args)

As the error referred to a security object error, my thinking immediately went to the new security store as being the issue. Due to the change in method I opted to remove the web services and do a reinstall; I tried using the built-in removal process in the Web Services Configuration Wizard, but this took a very long time to run and then crashed, so I used a couple of Microsoft supplied scripts to remove all of the web services objects from the company databases first and then from the system.

Once the web services had been completely removed, I re-ran the Configuration Wizard and deployed the web services again. The deployment ran through without any issues and we were able to test web services without encountering any further issues.

The lesson, which confirmed what I expected, is that if you change the security store type, you need to remove the web services from all companies and do a redeploy.

Remember to Reactivate Jet Reports When User Made Designer

Jet ReportsI’ve been doing a lot of work with a client on Jet Reports recently. While we were doing a training session, we created some new users and changed some from being ordinary users to being designers via the Jet Licensing portal. When the first user launched Excel, they found they were still an ordinary user without access to design mode.

They logged out and back in without the license changing at all.

The key to resolving this, was for that user to reactivate Jet Reports using the Activate from Jet Service Tier option on the Jet ribbon to to pick up the changed license.

Web Services for Microsoft Dynamics GP: How to Setup an Active Directory Security Store

Microsoft Dynamics GPTo use the Web Services for Microsoft Dynamics GP with TLS 1.2 you need to do two things:

  1. Upgrade to the Fall 2020 Release; you can’t just apply the hotfix to an October 2019 or earlier release, but have to use the full download media.
  2. Create an Active Directory Security Store.

Upgrading Dynamics GP is not a problem, but Active Directory isn’t my area of expertise. Fortunately, Microsoft have a set of instructions on the Dynamics GP Support and Services Blog which takes you through the creation of the security store.

Once the security store has been created, you need three pieces of information for the install of the web services:

  1. Name of the domain controller.
  2. Port 389.
  3. The partition string entered when creating the security store.

Recent ISC Software Webinar: Controls & Security in Microsoft Dynamics GP

ISC Software SolutionsIn our most recent webinar, we took a look at Controls & Security in Microsoft Dynamics GP. In this webinar, we covered how you can improve controls and security in Microsoft Dynamics GP using a mix of standard ad third party functionality. If you want to catch up on this, or any other, webinar, you can do so here.

In this blog post, I am going to recap the webinar and cover the salient points:

  1. Introduction
  2. Posting Controls
  3. Workflow Approvals
  4. Automation
  5. Security Setup
  6. Field Level Security
  7. Report Security
  8. Single Sign-on
  9. Audit
  10. Conclusion

Introduction ^

As always in this type of webinar, I try to use standard functionality as far as possible, but this is not always possible as not all of the areas being covered are included as standard. This was a deliberate design decision from the very creation of Microsoft Dynamics GP where they decided that supporting third parties would allow many more developers to work on modules for the system and encourage competition and improved standards from those third parties (known as Independent Software Vendors or ISVs for short).

There are three key areas where I am recommending third party modules and I will try to be clear when I am doing this.

Continue reading “Recent ISC Software Webinar: Controls & Security in Microsoft Dynamics GP”