Populate a temporary table on a form

• Create a class to populate the temporarytable

 

public temporaryTable populateTemporaryTable()

{

   

 temporaryTable temporaryTable;

   

 int I;

   

 for(i = 0;i <= 5; i++)

 {

  //

  // Set the data in the table

  //

  temporaryTable.Field1 = "InterestungString";

  temporaryTable.Field2 = i;

  temporaryTable.insert();

 }

 return temporaryTable;

}

 

• In the form add the temporary table as a datasource
• In the form class declaration add a buffer based on the temporary table

 

public class FormRun extends ObjectRun

{

 //

 // Global temporaryTable

 //

 temporaryTable temporaryTable;

}

• Populate the table in the form (usually in the init method)

 

public void init()

{

 BuildTempTable BuildTempTable = new BuildTempTable();

   

 super();

 //

 // Fill the buffer with data

 //

 temporaryTable = BuildTempTable.populateTemporaryTable();

 //

 // link the form datasource with the buffer

 //

 temporaryTable.linkPhysicalTableInstance(temporaryTable);

}

List Security Roles in AX2012 to Excel

static void SecurityCreateExcelDocument(Args _args)
{
SysExcelApplication      xlsApplication;
SysExcelWorkBooks    xlsWorkBookCollection;
SysExcelWorkBook     xlsWorkBook;
SysExcelWorkSheets   xlsWorkSheetCollection;
SysExcelWorkSheet    xlsWorkSheet;
SysExcelRange            xlsRange;
int                                row = 1;
str                               fileName;
SecurityUserRole            _SecurityUserRole;
SecurityRoleTaskGrant       _SecurityRoleTaskGrant;
SecurityTask                _SecurityTask;
SecurityRole                _SecurityRole;
UserInfo                    _UserInfo;

fileName = "D:\\Security.xlsx";
xlsApplication           = SysExcelApplication::construct();
xlsWorkBookCollection    = xlsApplication.workbooks();
xlsWorkBook              = xlsWorkBookCollection.add();
xlsWorkSheetCollection   = xlsWorkBook.worksheets();
xlsWorkSheet             = xlsWorkSheetCollection.itemFromNum(1);
xlsWorkSheet.cells().item(row,1).value("User Role");
xlsWorkSheet.cells().item(row,2).value("User Id");
xlsWorkSheet.cells().item(row,3).value("Legal Entity");
row++;
    while select _UserInfo
    {
        while select _SecurityUserRole where _SecurityUserRole.User==_UserInfo.id
        {
                while select _SecurityRole where _SecurityRole.RecId==_SecurityUserRole.SecurityRole
                {
                xlsWorkSheet.cells().item(row,1).value(_SecurityRole.Name);
                xlsWorkSheet.cells().item(row,2).value(_UserInfo.id);
                xlsWorkSheet.cells().item(row,3).value(_UserInfo.company);
                row++;
                }
        }
    }
   
if(WinApi::fileExists(fileName))
{
    WinApi::deleteFile(fileName);
}

xlsWorkbook.saveAs(fileName);
xlsApplication.visible(true);
}

Passing a temp table between forms

Sometimes you need to pass a built temp table from one form to another.

The following is a button opening another form, but the buffer is shared between them.

Form 1:

It's assumed that the temp data is already built by this point

Override the clicked event on the button



void clicked()
{
Args args;
FormRun formRun; super();
args = new args(formStr(Form1Name));
args.record(tempTable);
formrun = classFactory.formRunClass(args);
formRun.init();
formRun.run();
formRun.wait();
formRun.detach();
}






Form 2:



Code in form, in the temporary table datasource – tempTable:
Override the init event in the table

public void init()
{
tempTable _tempTable;
_tempTable = element.args().record();
super();

tempTable.linkPhysicalTableInstance(_tempTable);
tempTable_ds.refresh();
}

Multiselection lookup

Put a stringEdit in the form and set the AutoDeclaration to Yes, set the name to something you will remember ; <name of control>


In the init method of the form;

    query msQuery = new Query();
    QueryBuildDataSource msQbds;
    container cont;

    super();

Fill the control from a query

    msQbds = msQuery.addDataSource(tableNum(<tablename>));
    msQbds.fields().dynamic(NoYes::Yes);
    msQbds.fields().addField(fieldNum(<tablename>,<fieldname>));
    msCtrlGroup = SysLookupMultiSelectCtrl::constructWithQuery(element,<name of control> ,msQuery);

   If you wanted to pre-select (tick) some of the data using the drop-down, do this in the init method

   cont = str2con("3-BookedSV;4-Diagnose;6-QuoteA",";",false);

    msCtrlGroup.set(this.presetStatus(cont));

   <tempfile>_ds.executeQuery();

The presetStatus sets the filter

private container presetStatus(container _container)

{

    <Table> <table>;

    int counter,containerLength;

    str 20 lookFor;

    container <visibleItem>,<recordIdOfItem>;

    containerLength = conLen(_container);

    for(counter=1;counter<=containerLength;counter++)

    {

        lookFor = conPeek(_container,counter);

        select <table> where <table>.<visibleItem> == lookFor;

        <visibleItem>+= [<table>.<visibleItem>];

        <recordIdOfItem>  += [<table>.RecId];

    }

    return [<recordIdOfItem>,<VisibleItem>];

}

