Change Text/BackGround Color Based Off Cell Value Within a Grid

In some cases within AX you may want to treat data being displayed on the form as a quick report and within reports sometimes it’s nice to be able to change a cells text color or back ground color based on the cells value wither its a stored value or display value shouldn’t matter. Below will show you how to change a single cell based on its value or change the entire row. Since all of this code gets executed at the same location is it almost the same code it just has subtle differences. The examples shown will show you the difference between changing a cell text color, cell background color and row background color which are all based off the display field/method InventQualityOrderTable.numberOfDueDays()

 

Note: the method qualityOrder.NumberOfDaysDueDate() will return either a positive number, negative number or the text "No Due Date"

 

Example 1. Change Cell Value Text Color Based on Display Field.

Step 1. Set AutoDeclaration on the field control to true (in the example shown it’s called DifferenceDueDate)

Step 2. Go to the forms datasource and create the override method “displayOption”

Step 3. Enter the following code

 

 

public void displayOption(Common _record, FormRowDisplayOption _options)

{

    InventQualityOrderTable qualityOrder;

    str 15 numberOfDueDays;

 

    qualityOrder = _record;

 

    //get the number of days between due date/complete or now/duedate

    numberOfDueDays = qualityOrder.NumberOfDaysDueDate();

    //only do the compare if we have a number/due date

    if(CustomMethods::isNumeric(numberOfDueDays))

    {

        //if the value is bigger than 0 than it should be a miss (red)

        if(str2num(numberOfDueDays) > 0)

        {

            _options.affectedElementsByControl(DifferenceDueDate.id());

            //red

            _options.textColor(WinAPI::RGB2int(255,0,0));

        }

        else if(str2num(numberOfDueDays) <= 0)

        {

            //if the value is less than or equal to 0 than the due date was meet and the text should be green

            _options.affectedElementsByControl(DifferenceDueDate.id());

            //green

            _options.textColor(WinAPI::RGB2int(0,204,0));

        }

    }

    super(_record, _options);

}

 

 

Example 2. Change Cell Value Back Color Based on Display Field.

Step 1. Set AutoDeclaration on the field control to true (in the example shown it’s called DifferenceDueDate)

Step 2. Go to the forms datasource and create the override method “displayOption”

Step 3. Enter the following code

 

public void displayOption(Common _record, FormRowDisplayOption _options)

{

    InventQualityOrderTable qualityOrder;

    str 15 numberOfDueDays;

 

    qualityOrder = _record;

 

    //get the number of days between due date/complete or now/duedate

    numberOfDueDays = qualityOrder.NumberOfDaysDueDate();

    //only do the compare if we have a number/due date

    if(CustomMethods::isNumeric(numberOfDueDays))

    {

        //if the value is bigger than 0 than it should be a miss (red)

        if(str2num(numberOfDueDays) > 0)

        {

            _options.affectedElementsByControl(DifferenceDueDate.id());

            //red

            _options.backColor(WinAPI::RGB2int(255,0,0));

        }

        else if(str2num(numberOfDueDays) <= 0)

        {

            //if the value is less than or equal to 0 than the due date was meet and the text should be green

            _options.affectedElementsByControl(DifferenceDueDate.id());

            //green

            _options.backColor(WinAPI::RGB2int(0,204,0));

        }

    }

    super(_record, _options);

}

 

 

 

Example 3. Change Cell Value Back Color Based on Display Field.

Step 1. Go to the forms datasource and create the override method “displayOption”

Step 2. Enter the following code

 

public void displayOption(Common _record, FormRowDisplayOption _options)

{

    InventQualityOrderTable qualityOrder;

    str 15 numberOfDueDays;

    qualityOrder = _record;

 

    //get the number of days between due date/complete or now/duedate

    numberOfDueDays = qualityOrder.NumberOfDaysDueDate();

    //only do the compare if we have a number/due date

    if(CustomMethods::isNumeric(numberOfDueDays))

    {

        //if the value is bigger than 0 then it should be a miss (red)

        if(str2num(numberOfDueDays) > 0)

        {

            //red

            _options.backColor(WinAPI::RGB2int(255,0,0));

        }

        else if(str2num(numberOfDueDays) <= 0)

        {

            //if the value is less than or equal to 0 than the due date was meet and the text should be green

            //green

            _options.backColor(WinAPI::RGB2int(0,204,0));

        }

    }

    super(_record, _options);

}

 

 

