SQL Snippet: Get Dates for Accruals

Microsoft SQL ServerAs I’ve mentioned before I write a fair bit of SQL code for various projects or support calls and will be posting some of it here.

I recently created a report for a client to use to extract transaction lines to use to import as an accruals journal; as part of the extract I worked out the last day of the one month and the first day of the next to use as the transaction and reversing dates on the journal.

The scripts below has versions for both before and after SQL 2012 (with the introduction of the EOMONTH function in 2012, getting these dates became easier).

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (https://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).
*/
-- set date variable
DECLARE @Date DATETIME = GETDATE()

-- get last date of this month
SELECT CONVERT(VARCHAR(10), DATEADD(month, ((YEAR(@Date) - 1900) * 12) + month(@Date), -1), 126)

-- get last date of this month in SQL 2012
SELECT EOMONTH(@Date)

-- get first date of next month
SELECT CONVERT(VARCHAR(10), DATEADD(month, DATEDIFF(month, 1, DATEADD( month, 1, @Date )), 0), 126)

-- get first date of next month in SQL 2012
SELECT DATEADD(day, 1, EOMONTH(@Date))

SQL Collation — SQL_Latin1_General_CP1_CI_AS vs Latin1_General_CI_AS by Craig Verster via WinthropDC

Winthrop DCOver the last few years as we have grown the ERP Practice at Perfect Image we have taken over a number of clients from other Microsoft Dynamics GP partners, both in the UK and elsewhere. Far too many of them have had Dynamics GP installed with the incorrect collation.

According to the system requirements, Microsoft Dynamics GP is supported with two collations:

  • SQL_Latin1_General_CP1_CI_AS (the recommend one as it is case insensitive).
  • Latin1_General_BIN

Whenever I install SQL Server, I am careful to ensure that I pick the SQL_Latin1_General_CP1_CI_AS collation which is supported; this is important to do, as a UK language server has Latin1_General_CI_AS as the default and you need to proactively make the change. A US server has the correct collation by default.

If I’m honest, I couldn’t have told you why this was important, but it is something I have always been very careful to do. I couldn’t have told you why this was important, until today that is, when I read a guest post by Craig Verster, Senior Microsoft Dynamics GP Consultant at Microchannel Services, on the Winthrop DC blog.

The post by Craig explains why it is important to use the correct collation. Short version is that while both SQL_Latin1_General_CP1_CI_AS and Latin1_General_CI_AS are 1252 character collations, they handle the Þ character different which can cause incorrect data to be returned in the Reporting Service Reports; there could also be other places where this manifests.

I’d encourage everyone (especially consultants) to read and understand the post so we can stop clients having an incorrect collation installed on the SQL Server used with Microsoft Dynamics GP.

New GP Compatibility Page Available

Microsoft Dynamics GPI’ve added a GP Resources section to this site; as well as links to the GP Table Reference, I’ve created a Microsoft Dynamics GP Compatibility page which shows the compatibility with SQL Server, Windows Server and Windows from Dynamics GP 2010 through to 2018.

If you see any errors, or can contribute information on supported versions, prior to 2010, please contact me with the information.

Implementing Rockton’s SmartFill: Review

Rockton SoftwareThis post is part of a series of posts on Implementing Rockton’s SmartFill.

Over the course of this series, I’ve stepped through the steps to, on a simple level, implement the SmartFill product from Rockton Software.

I’ve been quite impressed with what I have seen of SmartFill. It is easy to install and configure and the search seems quite effective.

Using the Fabrikam sample database, I inserted 1,500,000 (yes, 1.5 million) vendor records and did searches against them from Payables Transaction Entry. The search took 5 seconds to locate and return relevant data when there was only a few matching returns on the search criteria; the more records returned to the search window, the longer it took to populate.

I’ve got a couple of clients with large datasets who I think would benefit a lot from this product; one especially has tens of thousands of inventory items with a segmented Item Number; SmartFill would make it easy for them to do lookups on part of the item number; and the subset filter will allow them to search within the initially returned dataset.

SmartFill is licensed at $220 (USD) per system user (as of July 2018). In simplest terms, this means that for each Microsoft Dynamics GP full user licences you own, you need to purchase one SmartFill user license; this is regardless of whether they will be using SmartFill or not. This licence model is not unusual for Microsoft Dynamics GP ISV solutions, but it can mean that for larger clients the cost can become prohibitive.

SmartFill is worth taking a look at if you, or your users, have trouble finding data with the regular lookups. The real benefit of SmartFill is how customisable and easy the search is to use.

Implementing Rockton’s SmartFill: Customising Search Options

Rockton SoftwareThis post is part of a series of posts on Implementing Rockton’s SmartFill.

While SmartFill can be used out of the box with the search being configured to lookup on several fields, it is possible to customise the search options so that more fields can be searched against.

If we take the Vendor search as an example, it uses the following fields for the sdearch:

  • Vendor ID
  • Vendor Name
  • Vendor Class

To change the search configuration, select SmartFill Objects (Administration area page » Setup » SmartFill » SmartFill Objects). Scroll down and locate the Vendors in the list; select it and click OK:

SmartFill Objects

Continue reading “Implementing Rockton’s SmartFill: Customising Search Options”

Implementing Rockton’s SmartFill: Configuring Security

Rockton SoftwareThis post is part of a series of posts on Implementing Rockton’s SmartFill.

As it installs, SmartFill is accessible and the search windows can be used by all users.

It can also be administered by anyone with the POWERUSER* role. However, security can be maintained in two ways.

The first type of security allowsd the lookup windows to be restricted so certain lookup windows can be accessed only by certain users.

To change the security on, for example, the vendor lookup, select SmartFill Objects (Administration area page » Setup » SmartFill » SmartFill Objects). Scroll down and locate the Vendors in the list; select it and click OK:

SmartFill Objects

Continue reading “Implementing Rockton’s SmartFill: Configuring Security”

WinthropDC Special Promotion for Pennies for Taylor

Winthrop DCMicrosoft Dynamics GP MVP Shawn Dorward’s son, Taylor, was involved in a drowning accident a few weeks ago. Thanks to Taylor’s friends and the emergency teams, Taylor survived but has suffered significant brain damage; he is now on the long road to recovery and making great progress.

A Go Fund Me has been running to assist in paying for Taylor’s medical expenses.

To assist further, MVP David Musgrave has decided to give 10% of all new sales of Winthrop Development Consultants products (GP Power Tools and Batch Posting Service Toolkit) sold until the end of September to Shawn.

Kevin Rasmussen from Ethotech told David that he will match the same offer for their products. David also has a challenge for other Dynamics GP ISVs: see David’s blog for details.

Please, if you can contribute something to help Taylor, even $5, do so and, if you’re an ISV, take a look at David’s blog.

Microsoft Dynamics GP Roadmap 2018-2020

Microsoft Dynamics GPAt the GP Tech Conference this week, a new road map for Microsoft Dynamics GP was released which runs through to 2020:

Microsoft Dynamics GP Roadmap 2018-2020

As announced, earlier this week, the next version of Microsoft Dynamics GP is 2018 R2 and is scheduled for release on the 1st October 2018.

As you can see, on the roadmap, Intelligent Edge will be playing more of a role in future releases, but there will also be features requested by customers and the wider community. It is this latter section which is a large part of the new features in the upcoming release.

Implementing Rockton’s SmartFill: Search Using SmartFill

Rockton SoftwareThis post is part of a series of posts on Implementing Rockton’s SmartFill.

With SmartFill now installed, the search windows are available to use. There are many SmartFill search windows available, which all work in the same way, but, for this example, I am using Vendor search.

Open the Vendor Maintenance window (Purchasing area page » Cards » Vendor). Type OFFICE in the Vendor ID field and hit tab:

Continue reading “Implementing Rockton’s SmartFill: Search Using SmartFill”

Microsoft Dynamics GP 2018 R2 and Year-End Upgrade Blog Series Schedule

Microsoft Dynamics GPAftre the announcement at GP TechConf2018 that the next version of Microsoft Dynamics GP would be 2018 R2, Derek Albaugh, on the Dynamics GP Support and Services Blog announce the schedule for the Microsoft Dynamics GP 2018 R2 and Year-End Upgrade Blog Series.

The Microsoft Upgrade Blog Series for Microsoft Dynamics GP 2018 R2 and Year-End Update will give you the information needed for a successful upgrade and answer questions about planning, known issues, troubleshooting, table and column changes in the new version and more. Here is the upcoming schedule:

Microsoft Dynamics GP 2018 R2 and Year-End Upgrade Series Schedule
Sept. 4th: Microsoft Dynamics GP 2018 R2 and Year-End Update: Lifecycle and Upgrade Services
Sept. 5th: Microsoft Dynamics GP 2018 R2 and Year-End Update Upgrade Planning – Let’s Get Started
Sept. 6th: Microsoft Dynamics GP 2018 R2 and Year-End Update – Known Upgrade Issues
Sept. 11th: Microsoft Dynamics GP 2018 R2 and Year-End Update – Version Checks & Upgrades
Sept. 12th: Microsoft Dynamics GP 2018 R2 and Year-End Update – The Database Upgrade
Sept. 13th: Microsoft Dynamics GP 2018 R2 and Year-End Update – Modified Dictionary Upgrade
Sept. 18th: Microsoft Dynamics GP 2018 R2 and Year-End Update — Upgrade Troubleshooting
Sept. 19th: Microsoft Dynamics GP 2018 R2 and Year-End Update is upgraded – What’s Next?
Sept. 20th: Microsoft Dynamics GP 2018 R2 and Year-End Update – Upgrading Microsoft Dynamics GP 2018 Web Client
Sept. 25th: Table and Column Changes in Microsoft Dynamics GP 2018 R2 and Year-End Update
Sept. 26th: New Security Roles and Tasks are missing when upgrading to Microsoft Dynamics GP 2018 R2

The Dynamics GP Support and Services Blog will be updated with links to the posts as they are posted, so make sure you check back on that page regularly once the series starts.