Now make a population method to fill your temp table and add this to check to see if  data should be inserted in to the temp table;

If the selection is empty, get all the data, otherwise loop through the ticked items and pick only those records

        groups = <name of control>.getSelectedFieldValues();

        insertData = false;

        if(conPeek(groups,1) == "")

        {

            insertData = true;

        }

        else

        {

            CountMax = conlen(groups);

            for(counterounter=1;counter <= countMax;counter++)

            {

                if(any2str(conPeek(groups,counter)) == <itemToCheck>)

                {

                    insertData = true;

                }

            }

        }

        wmpSS.clear();

        if(insertData)

        {

Insert data in here

         }

         else

         {

              continue();

          }

And finally override the executeQuery method in the temp table

public void executeQuery()
{
    delete_from <tempTable>;
    <tempTable>.linkPhysicalTableInstance(element.populate());
    super();
}

Default Dimension Information

To find the setting of a known attribute, feed the following (In this case, the inventJournalTrans table).

// _journalId = unique journal Id
// _attrToFind = the name of the attribute you are looking for

    InventJournalTrans                            inventJournalTrans;
    DimensionAttributeValueSet          dimAttrValueSet;
    DimensionAttributeValueSetItem  dimAttrValueSetItem;
    DimensionAttributeValue                dimAttrValue;
    DimensionAttribute                           dimAttr;
    DimensionFinancialTag                   dimFinTag;
   
    str 60 attrToFind;
    str 10 journalid;
    str 30 findValue;
   
    ;

    select inventJournalTrans        
           where inventJournalTrans.JournalId == _journalId        
           join dimAttrValueSet where     InventJournalTrans.defaultDimension == dimAttrValueSet.recId        
           join dimAttrValueSetItem where dimAttrValueSetItem.DimensionAttributeValueSet == dimAttrValueSet.RecId        
           join dimAttrValue where        dimAttrValue.RecId == dimAttrValueSetItem.DimensionAttributeValue        
           join dimAttr where             dimAttr.RecId == dimAttrValue.DimensionAttribute 
                        &&                dimAttr.Name == _attrToFind;


And to get the description of the attribute;


    _findValue = dimAttrValue.getValue();

    select firstOnly dimFinTag where dimFinTag.Value == _findValue;

Temporary Tables And Forms

Getting a form to display data in a temporary table can be difficult, so I have made a step-by-step method which has worked for me.


1. Create the temporary table

2. Create a form

3. Attach the temporary table as a dataSource of the form

4. Create a form method to populate the temporary table from normal datasources. This will be created in the upper Methods tree of the form. You can call it anything, but I am making the effort to call it populate() for consistency.

5. Override the init method of the form and add the following line after the super() call.

If the temporary table is of the type TempDB then the line should read;

<tempTableName>.linkPhysicalTableInstance(element.populate());

If the temporary table is of the type InMemory then the line should read;
<tempTableName>.setTmpData(element.populate());

Where <tempTableName> is the name of the temporary table.

On purpose I have made this as simple as I can, if you need the technical stuff, then I have copied the following from HariKiran Varre ;

In Ax 2012 we have 3 different types of tables. one type of temporary table is a TempDB table. We call them TempDB tables because their TableType property value is TempDB. This value comes from the TableType::TempDB enum value. The TableType property value can be set at AOT > Data Dictionary > Tables >MyTempDBTable > Properties > TableType.

• Regular - a standard

•      physical table

• InMemory - the type

•      of temporary table which existed in the previous versions of Dynamics Ax.

•      Such tables are held in memory and written to a local disk file once they

•      grow beyond a certain point

• TempDB - a new

•      option in Ax 2012. They are "physical" temporary tables held in

•      the SQL Server database.

The new TempDB tables operate in a similar manner to InMemory tables but support more features of standard physical tables:

• More powerful

•      joins with physical tables are possible, and are properly supported by the

•      database

• Can be

•      per-company or global

• Support for

•      normal tts transactions

To create a new instance link (populating data/copying reference) from one table instance
variable to the other with Temporary type tables:

• For InMemory
       tables, by using the setTmpData() method
• For TempDB
       tables, the linkPhysicalTableInstance() method
       replaces the setTmpData() call.

For more details on TempDB capabilities, Limitations, How to use, Its lifetime, please check here - http://msdn.microsoft.com/en-us/library/gg845661.aspx

Get Inventory Dimension

Given just the item Id, find or create the inventory dimension.

public InventDim GetInventoryDimension(ItemId _itemId)

{

    InventTable                 inventTable = inventTable::find(_itemId);    

    InventItemOrderSetupType    setupType   = InventItemOrderSetupType::Invent;    

    InventDim                   inventDim;   

 ;    

// Default Site    

inventDim.InventSiteId = inventTable.inventItemOrderSetupMap(setupType).inventSiteId(inventDim.InventSiteId, inventTable);    

// Default Location    

inventDim.InventLocationId  = inventTable.inventItemOrderSetupMap(setupType,                                                                   InventDim::findOrCreate(inventDim).InventDimId).inventLocationId(inventDim.InventLocationId,                                      inventTable, inventDim.InventSiteId);

    // Default ConfigId    

inventDim.ConfigId = inventTable.StandardConfigId;   

 // Find Default Item Dimension    

inventDim = InventDim::findOrCreate(inventDim);    

return inventDim;

}