AX Database Restore Scripts (List)


sqlserver (1)
A  common task in the administration of a Dynamics AX installation is restoring databases from a live application to a development environment or quality assurance (QA) environment. However when doing so one needs to be very careful to re-configure the database so that it behaves correctly in its new context. For example you would need to re-configure your SSRS servers to point to the correct DEV/QA SSRS instance. Many of these changes can be done via the front-end, but it is very useful to script these in SQL so that nothing is missed and alot of effort is saved.

microsoft-dynamics-ax-iconThe following post aims to list as many of the potential data items that need to be changed when doing a DB restore. I will in the following days be posting the relevant SQL alongside the front-end equivalent and ultimately a full DB restore script that you can use when doing such a restore. Some of these may need to be adapted for your specific configurations and you may need to add your own based on customization etc.

Infrastructure setups

  • SSRS Servers – Re-point or recreate your SSRS (SQL Server Reporting services) instances (View Details)
  • SSAS Servers – Re-point or recreate your SSAS (SQL Server Analysis services) instances (View Details)
  • Configure AX AOSs and batch AOSs – Live environments may typically have multiple AOS’ and batch servers. Cleaning up and reconfiguring these references will assist in getting new batch jobs and SSRS setups up and running. (View Details)
  • Batch Jobs/Batch Groups (View Details)
    • Reconfigure batch jobs and batch groups to use the new server configurations as soon as the AOS is started up (e.g. Workflow processing)
    • Disable certain critical batch jobs – This is especially important if there are batch jobs that should NOT run in a DEV/QA environment. E.G. Automatic placement of orders etc… These jobs should typically never be run outside of live so we want to disable them before the AOS even starts up.
  • Service accounts (Optional) Reset your AX service accounts (workflow execution and business connector proxy) if live and DEV/QA differ. (View Details)
  • Help server(Optional) Re-point your help server URL if necessary if live and DEV/QA differ. (View Details)
  • Reset outgoing email server (Optional) if live and DEV/QA differ. (View Details)
  • Reset Data Migration Framework’s shared folder (View Details)
  • Enterprise portal websites – Re-point your enterprise portal website urls to the DEV/QA instances.

Data safe guards (Optional)

  • Reset all email templates to have a sender name and address that clearly shows that they originate from a DEV/QA system. (View Details)
  • Reset Customer/Vendor/User email addresses – We may not want to inadvertently send out mails to clients, vendors or users while performing tests. These may include alert or workflow emails. (View Details)
  • Disable users – You may require that only certain users have access to DEV/QA databases for testing, scripting the disabling of the users in bulk will help prevent the wrong people from accessing the environment and save you having to do this manually. (View Details)
  • Clean up sensitive data if needed – E.G. Bank accounts, credit cards etc.
  • Set user status bar options – It is sometimes useful to set your users status bar options in DEV/QA so that you can easily identify critical bits of information relating to their sessions. (View Details)

Clean Ups

  • Clean up SYSServerSessions
  • Clean up SYSClientSessions

Creating a Custom Workflow Due Date Provider

