Monday, April 10, 2017

InMemory and TempDB in joins on forms

One of the tricky point of the previously announced project for AIF external code mapping and Reverse view is the usage of temporary tables in the latter's form.

As we know there are two different temporary table types in AX 2012: InMemory and TempDB.

In my project I needed to join a temporary table with internal values to the regular table with external codes.

"Cannot select a record in xxxx.
InMemory temporary tables must be the outer tables when they are joined to a TempDB table or permanent table."

How to avoid this famous error?

Brief, I need to populate the temp buffer at the server side and then to pass it to the form data source.

The easiest way to understand how they are processed by AX is switching the type for and debugging then the Reverse view form opening in Init and temporary table populating method. Seeing is believing.

This is how Reverse View regular and temp table are joined.





Let's start with InMemoryType. The form considers it as the client tier based table.



In the server based populating method, we need to instantiate the local temp buffer and then set it to the argument buffer via setTmpData() method so that it was still on the server tier. Old school.


Then the same approach to set it to the caller data source. Our temp InMemory table is still on the server and can be joined.


Now, change the table type to TempDB and debug it again. As you can see the form determines it as the server based object.


This time we need to insert new records directly to the argument buffer so that it could be linked to the caller form data source via linkPhysicalTableInstance() method.



If do not have any special reason, the TempDB is recommended to use.




AIF Many to One External Codes Value Mapping and Reverse View Extension

I do not see any reason why we are not allowed to map many external codes to one internal for AIF inbound port value mapping.

In fact, this is just a question of one additional table, which can be easily created as a copy of the exting one, and a slight change to three classes and AIF related forms.


For the demo's sake it is implemented for Customer and Units only, but you can add the same to any AX externally enabled table.

 Please download and use this extension to the standard AIF in AX 2012.

Another valuable feature of this project is the External codes Reverse View.


Any time I saw something like depicted, I dreamt to have a way to look into this halo in reverse.



The Reverse view enables you to find any existing relation between 1:1 and N:1 external and internal codes.

Filter by any column, export them to Excel, and go directly to the internal table by Edit or double-clicking.

Besides aforementioned, there are examples of using the powerfull AX objects, like:
- table map;
- Data Dictionary operations for scalability;
- set;
- InMemory and TempDB usage in Form and joins.


Monday, February 13, 2017

Get Model element type name from its ID

Just to cover a gap in system data, you can use the following code to get a model element type name based on its ID in AX 2012. Thanks to Martin Drab!

