Populating a form from a temporary table in a query

Dear Sir/Madam

I’m working in AX 2009. I have created a temporary table (TmpOTD_Vendors_Table) containing 10-DataFields, 8 of which I want to use to populate a form with a grid.

The form has a query, which uses InventTrans as the main table and outer joins InventTable and PurchLine table. It then outer joins PurchTable to PurchLine table.

The purpose is to pull the Vendor number (PurchTable.OrderAccount), Vendor name (PurchTable.PurchName), and Purchase order (PurchTable.PurchId), ItemId (PurchLine.ItemId), ItemGroupId (InventTable.ItemGroupId), DeliveryDate (PurchLine.DeliveryDate), ConfirmedDate (PurchLine.ConfirmedDlv), DatePhysical (InventTrans.DatePhysical).

I can set a break point and step thru the code and see data. However, the form doesn’t open with any data populated in it.

Could someone please suggest what steps I may be missing? I am including my code below. In my class declaration I have the following:

public class FormRun extends ObjectRun
{
TmpOTD_Vendors_Table tmpDS; //name of my temp table.
}

In my form’s run method I have the following:

public void run()
{
Query query;
QueryRun queryRun;
QueryBuildDataSource qbds1, qbds2, qbds3, qbds4;
QueryBuildRange qbr1, qbr2;

InventTrans inventTransTmp;
InventTable inventTableTmp;
PurchLine purchLineTmp;
PurchTable purchTableTmp;
;

delete_from tmpDS;

query = new Query();
qbds1 = query.addDataSource(tableNum(InventTrans));
qbds2 = qbds1.addDataSource(tableNum(InventTable));
qbds2.relations(true);
qbds2.joinMode(JoinMode::OuterJoin);
qbds2.fetchMode(0);
qbr1 = qbds1.addRange(fieldNum(InventTrans, DatePhysical));
qbr2 = qbds1.addRange(fieldNum(InventTrans, TransType));

qbds3 = qbds1.addDataSource(tableNum(PurchLine));
qbds3.relations(true);
qbds3.joinMode(JoinMode::OuterJoin);
qbds3.fetchMode(0);

qbds4 = qbds3.addDataSource(tableNum(PurchTable));
qbds4.relations(true);
qbds4.joinMode(JoinMode::OuterJoin);
qbds4.fetchMode(0);

queryRun = new QueryRun(query);

if (queryRun.prompt())
{
while (queryRun.next())
{
inventTransTmp = queryRun.get(tableNum(InventTrans));
inventTableTmp = queryRun.get(tableNum(InventTable));
purchLineTmp = queryRun.get(tableNum(PurchLine));
purchTableTmp = queryRun.get(tableNum(PurchTable));

if (inventTransTmp.TransType == 3 && inventTransTmp.ItemId == inventTableTmp.ItemId
&& inventTransTmp.InventTransId == purchLineTmp.InventTransId
&& purchLineTmp.PurchId == purchTableTmp.PurchId)
{
tmpDS.OrderAccount = purchTableTmp.OrderAccount;
tmpDS.PurchName = purchTableTmp.PurchName;
tmpDS.PurchId = purchTableTmp.PurchId;
tmpDS.ItemId = inventTableTmp.ItemId;
tmpDS.ItemGroupId = inventTableTmp.ItemGroupId;
tmpDS.DeliveryDate = purchLineTmp.DeliveryDate;
tmpDS.ConfirmedDlv = purchLineTmp.ConfirmedDlv;
tmpDS.Qty = inventTransTmp.Qty;
tmpDS.DatePhysical = inventTransTmp.DatePhysical;

tmpDS.insert();
}
}
}
}

Finally, in my form’s data source init method I have the following to set the table as temporary.

public void init()
{
super();
TmpOTD_Vendors_Table.setTmpData(tmpDS);
}

In my temp table I have the following fields: Qty, DatePhysical, OrderAccount, PurchName, PurchId, ItemId, DeliveryDate, ConfirmedDlv, InventTransId, ItemGroupId.

Any suggestions are much appreciated. Thank you.

Hi,

As far as I know the form datasource init method will get executed before form run method.

so the data should be inserted to table before the data source init method.

  • Arun

Please check this example for showing data in form from temp table

http://learnax.blogspot.com/2010/01/how-to-use-temporary-table-in-form.html

Hello Sayeed,

I checked out the example on this URL and attempted to duplicate his code just for testing. In addition to his code, I created added a Tab control, TabPage, and Grid to the form. I placed the 3-stringedit controls, i.e., ItemId, ItemName, and ItemGroupId on the grid.

However, I wasn’t able to get it to populate with any data. Thanks for the article though.

Hello Arun,

I appreciate your insight on the datasource’s init method being executed before the form run method.

I modified my code methods as follows: In the FormRun I added the line TmpOTD_Vendors_Table tmpDS;

I created a new method void populateRecords(). In this method I declared my table buffers, query and queryRun, QueryBuildDataSource, and QueryBuildRange objects. I then placed my query statements, followed by the call to setTmpData, i.e., TmpOTD_Vendors_Table.setTmpData(tmpDS);

Finally in my init method, after the call to super();, I called my new method populateRecords like so. this.populateRecords();

This actually queried the AOS and brought my form up populated with data from the temp table, which had been populated from the aforementioned 4-tables queried in my code.

Thank you for pointing out the problem in the code for me.