How to Recall/Cancel a workflow through code in AX 2012

To recall a workflow through code you can use the cancelworkflow method in workflow class. It takes the correlationId and a comment as parameters and gets the job done for you. Example

workflow::cancelworkflow(CorrelationId, “Cancelling this workflow”)

Now you are probably thinking How do I find my workflows CorrelationId. You can do that by querying WorkflowTrackingStatusTable sorting it with time and instance number and joining it with SysWorkflowtable against the correlationId field and in the contextId you pass the RecId of your record for example PO RecId.

Fix for Error: Field ‘Company’ and ‘Lot ID’ must be filled in”

When editing a PO line for procurement category you receive Field ‘Company’ and ‘Lot ID’ must be filled in” error. This usually happens when you have brought in the PO line through some form of integration either AIF, excel plugin etc.

This error has also been seen on salesLine and there is a hotfix(KB 2756645) for when it happens on salesline.

Error

Reason:

There is no record in PurchLineForeignTradeCategory table related to PO line added.

 

Solution:

Create a record against the PO line causing this error in the PurchLineFroeignTradeCategory table in your integration and you will see the error disappear.

Difference between Refresh(), Reread(), Research() and ExecuteQuery() in AX 2012

In X++ we have the below methods for fetching data after any changes are made, below is the short description of what they are and when to use each one.

Refresh() refreshes the user view with whats stored in the caches. This does not touch the DB.
Use this after any form change has been made through code.

ReRead() fetches only the current record from database and does not re read the complete datasource.
Use this when you need to update only the current record after modifying any value.

ReSearch() will execute the same query again and fetch the results from the database.
Use this if you need to get the current most data from database.

ExecuteQuery() will run the query again just like research does but it will also take any query changes into account.
Use this is you have modified the query on run-time and need the updated results according to the new query.

Connecting to an External Database from AX 2012 using ODBC

To connect to an external database with X++ you can use Open Database Connection (ODBC) protocol through the OdbcConnection class among other ways. This approaches requires you to setup us a Data Source Name(DSN) on your server machine with the required access. Below is the code sample for how you can achieve this.To read more about this visit MSDN.

 

// X++, Main method in a class.
static public void Main(Args _args)
{
    LoginProperty loginProperty;
    OdbcConnection odbcConnection;
    Statement statement;
    ResultSet resultSet;
    str sql, criteria;
    SqlStatementExecutePermission perm;
    ;

    // Set the information on the ODBC.
    loginProperty = new LoginProperty();
    loginProperty.setDSN("dsnName");
    loginProperty.setDatabase("databaseName");

    //Create a connection to external database.
    odbcConnection = new OdbcConnection(loginProperty);

    if (odbcConnection)
    {
        sql = "SELECT * FROM MYTABLE WHERE FIELD = "
            + criteria
            + " ORDER BY FIELD1, FIELD2 ASC ;";

        //Assert permission for executing the sql string.
        perm = new SqlStatementExecutePermission(sql);
        perm.assert();

        //Prepare the sql statement.
        statement = odbcConnection.createStatement();
        resultSet = statement.executeQuery(sql);

        //Cause the sql statement to run,
        //then loop through each row in the result.
        while (resultSet.next())
        {
            //It is not possible to get field 3 and then 1.
            //Always get fields in numerical order, such as 1 then 2 the 3 etc.
            print resultSet.getString(1);
            print resultSet.getString(3);
        }

        //Close the connection.
        resultSet.close();
        statement.close();
    }
    else
    {
        error("Failed to log on to the database through ODBC.");
    }
}

Connecting to an External Database from AX 2012 using SqlClient classes

Here is how you can connect to an external database using X++ SqlClient class. This is among the few others approaches you can connect to an external database.

public System.Data.SqlClient.SqlConnection getConnection()
{
str connectionString;

System.Exception exception;
System.Data.SqlClient.SqlConnection connection;

connectionString = “Persist Security Info=False;User ID=UserID;Pwd=Password;Initial Catalog=DatabaseName;Data Source=localhost;”;

try
{
connection = new System.Data.SqlClient.SqlConnection(connectionString);
connection.Open();

return connection;
}
catch (Exception::Error)
{
error(“An exception has occurred.”);
}
catch (Exception::CLRError)
{
error(“A CLR exception has occurred.”);

exception = CLRInterop::getLastException();

if (exception != null)
{
info(exception.ToString());
}
}

return null;
}

To run a query after a successful connection, use this code:

System.Data.SqlClient.SqlConnection conn;
System.Data.SqlClient.SqlCommand cmd;
System.Data.SqlClient.SqlDataAdapter da;
System.Data.SqlClient.SqlDataReader dr;
System.Exception netExcepn;

str sql, attributeName, attributeValue;
str 30 materialCode;

mzkConnectToDB = new MzkConnectToDB();
conn = mzkConnectToMSDgen.getConnection();

sql = strFmt(“select * from tablename;”);
cmd = new System.Data.SqlClient.SqlCommand(sql,conn);

dr = cmd.ExecuteReader();

while(dr.Read())
{
try
{
materialCode = dr.get_Item(‘material_id’);

}
}
catch(Exception::Error)
{
error(“@SYS85688”);
}
}

//Close the connection.
dr.Close();
conn.Close();
}

Solution to Error: Save encoded certificate to store failed => 0x5 (5)

While creating a certificate with the MakeCert utility you might face this Error: Save encoded certificate to store failed => 0x5 (5) as I did, dont be worried its not about your command the solution to this is to simply start your command prompt or powershell as an administrator and then run the command; the certificate will be created successfully.