static void tmxElementTypes(Args _args)
{
    int             elementTypeId = 300;
    str             elementTypeName;
    
    switch (elementTypeId)
    {
        case 1 : elementTypeName = 'DisplayTool'; break;
        case 2 : elementTypeName = 'OutputTool'; break;
        case 3 : elementTypeName = 'ActionTool'; break;
        case 4 : elementTypeName = 'Macro'; break;
        case 5 : elementTypeName = 'Job'; break;
        case 6 : elementTypeName = 'WorkflowProcess'; break;
        case 7 : elementTypeName = 'AdminUserSetup'; break;
        case 8 : elementTypeName = 'SysXal'; break;
        case 9 : elementTypeName = 'UserSetupQuery'; break;
        case 10 : elementTypeName = 'LegacyMenu'; break;
        case 11 : elementTypeName = 'Form'; break;
        case 12 : elementTypeName = 'TableInstanceMethod'; break;
        case 13 : elementTypeName = 'ClassStaticMethod'; break;
        case 14 : elementTypeName = 'ClassInstanceMethod'; break;
        case 15 : elementTypeName = 'LicenseCode'; break;
        case 16 : elementTypeName = 'Menu'; break;
        case 17 : elementTypeName = 'UserMenu'; break;
        case 18 : elementTypeName = 'Report'; break;
        case 19 : elementTypeName = 'ReportTemplate'; break;
        case 20 : elementTypeName = 'Query'; break;
        case 21 : elementTypeName = 'Resource'; break;
        case 22 : elementTypeName = 'TableStaticMethod'; break;
        case 23 : elementTypeName = 'ClassInternalHeader'; break;
        case 24 : elementTypeName = 'TableInternalHeader'; break;
        case 25 : elementTypeName = 'TableRelation'; break;
        case 26 : elementTypeName = 'TableMap'; break;
        case 27 : elementTypeName = 'ReportSectionTemplate'; break;
        case 28 : elementTypeName = 'ViewQuery'; break;
        case 29 : elementTypeName = 'Usersetup'; break;
        case 30 : elementTypeName = 'WebMenu'; break;
        case 33 : elementTypeName = 'RESERVED33'; break;
        case 34 : elementTypeName = 'WebForm'; break;
        case 35 : elementTypeName = 'ConfigurationKey'; break;
        case 36 : elementTypeName = 'SecurityKey'; break;
        case 37 : elementTypeName = 'SharedProject'; break;
        case 38 : elementTypeName = 'PrivateProject'; break;
        case 39 : elementTypeName = 'LegacyFeatureKey'; break;
        case 40 : elementTypeName = 'Enum'; break;
        case 41 : elementTypeName = 'ExtendedType'; break;
        case 42 : elementTypeName = 'TableField'; break;
        case 43 : elementTypeName = 'TableIndex'; break;
        case 44 : elementTypeName = 'Table'; break;
        case 45 : elementTypeName = 'Class'; break;
        case 46 : elementTypeName = 'TableFieldGroup'; break;
        case 47 : elementTypeName = 'ReportUser'; break;
        case 48 : elementTypeName = 'TableCollection'; break;
        case 52 : elementTypeName = 'WebReport'; break;
        case 53 : elementTypeName = 'Reference'; break;
        case 55 : elementTypeName = 'WebUrlItem'; break;
        case 56 : elementTypeName = 'WebActionItem'; break;
        case 57 : elementTypeName = 'WebDisplayContentItem'; break;
        case 58 : elementTypeName = 'WebOutputContentItem'; break;
        case 59 : elementTypeName = 'WebletItem'; break;
        case 60 : elementTypeName = 'WebWebPart'; break;
        case 61 : elementTypeName = 'WebSiteDef'; break;
        case 62 : elementTypeName = 'WebSiteTemp'; break;
        case 63 : elementTypeName = 'WebPageDef'; break;
        case 64 : elementTypeName = 'WebStaticFile'; break;
        case 66 : elementTypeName = 'Perspective'; break;
        case 67 : elementTypeName = 'WebModule'; break;
        case 68 : elementTypeName = 'WorkflowType'; break;
        case 69 : elementTypeName = 'WorkflowTask'; break;
        case 70 : elementTypeName = 'WorkflowApproval'; break;
        case 71 : elementTypeName = 'WorkflowCategory'; break;
        case 72 : elementTypeName = 'DataSet'; break;
        case 73 : elementTypeName = 'WebControl'; break;
        case 74 : elementTypeName = 'WebSourceFile'; break;
        case 75 : elementTypeName = 'WebManagedContentItem'; break;
        case 76 : elementTypeName = 'Service'; break;
        case 77 : elementTypeName = 'CompositeQueryNode'; break;
        case 78 : elementTypeName = 'WebListDef'; break;
        case 79 : elementTypeName = 'ReportLibrary'; break;
        case 80 : elementTypeName = 'SecurityTask'; break;
        case 81 : elementTypeName = 'InfoPart'; break;
        case 82 : elementTypeName = 'FormPart'; break;
        case 83 : elementTypeName = 'PartReference'; break;
        case 85 : elementTypeName = 'SSRSReport'; break;
        case 87 : elementTypeName = 'SSRSReportLayoutTemplate'; break;
        case 88 : elementTypeName = 'SSRSReportListStyleTemplate'; break;
        case 89 : elementTypeName = 'SSRSReportMatrixStyleTemplate'; break;
        case 90 : elementTypeName = 'SSRSReportPieChartStyleTemplate'; break;
        case 91 : elementTypeName = 'SSRSReportTableStyleTemplate'; break;
        case 92 : elementTypeName = 'SSRSReportXYChartStyleTemplate'; break;
        case 93 : elementTypeName = 'SSRSReportDataSource'; break;
        case 94 : elementTypeName = 'SSRSReportImage'; break;
        case 95 : elementTypeName = 'WorkflowAutomatedTask'; break;
        case 96 : elementTypeName = 'Event'; break;
        case 97 : elementTypeName = 'EventHandler'; break;
        case 98 : elementTypeName = 'Cue'; break;
        case 99 : elementTypeName = 'CueGroup'; break;
        case 100 : elementTypeName = 'CueReference'; break;
        case 101 : elementTypeName = 'DocSet'; break;
        case 104 : elementTypeName = 'VisualStudioProjectFolder'; break;
        case 105 : elementTypeName = 'VisualStudioProjectFile'; break;
        case 106 : elementTypeName = 'InfoPartLayout'; break;
        case 107 : elementTypeName = 'InfoPartGroup'; break;
        case 108 : elementTypeName = 'InfoPartField'; break;
        case 109 : elementTypeName = 'InfoPartAction'; break;
        case 110 : elementTypeName = 'MenuItem'; break;
        case 111 : elementTypeName = 'MenuSeparator'; break;
        case 112 : elementTypeName = 'MenuReference'; break;
        case 113 : elementTypeName = 'TableFullTextIndex'; break;
        case 114 : elementTypeName = 'VisualStudioProjectType'; break;
        case 115 : elementTypeName = 'SecCodePermission'; break;
        case 116 : elementTypeName = 'EventHandlerMethod'; break;
        case 117 : elementTypeName = 'LabelFile'; break;
        case 118 : elementTypeName = 'LabelFileLanguage'; break;
        case 119 : elementTypeName = 'SecPolicy'; break;
        case 120 : elementTypeName = 'FormMethod'; break;
        case 121 : elementTypeName = 'VisualStudioProjectLink'; break;
        case 122 : elementTypeName = 'SubMenu'; break;
        case 123 : elementTypeName = 'SubWebMenu'; break;
        case 124 : elementTypeName = 'SubWebModule'; break;
        case 125 : elementTypeName = 'FormDesign'; break;
        case 126 : elementTypeName = 'FormControl'; break;
        case 127 : elementTypeName = 'VSProject_AXModel'; break;
        case 128 : elementTypeName = 'VSProject_CSharp'; break;
        case 129 : elementTypeName = 'VSProject_VB'; break;
        case 130 : elementTypeName = 'VSProject_Web'; break;
        case 131 : elementTypeName = 'VSProject_Analysis'; break;
        case 133 : elementTypeName = 'SecRole'; break;
        case 134 : elementTypeName = 'SecPrivilege'; break;
        case 135 : elementTypeName = 'SecDuty'; break;
        case 136 : elementTypeName = 'SecProcessCycle'; break;
        case 137 : elementTypeName = 'ServiceGroup'; break;
        case 138 : elementTypeName = 'ServiceNodeReference'; break;
        case 139 : elementTypeName = 'WorkflowHierarchyProvider'; break;
        case 140 : elementTypeName = 'WorkflowParticipantProvider'; break;
        case 141 : elementTypeName = 'WorkflowQueueProvider'; break;
        case 142 : elementTypeName = 'WorkflowDueDateProvider'; break;
        case 143 : elementTypeName = 'FormDataSources'; break;
        case 144 : elementTypeName = 'SecurityPermissionSet'; break;
        default :
            elementTypeName = 'Error: not implemented';
            warning(strFmt("Element type %1 : %2", elementTypeId, elementTypeName));
    }
    info(strFmt("Element type %1 : %2", elementTypeId, elementTypeName));
}