Conclusion: The difference between setting an entire rows color and setting a cell/field value is only different by the method _options.affectedElementsByControl(<field control>.id()); which limits what fields the next color change affects.

 The only difference between setting a text color and a back ground color is the property textColor vs backColor()

C# - AX AIF webservice apply company filer/field filter to existing AOT query or create query on the fly within c#

Per the past previous posts of showing how to create a query within AX and calling it via the AIF in C# I ran into the dilemma of needing to add a company filter to said defined query on the fly within the C# application.
You can do this multiple ways. You can also use the examples on how to apply any filter to a query on the fly. If you are not applying filters than the static query example(s) shown in the previous post would be the way to go.

1. Create the query on the fly via code. Apply filter via code.
Pros: complete control over the structure of the query, no need for AOT object, good for small querys with only a couple tables/fields, only 1 webservice connection is needed
Cons: lots and lots of code in order to do this. The more complex the query the more code that is needed to do this, have to define every field you want to add unless you want all fields
Services needed: QueryService: http://<AOSServer>:8101/DynamicsAx/Services/QueryService
Code Example:
  string axCompanyName = "<company name within AX/dataAreaId>";
  DataSet dataSet;
  AXQueryService.QueryMetadata query = new AXQueryService.QueryMetadata();
                AXQueryService.QueryServiceClient client = new AXQueryService.QueryServiceClient();
                AXQueryService.Paging paging = null;
                query.DataSources = new AXQueryService.QueryDataSourceMetadata[2];
                query.Name = "AXCustomerInfo";
                //set up the main customer table to pull the id from
                AXQueryService.QueryDataSourceMetadata custTableDS = new AXQueryService.QueryDataSourceMetadata();
                custTableDS.Name = "CustTable";
                custTableDS.Table = "CustTable";
                custTableDS.Enabled = true;
                //define the current company to search by within AX
                custTableDS.Company = axCompanyName;
                //define the main datasource
                query.DataSources[0] = custTableDS;
                //define the field listing so we dont pull in all fields
                custTableDS.DynamicFieldList = false;
                custTableDS.Fields = new AXQueryService.QueryDataFieldMetadata[1];
               
                AXQueryService.QueryDataFieldMetadata accountNum;
                accountNum = new AXQueryService.QueryDataFieldMetadata();
                accountNum.FieldName = "AccountNum";
                accountNum.SelectionField = AXQueryService.SelectionField.Database;
                custTableDS.Fields[0] = accountNum;
      
                //let the query know we need to join the table
                custTableDS.HasRelations = true;
                custTableDS.JoinMode = AXQueryService.JoinMode.InnerJoin;
                //add the dirtparty table to the query so we can pull in the name
                AXQueryService.QueryDataSourceMetadata dirPartyTableDS = new AXQueryService.QueryDataSourceMetadata();
                dirPartyTableDS.Name = "DirPartyTable";
                dirPartyTableDS.Table = "DirPartyTable";
                dirPartyTableDS.Enabled = true;
                //define the current company to search by within AX
                dirPartyTableDS.Company = axCompanyName;
               
                //define the sub datasource
                query.DataSources[1] = dirPartyTableDS;
               
                //define the field listing so we dont pull in all the fields
                dirPartyTableDS.DynamicFieldList = false;
                dirPartyTableDS.Fields = new AXQueryService.QueryDataFieldMetadata[1];
               
                AXQueryService.QueryDataFieldMetadata name;
                name = new AXQueryService.QueryDataFieldMetadata();
                name.FieldName = "Name";
                name.SelectionField = AXQueryService.SelectionField.Database;
                dirPartyTableDS.Fields[0] = name;
                dirPartyTableDS.HasRelations = false;
           
                //execute the new dynamic query
                dataSet = client.ExecuteQuery(query, ref paging);



