Today I want to show you how to extract Table metadata properties i.e Field Name, label, size, EDT
Here is the snippet of code.
static void ExtractMetaData(Args _args)
{
// Metadata related variables
DictTable table;
DictIndex dictIndex;
DictField field;
DictEnum enum;
DictType type;
DictRelation relation;
int i = 0, j,k;
SQLDictionary sqlDictionary;
Tablename _tablename;
str enumDetails;
#DictField
// Excel related variables
int rowNum = 1,rowNum2;
Filename _filename;
SysExcelApplication_2007 excel;
SysExcelWorkBook_2007 workBook;
SysExcelWorkSheet workSheet;
SysExcelCells cells;
#Excel
// User interaction related variables
Dialog dialog;
DialogField dialogField_Table, dialogField_FilePath;
;
// Get Table name and file save path from user
/*dialog = new Dialog();
dialog.caption('Metadata Extractor');
dialogField_Table = dialog.addField(typeid(TableName));
dialogField_Table.label('Table name');
dialogField_FilePath = dialog.addField(typeid(FilePath));
dialogField_FilePath.label('Directory to create output file');
if(dialog.run())
{
_tablename = dialogField_Table.value();
_filename = dialogField_FilePath.value() + '\\'+ _tablename + '_Metadata.xlsx';
}
else
throw error::missingParameter(dialog);*/
_tablename = "DMFSubledgerEntityOCP";
_filename = "D:\\DMF\\SubledgerBalances.xlsx";
// Start execution
try
{
// Create instance of the excel application
excel = SysExcelApplication_2007::construct(new COM(#excel));
// Create a excel workbook
workBook = excel.workbooks().add();
// Name the 1st worksheet in the workbook
workBook.worksheets().itemFromNum(1).name(_tableName);//_tableName
// Delete the other worksheets from the workbook
/*worksheet = workBook.worksheets().itemFromNum(2);
worksheet.delete();
workSheet = workBook.worksheets().itemFromNum(2);
workSheet.delete();*/
// Access the cells of the 1st worksheet in the workbook and add the title row
cells = workBook.worksheets().itemFromNum(1).cells();
cells.item(1,rowNum).value('Field Name');
cells.item(2,rowNum).value('Field Label');
cells.item(3,rowNum).value('Data Type');
cells.item(4,rowNum).value('Length');
cells.item(5,rowNum).value('Unique Field');
cells.item(6,rowNum).value('Mandatory Field');
cells.item(7,rowNum).value('Reference Field');
cells.item(8,rowNum).value('Reference Table');
cells.item(9,rowNum).value('Other constraints');
// Iterate through all the fields of the table and add the metadata to the excel worksheet
while select sqlDictionary
//order by fieldId
where
sqlDictionary.fieldId != 0 &&
sqlDictionary.tabId == tableName2Id(_tableName)
{
// Increment the working row
rowNum++;
// Retrieve the "DictField" object for reference
field = new DictField(sqlDictionary.tabId,sqlDictionary.fieldId);
// Populate the excel with metadata
cells.item(1,rowNum).value(field.name()); //Field Name
cells.item(2,rowNum).value(field.label()); //Data Type
cells.item(3,rowNum).value(enum2str(sqlDictionary.fieldType)); //Data Type
// Length of a field is only populated if it is exists
if(sqlDictionary.strsize != 0)
cells.item(4,rowNum).value(sqlDictionary.strSize); //Length
// Retrieve the "DictType" object for reference
type = new DictType(field.typeId());
// Populate the excel with metadata
if(bittest(field.flags(),#DBF_MANDATORY))
cells.item(6,rowNum).value('Yes'); //Mandatory field
// Check if the field has a EDT attached to it
if(type)
{
// Extract the relationship defined on the EDT
relation = type.relationObject();
// Proceed if a relationship exists
if(relation)
{
// Populate the excel with metadata
cells.item(7,rowNum).value(fieldid2name(relation.table(),relation.lineExternTableValue(1))); //Reference Field
cells.item(8,rowNum).value(tableid2name(relation.table())); //Reference Table
}
}
// Check if the field has a Enum attached to it
if(field.enumId())
{
// Retrieve the "DictEnum" object for reference
enum = new DictEnum(field.enumId());
// Initialize variable to temporarily store enum values
enumDetails = 'Possible values = ';
// Extract the enum values into the temporary variable
for(i=0;i<enum.values();i++)
{
enumDetails = enumDetails +'\n'+ int2str(enum.index2Value(i)) + ' - ' + enum.index2Label(i);
}
// Populate the excel with metadata
cells.item(9,rowNum).value(enumDetails); //Other constraints
}
}
// Retrieve the "DictTable" object for reference
table = new DictTable(tableName2Id(_tableName));
/*
// Loop through all the indices of the table
for (j=0;j<table.indexCnt();j++)
{
// Retrieve the "DictIndex" object for reference
dictIndex = new DictIndex(tableName2Id(_tableName),table.indexCnt2Id(j));
// Check if the index is a unique index
if(dictIndex.allowDuplicates() == NoYes::No)
for(k=1;k<=dictIndex.numberOfFields();k++) // Iterate through all the fields of the index
{
// Find the position of the field in the worksheet and set the Unique Index property to Yes
cells.item(4,(cells.range('A:A').find(fieldid2name(table.id(),dictIndex.field(k))).column())).value('Yes');
}
}
*/
// Format the worksheet - Make the header row grey and bold, and autofit the columns
workBook.styles().add('1').interior().color(WinApi::RGB2int(190, 190, 190));
workBook.styles().item(1).font().bold(true);
workBook.worksheets().itemFromNum(1).range('A1:H1').style('1');
workBook.worksheets().itemFromNum(1).columns().autoFit();
// Save the workbook and present it to the user
workBook.saved(true);
//workBook.saveAs(_filename);
excel.visible(true);
}
catch(Exception::Error)
{
throw error("error");
}
}
D365 F&O Standard
Table - DataFeedsTableCatalog
/// <summary>
/// This table store a list of
tables that can be used to configure a data feed
/// </summary>
/// <summary>
/// resets the contents of the table catalog
/// </summary>
internal static void resetTableCatalog()
{
DataFeedsTableCatalog tableCatalog;
delete_from tableCatalog;
ttsbegin;
System.Collections.IEnumerator tableCollectionEnumerator
= DataFeedsTableCatalog::getTableQueryEnumerator();
while (tableCollectionEnumerator.MoveNext())
{
DataFeedsTableCatalog::createTableRefRecord(tableCollectionEnumerator.Current
as
Microsoft.Dynamics.AX.Metadata.NodeLib.Node);
}
ttscommit;
}
/// <summary>
/// Gets the table query enumerator.
/// </summary>
/// <returns>The instance of the
<c>System.Collections.IEnumerator</c> class that can iterate table
nodes.</returns>
internal static System.Collections.IEnumerator getTableQueryEnumerator()
{
System.Collections.IEnumerator tableCollectionEnumerator;
var query = Microsoft.Dynamics.AX.Metadata.NodeLib.Specialized.TableQuery::Construct();
query.AllowTemporary = false;
query.AllowSystem = true;
tableCollectionEnumerator =
query.GetEnumerator();
return tableCollectionEnumerator;
}
internal static void createTableRefRecord(Microsoft.Dynamics.AX.Metadata.NodeLib.Node _node)
{
DataFeedsTableCatalog tableCatalog;
SysDictTable dictTable = new SysDictTable(tableName2Id(_node.Name));
tableId tableId = dictTable.id();
if (dictTable && dictTable.enabled())
{
// don't
include views, temp tables, in memory tables, dixf staging tables
if ( DictTable.isView() || dictTable.tableType() != TableType::Regular || dictTable.tableGroup()
== TableGroup::Staging)
{
return;
}
tableCatalog.TableIdValue =
tableId;
tableCatalog.TableObjectName =
dictTable.name();
tableCatalog.TableLabel =
dictTable.label();
tableCatalog.TableConfigurationKey
= dictTable.getRootConfigKeyLabel();
tableCatalog.SavePerCompany =
dictTable.dataPrCompany();
tableCatalog.TableGroup =
dictTable.tableGroup();
var tableMetadata = Microsoft.Dynamics.Ax.Xpp.MetadataSupport::GetTable(tableCatalog.TableObjectName);
tableCatalog.Tags =
tableMetadata.Tags;
tableCatalog.insert();
}
}
