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!");
}

HOWTO: Save SSRS Report to Excel from X++

Ussally you have to save a report to an output file for example store it or send it over mail.

Here an example to save a report into Excel format

SrsReportRunController          controller = new SrsReportRunController();
ReportContract   contract = new ReportContract();

controller.parmReportName(ssrsReportStr(ReportName, Report));

controller.parmShowDialog(false);
controller.parmReportContract().parmPrintSettings().printMediumType(SRSPrintMediumType::File);
controller.parmReportContract().parmPrintSettings().fileFormat(SRSReportFileFormat::Excel);
controller.parmReportContract().parmPrintSettings().overwriteFile(true);
controller.parmReportContract().parmPrintSettings().fileName(fileName);

controller.parmReportContract().parmRdpContract(contract);
contract.parmXYZ(_parmXYZValue);

controller.startOperation();

HOWTO: Retrive list of locator of LogisticsElectronicAddress by DirParty and Role

The AX 2012 method findElectronicAddressByRole is deprecated on lasted version.

On the lasted version you can define custom roles and use these roles for define custom elettronic address. This mean you cannot more use standard method for retrive address

This methods retrive the list of locators and retrive a container:

LogisticsLocationRoleName logisticsLocationRoleName;

container results;

int i;

;

logisticsLocationRoleName = "CustomRole";

results = DirParty::electronicAddressLocatorsByRole(

                [PartyId],

                [LogisticsElectronicAddressMethodType],

                logisticsLocationRoleName);

for(i=1;i<=conLen(results);i++)

{

                info(conPeek(results, i));

}

HOWTO: Dynamics AX Custom Lookup

As a Dynamics AX developer, you'll often have to perform custom lookups. Meaning that the user may only select records in another table depending on some condition on the form, or elsewhere.


There are two ways of creating custom lookups. I'll show the easiest here, and maybe show the other way in the future.
So if you're thinking you'll have to create a new form and all that, forget it, you won't. All you have to do is write some pieces of code.


First let's create our lookup method directly on the table. You can check other tables for these special lookup methods. They appear in lots of tables. Take for example the table ContactPerson. It has the following custom lookup methods by default:

• lookupActionContactPerson

• lookupCustContactPerson

• lookupDirContactPerson

• lookupDirContactPersionId

• lookupVendContactPerson

Now, to create a custom lookup, we'll use the class SysTableLookup as it provides us a set of methods to do so.

Here's our method, to be created directly on the table DummyTable, with some dummy data and names.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35

public client static void lookupDummyTable(FormStringControl _ctrl,                                            MyFilterEDT _filter) {         // Intantiantes a SysTableLookup object telling it which control activated the lookup, and     // what table should be displayed inside the lookup form.     SysTableLookup sysTableLookup = SysTableLookup::newParameters(tablenum(DummyTable), _ctrl);     Query query = new Query(); // This query will be used by the lookup form.     QueryBuildDataSource qbds;     ;     // The "addLookupField" method adds the the fields that will be shown inside the lookup's grid.     // The second, boolean parameter indicates that this field will be returned from the lookup when a     // record is selected. In this case, we'll return our DummyPK.     sysTableLookup.addLookupField(fieldnum(DummyTable, DummyPK), true);     sysTableLookup.addLookupField(fieldnum(DummyTable, Field1));     sysTableLookup.addLookupField(fieldnum(DummyTable, Field2));     sysTableLookup.addLookupField(fieldnum(DummyTable, Field3));     // Using our dummy table as a DataSource Table.     qbds = query.addDataSource(tablenum(DummyTable));     // This is the key part, what we want to filter to be displayed in the grid.     qbds.addRange(fieldnum(DummyTable, MyFilterColumn)).value(_filter);     // Passes the query to the sysTableLookup.     sysTableLookup.parmQuery(query);     // Activates the lookup.     sysTableLookup.performFormLookup(); }

With that little piece of code, Dynamics AX will already display a lookup form with the table you've specified as a DataSource, displaying the fields you specified with the method addLookupField and following the results criteria of the Query you've passed to it.

Now all we have to do is actually call our lookup method, by overriding the lookup of the correct field in our Form's Data Source:

1 2 3 4 5 6

public void lookup(FormControl _formControl, str _filterStr) {     ;     DummyTable::lookupDummyTable(_formControl, "SomeFilter"); }

And that's it! The lookup will be performed. But there's one additional step we'll have to take here...

When the user tries to select our DummyTable reference, Dynamics AX will display the results of the query, based on the filter we've created. So the user will only be able to see what we want him to see, and so he'll only be able to select what we want. The problem is that if he types in an existing record for the DummyTable in the control, even if does not get shown in our lookup, Dynamics AX will accept that. Remember: we're only limiting what the user may see and select for that field, but we're not limiting what he can actually type in the control. So if we don't validate it somehow, Dynamics AX will run the standard validation, which will only check if a record exists for that EDT and so on. To avoid that the user does in fact type in something we don't want, we'll have to override the validate method for our field in our Data Source. The simplest logic is to check if the DummyTable record the user typed in does attend the filter we've specified:

1 2 3 4 5 6 7 8 9 10 11

public boolean validate() {     DummyTable    dummyTable;     ;     dummyTable = DummyTable::find(currentRecord.DummyTableRefField);     return dummyTable.MyFilterColumn == "SomeFilter";       }

So if the user simply types in for a record that does not attend our filter, Dynamics AX won't let the user save that value.

This is the easiest way to effectively implement a custom lookup and validate the user input in case he doesn't select the record from the lookup. For this example I used a literal filter, a string. But in most cases, you'll have to filter according to what the user selected for another field in your form. Things should usually be dynamic.

Reposted from: http://devexpp.blogspot.it/2012/02/dynamics-ax-custom-lookup.html