X++: Find a shared project by name

This code allow you to search a project by name

ProjectListNode projectListNode;
TreeNodeIterator treeNodeIterator;
TreeNode treeNode;
str projectName;
;
projectName = "*xxx*";
projectListNode = SysTreeNode::getSharedProject();
treeNodeIterator = projectListNode.AOTiterator();
treeNode = treeNodeIterator.next();
while(treeNode)
{
  if (treeNode.AOTname() like projectName)
    info(treeNode.AOTname());
  treeNode = treeNodeIterator.next();
}

HOWTO: Turning off the Synchronize Database form on Dynamics AX 2012

It is possible that the Synchronize Database form is now showing up unexpectedly during some of your scripts, if so you can programmatically set it on or off using the SysSqlSync ShowSysSqlSync global cache setting, here is a job that will turn off the form and automatically synchronize:

static void ShowSysSqlSync(Args _args)

{

    SysGlobalCache gc;

    str owner = 'SysSqlSync';

    str key   = 'ShowSysSqlSync';

    boolean showEnabled = false; //set to true to enable.

    ;

    gc = appl.globalCache();

    gc.set(owner, key, showEnabled);

    info(strfmt('Show SqlSync: %1', gc.get(owner, key))); 

}

Check more info on: http://blogs.msdn.com/b/daxis/archive/2009/04/26/synchronization-enhancements-in-dynamics-ax-2009.aspx

TIP: InventSum don't update ModifiedDateTime and ModifedBy fields

On my project i need to know how InventSum are updated , but when i activate the modified datetime the field this has no be automatic updated.
Than, thanks to this post http://blogs.technet.com/b/dynamicsaxse/archive/2010/12/29/fields-modifieddatetime-and-modifiedby-on-table-inventsum.aspx, i found a good solution.

Warning:, it's a kernel solution, than use it with care.

The InventSum update is made by a class, the InventUpdateOnHand
On this classe there is a method that build a SQL statement for the UPDATE of InventSum table.
I made a customization here and add on the UPDATE statement the MODIFIED fields.
There customization are made only for SQL server database.

Here the XPO of customization:
https://drive.google.com/open?id=0B3gigtHeLE6gY1pLVEVNcUpvLTA

On the method sqlUpdateStr you can comment out the IF clause to debug it on runtime, remember to comment out on production env.

HOWTO: Detecting default values

So I haven’t posted in a long time. I finally fixed some technical issues on the blog and upgraded to a recent version of WordPress. Let’s start with a quick tip to warm up
As you know X++ supports methods with default values. In Ax it’s possible to detect when such a default value was used at runtime with prmIsDefault(). To see this for yourself, create a class with a method like this:

void someMethod(int i = 1) { ;   if (prmIsDefault(i)) { info('Default'); } else { info('Value given'); } }

And call it from another method.

o.someMethod(); o.someMethod(1);

Even though the value is the same in both cases a different info message will be displayed.
Now you may be wondering how this can be useful. Some scenarios:

• Avoiding expensive operations if the default value was used
• Avoiding unwanted initializations
• Dealing with customizations that require extra parameters in standard Ax classes

If you do a search for prmIsDefault() in the AOT you will see it’s used often. Classes with the Ax prefix are the most obvious example. Because getters and setters are rolled into a single method it’s necessary to know if the method was used as a getter or a setter and keep track of modified fields.

Reposted from http://sysdictcoder.com/detecting-default-values/

HOWTO: Get EcoResColor name by InventColorId

Step 1: Retrive ecoResProductMasterColor

select ecoResProductMasterColorwhere ecoResProductMasterColor.Color == EcoResColor::findByName(inventDim.InventColorId).RecId
&& ecoResProductMasterColor.ColorProductMaster == inventTable.Product
&& ecoResProductMasterColor.ColorProductDimensionAttribute == EcoResProductDimensionAttribute::inventDimFieldId2DimensionAttributeRecId(fieldNum(InventDim, InventColorId))
;

Step 2: Get color translation

EcoResProductMasterDimValueTranslation::findByProductMasterDimValLanguage(ecoResProductMasterColor.RecId, 'it').Name 

HOWTO: Check is a field is mapped to a field on a Map

If you add a new field on a Map, but you cannot map the field to all tables you need to check, on map common methods, if the field is mapped on the table you are going to update, if you don't to it the system raise an error.

For to this you can use this function:
https://msdn.microsoft.com/en-us/library/global.mappingexists.aspx

An example of use is:

if (Global::mappingExists(tableNum(SalesPurchLine), this.TableId, fieldNum(SalesPurchLine, MYFIELD)))
  this.MYFIELD = AVALUE;


Check MSDN docs for details of the mappingExists global function

