Notepad++ Find and Replace numbers in square brackets

Useful ApplicationsI was working with some text the other day which included a lot of references using numbers in square brackets; I needed to remove this text and wanted an easy way of doing it.

I did a little research and found that Notepad++, an application I already use, can do regular expression find and replace.

The regular expression for a number surrounded by square brackets is:

\[(\d+)\]

To use the regular expression in find and replace, enter the regular expression into the Find what box and change the Search Mode to Regular expression:

Regul;ar expression find and replace

When you hit Replace, or Replace All, the regular expression will be used to repalce the matching text with the supplied replacement value.

Books referenced by Bernard Cornwell in the Sharpe series’ historical notes

LiteratureThe Richard Sharpe series is a series of historical fiction novels and short stories by Bernard Cornwell centred on the character of Richard Sharpe, charting Sharpe’s progress in the British Army during the Napoleonic Wars, starting from his early career in India through to the Battle of Waterloo and beyond (further details on the series can be found on Wikipedia).

In some of the books, Bernard Cornwell mentioned books he used as references when writing the series. I’ve not found a list of these online, so on a recent reading kept a list myself, but hadn’t actually done anything with the list. On a recent call with a client, I mentioned looking to book sightseeing holidays in Italy and Portugal. He recommended a book on the Roman period for me, Rubicon: The Triumph and Tragedy of the Roman Republic by Tom Holland, which reminded me that I’d been planning to look up the books on the Peninsular War mentioned by Cornwell.

The books Cornwell mentioned in the Sharpe series are:

The Sharpe books in which the above are referenced were:

  1. Sharpe’s Tiger
  2. Sharpe’s Eagle
  3. Sharpe’s Escape
  4. Sharpe’s Waterloo
  5. Sharpe’s Devil

I’ve picked up the Elizabeth Longford book on Wellington as a starting point along with Donald Tomas’ book on Thomas Cochrane, who had a varied and controversial career which I’m sure is going to be a fascinating read.

Berard Cornwell has himself written a book on the Battle of Waterloo: The History of Four Days, Three Armies and Three Battles which has been in my “to read” stack for quite a while, being joined now by my recently purchased copy of Tom Holland’s book.

Find text in any SQL object

Microsoft SQL ServerI needed to find custom triggers or views which referenced a certain table and although I thought I had a script which would find text in a SQL object like a trigger, view or stored procedure, I couldn’t find one when I searched my site the other day.

