Query execution using x++

 In this article I will be going over how we can write a code based query to retrieve and filter data from the database without having to write a select statement.

For this example, I am going to extract the customer and sales order information for a specific customer.

Query query = new Query();
QueryBuildDataSource salesOrderQueryBuildDataSource = query.addDataSource(tableNum(SalesTable));

// add range for sales order with Id 1003
salesOrderQueryBuildDataSource.addRange(fieldNum(SalesTable, SalesId)).value(queryValue('1003'));

// join the customer table to retrieve the customer details
QueryBuildDataSource custTableQueryBuildDataSource =
    salesOrderQueryBuildDataSource.addDataSource(tableNum(CustTable));

// add join type
custTableQueryBuildDataSource.joinMode(JoinMode::Exists);

// add join link
custTableQueryBuildDataSource.addLink(fieldNum(SalesTable, CustAccount), fieldNum(CustTable, AccountNum));

// initiate query execution
QueryRun queryRun = new QueryRun(query);

// execute query
while (queryRun.next())
{
    // get the records for processing
    SalesTable salesTable = queryRun.get(tableNum(SalesTable));
    CustTable custTable = queryRun.get(tableNum(CustTable));

    // infolog to display selected records
    info(strFmt('Current record - Sales ID: %1, Customer Name: %2', salesTable.SalesId, custTable.name()));
}

Comments

Post a Comment

Popular posts from this blog

D365FO - Create a multi-select lookup in batch jobs

Image
D365FO - Create a multi-select lookup in batch jobs In this article, I will be describing how we can create a multi-select lookup for display within the batch job dialog. Contract class Create a new variable of type 'List'. This will store the selected values of the user for the dropdown from the dialog. Create a parm method to access the new list object Ensure to specify the DataMember attribute Add an appropriate label Specify the data type for the list's content Ensure to specify the DataContract and SysOperationContactProcessing attributes above the class declaration. An UI builder is also required. The requirements for this is explained in the next section. Example: UI builder class Create an UI builder class that extends SrsReportDataContractUIBuilder Add a new variable of type 'DialogField'. This will be displayed on the UI for user. Implement the postBuild method. Initialize the contract from this.dataContractObject() and bind the dialog field variable to th...
Read more

Generate and download a csv file using X++

Within Dynamics 365 HQ we often need to be able to export data as a csv file. This article explains how we can generate a csv file. The following lines of code can be used to create and download a csv file Create a writable stream. CommaStreamIo streamIO = CommaStreamIo::constructForWrite();   streamIO.writeExp('Id', 'Title'); // These will represent the header of the csv file  streamIO.writeExp('001', 'Science'); // Row 1 streamIO.writeExp('002', 'Nature'); // Row 2 // Get the stream System.IO.Stream        stream              = streamIO.getStream(); const str               extensionCsv        = '.csv'; stream.Position = 0; // StreamReader to read as str System.IO.StreamReader...
Read more

Creating a batch job with query based filter in D365 back-office

There are often business processes that requires us to run specific operations as a batch process. Dynamics 365 Head quarters (HQ) allows us to be able to periodically execute batch jobs. To create a custom batch job you need to extend the  RunBaseBatch class. The following lines of code show how we can create a simple export job.  class InvoiceExport extends RunBaseBatch {      Query                   query;     QueryRun                invoiceQueryRun; // QueryRun object that will be used to generate data for invoices to be exported.      DialogGroup             dialogGroupSetup; // This is a dialog group.     DialogField      ...
Read more