HOWTO: Merge ledger and financial dimensions using X++ code

To merge two or more financial dimensions use this script.

DimensionDefaultingService::serviceCreateLedgerDimension(ledgerDimension,customerDefaultDimension);

You can merge more  than 2 dimension, infact you case specify some default dimensions to merge

More details and a real case implemetation detail on: http://community.dynamics.com/ax/b/dynamicsaxconcoction/archive/2014/07/25/ax-2012-merge-ledger-and-financial-dimensions-using-x-code.aspx

ISSUE: Standard view is displayed as table in AOT on AX2012

Today I found a strange error in the development environment, do not know what was the triggering event but a standard view become visible under table node and the syncronization failed.

From research I found this post:
http://community.dynamics.com/ax/f/33/t/118864.aspx

and then another post with a more in-depth: http://community.dynamics.com /ax/f/33/p/119471/249240.aspx?WT.mc_id=ForumPost#249240

In my case I solved using a direct SQL patch way.

The solution is to restore the table properties on model database coping it from another standard model store or as in my case the production env database.

1. Stopped the dev AOS
2. Removed from the table SQLDictionary any object references
3. Removed from the database the view refers to object (check if there are a table also)
4. Check the object properties on the model database:

SELECT m.ElementType, m.Name, m.AxId, md.LayerId, manifest.DisplayName, md.Properties as Metadata
              FROM [dbo].ModelElement AS m
              INNER JOIN [dbo].ModelElementData AS md
                             ON m.ElementHandle = md.ElementHandle
              INNER JOIN [dbo].ModelManifest AS manifest
                             ON md.ModelId = manifest.ModelId
              AND m.ElementType = 44 AND m.Name = 'DirPartyLookupGridView'

5. Check the object properties on the other model database:

SELECT m.ElementType, m.Name, m.AxId, md.LayerId, manifest.DisplayName, md.Properties as Metadata
              FROM MicrosoftDynamicsAXBaseline.[dbo].ModelElement AS m
              INNER JOIN MicrosoftDynamicsAXBaseline.[dbo].ModelElementData AS md
                             ON m.ElementHandle = md.ElementHandle
              INNER JOIN MicrosoftDynamicsAXBaseline.[dbo].ModelManifest AS manifest
                             ON md.ModelId = manifest.ModelId
              AND m.ElementType = 44 AND m.Name = 'DirPartyLookupGridView'
6. Update the object properties coping it from the other model database:

Update md Set md.Properties = md_base.Properties
FROM [dbo].ModelElement AS m
              INNER JOIN [dbo].ModelElementData AS md
                             ON m.ElementHandle = md.ElementHandle
              INNER JOIN MicrosoftDynamicsAXBaseline.[dbo].ModelElement AS m_base
                             ON m.ElementType = m_base.ElementType AND m.Name = m_base.Name
              INNER JOIN MicrosoftDynamicsAXBaseline.[dbo].ModelElementData AS md_base
                             ON m_base.ElementHandle = md_base.ElementHandle
AND m_base.ElementType = 44 AND m_base.Name = 'DirPartyLookupGridView' AND md_base.LayerId = 0

WARNING: Use the script on your own risk and evalute the impact on you env and warranty

HOWTO: Preview parts and surrogate keys in Ax2012

This post is a quick walk-through of surrogate keys, replacement keys, and field preview parts, which are new concepts in Ax2012.

The requirement used in this example is as follows:

• Have a new field on the sales order header called 'Priority'.
• This should point to a user-configurable table, containing a priority code and description.

Before we start there are a couple of terms you need to understand:

• Natural key. Think of this as the primary key that makes the most sense. eg CustTable.AccountNum and InventTable.ItemID. We can ignore the effect of DataAreaID for non-shared tables for now.
• Surrogate key. The surrogate key in database terms refers to a field that also uniquely identifies a record, but isn't a natural selector. When looking at Ax, this is the RecID. In other systems this could be a sequential number, or GUID. Typcially, it's something created by the database itself, like an identity column, however in Ax it's managed by the kernel.
• Primary key. The unique primary key should point to either the natural key, or the surrogate key.
• Clustered index. This affects the physical layout of records in the database. This doesn't have any real functional impact, but you do need to be very careful when selecting the clustered index as it can have a serious effect on performance if setup incorrectly.

These aren't new concepts - Wikipedia has tonnes of information on general database theory. One of the main changes in Ax2012 is that it more heavily promotes the use of the surrogate keys when relating tables. This is something that was always used in Ax, but more often when we had general-purpose tables, such as document-handling entries/misc. charge lines, that pointed to different types of records. Now you'll find that in a lot of places when two tables are related it's by the RecID instead of the natural key.