2. Create AOT query object. Read in properties(meta data) from the AOT object, apply filter and run query.
Pros: complex queries can be defined and managed within the AOT, less code is needed
Cons: You need to use 2 services instead of 1 (Query & MetaData Services, both are built into default AX)
Services needed: QueryService: http://<AOSServer>:8101/DynamicsAx/Services/QueryService / Metadata Service: http://<AOSServer>:8101/DynamicsAx/Services/MetadataService
Code Example:
 
  string axCompanyName = "<company name within AX/dataAreaId>";
  DataSet dataSet;
  AXMetadataService.AxMetadataServiceClient metaClient = new AXMetadataService.AxMetadataServiceClient();
                AXQueryService.QueryServiceClient client = new AXQueryService.QueryServiceClient();
               
                AXMetadataService.QueryMetadata metaQuery = metaClient.GetQueryMetadataByName(new string[] { "<aot query object name>" })[0];
                AXQueryService.QueryMetadata query = new AXQueryService.QueryMetadata();
               
                AXQueryService.Paging paging = null;
             
                //convert the metadataservice metadata to the queryservice metadata so we can execute the query with the properties/metadata found on the aot query object
                using (MemoryStream memoryStream = new MemoryStream())
                {
                    DataContractSerializer metaSerializer = new DataContractSerializer(typeof(AXMetadataService.QueryMetadata));
                    DataContractSerializer querySerializer = new DataContractSerializer(typeof(AXQueryService.QueryMetadata));
                   
                    metaSerializer.WriteObject(memoryStream, metaQuery);
                    memoryStream.Seek(0, SeekOrigin.Begin);
                    query = (AXQueryService.QueryMetadata)querySerializer.ReadObject(memoryStream);
                }

                //define the company filter based on what the user has selected
                query.DataSources[0].Company = axCompanyName;
                query.DataSources[0].DataSources[0].Company = axCompanyName;
             
                //execute the query and get the results
                dataSet = client.ExecuteQuery(query, ref paging);

As you can see both examples return the same results however the 2nd example is far less code. The example were using only has 2 fields that are being returned but on a more complex query that may contain 5-10 tables and more fields its pretty easy to tell that the easy way to go is to define the query within the AOT and get the properties , apply filter, execute query (2nd example)


You can also apply a filter to any range/field via the following method (this would work on both examples)
  AXQueryService.QueryDataRangeMetadata range = new AXQueryService.QueryDataRangeMetadata()
  {
    Enabled = true,
    FieldName = "dataAreaId",
    Value = axCompanyName
  };
   query.DataSources[0].Ranges = new AXQueryService.QueryRangeMetadata[] { range };

C# - Creating a custom AX AIF web service that accesses logic defined within an AX AOT Class object.

In order to support legacy applications sometimes you may need to pull in data from AX into standalone applications.
Below will show you how to pull in the customer names via a custom class defined within AX but called within C#

Step 1(in AX) Create Class called CustomerInfo.

Step 2(in AX) Add method called getCustomerNameList()  It should contain the following

/// <summary>
/// Get a list of customer names
/// </summary>
/// <returns>
/// List of customer names
/// </returns>
/// <remarks>
/// Used for AIF Intergration with Escalus
/// </remarks>
[SysEntryPointAttribute(true),AifCollectionTypeAttribute('return', Types::String)]
public List getCustomerNameList()
{
    CustTable custTable;
    DirPartyTable  dirPartyTable;
    List customerNames = new List(Types::String);
    //select all customers and names
    while select * from custTable
        join dirPartyTable
        where dirPartyTable.RecId == custTable.Party
    {
        //save the current customer
        customerNames.addEnd(dirPartyTable.Name);
    }
    return customerNames;
}


Step 3(in AX) Create a new service called CustomerInfo. Defined the class as 'CustomerInfo' under the properties for the service.
Then right click on the 'Operations' node on the new service and defined the method (under properties) as getCustomerNameList or whatever you called the method in step 2.


