Hello DAX DEV,
In this blog post, we’ll be diving into an X++ class called FNGTableInfoHelper Created to extracts information about tables with a specific prefix and without the “Staging” suffix. The class gathers specific details about these tables and their fields in Dynamics 365 Finance and Operations and exports the data to a CSV file using the TreeNode class.
The class code is available in this blog post, hosted in github, To execute the class one should use SysClassRunner with the class name in the URL, the system will hang for a while to get the full list of tables than extracts table by table with the users ok, the result of the execution is a series of CSV files:
You’ll choose a directory and save the files. process takes 3-10 min depending on the amount of tables, in my case my 64 tables got extracted in less than 5m.
CSV Files are as following:
Below is an overview of the entireFNGTableInfoHelperclass:This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode characters
internal final class FNGTableInfoHelper { void showFieldInfo(TableId tableId) { int i,j; SysDictType dictType; SysDictEnum dictEnum; commaStreamIo iO = commaStreamIo::constructForWrite(); filename filename = strFmt("%1.csv",tableId2PName(tableId)); DictTable dictTable = new DictTable(tableId); DictField dictField; str lastFieldName; TableName tableName = dictTable.name(); FieldName fieldName; str label; container writingContainer; str getEnumValues(DictEnum _dictEnum) { str ret; int enumValue; for (enumValue = 0; enumValue <= _dictEnum.values(); enumValue++) { str enumElement = strFmt("%1", _dictEnum.index2Symbol(enumValue)); str enumLabelId = strFmt("%1", _dictEnum.index2LabelId(enumValue)); str enumLabel = strFmt("%1", SysLabel::labelId2String(_dictEnum.index2LabelId(enumValue))); ret += enumElement ? (strFmt(" [%1:%2-%3-%4] |", enumValue,enumElement,enumLabelId,enumLabel)) : ""; } ret = subStr(ret,1, strLen(ret) - 2) + ")"; return ret; } str if_enum() { return (dictEnum == null ? "" : "Enum " + dictEnum.name() + ": ("+ getEnumValues(dictEnum)); } void setTableHeaderInfo() { #Properties TreeNode treeNode = TreeNode::findNode(@"\Data Dictionary\Tables").AOTfindChild(tableName); iO.writeExp(["TableName:",tableName]); iO.writeExp(["Label:",tableId2PName(tableId)]); iO.writeExp(["ConfigurationKey:",treeNode.AOTgetProperty(#PropertyConfigurationKey)]); iO.writeExp(["PrimaryIndex:",treeNode.AOTgetProperty(#PropertyPrimaryIndex)]); iO.writeExp(["TitleField1:",treeNode.AOTgetProperty(#PropertyTitleField1)]); iO.writeExp(["TitleField2:",treeNode.AOTgetProperty(#PropertyTitleField2)]); iO.writeExp(conNull()); iO.writeExp(conNull()); } setTableHeaderInfo(); writingContainer = ["Number","Field Name","Label","Allow edit", "Mandatory","Type","Help Text"]; iO.writeExp(writingContainer); writingContainer = conNull(); dictField = dictTable.fieldObject(dictTable.fieldCnt2Id(i)); while (dictField) { i++; lastFieldName = fieldName; fieldName = dictField.name(); if (lastFieldName == fieldName) { break; } label = dictField.label(); if (!dictField.isSystem()) { j++; Types type = dictField.baseType(); str isMandatory = dictField.mandatory() ? "Yes" : "No"; str allEdit = dictField.allowEdit() ? "Yes" : "No"; dictType = new SysDictType(dictField.typeId()); dictEnum = new SysDictEnum(dictField.enumId()); str typeStr = dictType == null ? if_enum() : strFmt("%1 %2 %3",dictField.name(), type, dictField.stringLen() ? strFmt("Len: %1", dictField.stringLen()) : if_enum()); writingContainer = [j,fieldName, label, allEdit, isMandatory, typeStr, dictField.help()]; iO.writeExp(writingContainer); } dictField = dictTable.fieldObject(dictTable.fieldCnt2Id(i)); } System.IO.Stream stream = iO.getStream(); stream.Position = 0; System.IO.StreamReader reader = new System.IO.StreamReader(stream); str csvFileContent = reader.ReadToEnd(); File::SendStringAsFileToUser(csvFileContent, filename); info(strFmt("CSV file %1 Sent to user", filename)); } static FNGTableInfoHelper construct() { return new FNGTableInfoHelper(); } public static void main(Args args) { void extract(tableName tableName) { str message = 'Extract field info of %1 Table'; if (box::yesNo(strFmt(message,tableName),DialogButton::Yes) == DialogButton::Yes) { FNGTableInfoHelper::construct().showFieldInfo(tableName2Id(tableName)); } } container tables = FNGTableInfoHelper::getFNGTables();// ["FNGJobTable","FNGJobLine","FNGJobServiceReports","FNGJobActivities","FNGCustomerAssetTable","FNGCustomerAssetJobTable","SalesTable","SalesLine"]; while (conLen(tables)) { extract(conPeek(tables,1)); tables = condel(tables,1,1); } } public static container getFNGTables() { TreeNode tablesNode = TreeNode::findNode(@"\Data Dictionary\Tables"); TreeNodeIterator iterator; TreeNode tableNode; container tableNames = conNull(); int i,time = timenow(); if (tablesNode) { iterator = tablesNode.AOTiterator(); tableNode = iterator.next(); while (tableNode) { str name = tableNode.AOTname(); int len =strLen(name); str prefix = subStr(name,1,3), sufix = subStr(name, len - 6, 8); if (prefix == "FNG" && sufix != "Staging") { tableNames = conIns(tableNames, conLen(tableNames) + 1, name); } tablesNode = null; tableNode = iterator.next(); } iterator = null; } str message = (strFmt("FNG tables collected in %1 sec",timeNow()-time)); info(message); return tableNames; } }
Key methods in the class include:
showFieldInfo(TableId tableId): This method extracts table and field information based on the given table ID, writes it to a CSV file, and sends the file to the user. The method uses the DictTable and DictField classes to access the table and field information. It also writes the table header information by calling thesetTableHeaderInfo()method.getEnumValues(DictEnum _dictEnum): This is a nested method withinshowFieldInfo(). It takes a DictEnum object as an argument and returns a formatted string with all enumeration values and labels for the given enumeration object.if_enum(): Another nested method withinshowFieldInfo(). It checks if the dictEnum object is not null, and if so, it returns a formatted string containing the enumeration name and the result of callinggetEnumValues(dictEnum).setTableHeaderInfo(): This nested method withinshowFieldInfo()retrieves and writes the table header information using the TreeNode class to navigate the Application Object Tree (AOT) and access table properties.construct(): This static method is a constructor for the FNGTableInfoHelper class. It returns a new instance of the FNGTableInfoHelper class.main(Args args): This static method is the entry point of the class. It defines a local method calledextract()that takes a table name as an argument and callsshowFieldInfo()with the table ID for the given table name. Themain()method then loops through a container of FNG tables, calling theextract()method for each table.getFNGTables(): This static method retrieves all tables with the “FNG” prefix and without the “Staging” suffix. It uses the TreeNode class to navigate the AOT, collecting table names in a container. The method returns the container of table names.- These methods work together to extract information about FNG tables and their fields, then export the data to a CSV file using the TreeNode class for AOT navigation.
The class’s entry point, main(Args args), calls the extract method for each table name in the tables container. The extract method uses the showFieldInfo method to get the table information and writes it to a CSV file.
The TreeNode class is used to navigate the Application Object Tree (AOT) and access table information. For example, in the setTableHeaderInfo() and getFNGTables() methods, TreeNode objects are used to find table nodes and retrieve the necessary properties.
With the help of the FNGTableInfoHelper class, you can easily generate CSV files containing information about specific tables in the Dynamics AX system. This can be particularly useful for developers and administrators working with Dynamics 365 Finance and Operations.
That’s all for today’s post. We hope you find the FNGTableInfoHelper class helpful for extracting table information and exporting it to CSV files in your Dynamics 365 projects. Happy coding!
AXAPTA Hut 