In my series of posts on SQL scripts I use when restoring Dynamics AX databases from one environment to another, we have so far covered various configuration and setting changes to Dynamics AX. Today I will move on to some suggested data changes that you may wish to consider making when doing database restores. These changes won’t affect the functioning of AX, but may prevent some awkward or confusing situations like clients or user’s receiving emails etc..
These data cleanup scripts are really meant as guidelines to help you in creating your own scripts, rather than a comprehensive set of “must-do” scripts.
The two changes for today are as follows
- Setting your email templates to clearly indicate that they originate from a testing system. It may be perfectly legitimate for users to receive emails from your testing or development environments, e.g. for testing workflows, however it is very helpful for the users to clearly see that they are viewing test data rather than production data. So setting the senders name and address to indicate “Development” will assist.
Email templates are found under: Organisation administration -> Setup -> Email templates
2. Setting user email addresses. We obviously don’t want to send emails to users who have no interest in our development systems so one may, depending on your needs, want to do one of the following.
- Clear all user’s email addresses, except for a specific subset.
- Set some or all user’s email addresses to a single address (e.g. You want emails to still be generated to see that they are working, but you would like to see the contents of them yourself and not expose the user to the test data)
These settings can be found under System Administration -> Users -> Options (ribbon button) -> Email
The following SQL code will accomplish the above. I have parameterised the SQL for easier reuse or adjustment.
---Email Template Settings---Declare @WFAPPROVER_ADD varchar(50) = 'firstname.lastname@example.org'
Declare @EMAIL_TEMPLATE_NAME varchar(50) = 'Dynamics AX - DEVELOPMENT'
Declare @EMAIL_TEMPLATE_ADDRESS varchar(50) = 'email@example.com'
---Email Address Clearing Settings---
DECLARE @ExclUserTable TABLE (id varchar(10))
insert into @ExclUserTable values ('userid1'), ('userid2')
---Update Email Templates---
update SYSEMAILTABLE set SENDERADDR = @EMAIL_TEMPLATE_ADDRESS, SENDERNAME = @EMAIL_TEMPLATE_NAME where SENDERADDR!=@EMAIL_TEMPLATE_ADDRESS OR SENDERNAME!=@EMAIL_TEMPLATE_NAME;
update SYSEMAILSYSTEMTABLE set SENDERADDR = @EMAIL_TEMPLATE_ADDRESS, SENDERNAME = @EMAIL_TEMPLATE_NAME where SENDERADDR!=@EMAIL_TEMPLATE_ADDRESS OR SENDERNAME!=@EMAIL_TEMPLATE_NAME;
---Update User Email Addresses---
update sysuserinfo set sysuserinfo.EMAIL = '' where sysuserInfo.ID not in (select id from @ExclUserTable)
Note, the above example clears all user email addresses except for a specific set, you could easily adjust this to set them all to a specific address as follows:
update sysuserinfo set sysuserinfo.EMAIL = 'firstname.lastname@example.org'
I hope this assists you and will be useful in your management of your AX environments. Keep an eye out for new posts in this series!
View Previous – AX DB Restore Scripts #6 – SMTP Server and DMF Folder
View Next – AX DB Restore Scripts #8 – Disable users
Back to List
Setting up AX Database logging via the user interface wizard can at times be a bit of a cumbersome and slow task, especially for tables that are not very common. The job below will simply set database logging for all fields on a specific table. Simply replace “InventSalesSetup” with the table name of your own choosing. As always, test this in a non-production environment to confirm that it performs suits your own needs.
static void setDBLogOnTable(Args _args)
TableId tableId = tableNum(InventItemSalesSetup);
SysDictTable dictT = new SysDictTable(tableId);
log.logType = DatabaseLogType::Update;
log.logTable = tableId;
fields = dictT.fields(false,false,true);
se = fields.getEnumerator();
dictF = se.current();
log.logField = dictF.id();
info(strFmt("Adding field %1", dictF.name()));
Challenge/Problem: Many Dynamics AX Workflow work-items that need to be reassigned at once.
Description: At times we have had the request from clients to reassign many workitems from one person to another. My first reaction is why weren’t delegation parameters setup on the user so that one doesn’t need to manually reassign. However I have come to realise over time that there are a couple of good reasons to do so such as a person falling ill suddenly, a mistake in the workflow config, failure to set delegation parameters in time etc…
Solution: The following script/job designed for AX workflow will reassign workitems from one user to another. You can modify your query a bit to restrict the items to just the one that you want. The attached one simply reassigns all items currently assigned to user “123456” to user “654321”.
NOTE: This will not affect items in your workitem list as a result of queues
static void workflowMassReassign(Args _args)
UserId fromUser = "123456"
UserId toUser = "654321";
//Comment for workflow history
str comment = "Auto-Reassign 2014/11/26 08:00";
while select workitems where workitems.Status == WorkflowWorkItemStatus::Pending && workitems.UserId == fromUser
WorkflowWorkItem::delegateWorkItem(workitems.Id, toUser, comment);
info(strFmt("Items re-assigned: %1",i));
Challenge / Problem: Maintaining email templates across multiple companies.
Descritpion: Dynamics AX makes use of email templates for various bits of functionality in Dynamics AX, including workflow notifications and alert notifications. If you are using workflow in multiple companies and want to keep the same workflow template across the board, it can be quite frustrating to have to make the same changes in every company. The following script/job in X++ will help sync all (or some) system email templates and their respective languages into every company in Dynamics AX.
/// Copies All System Email Template to all companies
/// WARNING: Will create or overwrite existing templates in other companies
static void syncWorkflowTemplates(Args _args)
local = SysEmailTable::find(email.EmailId, true);
info(strFmt("Deleting %1 (%2)", local.EmailId, curext()));
local.DefaultLanguage = email.DefaultLanguage;
local.Description = email.Description;
local.EmailId = email.EmailId;
local.Priority = email.Priority;
local.SenderAddr = email.SenderAddr;
local.SenderName = email.SenderName;
info(strFmt("Adding %1 (%2)", email.EmailId, curext()));
localMess = SysEmailMessageTable::find(message.EmailId, message.LanguageId, true);
info(strFmt("Deleting %1 %2 (%3)", localMess.EmailId, localMess.LanguageId, curext()));
localMess.EmailId = message.EmailId;
localMess.LanguageId = message.LanguageId;
localMess.LayoutType = message.LayoutType;
localMess.Mail = message.Mail;
localMess.Subject = message.Subject;
localMess.XSLTMail = message.XSLTMail;
info(strFmt("Adding %1 %2 (%3)", localMess.EmailId, localMess.LanguageId, curext()));
// Restrict to specific templates in a where clause if necessary
while select email
while select DataArea where !DataArea.isVirtual && DataArea.id != email.dataAreaId
while select message where message.EmailId == email.EmailId