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

Query Build Range in Forms

Override the executeQuery() method the data source (on the Form/Data Sources/data source) to set the query. Note that the ranges are cleared before setting as they are persistent.

This example assumes that there is one datasource table, to make it easier;


public void executeQuery()
{  
    <variable definitions here>

    QueryBuildRange qbr;

    <dataNameOne> = element.design().controlName("<controlNameOne>").valueStr();
    <dataNameTwo> = str2enum(prodStatus,element.design().controlName("<controlNameTwo>").valueStr());
    <dataNameThree = str2enum(calcType,element.design().controlName("<controlNameThree>").valueStr());
    <dataNameFour> = str2enum(bomCalc,element.design().controlName("<controlNameFour>").valueStr());
    this.query().dataSourceTable(tableNum(<datasourceTable>)).clearRanges();
    qbr = this.query().dataSourceTable(tableNum(<datasourceTable>)).addRange(fieldNum(<datasourceTable>,<fieldNameOne>));
    qbr.value(<dataNameOne>);
    qbr = this.query().dataSourceTable(tableNum(<datasourceTable>)).addRange(fieldNum(<datasourceTable>,<fieldNameTwo>));
    qbr.value(strFmt("%1",<dataNameTwo>));
    qbr = this.query().dataSourceTable(tableNum(<datasourceTable>)).addRange(fieldNum(<datasourceTable>,<fieldNameThree>));
    qbr.value(strFmt("%1",<dataNameThree>));
    qbr = this.query().dataSourceTable(tableNum(<datasourceTable>)).addRange(fieldNum(<datasourceTable>,<fieldNameFour>));
    qbr.value(strFmt("%1",<dataNameFour>));
super();
}

Then change the trigger, in this case a click event, to run the execute the query above. Note the _ds extension, which denotes a datasource object.

void clicked()
{
    super();
    <datasourceTable>_ds.executeQuery();
}

This is not best practice, as you can build the range as a global object and add the values in the ranges in the executeQuery() method.

File Dialog

<URL> is the start folder

  FilePath  _selectedPath = @"<URL>";
  str _caption = "Test";
  boolean _showNewFolderButton;
  destinationFolder = WinAPI::browseForFolderDialog(_caption, _selectedPath, _showNewFolderButton);

Selective Data In Form Display

On the data sources section of the form ladder, under the data source, change the init method to select the data you require.

public void init()

{

    QueryBuildRange qryBuildRange;

    super();

    qryBuildRange = this.query().dataSourceName(this.name()).addRange(fieldNum(Addresses,AddressType));

    qryBuildRange.value(queryValue('D'));

    qryBuildRange = this.query().dataSourceName(this.name()).addRange(fieldNum(Addresses,dateCommited));

    qryBuildRange.value(queryValue(dateNull()));

    qryBuildRange.status(RangeStatus::Hidden);

}

The .Status being set to RangeStatus::Hidden stops the user over-ruling the selection manually.