Although not often needed within workflow, one sometimes has the requirement to set due dates, escalation dates etc based on some sort of custom criteria E.G Complete by My Birthday or Complete at least one day before the documents requested date. Recently on the Dynamics AX forum this question was raised (https://community.dynamics.com/ax/f/33/t/156937.aspx) so I thought I’d write up a “short” post on how to create a custom WorkflowDueDate provider.

1. Create a new class in the AOT that implements “WorkflowDueDateProvider” e.g. MyCustomDueDateProvider
public class MyCustomDueDateProvider implements WorkflowDueDateProvider
{
}

2. Create method getCalendarTokens

The getCalendarTokens method will return a list of items that will display in the Time Limit’s Calendar option in your workflow configuration.
D2
You can return any list of items here that you would like to allow the user to select for resolution. For example in the standard Calendar Due Date provider a list of all the configured calendars in the system. However you could customise this to return an entirely personal list of options such as “My Birthday” and “My Anniversary” that will set the workitem’s due date to my next birthday (26th of June 2015) or next anniversary (24 of March 2016). You could also provide an option that is document specific such as “1 Day Before Requested Date”. For this example I am going to use these options to illustrate the true arbitrary nature of the tokens.

public WorkflowCalendarTokenList getCalendarTokens()
{
    WorkflowCalendarTokenList tokens = WorkflowCalendarTokenList::construct();
    ;
    tokens.add(“MYBIRTHDAY”, “My Birthday”);
    tokens.add(“MYANNIVERSARY”, “My Anniversary”);
    tokens.add(“1DAYBEFOREREQ”, “1 Day Before Requested Date”);
    return tokens;
}

3. Create the resolve and resolveNonUser methods

After creating the tokens list we need to create a way to resolve the specific option that the user has selected in his configuration. I’ve kept my code very simple (and hardcoded) to illustrate how this works.

public WorkflowDateTime resolve(WorkflowContext _context, WorkflowCalendarToken calendarToken, WorkflowTimeSpanMinutes _timeSpanMinutes, WorkflowUser _userId)
{
    Date tmpDate;
    WorkflowDateTime ret;
    PurchReqTable req;
    ;
    if (!_calendarToken)
        throw error(“@SYS105450″);

    if (_calendarToken == “MYBIRTHDAY”)
    {
        tmpDate = str2DateDMY(“26062010″); //specific date
        ret = DateTimeUtil::newDateTime(tmpDate, 0);
        ret = MyCustomDueDateProvider::nextDate(ret);
    }
    else if (_calendarToken == “MYANNIVERSARY”)
    {
        tmpDate = str2DateDMY(“24032010″); //specific date
        ret = DateTimeUtil::newDateTime(tmpDate, 0);
        //next date is a custom method that simply finds the next occurance of my birthday by adding years to the provided date until it is greater than today.
        ret = MyCustomDueDateProvider::nextDate(ret);
    }
    else if (_calendarToken == “1DAYBEFOREREQ”)
    {
        if (_context.parmTableId() == tableNum(PurchReqTable))
        {
            //retrieve original document form WorkflowContext
            req = PurchReqTable::find(_context.parmRecId());
            ret = DateTimeUtil::newDateTime(req.RequiredDate,0);
            ret = DateTimeUtil::addDays(ret, -1);
        }
       else
        {
            throw error(“Cannot use 1DAYBEFOREREQ with any document except PurchReqTable”);
        }
    }

return ret;
}

For this example we are not going to use user specific resolution, so the resolveNonUser and resolve methods will return the same values

public WorkflowDateTime resolveNonUser(WorkflowContext _context,
WorkflowCalendarToken _calendarToken,
WorkflowTimeSpanMinutes _timeSpanMinutes)
{
return this.resolve(_context, _calendarToken, _timeSpanMinutes, ”);
}

4. Create new and construct methods

protected void new()
{
}

public static MyCustomDueDateProvider construct()
{
return new MyCustomDueDateProvider();
}

5. Create a due Date provider in the AOT.

1. Navigate to Workflow -> Providers -> Due Date Calculation
2. Right click, click “new Due Date Calculation Provider”
3. Right click on your new provider and click properties.
4. Provide an appropriate name e.g. “MyCustomDueDateProvider”
5. Provide a relevant label. This will appear in your workflow configuration under “Calendar Name”
D1
6. Depending on how you have coded your provider you may need to select an Association Type of company and AvailableForAllWorkflowTypes as “no” if you rely on company specific information or on record specific information.
7. Most importantly: Select the the class that you have created under “ProviderClass”

6. Perform CIL Compile and restart your client
7. Configure your workflow:
 Open your workflow configuration and navigate to the place where you would like to make use of your new functionality. E.G. Any Assignment’s “Time Limit” tabYou should now be able to select your provider under the “Calendar Name” option and select the resolution type under the “Calendar” option. Note our code didnt make use of the “_timespanInMinutes” parameter so the Duration option can be set to anything. We could have used this parameter to increase the flexibility on the “Before Requested Date” option if necessary.

I hope this assists someone sometime! Happy Daxing

Workflow Notifications to Custom list of Users

Problem statement: It is quite frequent in workflow in Dynamics AX that one would like to notify users when certain approvals have occurred, tasks have been completed or when errors occur. This is pretty easy to accomplish out of the box with Dynamics AX by using the notifications on approvals, tasks or on the workflow as a whole. However the options are pretty limited and not very dynamic (especially in AX 2009) and up until AX2012 you have only really been allowed to assign to specific users (i.e. by user id). This means that it is very difficult to accomplish complex notifications such as notify the following people when a document has been approved: Document Creator, Project Manager for the document’s project and all users assigned to the “Accountants” security role in AX.

AX 2012 however has now allowed you the ability to make use of “Participant Providers” as an assignment option in notifications.
participant

This means that you can create a Custom “notification” participant provider for your system that can resolve any number of personalised lists of users as above.

For example we created a particpant provider with a couple these lists. In our custom participant provider we returned a unique token per list e.g. “Originator, Project Manager, Requester and CEO” and “Originator, Project Manager, Financial Manager and CEO”.

In the resolve method of the participant provider we then retrieved the workflow document out of the workflow context object and returned the “Created by” user,  looked up the project on the document and returned the associated Project manager and finally looked up the Financial Manager and CEO from a custom parameters table in AX and returned them as well.

The actual creation of a custom participant provider is beyond the scope of this article but you can find some more resources over here:

https://workflowax.wordpress.com/2009/10/30/participant-provider-example/

I hope this sparks some thought and is useful to some:

Inspired by: https://community.dynamics.com/ax/f/33/t/155436.aspx

Workflow: User Selectable Approver

A question that has come across on a number of occassions on the Dynamics Community forum is how can one allow the creator of a document to select an approver to approve the document. We did do a variation on this request for a client a couple of years ago, so here is one way to do it.

Summary

1. Create a custom field to store the approver on the record you are workflowing
2. Allow the user to populate this field (either directly on the record’s form, or via a pop-up request) on submission.
3. Create a custom participant provider that can resolve the user selected in #2
4. Modify your configuration to use this participant provider.

Details
For this example I will make use of the PurchReqTable and its workflows

1. Create Custom Approver field

  1. Open the AOT, Data Dictionary, expand Tables, locate PurchReqTable. Expand the node and expand the fields node.
  2. Right Click on fields, click new -> String
  3. Rename your field (e.g. ChosenApprover) and provide a suitable Label

C1

2. Allow the user to populate this field
Option A – Allow modification directly on the form:

  1. Open the AOT, expand the forms node, locate and expand the PurchReqTable node.
  2. Right click on data sources, click “open in new window”
  3. Expand the PurchReqTable datasource, expand the fields node
  4. Drag the “Chosen Approver” field to a relevant place on your form E.G. Designs->Design->Tabl->TabPageDetails->TabDetails->TabPageheader->TabHeader->TabPageHeaderGeneral->HeaderAdministration

C2

Option B:

  1. Locate the Class behind your workflow’s Submit button. E.G. PurchReqWorkflow.
  2. Before the code line “if (ret && purchReqWorkflow.dialogOk())’ add code to prompt the submitter to enter a user.
  3. Save the selected user to the PurchReqTable

C3

3. Create a custom participant provider to resolve the ChosenApprover

  1. Open the AOT, right click on Classes, click “New Class”
  2. Rename Your class to “ChosenApproverParticipantProvider”
  3. Edit the class declarationand implement the “WorkflowParticipantProvider” interface
    E.g. class ChosenApproverParticipantProvider implements WorkflowParticipantProvider
  4. Create a new method called “getParticipantToken” with the following code to present a single resolution option to the users…
    public WorkflowParticipantTokenList getParticipantTokens()
    {
    WorkflowParticipantTokenList tokens = WorkflowParticipantTokenList::construct();
    ;
    tokens.add(“CHOSENAPPROVER”, “User selected Approver”);
    return tokens;
    }
  5. Create a new method to resolve the token above called “resolve” with the following code (this code assumes that the provider will only be used from a purchase requisition and will not have resolution options other than “chosenApprover”, also it assumes that all requisitions will have the ChosenApprover field filled in:
    public WorkflowUserList resolve(WorkflowContext _context,
    WorkflowParticipantToken _participantTokenName)
    {
    PurchReqTable purchReqTable;
    WorkflowUserList userList = WorkflowUserList::construct(); //Retrieve the requisition from the workflow context
    purchReqTable = PurchReqTable::find(_context.parmRecId());
    userList.add(PurchReqTable.ChosenApprover); return userList;
    }
  6. Save your class.
  7. Expand the Workflow node in the AOT, expand “providers”, right click on “participantProviders” and click “New Participant Assignment Provider”
  8. Rename the Provider to ChosenApproverParticipantProvider (similar to your class name)
  9. Right click on the provider, click properties.
  10. Provide a suitable label for the provider e.g. “Chosen approver provider”
  11. If your record is a cross company table select AssociationType of “Global”
  12. Choose “No” under “available for all workflow types” as this provider assumes that a PurchaseRequisition is being used.
  13. Under “ProviderClass” enter the name of your class created above. “ChosenApproverParticipantProvider”
    C4
  14. Associate your provider with the PurchReqWorkflow type: On expand your “ChosenApproverParticipantProvider” AOT node, right click on “workflow types”, click “new workflow type”. Right click on the newly created node, click properties, select the relevant workflow type under the “workflowType” Option. E.G. PurchReqReview
    C5
  15. Save the ParticipantProvider.

4. Modify your configuration to use this participant provider.

  1. Perform a CIL compile and close and open AX client for your changes to take effect.
  2. Navigate to your workflow configuration and click “edit”. E.G. Procurement and Sourcing -> Setup -> Procurement and sourcing workflows -> Select PurchReqReview configuration.
  3. Locate the node that you would like to assign to your Chosen User. Click assignment in the toolbar.
  4. On the assignment type tab, select Participant
  5. Click on the “Role Based” tab.
  6. Under type of participant select “Chosen Approver Provider”
  7. Under Participant select “User selected Approver”
  8. Save and close your workflow
  9. Ensure you activate the workflow version you just created!

All done! Test and enjoy!