Year: 2022

In Microsoft Dynamics 365 Business Central (Customisation), how do I… Remove Personalization from a Page

Ian Grieve2 minute read0 comment

Microsoft Dynamics 365 Business CentralThis post is part of the In Microsoft Dynamics 365 Business Central (Customisation), how do I… series and of the wider In Microsoft Dynamics 365 Business Central, how do I… series which I am posting as I familiarise myself with Microsoft Dynamics 365 Business Central.

If you have personalized a page in Dynamics BC, there are two ways for you to undo this personalization.

For both you need to click the Settings cog icon in the top right corner and select Personalize.

If you want to remove all personalizations, click the Clear personalization… button on the Personalizating toolbar:

Sales Order page with Personalizating toolbar open

Continue reading “In Microsoft Dynamics 365 Business Central (Customisation), how do I… Remove Personalization from a Page”

SQL Snippets: Select Primary Keys for All Tables in Database

Ian Grieve2 minute read0 comment

Microsoft SQL ServerThis post is part of the series on SQL Snippets.

The following SQL snippet will select all primary keys for all tables in the database in which the script is run.

/*
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
	['Table'].TABLE_NAME
	,['Column'].COLUMN_NAME
FROM
	INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS ['Table']
INNER JOIN
	INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS ['Column']
		ON
			['Column'].CONSTRAINT_NAME = ['Table'].CONSTRAINT_NAME
WHERE
	['Table'].CONSTRAINT_TYPE = 'PRIMARY KEY'

I used this script recently so that I could add primary keys to the Dymamics 365 BC Table Reference website.

In Microsoft Dynamics 365 Business Central (Customisation), how do I… Personalize a Page

Ian Grieve3 minute read4 Comments

Microsoft Dynamics 365 Business CentralThis post is part of the In Microsoft Dynamics 365 Business Central (Customisation), how do I… series and of the wider In Microsoft Dynamics 365 Business Central, how do I… series which I am posting as I familiarise myself with Microsoft Dynamics 365 Business Central.

There are more fields available to Business Central than are on page or lists by default. You can use the Personalize feature to add these fields if you want to, but also you sometimes have to use Personalize to expose functionality you want to use.

One example of this is the use of the deferral functionality on purchase or sales invoices; to apply a deferral to a purchase or sales invoice you need to add the Deferral Code field which allows you to pick the Deferral Template to apply (I will cover deferrals themselves in a future post, but just want to use this as the example to show personalization).

If we use Sales Orders as an example, open the page and click the Settings cog icon in the top right corner and click Personalize:

Sales Order page with the Settings tab open

Continue reading “In Microsoft Dynamics 365 Business Central (Customisation), how do I… Personalize a Page”

SQL Snippets: Select All Fields for All Tables In Database

Ian Grieve8 minute read0 comment

Microsoft SQL ServerThis post is part of the series on SQL Snippets.

The following SQL snippet will select all fields for all tables in the selected database returning formatted column types.

/*
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
	['Tables'].name AS 'Table Name'
	,['Columns'].column_id AS 'Column ID'
	,['Columns'].name AS 'Column Name'
	,CASE ['Types'].name
		WHEN 'int' THEN ['Types'].name
		WHEN 'tinyint' THEN ['Types'].name
		WHEN 'smallint' THEN ['Types'].name
		WHEN 'bigint' THEN ['Types'].name
		WHEN 'uniqueidentifier' THEN ['Types'].name
		WHEN 'timestamp' THEN ['Types'].name
		WHEN 'datetime' THEN ['Types'].name
		WHEN 'image' THEN ['Types'].name
		WHEN 'text' THEN ['Types'].name
		WHEN 'varbinary' THEN ['Types'].name + '(' + CAST(['Columns'].max_length AS VARCHAR(10)) + ')'
		WHEN 'binary' THEN ['Types'].name + '(' + CAST(['Columns'].max_length AS VARCHAR(10)) + ')'
		WHEN 'char' THEN ['Types'].name + '(' + CAST(['Columns'].max_length AS VARCHAR(10)) + ')'
		WHEN 'varchar' THEN ['Types'].name + '(' + CAST(['Columns'].max_length AS VARCHAR(10)) + ')'
		WHEN 'nvarchar' THEN ['Types'].name + '(' + CAST(['Columns'].max_length AS VARCHAR(10)) + ')'
		WHEN 'decimal' THEN ['Types'].name + '(' + CAST(['Columns'].precision AS VARCHAR(10)) + ',' + CAST(['Types'].scale AS VARCHAR(10)) + ')'
		WHEN 'numeric' THEN ['Types'].name + '(' + CAST(['Columns'].precision AS VARCHAR(10)) + ',' + CAST(['Types'].scale AS VARCHAR(10)) + ')'
	END AS'Data Type'
FROM
	sys.tables AS ['Tables']
INNER JOIN
	sys.columns AS ['Columns']
		ON
			['Columns'].object_id = ['Tables'].object_id
INNER JOIN
	sys.types AS ['Types']
		ON
			['Types'].system_type_id = ['Columns'].system_type_id
WHERE
	['Tables'].type = 'U'

In Microsoft Dynamics 365 Business Central (Customisation), how do I… Series Index

Ian Grieve2 minute read0 comment

Microsoft Dynamics 365 Business CentralI have recently started working with Microsoft Dynamics 365 Business Central and have decided to blog about it as I learn; I’ve started a In Microsoft Dynamics 365 Business Central, how do I… series which will include everything, but have also decided to break that down into a set of smaller series on specific areas of Dynamics BC.

This is the series index for the Customisation related posts I will be writing. The series index, below, will automatically update as each post in the series goes live so make sure you bookmark this post so you can see keep up-to-date with my journey into Business Central.

This series will also include “personalisations” which are a form of customisation limited to the person doing the personalization for themselves.

Continue reading “In Microsoft Dynamics 365 Business Central (Customisation), how do I… Series Index”

In Microsoft Dynamics 365 Business Central (Administration), how do I… Understand Search

Ian Grieve2 minute read0 comment

Microsoft Dynamics 365 Business CentralThis post is part of the In Microsoft Dynamics 365 Business Central (Administration), how do I… series and of the wider In Microsoft Dynamics 365 Business Central, how do I… series which I am posting as I familiarise myself with Microsoft Dynamics 365 Business Central.

I am very much used to navigating through applications using menus, whether they be flat or drop down menus. While it is possible to do this with Dynamics BC, it is by no means quick or efficient to do so. You may have noticed in articles I’ve posted so far, but I have always been using search using the Tell me what you want to do search function to find pages.

This is because this is the quickest and most efficient way of finding and navigating to the page you want. If I wanted to find a Warehouse page, but don’t recall the exact name, I can click the Tell me what you want to do and type warehouse and the search will return relevant entries broken into sections:

Search results for warehouse

Continue reading “In Microsoft Dynamics 365 Business Central (Administration), how do I… Understand Search”

Auto Restart Post Master Enterprise

Ian Grieve3 minute read0 comment

Microsoft Dynamics GPI’m a big fan of Post Master Enterprise from Envisage Software Solutions and implemented it for a few clients.

For most clients we’ve installed it, configured it and it just runs without issue.

One client though had a few issues where it would suddenly stop processing. This client though was using more than the standard posting; they also had the modules for transferring sales order to invoice and emailing the sales invoice to a customer and saving a copy of the invoice to a network share. They also process quite a lot of transactions.

I flagged this up to Envisage and they pointed me in the direction of one of the Advanced Settings:

Advanced Settings

This setting makes Post Master check if the application is running correctly and if it isn;t, it will initiate a restart. We set this to 300 seconds (5 minutes)and asked the client to monitor. this was several weeks before I left ISC and, as of when I left at the end of August, the issue had not recurred; or if it had, the auto restart had resolved it without the client being aware.

In Microsoft Dynamics 365 Business Central (Administration), how do I… Know Which Keyboard Shortcuts Are Available

Ian Grieve1 minute read0 comment

Microsoft Dynamics 365 Business CentralThere are a number of keyboard shortcuts available in Microsoft Dynamics 365 Business Central which, by nature, are not very discoverable. Fortunately, there is a page on Microsoft Docs which provides an overview of the available keyboard shortcuts.

This is a very comprehensive list of the available shortcuts; fortunately, there is second page listing only the most popular shortcuts.

Continue reading “In Microsoft Dynamics 365 Business Central (Administration), how do I… Know Which Keyboard Shortcuts Are Available”

SQL Script to Alter Server, Instance and Database References in Views After Copying Microsoft Dynamics GP Databases To A New Server

Ian Grieve5 minute read0 comment

Microsoft Dynamics GPFour years ago I wrote a script which changed server and database names embedded within SQL views after a live Microsoft Dynamics GP company had been copied into a test one. This script is used by a client as one of several which are all run when they refresh live into test, but they also sometimes use it when copying databases between a live server and a test one.

They recently ran it when creating a new test server, but found afterwards that it hadn’t worked correctly. Or more accurately, it had worked correctly as it was created to do. However, the new SQL Server had a different Instance name than the original.

The script below is an update of the original which adds in a change of Instance alongside the existing server and company.

The three highlighted sections are for the old values which need to be changed; the new values are all determined automatically based on the database in which the script is run.

/*
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 @ViewDefinition AS NVARCHAR(max)

DECLARE @OldServer AS VARCHAR(50) = '2018SQL1'
DECLARE @NewServer AS VARCHAR(50) = CAST(SERVERPROPERTY('MachineName') AS VARCHAR(50))

DECLARE @OldDB AS VARCHAR(50) = 'FINANCE'
DECLARE @NewDB AS VARCHAR(50) = CAST(SERVERPROPERTY('InstanceName') AS VARCHAR(50))

DECLARE @OldCompany AS VARCHAR(5) = 'TWO'
DECLARE @NewCompany AS VARCHAR(5) = DB_NAME()

CREATE TABLE #ViewDefinitions(
	ViewDefinition NVARCHAR(MAX)
	,ROW_ID INT IDENTITY
)

INSERT INTO #ViewDefinitions
	(ViewDefinition)
--VALUES
	(SELECT
		REPLACE(
			REPLACE(['SQL Modules'].definition, 'CREATE VIEW', 'ALTER VIEW')
		,'Db=' + @OldDB + '&Srv=' + @OldServer + '&Cmp=' + @OldCompany,'Db=' + @NewDB + '&Srv=' + @NewServer + '&Cmp=' + @NewCompany)
	FROM
		sys.all_views AS ['All Views']
	JOIN
		sys.sql_modules AS ['SQL Modules']
			ON
				['SQL Modules'].object_id = ['All Views'].object_id
	and
		['SQL Modules'].definition LIKE '%Db=' + @OldDB + '&Srv=' + @OldServer + '&Cmp=' + @OldCompany + '%')

DECLARE
	cursor_Views Cursor
FOR
	SELECT
		ViewDefinition
	FROM
		#ViewDefinitions
	
	Open cursor_Views

	FETCH NEXT FROM
		cursor_Views
	INTO
		@ViewDefinition
	WHILE (@@FETCH_STATUS <> -1)
		BEGIN
		IF (@@FETCH_STATUS <> -2)
			EXEC (@ViewDefinition)
			FETCH NEXT FROM
				cursor_Views
			INTO
				@ViewDefinition
		END
	CLOSE cursor_Views
DEALLOCATE cursor_Views

DROP TABLE #ViewDefinitions
GO

As always before using a script, make sure you understand wat it is going to do and also ensure you have a good backup.

In Microsoft Dynamics 365 Business Central (Administration), how do I… Extend Trial

Ian Grieve2 minute read0 comment

Microsoft Dynamics 365 Business CentralThis post is part of the In Microsoft Dynamics 365 Business Central (Administration), how do I… series and of the wider In Microsoft Dynamics 365 Business Central, how do I… series which I am posting as I familiarise myself with Microsoft Dynamics 365 Business Central.

When you sign up for a free trial of Dynamics BC you get 30 days to try it out. Once this thirty days expires, you can subscribe to use Dynamics BC as your new ERP or, if you still want to do some testing, extend the trial for a further thirty days.

You can request the extension yourself and it will start immediately from when you click the button, but note that you only get one extension of thirty days.

To extend your free trial, sign in with the account used to create the trial initially and at the top of the page, you will see a notification that your trial has expired; click the Extend trial… link in the notification:

Dynamics BC free trial expired notification

Continue reading “In Microsoft Dynamics 365 Business Central (Administration), how do I… Extend Trial”