Wednesday 2 December 2015

How to add a range or data source to a form data source


Adding a second datasource with X++, or a range to a form datasource should be simple, yet today I spent ages trying strange things with multiple records. In the end it took me, two senior developers and a existing example in Dynamics AX to get to this simple solution. It might look silly, but I am definitely documenting this one for future reference!
I used to do the same thing by adding code in the form’s class declaration and the data source’s init and executeQuery methods. That is way too complex. Just add the code below to the data source’s init. Also, make sure you do this after the super() call, otherwise this query will not be initialized.
Add a range to a form
Suppose you have a form, with a PurchLine datasource added as a node in the AOT:

FormDatasource

If you only want to see purchase lines that are linked to open orders, then you need to add a range with PurchStatus equal to Backorder. You create this range in the data source’s init method:

  1. public void init()
  2. {
  3.     QueryBuildDataSource        qbdsPurchLine;
  4.  
  5.     super();
  6.  
  7.     qbdsPurchLine = this.query().dataSourceTable(tableNum(PurchLine));
  8.     qbdsPurchLine.addRange(fieldnum(PurchLine, PurchStatus)).value(queryvalue(PurchStatus::Backorder));
  9. }

Add an additional datasource to the form

 Now suppose you want to filter the PurchLine based on some criteria on the PurchTable. For example, you only want to see lines from derived intercompany orders. (This is just an example, PurchLine actually has it’s own intercompany origin field.) You could add an additional PurchTable datasource to the form, and join the datasources in the form properties. Every easier is adding the following to the init of the PurchLine datasource:

  1. public void init()
  2. {
  3.     QueryBuildDataSource        qbdsPurchLine;
  4.     QueryBuildDataSource        qbdsPurchTable;
  5.  
  6.     super();
  7.  
  8.     qbdsPurchLine = this.query().dataSourceTable(tableNum(PurchLine));
  9.     qbdsPurchLine.addRange(fieldnum(PurchLine, PurchStatus)).value(queryvalue(PurchStatus::Backorder));
  10.  
  11.     qbdsPurchTable = qbdsPurchLine.addDataSource(tableNum(PurchTable));
  12.     qbdsPurchTable.addLink(fieldNum(PurchTable, PurchId), fieldNum(PurchLine, PurchId));
  13.     qbdsPurchTable.addRange(fieldnum(PurchTable, InterCompanyOrigin)).value(queryValue(InterCompanyOrigin::Derived));
  14. }