Ax also has the concept of the Replacement key. This is used to indicate which fields are displayed on the UI, regardless of the table's primary key. 

Creating the objects

First we create the basic datatype and table structure:

• Create an extended data type (string) called SalesPriorityID. This will be the priority 'code' displayed on forms.
• Create table SalesPriority.
• Add types SalesPriorityID and Description to the table.
• Create index SalesPriorityIdx containing field SalesPriorityID. Set AllowDuplicates to No, and AlternareKey to Yes. Note than an index cannot be designated as a table's alternate key unless it's unique.
• In the table properties, you'll notice that the PrimaryIndex and ClusterIndex have already been set to SurrogateKey. You'll also notice that the CreateRecIdIndex table is set to Yes and locked for editing. 
• Set the ReplacementKey to SalesPriorityIdx. This indicates to Ax that even though our primary key is SurrogateKey (automatic index on RecID), we want the priority code displayed on forms. 

Now we have a basic table with a primary key (on RecID), and unique replacement key (on SalesPriorityID). To reference this table using the RecID, we need a new extended data type:

•  Create an extended data type (Int64) called SalesPriorityRefRecID. Make sure this extends RefRecID.
• Set the ReferenceTable property to SalesPriority.
• Under the Table References node, set it so that SalesPriorityRefRecID = = SalesPriority.RecID.

Don't forget to extend RefRecID. It looks like if you forget to do this, the replacement key functionality doesn't work correctly, even if the data-type is Int64. 

So we now have a table, and an extended data type that references it, via the RecID. All we have to do is drag this field onto SalesTable. You'll notice that when we do this, Ax prompts you to automatically create a relationship based on the EDT reference. Click yes. Rename the field to SalesPriority.

The field can now be dragged from the form data-source onto the design as normal. You'll see that instead of adding an Int64 control, it adds a reference, since Ax has determined the relations automatically. When a reference group is shown on the form, it will display the value of the alternate/replacement key instead of the underlying RecID pointing to the SalesPriority table. I added the field to the 'Status' group, shown below:

In the underlying SalesTable record, the field SalesPriority points to SalesPriority.RecID, but displays the value of SalesPriorityID since it's contained in the nominated Replacement key.

Adding a preview part

I won't go into too much detail here, but the idea is to add a preview to the field, which acts as a sort of extended tool-tip:

The basic steps are:

• Create form SalesPriorityPreview. Add table SalesPriority as a datasource, setting allow edit/add/delete to false. Add fields directly onto the design.
• Create FormPart of the same name and set the Form property.
• Create a display menu-item of the same name, pointing to the FormPart.
• On the SalesPriority table, set property PreviewPartRef to the menu item.

Now when you hover the priority value on the sales order form, you'll see your preview form popup automatically.

There's plenty of information on this - Another good post on the subject can be read here, and of course the MSDN.

Thanks to: http://axinternals.blogspot.it/2011/10/preview-parts-and-surrogate-keys-in.html

ISSUE: SSRS MetdataException: An exception of type MetdataException has occurred while performing the operation

If you receive the following error when running an SSRS report –
“An exception of type MetdataException has occurred while performing the operation.”
– the issue most likely has to do with the AX datasource metadata.  One such possible cause is an incomplete definition of a relationship on a table.  For example, we ran into this issue when printing the Project Invoice Journals report.  The report was not customized, but it turned out that the datasource table was customized by a previous implementation partner.  A table relation was added to the ProjInvoiceJour table, which contained no lines.  Fixing this relationship resolves the metadata issue.

In my specific case i have a index on a table without any fields definied

Thanks to: https://wolftekdynamicsaxblog.wordpress.com/2013/07/26/ssrs-metdataexception/

HOWTO: Enable remote errors on SSRS

Enable remote errors for SSRS is very simple:
The setting is stored in the table named ConfigurationInfo on the report server database.
Just open the table and set Enable Remote Errors to true.
After that, restart SSRS service

HOWTO: DIXF – How to regenerate target entities

Sample script to regenerate target entity mapping from code

static void regenerateDMFTargetMapping(Args _args)
{
    DMFTargetXMLToEntityMap     dmfTargetXMLToEntityMap;
    DMFEntity                   dmfEntity;

    // delete specified target Mapping
    ttsBegin;
        delete_from dmfTargetXMLToEntityMap where dmfTargetXMLToEntityMap.Entity = <EntityName>;
    ttsCommit;

    // Regenerate Target Mapping for specified Entity
    while select dmfEntity where dmfEntity.EntityName = <EntityName>
    {
        DMFTargetXMLToEntityMap::generateMapping(dmfEntity);
    }

    info("All Done!");
}