Creating an ISV License File from MS Dynamics AX 2012 Model using AxUtil

Us ISVs( Independent Software Vendors) want to make our own customized Ax solutions to run on predefined licenses so we can better monetize our solutions, well thanks to Microsoft we now have an ISV licensing feature to do this and do not need to create our own licensing mechanisms. The ISV licensing feature includes the following key capabilities:

  • ISVs can generate their own Boolean licenses.
  • A run-time check that ensures an ISV-generated license key exists.
Dynamics AX ISV licensing

You can read more about ISV licensing here. In this post I will be showing you step by step how you can create your own ISV licence against a model. Before you can create a license you also need to have certificates in order to sign that license so here are the complete steps from start to end:

  1. First and foremost make sure you implement the desired roles and security in your code and tie all objects in your solution to it.
  2. Create a configuration key (or a parent-child configuration key hierarchy) for the solution.
  3. All code elements must bind to a proper configuration key or a hierarchy of configuration keys.
  4. Make sure all code written for this solution has been moved to ISV layer and to a particular model.
  5. Create a license code in the AOT.
  6. Set the Authenticode (x.509 Certificate) to the license file. Go to the license code properties and find the ‘CertificateName’ item and provide ‘cer’ file (the public part of the certificate, You will use the SPC.cer file for this). Keep the private key (.pfx) part secure as it will be used to generate license file. Here are some details on how to do this:
      1. Locate windows SDK on your machine, usually comes with .Net Framework, in my case I found it at following location

      C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\bin

      1. Copy following files from above folder to a new folder D:\Certificate folder, this would avoid wasting your time trying to run command on C:\ with no rights
        1. MakeCert.exe
        2. pvk2pfx.exe
    • To create a test certificate you can use the makecert utility, this will give you the .cer and .pvk file; as some of you faced problems with certificates I would like to add something here that I previously missed to mention. To use our own certificate we first need to create a CA(Certificate Authority) and then publish  a code signing certificate through that authority. Creating a certificate authority is done through makecert as follows on power shell run the following command to create a CA:
      • .\makecert -r -pe -n “CN=CompanyName O=CompanyName” -ss CA -sr LocalMachine -a sha256 -len 2048 -cy authority -sky signature -sv CA.pvk CA.cer
    • Now to publish a code signing certificate from our created authority; on powershell run the following command:
      • .\makecert -pe -n “CN=CompanyName O=CompanyName” -ss ISVStore -sr LocalMachine -a sha256 -len 2048 -cy end -sky signature -eku 1.3.6.1.5.5.7.3.3 -ic CA.cer -iv CA.pvk -sv SPC.pvk SPC.cer
    • To create the .pfx file you will need to convert the pvk to pfx; run the following command:
      • pvk2pfx.exe -pvk SPC.pvk -spc SPC.cer -pfx FileName.pfx -po password
  7. Assign the license code to the configuration key. In the parent configuration key, select the license code in the properties. This locks everything together if hierarchy is maintained among other configuration keys.
  8. Generate a FULL CIL.
  9. Export the model by going to Start > Administrative Tools > Microsoft Dynamics AX 2012 Management Shell. Enter following command: axutil export /model:[ModelName] /file:[modelfilename] /key:[keyfilename]
    1. [ModelName] is the name of the model
    2. [modelfilename] is the path with filename to export the model to.
    3. [keyfilename] is the strong name key generated using SN.EXE tool, in the case you want to sign the model with a strong named key. You can skip this part if you apply next step, which is to sign the model using a certificate.
  10. Sign the model with certificate
    1. You need a tool ‘SignTool’. You can get by installing Windows SDK.
    2. Run following command:
      • signtool sign /f “[PFX file]” /p [PFX password] “[Path to the model file]”

Once you have implemented the code with the above approach, you can then generate a license for your solution as explained below:

  1. Axutil genlicense /file:licensefile /certificatepath:filepath /licensecode:name /customer:name /serialnumber:number /password:value /expirationdate:date /usercount:count
    1. /file:licensefile specifies the name of the generated license file
    2. /certificatepath:filepath specifies the path to the certificate used to generate the license file. It is the private part of the X.509 certificate used on the licensed Code within AOT; basically the .pfx file.
    3. /licensecode:name specifies the name of the license code used to generate the license file.
    4. /customer:name specifies the customer name used to generate the license file. This will be provided by the Customer, it will be the Customer name on the AX license they have on their installation.
    5. /serialnumber:number specifies the serial number used to generate the license file. This will be provided by the Customer, it will be the serial number of AX license they have on their installation.
    6. /password:value is the value that must match the password of the certificate used to generate the license file.
    7. /expirationdate:date specifies expiration date of the generated license. This parameter is optional.
    8. /usercount:count specifies the number of simultaneous users for the generated license. This parameter is optional. (This is not supported anymore, so you can skip this)
    9. After running this command you can write “type [licensefilename]” to see the content if the license file.
  2. After you have your license you will share the model file and license file with the customer, also if you have created a self signed certificate(as we did with the makecert utility) then your customer needs to trust your Certificate Authority before they import the model and license. To do this simply run the command below on your customers environment:
    • certutil -addstore Root CA.cer
      • This adds it into the Windows certificate store.

Hope this post was helpful, for any questions feel free to leave a comment. Here are some good resources that helped me understand ISV licensing.

UPDATED: To overcome the “Certificate associated with license XXX is not a trusted certificate.” Error while importing license file the complete steps for creating our own certificate have been update, please find the updates in blue text.