SysTableBrowser form or simply Table browser is form widely used from developers within the development workspace in Dynamics AX.
This is a view for any table data but often there are tables with many fields and finding and positioning to the wanted field to see the value could be exhaustive task which involves a lot of scrolling. There is also the out of box possibility to use a field list select statement in the SQL pane on the table browser form but what the execution in this pane does is just to null the values in the non-selected columns and is not positioning to the selected column. So there is a solution to extend the table browser with a tree view pane which from which any field could be selected and positioned no matter of how many fields or what kind of data is in the table itself. This solution was originally created from Ruslan Goncharov for Dynamics AX 4.0, which was working until Dynamics AX 2009. The changes in Dynamics AX 2012 in the table browser are that the field list alphabetical ordering does not correspond with the sequence of the table field Id’s. So in the order the same solution to work in Dynamics AX 2012 we need some sorting of the pairs of table field id with field name. For this cause we will use the quicksort algorithm from one of my previous articles.
The changes in the form SysTableBrowser step by step are:
Step 1
Add a tree control to the table browser form and name it ‘FormTreeControl’:
Step 2
Override the methods keyDown and mouseDblClick as follows:
public boolean keyDown(int _vKey, boolean _Ctrl, boolean _Shift)
{
TreeItemIdx itemIdx;
boolean ret;
#KeyPressed
ret = super(_vKey, _Ctrl, _Shift);
if((_formGridControl)&&(_vKey == #space))
{
itemIdx = FormTreeControl.getSelection();
_formGridControl.controlNum( conFind(itemIdxContainer, itemIdx ) ).setFocus();
}
return ret;
}
And
public int mouseDblClick(int _x, int _y, int _button, boolean _Ctrl, boolean _Shift)
{
TreeItemIdx itemIdx;
int ret;
;
ret = super(_x, _y, _button, _Ctrl, _Shift);
if((_formGridControl))
{
itemIdx = FormTreeControl.getSelection();
_formGridControl.controlNum( conFind(itemIdxContainer, itemIdx ) ).setFocus();
}
return ret;
}
Step 3
Add the following variables in the class declaration:
Container itemIdxContainer, conNew;
FormGridControl _formGridControl;
Step 4
Create a quicksort method that will sort pairs of values:
protected container conQuickSort(container _conSort)
{
container conLess = conNull(), conGreater = conNull();
str pivotName;
int pivotId, pivotIdx, i;
;
// Check for null array
if (conLen(_conSort) <= 2)
return _conSort;
// Select pivot value and remove it from the container
pivotIdx = conLen(_conSort)/2 + ((conLen(_conSort)/2) mod 2);
pivotName = conPeek(_conSort, pivotIdx);
pivotId = conPeek(_conSort, pivotIdx-1);
_conSort = conDel(_conSort, pivotIdx-1,2);
// Partinioning of the main array
for (i = 1; i <= conLen(_conSort); i=i+2)
{
if (conPeek(_conSort, i+(i mod 2)) <= pivotName)
{
conLess += conPeek(_conSort, i+(i mod 2)-1);
conLess += conPeek(_conSort, i+(i mod 2));
}
else
{
conGreater += conPeek(_conSort, i+(i mod 2)-1);
conGreater += conPeek(_conSort, i+(i mod 2));
}
}
// Recursive call
return (this.conQuickSort(conLess) + pivotId + pivotName + this.conQuickSort(conGreater));
}
Step 5
Create the method that will fill the tree control:
void fillTree()
{
int _i;
FormDesign Ctrl = element.design();
FormControl _buildCtrl;
FormControl _formControl;
TreeItemIdx treeItemIdx;
ImageListAppl_Aot ImageListAppl_Aot;
DictField dictField;
fieldId fieldId;
int i,j,k;
int fieldCnt;
#DictField
#resAppl
ImageRes findOverlayImage(Types _type)
{
ImageRes imageRes;
switch(_type)
{
case Types::STRING, Types::VARSTRING :
return #ImageOverlayString;
case Types::ENUM:
return #ImageOverlayEnum;
case Types::REAL:
return #ImageOverlayReal;
case Types::DATE:
return #ImageOverlayDate;
case Types::INTEGER:
return #ImageOverlayInteger;
case Types::UtcDateTime:
return #ImageOverlayDate;
case Types::CONTAINER:
return #ImageOverlayContainer;
default:
return 0;
}
return imageRes;
}
;
// Find 'Grid' object -->
for(_i=1; _i<=Ctrl.controlCount(); _i++)
{
_buildCtrl = Ctrl.controlNum(_i);
_formControl = element.control(_buildCtrl.id());
if(_formControl.name() == 'allFieldsGrid')
{
_formGridControl = _formControl;
}
}
// Find 'Grid' object <--
// Fill tree -->
if((FormTreeControl.visible())&&(dictTable))
{
ImageListAppl_Aot = new ImageListAppl_Aot();
formTreeControl.setImagelist(ImageListAppl_Aot.imageList());
formTreeControl.deleteAll();
fieldCnt = dictTable.fieldCnt();
for (i=1; i<=fieldCnt; i++)
{
fieldId = dictTable.fieldCnt2Id(i);
dictField = new DictField(dictTable.id(), fieldId);
if (bitTest(dictField.flags(), #DBF_STORE) && !dictField.isSystem())
{
for(j = 1;j <= dictField.arraySize(); j++)
{
treeItemIdx = SysFormTreeControl::addTreeItem(formTreeControl, dictField.name(), 0, null, imageListAppl_Aot.image(#ImageField));
SysFormTreeControl::setOverlayImage(formTreeControl, treeItemIdx, imageListAppl_Aot.image(findOverlayImage(dictField.baseType())));
itemIdxContainer += [treeItemIdx, dictField.name()];
}
}
}
SysFormTreeControl::expandTree(formTreeControl, formTreeControl.getRoot(), 1);
// Do predefined design width more wider
element.design().width( element.design().widthValue()+FormTreeControl.widthValue() );
if(_formGridControl)
_formGridControl.topMode(FormTop::TopEdge);
}
// Fill tree <--
// Sort container and leave only ids
conNew = this.conQuickSort(itemIdxContainer);
itemIdxContainer = conNew;
for (k = 1; k <= conLen(itemIdxContainer); k++)
{
if (typeOf(conPeek(itemIdxContainer,k)) == Types::String)
itemIdxContainer = conDel(itemIdxContainer, k, 1);
}
// Sort container <--
}
Step 6
Call the fillTree method in the existing run method:
void run()
{
super();
dictTable = new DictTable(tableId);
this.parmSQLStmt('SELECT * FROM '+tableid2name(tableId));
this.fillTree();
}
Step 7
The final step is to add handling for key stroke events when you are positioned on the tree. I’ve added possibility to select the particular field with Enter key stroke and Space key as well. In order to achieve this the Task method in table browser should modified as follows:
int task(int p1)
{
TreeItemIdx itemIdx;
#Task
FormControl cur = element.selectedControl();
int ret;
if ((p1 == #taskTab) && (cur.hWnd() == SQL.hWnd()))
{
SQL.pasteText(' ', true);
SQL.updateWindow();
ret = 0;
}
else if (p1 == #taskf5)
{
ExecuteSQL.clicked();
ret = 1;
}
else if (p1 == #taskEnter)
{
if(_formGridControl)
{
itemIdx = FormTreeControl.getSelection();
_formGridControl.controlNum( conFind(itemIdxContainer, itemIdx ) ).setFocus();
}
}
else
ret = super(p1);
return ret;
}
Step 8
So finally you can enjoy your enhanced table browser with just typing the name of the field in the tree view pane and hitting Enter or Space or Double click:
Happy DAXing.