Thursday, January 19, 2017

How to restore a hidden Fact box in the form without File and View menu options

There is no easy way out if you hid fact boxes in a modal form, like a wizard, for example, which has neither File nor View menu option.


Personalise/Reset won't help with it.




This is a trick to make hidden fact boxes visible again.
Take the form name in question.


Then go to your user's usage data and delete the records selected by the name in second "element name" column.


Now, welcome back your fact boxes!

Wednesday, January 11, 2017

Heavy form performance issue

One of my client complained about very slow opening of one form, which they use as a core functionality for supporting customer service calls. This form is really heavy equipped with many form controls, like grids, and dozen of linked data sources.

The behaviour was really strange: for some users it worked more or less fast, say, 3-6 secondes, for certains, on the contrary, it could take up to 25-30 secondes.

All of them were assigned to System admin role. No special security, like, RLS or whatsoever was implemented.

Trace Parser and Code Profiler showed that the sequence of the execution flow was the same; however, almost all of the methods executed as twice as longer for the"slow" user than for the "rapid" one.

The strangest thing was in the fact that Trace Parser showed inclusive execution time which was not the sum of all its including methods: evidently something happened behind the scene.

Another funny thing, after clearing the "slow" user's cache files, the first run was slow, which is normal, the second run was incredibly fast, as much fast as for the "rapid" users, but starting the third run it fell down to slowliness.

The key was actually in the small option as it explained on the article Configure client performance options:

Preload complex forms

By default, forms that include more than 80 controls are preloaded and added to a preload cache. When the user opens a form, the system checks the preload cache for a preloaded version of the form. If a preloaded version is found, the system completes the initialization process and loads the form. Not all forms are preloaded. If resource limitations are met, the system starts to remove forms from the cache, starting with the forms that were least recently used. Forms such as lookups, parts, preview panes, and system forms are excluded from this mechanism.
You can turn off preloading by using the following methods:
  • To turn off preloading for the whole system, in the Client performance options form, clear the Form pre-loading enabled (requires a client restart) option.
  • To turn off preloading for a form, follow one of these steps:
    • Set form argument allowUseOfPreloadedForm for the X++ method to true.
    • Set the Form.AllowPreLoading metadata property to No.

So, once I changed the latter for this heavy form that some users personalized, it started to open very fast for all of them.

I want to thank:

All my colleagues at work;
Brandon Wiese;
Brandon Ahmad;
Freeangel and all other members from this thread (in Russian).



Monday, December 5, 2016

Mass update Tracking dimension group

Just a job that makes the subject in three big steps.



Let's say we have a bunch of items for which we set up a wrong Tracking dimension group, 'Multi', for example.

First, it empties the current value in Product Tracking dimension for those with 'Multi' in their items, making possible to change the value in Item Tracking dimension.


Then it set up the new value, for example, 'MultiS' for items and, as the third step, for related Products.

If you press No in the dialog, it just shows the current values without updating anything. So you can estimate the scale of eventual changes.


Please make appropriate customization and use it at your own risk. It calls update().

By the way, if you need to change values en masse without calling update(), please use Universal Field Changer, which could be imported as one class that makes everything possible in any table.

Thursday, December 1, 2016

Access denied to method processReport in class whateverReportDP

If after all sorts of checking your security settings you still have the subject error, please just re-import the problematic SSRS report.

As explained by Nicolas GRANJON, under the hood, I quote, re-importing the report (re)generate data in the ModelSecurityPermission table of the model database, especially the permissions links between the report and the tables that it uses, and the data provider class.