It only took me a few minutes to write one; the first highlighted text is the text to search for and the second a limitation on the name of the SQL objects to check.

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (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 @Search varchar(255) SET @Search='PM00200' SELECT DISTINCT ['SQL Objects'].name AS Object_Name ,['SQL Objects'].type_desc FROM sys.sql_modules AS ['SQL Modules'] INNER JOIN sys.objects AS ['SQL Objects'] ON ['SQL Objects'].object_id=['SQL Modules'].object_id WHERE ['SQL Objects'].name LIKE 'u%_AZRCRV_%' AND ['SQL Modules'].definition LIKE '%'+@Search+'%'
ORDER BY ['SQL Objects'].name ,['SQL Objects'].type_desc

SQL script to create macro to delete items in Microsoft Dynamics GP

Microsoft Dynamics GPI’ve been doing some work with SmartConnect for a client recently where one of the integrations was creating new items. As the project progressed, some of the items which had been imported needed to be replaced with different item numbers. To ensure we didn;t cause problems, I didn’t want to delete items through SQL directly, due to the number and variety of tables involved, so needed to come up with a way of generating the macro.

Macros are useful ways of repeating an action, such as deleting items, but any variation to the data and the macro will fall over. One variation was that some items had posted transactions against them so I needed to avoid these transactions.

I created the below script to create the macro for me, with SSMS set to output to text, and built in joins and checks on all of the relevant tables in Purchase Order Processing, Inventory and Sales Order Processing which may have contained data. The script is probably a little overkill on the checks it does, but I wanted to make sure it caught as much as possible.

Once the script has been run, you can copy the macro text into a file and play the macro to delete the items (if you have a lot of items, you can play the macro fast).

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (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 '# DEXVERSION=14.00.0085.000 2 2
CheckActiveWin dictionary ''default'' form ''IV_Item_Maintenance'' window ''IV_Item_Maintenance''
TypeTo field ''Item Number'' , '''
+ CAST(RTRIM(['Item Master'].ITEMNMBR) AS VARCHAR(50)) + '''
MoveTo field Inactive # ''FALSE''
CommandExec dictionary ''default'' form ''IV_Item_Maintenance'' command ''Delete Button_w_IV_Item_Maintenance_f_IV_Item_Maintenance''
# Are you sure you want to delete this record?
NewActiveWin dictionary ''default'' form DiaLog window DiaLog
ClickHit field OK
NewActiveWin dictionary ''default'' form ''IV_Item_Maintenance'' window ''IV_Item_Maintenance'' '
FROM IV00101 AS ['Item Master'] LEFT JOIN ( SELECT ITEMNMBR
FROM POP10110
UNION SELECT ITEMNMBR
FROM POP30110
UNION SELECT ITEMNMBR
FROM POP10210
UNION SELECT ITEMNMBR
FROM POP30210
UNION SELECT ITEMNMBR
FROM SOP10200
UNION SELECT ITEMNMBR
FROM SOP30300
UNION SELECT ITEMNMBR
FROM IV30300
) AS ['Used Items'] ON ['Used Items'].ITEMNMBR = ['Item Master'].ITEMNMBR
INNER JOIN IV00102 AS ['Item Quantities'] ON ['Item Quantities'].ITEMNMBR = ['Item Master'].ITEMNMBR
AND ['Item Quantities'].LOCNCODE = '' LEFT JOIN IV10301 AS ['Stock Count Line'] ON ['Stock Count Line'].ITEMNMBR = ['Item Master'].ITEMNMBR
WHERE ['Used Items'].ITEMNMBR IS NULL AND ['Item Quantities'].QTYONHND = 0
AND ['Item Quantities'].QTYRTRND = 0
AND ['Item Quantities'].QTYINUSE = 0
AND ['Item Quantities'].QTYINSVC = 0
AND ['Item Quantities'].QTYDMGED = 0
AND ['Item Quantities'].ATYALLOC = 0
AND ['Stock Count Line'].ITEMNMBR IS NULL

If when you run the script you only get part of the macro text, you can change the query results length.

Permissions error exporting Microsoft Dynamic GP SmartList Object

Microsoft Dynamics GPA client recently reported a problem whereby users were no longer able to export a SmartList favourite to Excel as they were receiving a permissions error when the Excel file opened:
The SELECT permission was denied on the object 'uv_AZRCRVBudgetByMonth', database LIVE', schema 'dbo'

I did some checking and realised that the problem was that the file being opened when the user tried to do an export, was named like a Refreshable Excel report; a little more checking showed that there was a Refreshable Excel report of that name using the view from the SmartList. The SmartList object being used was using a SQL View to return the data and this had made the Publish button available. An accidental click had then published the Excel report, but, as the user did not have permissions to execute the view under their Windows account, the Excel report produced the error.

To confirm this I ran the below script to see what published Refreshable Excel reports were present:

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (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 DEX_ROW_ID
FROM syDeployedReports
WHERE ObjectType = 5

In this case, a few items were returned, but none of the SmartList Designer SmartLists should have been published, so I was able to take the above script and include it in a delete statement:

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (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). */
DELETE FROM syDeployedReports
WHERE DEX_ROW_ID IN
(
SELECT DEX_ROW_ID
FROM syDeployedReports
WHERE ObjectType = 5
)

After running this, we just needed to delete the Excel report from the published location (same as the ones published from Reporting Tools Setup).

Now when a user tries to export the SmartList, the data is exported to Excel using the standard export function.

Jet Hub’s Supported Excel Functions

Jet HubI’ve been doing an increasing amount of work with Jet Reports and have come across two Excel functions which do not work with Jet Hub; they work fine in a Jet Report in Excel, but are apparently not supported in the Jet Hub.

The two I’ve encountered problems with are:

  • CONCAT isn’t supported, but the older CONCATENATE function is supported.
  • RATE; this was actually used by a client in a formula and we then discovered it wasn’t supported when uploading the report to jet Hub when I was showing him how to use it. We’ll need to find a way to work around this unsupported function.

There is a list of supported Excel functions in Jet Hub available from insight Software; I need to start referring to this list more often when creating reports and make sure clients do the same.

Select all user views and create GRANT SELECT TO DYNGRP statement

Microsoft Dynamics GPI have a Microsoft Dynamics GP project deployment coming up soon for a client project for which a number of SQL views have been created; I can easily grab a create script for the views from Object Explorer Details in SQL Server Management Studio, but I also need to create the grant statement to give permissions to Dynamic GP users to use the SmartLists Builder objects which use these views.

INFORMATION_SCHEMA.VIEWS is available for querying in SQL Server and allows you to get a listing of the views. The following script selects all views with my custom user view prefix (highlighted).

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (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 CONCAT('GRANT SELECT ON ', TABLE_NAME,' TO DYNGRP', CHAR(13), 'GO') FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME LIKE 'uv_AZRCRV_%'

Run the script with the output set to text as for each view the script creates the GRANT and subsequent GO commands.

Jet Reports Snippet: Calculate last day of last month

Jet ReportsI’ve been doing some work with a client on Jet Reports recently and one of the items which we discussed, was running a report for the previous month without the user needing to set the date each time; this would allow them to use Jet Hub and schedule the report.

A few days ago, I posted an article on getting the last day of the month using an Excel formula. To get the date for use in Jet Reports, we can use the same formula, but can wrap it in an NP function to evaluate the formula:

=NP("Eval","=EOMONTH(Today(),-1)")

As you can see, the entire formula, which would usually be in the cell, has been included in the NP function wrapped in double quotes. Wrapping it in the NP function will ensure that the formula is calculated and the date available before other Jet functions run.

Custom Refreshable Excel Reports in Microsoft Dynamics GP navigation lists

Microsoft Dynamics GPMark Polino highlighted a post on Getting Custom with Excel Refreshable Reports on the Dynamics GP Support and Services Blog.

I’ve made a lot of use for clients on refreshable Excel reports and have made them available in the navigation list for clients (more recently, I’ve used that to make Jet Reports accessible in the same way):

Custom refreshable Excel reports in the navigation lists

Continue reading “Custom Refreshable Excel Reports in Microsoft Dynamics GP navigation lists”

SQL Stored Procedure to remove Jet Reports’ Report Run History

Jet ReportsIf you’re using Jet Hub with Jet Reports, then when you run a report, the report run is stored in the Jet Services database;through time, this report run history can grow quite large if you either have large reports or are generating lots of reports; combine these together and the history can grow to potentially massive sizes.

Jet Hub does not, unfortunately, include an automated clear-down routine for the report run history, but, fortunately, the history is only stored in a single table.

The below stored procedure can be created against the Jet Services database and scheduled to run with SQL Server Agent; the highlighted parameter at the top can be changed to alter the number of months for which history should be kept:

IF OBJECT_ID (N'usp_AZRCRV_DeleteJetReportsReportRuns', N'P') IS NOT NULL
    DROP PROCEDURE usp_AZRCRV_DeleteJetReportsReportRuns
GO CREATE PROCEDURE dbo.usp_AZRCRV_DeleteJetReportsReportRuns
@iAge INTEGER = 12 AS /*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (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). */
DELETE FROM ReportRuns
WHERE Runtime < DATEADD(month, -@iAge, GETDATE()) GO

As always, test the script before using against a live system and ensure you have a good backup before