Dynamic query x++ D365FO

// **Create a data source for LedgerJournalTrans table in the query**

QueryBuildDataSource ledgerJournalTrans_ds = query.addDataSource(tableNum(LedgerJournalTrans));

// **Prevent adding additional fields to the query (restrict fields auto-expansion)**

ledgerJournalTrans_ds.allowAdd(QueryAllowAdd::NoFields);

// **Enable update access on this datasource**

ledgerJournalTrans_ds.update(true);

// **Add a range for filtering by AccountType (Cust/Vend/Ledger etc.)**

ledgerJournalTrans_ds.addRange(fieldNum(LedgerJournalTrans, AccountType));

// **Add a dimension attribute range on LedgerDimension (main account dimension filtering)**

SysQuery::addDimensionAttributeRange( query,ledgerJournalTrans_ds.name(),fieldStr(LedgerJournalTrans, LedgerDimension),

    DimensionComponent::LedgerDimensionDisplayValue,'', '', true);

// **Add a range for filtering by OffsetAccountType**

ledgerJournalTrans_ds.addRange(fieldNum(LedgerJournalTrans, OffsetAccountType));

// **Add a dimension attribute range on OffsetLedgerDimension (offset account dimension filtering)**

SysQuery::addDimensionAttributeRange(query,ledgerJournalTrans_ds.name(),fieldStr(LedgerJournalTrans, OffsetLedgerDimension),DimensionComponent::LedgerDimensionDisplayValue, '', '', true);

// **Add a locked range on PaymentStatus field**

qbdsLedgerJournalTrans.addRange(fieldNum(LedgerJournalTrans, PaymentStatus)).status(RangeStatus::Locked);

// **Add dimension attribute filters for all dimensions that belong to Ledger dimension set**

DimensionAttributeSetItem dimAttrSetItemTb;

while select DimensionAttribute from dimAttrSetItemTb where dimAttrSetItemTb.DimensionAttributeSet ==DimensionCache::getDimensionAttributeSetForLedger()

{

    SysQuery::addDimensionAttributeRange(query,ledgerJournalTrans_ds.name(),

        fieldStr(LedgerJournalTrans, DefaultDimension),DimensionComponent::DimensionAttribute,

        '', DimensionAttribute::find(dimAttrSetItemTb.DimensionAttribute).Name);

}

// **Filter ledger lines where PaymentStatus is either None or Rejected**

ledgerJournalTrans_ds.rangeField(fieldNum(LedgerJournalTrans, PaymentStatus))

                     .value(strFmt('%1,%2', CustVendPaymStatus::None, CustVendPaymStatus::Rejected));

// **Join VendPaymTable with LedgerJournalTrans on PaymMode field**

QueryBuildDataSource qbdsPaymModeTable = qbdsLedgerJournalTrans.addDataSource(tableNum(VendPaymTable));

qbdsPaymModeTable.addLink(fieldNum(LedgerJournalTrans, PaymMode), tableNum(VendPaymTable), fieldNum(VendPaymTable, PaymMode));

// **Make it an InnerJoin (only matching records are included)**

qbdsPaymModeTable.joinMode(JoinMode::InnerJoin);

// **Allow updates on VendPaymTable datasource**

qbdsPaymModeTable.update(true);

// **Disable this datasource (fields not directly editable in query execution)**

qbdsPaymModeTable.enabled(false);