[ ax2012 ] query object : join, where

SQL

SELECT LedgerJournalTrans.Voucher, DimensionAttributeValueCombination.DisplayValue
FROM [MicrosoftDynamicsAX].[dbo].[DimensionAttributeValueCombination], [MicrosoftDynamicsAX].[dbo].[LedgerJournalTrans]
WHERE LedgerJournalTrans.LedgerDimension = DimensionAttributeValueCombination.RecId
AND LedgerJournalTrans.Voucher='PSR14010006'
Ax query object ( Build new Query )
static void FSA_pongTestJob1(Args _args)
{
    ///// --- 1. declaration ---
    LedgerJournalTrans _LedgerJournalTrans;
    DimensionAttributeValueCombination _DimensionAttributeValueCombination;
    QueryBuildRange qbr;
    QueryRun qr;
    ///// -- 2. prepare query ---
    Query q = new Query();
    QueryBuildDataSource qbds,qbds1,qbds2;
    qbds = q.addDataSource(tableNum(LedgerJournalTrans));
    qbds1 = qbds.addDataSource(tableNum(DimensionAttributeValueCombination));
    // qbds2 = qbds1.addDataSource(tableNum(CustLedgerTransTypeMapping));
    qbds1.addLink(fieldNum(LedgerJournalTrans,LedgerDimension),fieldNum(DimensionAttributeValueCombination,RecId));
    // qbds2.addLink(fieldNum(CustTrans,TransType),fieldNum(CustLedgerTransTypeMapping,CustSettleTransType));
    info(q.toString());
    ///// --- 3. execute query ---
    qbr = qbds.addRange(fieldnum(LedgerJournalTrans, Voucher));
    qbr.value("PSR14010006");
    qr = new QueryRun(q);
    while(qr.next())
    {
        // do something
        _LedgerJournalTrans = qr.get(tableNum(LedgerJournalTrans));
        info(strFmt('%1', _LedgerJournalTrans.Voucher));
        _DimensionAttributeValueCombination = qr.get(tableNum(DimensionAttributeValueCombination));
        info(strFmt('%1 %2', _LedgerJournalTrans.Voucher, _DimensionAttributeValueCombination.DisplayValue));
    }
}

Another Join Example in Query, same conceptsamesameQueryJoinExampleAx2012select only some fields ( ref )

//This will enable you to add the fields you like
//If dynamics is yes all the fields will be automatically added
custTableQBDS.fields().dynamic(NoYes::No);
qbFl = custTableQBDS.fields().addField(fieldNum(CustTable, AccountNum));
qbF2 = custTableQBDS.fields().addField(fieldNum(CustTable, InvoiceAccount));
qbF3 = custTableQBDS.fields().addField(fieldNum(CustTable, CustGroup));

get query from query object ( ref )

info(this.query().dataSourceNo(1).toString());

select statement

or alternatively we can use `select statement`

This also can use group by ( see microsoft )

Modified existing query

use dataSourceTable(tableNum(TableName))

see example in EcoResAttributeValue

[ ax2012 ] Product dimensions v.s. Storage dimensions v.s. Tracking dimensions

1) Product dimensions

1. Size
2. Color
3. Configuration

2) Storage dimensions ( tick Active to activate )

1. Site
2. Warehouse
3. Location
4. Pallet id

· tick Mandatory, To make warehouse a mandatory.
· tick ‘Primary stocking’, To make Primary stocking active for warehouse

3) Tracking dimensions

1. Serial number
2. Batch number

 

ref : ramdynamicsax

[ ax2012 ] EcoResAttributeValue form

     // EcoResAttribute
    //   EcoResAttributeType (Active JOIN)
    //     EcoResBoundedAttributeTypeValue (OUTER JOIN)
    //   EcoResCategoryAttributeLookup
    //     EcoResCategory (WHERE CategoryHierarchy is Procurement)
    //       EcoResProductCategory
    //         EcoResProduct (WHERE RecId=givenRecId)
    //   EcoResProductAttributeValue (OUTER JOIN; WHERE Product=givenRecId && Attribute=EcoResAttribute.RecId)
    //     EcoResValue (OUTER JOIN)

Example : Join in datasources ie_ecoResCategoryAttributeLookup

 

Join using which field is defined in the Data Dictionary > Table >  EcoResCategoryAttributeLookup > Relations

join_fields

Example : WHERE in EcoResProduct > Methods > init

public void init()
{
QueryBuildDataSource qbds;

super();
// To modify or replace the query automatically created by the form, do this after the super() call of this method.

qbds = ecoResProduct_ds.query().dataSourceTable(tableNum(EcoResProduct));
qbds.addRange(fieldNum(EcoResProduct, RecId)).value(queryValue(callerMasterOrDistinctProduct.RecId));
}

EcoResProductAttributeValue

public void init()
{
QueryBuildDataSource qbds;

super();

qbds = ecoResProductAttributeValue_ds.query().dataSourceTable(tableNum(EcoResProductAttributeValue));
qbds.addRange(fieldNum(EcoResProductAttributeValue, Product)).value(queryValue(callerMasterOrDistinctProduct.RecId));
qbds.addLink(fieldNum(EcoResAttribute, RecId), fieldNum(EcoResProductAttributeValue, Attribute));
}

NOTE : auto-generated fields in the table
– recVersion
– Partition
– RecId

[ ax2012 ] print to send mail

Goal : print PO by send mail

0. set e-mail parameters like in this

System administration > Setup > System > E-mail parameters

1. Set at vendor

General > Print mangement

รูปภาพ

2. right-click > override

enter email address

\รูปภาพ

3. when print PO, tick use print management

รูปภาพ

รูปภาพ

 

If you are running CU7, you can send to specific vendor email contact using suggestion in msdn.

NOTE : if not using print management ( use Printer setup > Purchase Order ) it will pop outlook program to send mail, not sending from the mail server.