Skip to main content

Populating a form Grid through a Temp table using a query

Hello All,

I'm working in AX 2009. I have a form with a grid control, which I am trying to populate by using a Temporary table and an X++ query. The query is joining 4-tables, i.e., CustInvoiceTrans, CustInvoiceJour, InventDim, and CustTable.

The only range is on CustInvoiceTrans, InvoiceDate. I have set "Relations" to true for each, as CustInvoiceTrans.InvoiceId == CustInvoiceJour.InvoiceId and CustInvoiceTrans.InventDimId == InventDim.InventDimId and CustInvoiceJour.OrderAccount == CustTable.AccountNum.

The fields on the grid are from the temp table, which I dragged to the form's grid.

I have used a temp table before with success, but I am unable to get my grid to populate with data, although by placing a breakpoint in code and stepping thru the debugger I have been able to see data.

I have 3-methods in my project, the classDeclaration, the init, and a method named populateRecords, where I define the query class objects. The following is my code.

classDeclaration:
public class FormRun extends ObjectRun
{
CustInvoiceTrans custInvTransTmp;
CustInvoiceJour custInvJourTmp;
InventDim inventDimTmp;
CustTable custTableTmp;

TmpSales_by_State_Table tmpDS;
}

populateRecords()
void populateRecords()
{
Query query;
QueryRun queryRun;
QueryBuildDataSource qbds1,qbds2,qbds3,qbds4;
QueryBuildRange qr;

;

delete_from tmpDS;

query = new Query();
qbds1 = query.addDataSource(tableNum(CustInvoiceTrans));
qbds2 = qbds1.addDataSource(tableNum(CustInvoiceJour));
qbds2.relations(true);
qbds2.joinMode(JoinMode::InnerJoin);

qbds3 = qbds1.addDataSource(tableNum(InventDim));
qbds3.relations(true);
qbds3.joinMode(JoinMode::InnerJoin);

qbds4 = qbds2.addDataSource(tableNum(CustTable));
qbds4.relations(true);
qbds4.joinMode(JoinMode::InnerJoin);
qr = qbds1.addRange(fieldNum(CustInvoiceTrans, InvoiceDate));

queryRun = new QueryRun(query);


if (queryRun.prompt())
{
while (queryRun.next())
{
custInvTransTmp = queryRun.get(tableNum(CustInvoiceTrans));
custInvJourTmp = queryRun.get(tableNum(CustInvoiceJour));
inventDimTmp = queryRun.get(tableNum(InventDim));
custTableTmp = queryRun.get(tableNum(CustTable));

tmpDS.InvoiceId = custInvJourTmp.InvoiceId;
tmpDS.OrderAccount = custInvJourTmp.OrderAccount;
tmpDS.InvoiceDate = custInvTransTmp.InvoiceDate;
tmpDS.SalesId = custInvJourTmp.SalesId;
tmpDS.ItemId = custInvTransTmp.ItemId;
tmpDS.ExternalItemId = custInvTransTmp.ExternalItemId;
tmpDS.LineAmount = custInvTransTmp.LineAmount;
tmpDS.Qty = custInvTransTmp.Qty;
tmpDS.PurchaseOrder = custInvJourTmp.PurchaseOrder;
tmpDS.DueDate = custInvJourTmp.DueDate;
tmpDS.DeliveryName = custInvJourTmp.DeliveryName;
tmpDS.DlvCountryRegionId = custInvJourTmp.DlvCountryRegionId;
tmpDS.DeliveryCity = custInvJourTmp.DeliveryCity;
tmpDS.DlvState = custInvJourTmp.DlvState;
tmpDS.DlvZipCode = custInvJourTmp.DlvZipCode;
tmpDS.DeliveryAddress = custInvJourTmp.DeliveryAddress;
tmpDS.InventLocationId = inventDimTmp.InventLocationId;
tmpDS.InvoiceAccount = custInvJourTmp.InvoiceAccount;
tmpDS.InvoicingName = custInvJourTmp.InvoicingName;
tmpDS.InvCountryRegionId = custInvJourTmp.InvCountryRegionId;
tmpDS.InvoiceCity = custInvJourTmp.InvoiceCity;
tmpDS.InvState = custInvJourTmp.InvState;
tmpDS.InvZipCode = custInvJourTmp.InvZipCode;
tmpDS.InvoicingAddress = custInvJourTmp.InvoicingAddress;

tmpDS.insert();

}
}
TmpSales_by_State_Table.setTmpData(tmpDS);
}

init():
public void init()
{

super();

this.populateRecords();

}


It turned out that the Grid's DataSource property was lost after removing and readding fields to the grid. After resupplying this property, the grid successfully displays data.
Labels:

Popular posts from this blog

What does this mean: "The form datasource query object does not support changing its AllowCrossCompany property after the form has executed the query."?

I have made a form with datasources vendtable and vendtrans. Inside vendtable_ds.executequery() looks like this: QueryBuildDataSource queryBuildDatasource ,queryBDS_VendTrans_Invoice; ; queryBuildDatasource = this.query().dataSourceTable(tablenum(vendtable)); queryBDS_VendTrans_Invoice = this.query().dataSourceTable(tablenum(vendtrans)); if (curext() == "MASTERCOMP") { this.query().allowCrossCompany(true); } else { this.query().allowCrossCompany(false); } //FilterVendorName = stringedit control on form if (FilterVendorName.text()) { queryBuildDatasource.addRange(fieldNum(VendTable,Name)).value(strfmt("*%1*", FilterVendorName.text())); } else { queryBuildDatasource.clearRange(fieldNum(VendTable,Name)); } //FilterInvoiceNumber = stringedit control on form if (FilterInvoiceNumber.valueStr() == "") { queryBDS_VendTrans_Invoice.enabled(false); } else { queryBDS_VendTrans_Invoice.enabled(true); queryBDS_VendTrans_In...
Read more

Credit Note [Dynamics AX] using X++

This post will help to create credit note for a sales order based on the invent lot id. All the invoices raised for a particular sales line – Lot Id will be raised back as a credit note. Information on Credit Note: A credit note or credit memorandum (memo) is a commercial document issued by a seller to a buyer. The seller usually issues a Credit Memo for the same or lower amount than the invoice, and then repays the money to the buyer or sets it off against a balance due from other transactions Below Code will help to create credit note for all the invoices raised against the sales line -lot id. Please note: This code can be customized as per your requirements. This is just a template to help creating credit note using X++ code. Please test the code before use. static void SR_CreateCreditNote_Sales(Args _args) { // Coded by Sreenath Reddy CustInvoiceTrans custInvoiceTrans; Dialog dialog = new Dialog(“Create credit note – for sales.”); DialogField dfInv...
Read more