Step 4(in AX) Create a new service group called Customers and include the service CustomerInfo or whatever the service was called in step 3.
Step 5(in C#) Add the following webservice to your project http://<AOSServer>:8101/DynamicsAx/Services/Customers (Customers is the service group created in step 4)


Step 6(in C#) Add the following using statement using <C# Project Class Name>.<WebService Name> example: using AXAIFTest.Customers;


Step 7(in C#) Add the following code to your load statement or a method that can be called via load
            CustomerInfoClient axClient = new CustomerInfoClient();
            CallContext axContext = new CallContext();
           
            //called the method that exists within an AX class object
            string[] customerList = axClient.getCustomerNameList(axContext);
  
            CustomerNames.DataSource = customerList;

As you can see from my previous posts of http://axcalated.blogspot.com/2015/06/c-creating-aif-webservice-to-access-ax.html and http://axcalated.blogspot.com/2014/10/accessing-ax-custtable-dirpatytable.html you can access data and/or logic from within
AX multiple ways. It really just depends on where you want to write the majority your code (C#, AX/X++, or by creating aot objects within AX) and what are the requirements for the client. Personally I would recommend writing a web service to access your data
that way it could not only be implemented within C#/.NET framework languages but any language that supports accessing web services which is really just about any language these days. The only requirement as well for accessing a web service is intranet/internet access
so it leaves a small footprint on the users system along with "planning for the future" as we all know everything is moving to the web including AX 7.

C# - Creating an AX AIF webservice to access AX CustTable & DirPatyTable (Customer Info) query

In order to support legacy applications sometimes you may need to pull in data from AX into standalone applications.
Below will show you how to pull in customer id's + names (CustTable + DirPartyTable) via a webservice from a query that is defined within AX that will allow you to accomplish the same thing as http://axcalated.blogspot.com/2014/10/accessing-ax-custtable-dirpatytable.html
but without requiring the user to install AX as its a webservice so technically you could implament this solution in any language.

Step 1(in AX) Create a query called CustomerInfo. This should contain the table CustTable and sub datasource table DirPartyTable. Only include the fields CustTable.AccountNum & DirPartyTable.Name so that the footprint is as small as can be.
Its a good idea to also add an order by of DirPartyTable.Name so the results are sorted A-Z. But you can sort by number as well.


Step 2(in C#) Add the following webservice to your project http://<AOSServer>:8101/DynamicsAx/Services/QueryService  (by default the port is 8101 but if you changed it during the install you would need to change it in the url as well.
This is a default/built-in webservice provided by AX and should not require you to create anything within AX in order to access it.


Step 3 (in C#) Add the following code

        //this will create a container for the customer number & name
        public class Customer
        {
            public string AccountNum { get; set; }
            public string Name { get; set; }
        }

add the following to the load method or create a new method that can be called from the load method
    


List<Customer> axList = new List<Customer>();
            DataSet dataSet;
            AXQueryService.QueryServiceClient client = new AXQueryService.QueryServiceClient();
            AXQueryService.Paging paging = null;
            //execute a static query defined with the AX AOT
            dataSet = client.ExecuteStaticQuery("CustomerInfo", ref paging);
       
     //go thru all of the results of the query and add them to your customer object list
            for (int custCounter = 0; custCounter <= dataSet.Tables[0].Rows.Count - 1; ++custCounter)
            {
         //get the current row information for the 2 tables
                DataRow custRow = dataSet.Tables["CustTable"].Rows[custCounter];
                DataRow dirPartyTableRow = dataSet.Tables["DirPartyTable.DirPartyTable"].Rows[custCounter];

  //create new customer entry
                Customer axCustomer = new Customer()
                {
                    AccountNum = custRow["AccountNum"].ToString(),
                    Name = dirPartyTableRow["Name"].ToString()
                };
  //add current customer to the overall list
                axList.Add(axCustomer);
            }
           
       
            //tie our newly created customer list to your data source
            CustomerNames.DataSource = axList;
            CustomerNames.DisplayMember = "Name";
            CustomerNames.ValueMember = "AccountNum";
            CustomerIds.DataSource = axList;
            CustomerIds.DisplayMember = "AccountNum";
            CustomerIds.ValueMember = "AccountNum";


That's it. You should now have 2 dropdowns being displayed on the form that are linked to each other.

How to get contact's email addresses from a customer or vendor

How to get  customer email addresses from all contacts associated with a customer from Company/Accounts receivable/Common/Customers/All customers/Contacts/Contact Info



Method exists on \Tables\CustTable

public container getInvoiceContacts()
{
    container invoiceEmailAddresses;
    DirPartyTable dirParty;
    LogisticsElectronicAddress electronicAddress;
    DirPartyLocation dirPartyLoc;
    ContactPerson contactPerson;
   
//find all of the customer contact email addresses that are currently active  
 while select * from contactPerson
        where contactPerson.CustAccount == this.AccountNum && contactPerson.Inactive == NoYes::No
        join  RecId from dirParty
        where dirParty.RecId == contactPerson.Party
        join Location, Type, SendInvoice, Locator FROM electronicAddress
         EXISTS JOIN Location, Party FROM dirPartyLoc
        WHERE electronicAddress.Location == dirPartyLoc.Location && dirParty.RecId==dirPartyLoc.Party
            && electronicAddress.Type == LogisticsElectronicAddressMethodType::Email
    {
        invoiceEmailAddresses += electronicAddress.Locator;
    }
    return invoiceEmailAddresses;
}



How to get vendor email addresses from all contacts associated with a vendor  from Company/Accounts payable/Common/Vendors/All vendors/Contacts/Contact Info

Method exists on \Tables\VendTable

public container getRemittanceContacts()
{
    container remittanceEmailAddresses;
    DirPartyTable dirParty;
    LogisticsElectronicAddress electronicAddress;
    DirPartyLocation dirPartyLoc;
    ContactPerson contactPerson;
   
//find all of the vendor contact email addresses that are currently active  
while select * from contactPerson
        where contactPerson.ContactForParty == this.Party && contactPerson.Inactive == NoYes::No
        join  RecId from dirParty
        where dirParty.RecId == contactPerson.Party
        join Location, Type, SendRemittance, Locator FROM electronicAddress
         EXISTS JOIN Location, Party FROM dirPartyLoc
        WHERE electronicAddress.Location == dirPartyLoc.Location && dirParty.RecId==dirPartyLoc.Party
            && electronicAddress.Type == LogisticsElectronicAddressMethodType::Email
    {
        remittanceEmailAddresses += electronicAddress.Locator;
    }
    return remittanceEmailAddresses;
}


As you can see customer & vendors operate pretty much the same except for the association of custaccount(customer) vs contactforparty(vendor) on the contactperson table.

I'm posting this since AX seems to love to use the view "DirPartyPostalAddressView" as the main listing for these contacts on the form. So it can take a little while of digging to discover the true relationship between these tables.

Unable to save. Version of [object] on the server is newer

I seem to be getting the following message on a very specific object whenever I check it out and make a modification. Unable to save. Version of <object> on the server is newer.

You usually get this message whenever someone else has modified the object since you opened MorphX/AX. However I was the one who checked this out and have tried closing out of  AX completely and I would still get the message and I know no one else is accessing this object.

I found that sometimes it would allow me to save the change but then would never show it in the Client. I would restart the client and check the AOT object but it would not have saved my change made. If I exported the object from a different environment and imported into the dev environment (where is the issue was happening) then everything from the import would save ok but it would still not allow me to manually make changes.


In order to fix this error right click (in MorphX/AOT) on the object and choose "Restore" This doesn't seem to discharge my changes but now whenever I add something to this object it seems to save it to the DB and displays correctly in the client.


According to https://msdn.microsoft.com/en-us/library/aa846291.aspx this shortcut "Reads the selected element from the database and discards the changes that were made since the last save."

Worker cannot be deleted while dependent Position worker assignments exist. Delete dependent Position worker assignments and try again

While trying to remove a worker from the HR module we got the following error and AX would not allow us to remove the worker.

"Worker cannot be deleted while dependent Position worker assignments exist. Delete dependent Position worker assignments and try again"


Currently in order to fix this you need to do the following

Step 1
Company/Human resources/Common/Organization/Positions/Positions
Find the worker name > edit the record > go to Worker assignment > End > choose a date time that has already happened unless you want to wait for the nightly job


Step 2
Company/Human resources/Common/Workers/Workers
Find the worker name > edit the record > employment (menu on left side) > Employment details > edit > change employment end date to same time as the one listed in step 1. Once you hit ok the record will be removed


I didn't get a chance to test this but executing step 2 might actually execute step 1 as well. I just don't have any more records atm that I can verify this on.

Error executing code: Insufficient memory to run script. (objects with large buffer ex: xml reponse streams)

​If you see the error "Error executing code: Insufficient memory to run script"

This means that an object/variable within AX is trying to load something into a variable that exceeds the max buffer size for a variable.



When you see this error you have 2 options to fix the issue. 1. reprogram the large object to only load sections at a time. However the bad part about this is you cant just load the large objects in chunks and then combine them into 1  because when you combine them they also can not exceed the max limit. So you would need to grab part 1/4 and display/process. Then dispose of the object and get part 2/4 then dispose and get part 3/4 if you wanted to view 1/4 then you would need to dispose of 3/4 then reload part 1/4.



However you also have option 2 which is you can up the maxbuffersize for variables. This is done via the ax config file (.axc files) that the client loads. This should not be confused with the buffer size set on an AOS server that is normally 48KB. By default AX is set to allow 4mb for this variable buffer size property however in some instances doing the "partial load" doesn't make sense so you need to bump it. For instance when returning large XML data response streams you may need to up this so you can read the response in and then parse it.



If you wish to up the size of the maxbuffersize for variables within X++ the setting that needs to be added to the config file is

maxbuffersize,Text,125



where 125 is the size in MB of the new max buffer size. You can make it 0 which will allow it to be unlimited however that is highly not recommend.

You can also add the change via the registry however in 2012 R2 I did not have much luck getting the client to respect it.
 
Client Registry
Key name: [HKCU\Software\Microsoft\Dynamics\6.0\Configuration\]
Value name: maxbuffersize
Value type: REG_SZ
Value: 125

Cannot create a record in tablename. The record already exists. / Get new recid for a table

I've seen this error a couple times within the past week after we migrated our data from PROD into DEV

"Cannot create a record in <table description> (<table name>). The record already exists."

What I was finding is the index was ok and everything was unique but for some odd reason we kept getting this error, even though I did a search on the data that was being inserted and it did not exist like the system was telling us.

What I figured out is sometimes the recid count gets messed up with transferring data and you need to reset it to a value greater than the max rec id of the current table. Here are the steps to fix it.

1. Create a Job to get the table id of the table having problems inserting data

static void GetTableIdFromNameJob(Args _args)
{
    info(strFmt("%1", tableName2id("<table name>")));
}

2. In sql mgt studio

select max(recid) from <table name from step 1>

3. In sql mgt studio
Edit table SYSTEMSEQUENCES
Select the record where tabid = '<table id from info box in step 1>
Take the max id from step 2 and add 100 to it to be safe and update the field  'nextval' from systemsequences on the tableid you have selected.

4. Restart AOS (the changes will not take affect until you restart the AOS)

AX client crashes whenever you check out a label file that is in version control/TFS (Dynamics AX 2012 R2)

I ran across something funky today where whenever we tried to check out or modify a label file it would crash the AX Client.

The following is present within the event viewer logs

 Application: Ax32.exe
Framework Version: v4.0.30319
Description: The process was terminated due to an unhandled exception.
Exception Info: System.AccessViolationException
Stack:
   at Microsoft.Dynamics.Kernel.Client.ActionPaneInterop.ClickEventHelper.Clicked(System.Object, System.EventArgs)
   at Microsoft.Dynamics.Framework.UI.WinForms.Controls.ActionItem.OnButtonClick()
   at Microsoft.Dynamics.Framework.UI.WinForms.Controls.ActionButton.OnMouseUp(System.Windows.Forms.MouseEventArgs)
   at System.Windows.Forms.Control.WmMouseUp(System.Windows.Forms.Message ByRef, System.Windows.Forms.MouseButtons, Int32)
   at System.Windows.Forms.Control.WndProc(System.Windows.Forms.Message ByRef)
   at System.Windows.Forms.Control+ControlNativeWindow.OnMessage(System.Windows.Forms.Message ByRef)
   at System.Windows.Forms.Control+ControlNativeWindow.WndProc(System.Windows.Forms.Message ByRef)
   at System.Windows.Forms.NativeWindow.Callback(IntPtr, Int32, IntPtr, IntPtr)
   at <Module>._wWinMainCRTStartup()


It appears there is a KB/Hot fix for this issue listed under
KB 2902776, Bug Id 773311: Access violations when synchronizing, checking in or checking out with TFS


It will have you modify the following objects
\Class\SysLabelFile\ & \tables\SysVersionControlSynchronizeLog

Take a look within Lifecycle services for the official code changes on these objects. Its a pretty quick fix though.