SQL Script: Get First Email from Semi-colon delimited string

Microsoft SQL ServerI had a request to produce a SQl view for a client recently which extracted the first email address from the EmailToAddress field in the Address Electronic Funds Transfer Master (SY06000) table linked to a creditor record in Microsoft Dynamics GP. This field generally stores a single email, but sometimes stores multiple email addresses separated with a semi-colon.

The below script will extract the first email address from the field if it is delimited with a semi-colon or the entire content of the field if there is no semi-colon.

/*
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). */
SELECT CASE WHEN SUBSTRING(EmailToAddress, 0, CHARINDEX(';', EmailToAddress)) = '' THEN EmailToAddress ELSE SUBSTRING(EmailToAddress, 0, CHARINDEX(';', EmailToAddress)) END AS Email FROM SY01200 -- Address Electronic Funds Transfer Master (SY06000)

ClassicPress Plugin Development: Format of a Plugin readme.txt

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.

A ClassicPress plugin should have a readme.txt file. A readme file is where you “sell” your plugin, it’s features and benefits and why users would want to use it over the competition. Being clear and concise in your explanations will be of benefit.

This file explains what the plugin does, what features it has and is used to present this and other information to the users when they view the plugin details page through their ClassicPress site. This is an example of the plugin details for my SMTP plugin:

Plugin details page

As you can see the user first sees the description, but there are a number of other tabs available as well, which are configured in the readme file which are created using a form of Markdown which allows the file to be human readable no matter how it is viewed (unlike HTML markup).

At the basic level, a plugin file does not need to contain much information. Below is an example of the template used by the Update Manager I use for updating my plugins (while the ClassicPress Directory is in development:

=== Plugin Name Here ===

Version:           1.0.0
Requires:          1.0.0
Download link:     https://

== Description ==

This text displays in the modal windows; it is required. Write something!

Continue reading “ClassicPress Plugin Development: Format of a Plugin readme.txt”

ClassicPress Plugin Development: Structure of a Plugin

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.

Before you start developing a plugin, I’d recommend deciding on the plugin structure you want to use. At the simplest level, a plugin only actually requires the file which holds enough code for the plugin and the folder it sits within, but in reality you will have other files which are needed as well, such as style sheets, language files, images and so on.

Planning a structure for the plugin will ensure your plugin files are well organised which will make it easier to work with both now and again in future.

John Alarcon of Code Potent, did a blog post on this subject a while ago. I use a structure fairly similar to the one he described, and have used the same format for showing the structure I use.

Continue reading “ClassicPress Plugin Development: Structure of a Plugin”

ClassicPress Plugin Development: Using Namespaces

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 in this series, on whether to use namespaces, I discussed whether they should be used or not and noted that I do not currently use them, but am debating whether I should.

At present, the azrcrv_tt_post_tweet function in my To Twitter plugin is called from a few other plugins in order to send a tweet; the function calls looks like this:

$tweet_result = azrcrv_tt_post_tweet($parameters);

This calls this function:

function azrcrv_tt_post_tweet($parameters){

If I was to update my plugins to use namespaces, in the To Twitter plugin a namespace would be added to the top of the PHP file (only the opening PHP tags and any comments should be before the namespace declaration). If I do make this change, I would use a developer and plugin specific namespace:

namespace azurecurve\ToTwitter;

The function to post the tweet, and all other functions, could then be renamed to remove the current developer and plugin specific prefixes thusly:

function post_tweet($parameters){

The other plugins which call this function, would need the function call to be amended to include the namespace:

$tweet_result = \azurecurve\ToTwitter\post_tweet($parameters);

With namespaces, it is possible to use a function name which matches that in the global namespace. For example, the get_option function is a standard ClassicPress function used to get the options for a plugin. I can create a function with the same name in a plugin without a conflict.

Calling the below will call the function in the plugin:

$options = get_option('azrcrv-tt');

To call the standard ClassicPress version in the global namespace I would prefix the function call with a \:

$options = \get_option('azrcrv-tt');

The final point to handle, is if you are using a ClassicPress hook such as add_action you are passing a string which will be executed in the global namespace so you need to pass the namespace of your plugin as part of the hook:

add_action('admin_menu', 'azurecurve\ToTwitter\create_admin_menu');

There is a predefined PHP constant available which you can use to avoid putting your namespace in many parts of your plugin; this can be useful in future if you need to change your namespace, as you then ony need to change the declaration at the top of the plugin:

add_action('admin_menu', __NAMESPACE__.'\create_admin_menu');

The same principles would apply PHP classes as well, but as I said in the coding paradigms blog post, I am not developing using object oriented programming and so am not covering classes.

Click to show/hide the ClassicPress Plugin Development Series Index

SQL View to Return Budgets with Account User-Defined Fields

Microsoft Dynamics 365 Business CentralI was helping a client create a budget report recently where they wanted to have the same information available in more than one reporting too. While queries could be written and embedded there is scope for them to then diverge over time; the solution to this is to create a SQL view which all of the reporting tools can then select to make sure they always have the same data.

The view uses data from the following tables:

-- drop view if it exists
IF OBJECT_ID(N'uv_AZRCRV_Budgets', N'V') IS NOT NULL
	DROP VIEW uv_AZRCRV_Budgets
GO
-- create view
CREATE VIEW uv_AZRCRV_Budgets AS
/*
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). */
SELECT ['Budget Master'].BUDGETID ,['Budget Master'].YEAR1 ,['Budget Summary Master'].PERIODID ,['Account Master'].ACTINDX ,['Account Master'].ACTNUMBR_1 ,['Account Master'].ACTNUMBR_2 ,['Account Master'].ACTNUMBR_3 ,['Account Master'].ACTNUMBR_4 ,['Account Master'].ACTNUMBR_5 ,['Account Master'].ACTNUMBR_6 ,['Account Master'].ACTNUMBR_7 ,['Account Master'].ACTNUMBR_8 ,['Account Master'].ACTNUMBR_9 ,['Account Master'].ACTNUMBR_10 ,['Account Index Master'].ACTNUMST ,['Account Master'].ACTDESCR ,['Account Category Master'].ACCATDSC ,['Account Master'].USERDEF1 ,['Account Master'].USERDEF2 ,['Account Master'].USRDEFS1 ,['Account Master'].USRDEFS2 ,['Budget Summary Master'].BUDGETAMT FROM GL00200 AS ['Budget Master'] INNER JOIN GL00201 AS ['Budget Summary Master'] ON ['Budget Summary Master'].BUDGETID = ['Budget Master'].BUDGETID INNER JOIN GL00100 AS ['Account Master'] ON ['Account Master'].ACTINDX = ['Budget Summary Master'].ACTINDX INNER JOIN GL00105 AS ['Account Index Master'] ON ['Account Index Master'].ACTINDX = ['Budget Summary Master'].ACTINDX INNER JOIN GL00102 AS ['Account Category Master'] ON ['Account Category Master'].ACCATNUM = ['Account Master'].ACCATNUM GO GRANT SELECT ON uv_AZRCRV_Budgets TO DYNGRP GO

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.

=TEXT(ROUND(B2,2),".00")

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