Back when this blog was a few months old, I posted a script on updating emails on vendors to keep emails internal for testing and have recently had the need to provide a customer a similar script for testing the emailing of invoices and statements.

This first script updates all existing records to replace the email address with one supplied by the customer (the highlighted email address should be changed to the required email address):

/*
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 @TestEmail VARCHAR(100)
SET @TestEmail = 'email@example.com'

UPDATE
	SY01200
SET
	INET1 = @TestEmail
	,EmailToAddress = @TestEmail
	,EmailCcAddress = ''
	,EmailBccAddress = ''
WHERE
	Master_Type = 'CUS'

this second script will insert an email address against all customer addresses which don’t currently have an email (the highlighted email address should be changed to the required email address).

/*
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 @TestEmail VARCHAR(100)
SET @TestEmail = 'email@example.com'

INSERT INTO SY01200
	(
		Master_Type
		,Master_ID
		,ADRSCODE
		,INETINFO
		,EmailToAddress
		,EmailCcAddress
		,EmailBccAddress
	)
--VALUES
	(
		SELECT 
			'CUS'
			,CUSTNMBR
			,ADRSCODE
			,''
			,@TestEmail
			,''
			,''
		FROM
			RM00102 AS ['Customer Master Address File']
		WHERE
			(
				SELECT
					COUNT(Master_ID)
				FROM
					SY01200 AS ['Internet Addresses']
				WHERE
					Internet Addresses.Master_Type = 'CUS'
				AND
					Internet Addresses.Master_ID = ['Customer Master Address File'].CUSTNMBR
				AND
					Internet Addresses.ADRSCODE = ['Customer Master Address File'].ADRSCODE
			) = 0
	)

These scripts are only intended for use on a development or test system so please be careful when running them; as always with update scripts, I’d recommend having a good backup before running them just in case of problems.

I recently wrote a report for a client which included payables distributions; in order to output the distribution type, I joined to the Payables Distribution Type SETP (PM40103) table. Unfortunately, when the report was deployed, it didn’t work: all of the rows had a blank distribution type.

When I investigated I found that the Payables Distribution Type SETP (PM40103) table was empty. My demo environment is running Fabrikam and had fifteen rows of data, so I decided to migrate my table content to the clients system using a simple SQL script to insert the extracted rows.

The script is a simple one which doesn’t do any checking so confirm the table is empty before running.

/*
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).
*/
INSERT INTO PM40103
	(DISTTYPE, DSNMSHRT, DSNMLONG)
VALUES
	(1, 'CASH', 'CASH')
	,(2, 'PAY', 'Accounts Payable')
	,(3, 'AVAIL', 'Discounts Available')
	,(4, 'TAKEN', 'Discounts Taken')
	,(5, 'FNCHG', 'Finance Charge')
	,(6, 'PURCH', 'Purchases')
	,(7, 'TRADE', 'Trade Discounts')
	,(8, 'MISC', 'Miscellaneous')
	,(9, 'FREIGHT', 'FREIGHT')
	,(10, 'TAXES', 'TAXES')
	,(11, 'WRITE', 'Write Offs')
	,(12, 'OTHER', 'OTHER')
	,(13, 'GST', 'GST')
	,(14, 'PPS', 'PPS')
	,(15, 'UNIT', 'UNIT')

I was recently showing some clients how to use wildcard searching in SmartListand looked up my old blog post to refresh mym memory. When I was searching for my page, I found one from Shawn Dorward which he posted a couple of years ago and which covered some additional searching which was available.

Shawn’s blog post contains a some really good example of searching and also covers a couple of items, such as all fields of which I was not aware.

I was recently doing some work for a client and an issue which came up was the posting date for transactions posted to the GL was not always correct. After some investigation we determined that they had their Posting Setup (Administration area page » Setup » Posting » Posting) was set to use the posting date from the batch instead of the transaction.

As they had a substantial number of companies, and a lot of settings to change, they did not want to make this change manually. I’ve previously written a script to update some settings in Posting Setup in all companies so I was quickly able to amend the script for this change:

/*
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_Statement VARCHAR(1000)

DECLARE
	cursor_InterID Cursor
FOR
	SELECT INTERID FROM DYNAMICS..SY01500

	Open cursor_InterID
	DECLAR @INTERID VARCHAR(100)
	FETCH NEXT FROM
		cursor_InterID
	INTO
		@INTERID
	WHILE (@@FETCH_STATUS <> -1)
		BEGIN
		IF (@@FETCH_STATUS <> -2)
			SET @SQL_Statement + 'UPDATE ' + RTRIM(@INTERID) + '.dbo.SY02300 SET UPSTDTFR = 1 WHERE UPSTDTFR = 0'
			exec (@SQL_Statement)
		FETCH NEXT FROM
			cursor_InterID
		INTO
			@INTERID
		END
	CLOSE cursor_InterID
DEALLOCATE cursor_InterID

This will update all companies so I would recommend testing on a standalone test system before running on live and making sure you have a good backup before using the script.