For SQL experts, the query tools available in the AOT for analysing data are pretty rudimentary. The standard table-browser tool in the AOT doesn’t even come close to what you can do with TSQL within SQL-Managament-Studio (SSMS).
 
The tools in the AOT are off course limited by the standards of the X++ query language which doesn’t allow you to do some of the more advanced TSQL language contructs like “table-aliasing”, “data-type-casting”, “index-targeting”, “subqueries” or “common-table-expressions”. Seriously, Microsoft, some catching up is required here!
 
I have found that it is genuinely faster to find data problems using TSQL. The biggest problem is that this approach bypasses the Dynamics framework (at every level). You have access to raw data and this data is obfusticated by “bigints” everywhere (RefRecId’s for foreign keys and Integers for enumerated types). This doesn’t help the problem analyser to understand the data very easily.
 
To overcome this problem you need to construct a series of views for the core tables that you need to analyse. For this article, I will create a friendly view of the [ProjTable] table.
 
The first thing you need to do is extract all the ENUMS from the data dictionary and construct a “user-friendly” table from it. The following X++ job will go through all the Base-Enums in the AOT and populate a user-defined table with the metadata:
 
X++
static void PopulateEnums(Args _args)
{
    Dictionary      objDictionary = new Dictionary();
    DictEnum        objDictEnum;
    Enums           objEnums;
    int             counter;
    boolean         found = true;
    ;
    delete_from objEnums;
    objDictEnum = objDictionary.enumObject(objDictionary.enumNext(0));
    while (found)
    {
        objDictEnum = objDictionary.enumObject(objDictionary.enumNext(objDictEnum.id()));
        if (objDictEnum == null)
        {
            found = false;
        }
        else
        {
            print objDictEnum.name();
            for(counter = 0; counter < objDictEnum.values(); counter ++)
            {
                ttsBegin;
                try
                {
                    objEnums.EnumName = subStr(objDictEnum.name(), 1, 50);
                    objEnums.EnumValue = objDictEnum.index2Value(counter);
                    objEnums.EnumLabel = subStr(objDictEnum.index2Label(counter), 1, 100);
                    objEnums.insert();
                    ttsCommit;
                }
                catch
                {
                    ttsAbort;
                    info("Unable to add Enum: " + objDictEnum.name() + ", Value: " + num2str(objDictEnum.index2Value(counter), 0, 0, 1, 0));
                }
            }
        }
    }
    pause;
}
 
 

·         Make sure this table has a unique-index defined on the [EnumName], [EnumValue] combination.

 
Now you can switch to SSMS and analyse the raw data by translating the Base-Enums to their label equivalents using subquery-syntax:
 
TSQL
 
IF OBJECT_ID ('dbo.PROJTABLEVIEW', 'view') IS NOT NULL
DROP VIEW dbo.PROJTABLEVIEW;
GO
CREATE VIEW dbo.PROJTABLEVIEW
AS
 
SELECT
       a.PROJGROUPID,
       a.PROJID,
       a.NAME,
       a.CREATED,
       a.STARTDATE,
       a.ENDDATE,
       a.CUSTACCOUNT,
       a.PROJINVOICEPROJID,
       a.PARENTID,
       a.TAXGROUPID,
       (SELECT TOP 1 ENUMLABEL FROM dbo.ENUMS x WHERE x.ENUMNAME = 'ProjStatus' AND x.ENUMVALUE = a.STATUS) AS STATUS,
       (SELECT TOP 1 ENUMLABEL FROM dbo.ENUMS x WHERE x.ENUMNAME = 'ProjType' AND x.ENUMVALUE = a.TYPE) AS TYPE,
       b.CURRENCYID,
       a.DATAAREAID,
       a.RECVERSION,
       a.RECID
FROM
       dbo.PROJTABLE a
              INNER JOIN dbo.PROJINVOICETABLE b ON a.PROJINVOICEPROJID = b.PROJINVOICEPROJID AND a.DATAAREAID = b.DATAAREAID
 
 
The framework table is now a lot more readable and you can use the view in further TSQL analysis (i.e. crosstabs, subqueries, CTE’s etc).
 
 
ENJOY