Dimension values from Ledger Dimension

Below code will get you the Default Dimension by passing the Ledger Dimension

public str getBusinessUnit(RecId _ledgerDimension)
{

// DimensionAttributeValueCombination stores the combinations of dimension values
// Any tables that uses dimension combinations for main account and dimensions
// Has a reference to this table’s recid
DimensionAttributeValueCombination dimAttrValueComb;
//GeneralJournalAccountEntry is one such tables that refrences DimensionAttributeValueCombination
GeneralJournalAccountEntry gjAccEntry;
// Class Dimension storage is used to store and manipulate the values of combination
DimensionStorage dimensionStorage;
// Class DimensionStorageSegment will get specfic segments based on hierarchies
DimensionStorageSegment segment;
int segmentCount, segmentIndex;
int hierarchyCount, hierarchyIndex;
str segmentName, segmentDescription;
SysDim segmentValue;
;

dimAttrValueComb = DimensionAttributeValueCombination::find(_ledgerDimension);

// Get dimension storage
dimensionStorage = DimensionStorage::findById(_ledgerDimension);
if (dimensionStorage == null)
{
throw error("@SYS83964");
}

// Get hierarchy count
hierarchyCount = dimensionStorage.hierarchyCount();
//Loop through hierarchies to get individual segments
for(hierarchyIndex = 1; hierarchyIndex <= hierarchyCount; hierarchyIndex++)
{

//Get segment count for hierarchy
segmentCount = dimensionStorage.segmentCountForHierarchy(hierarchyIndex);

//Loop through segments and display required values
for (segmentIndex = 1; segmentIndex <= segmentCount; segmentIndex++)
{
// Get segment
segment = dimensionStorage.getSegmentForHierarchy(hierarchyIndex, segmentIndex);

// Get the segment information
if (segment.parmDimensionAttributeValueId() != 0)
{
// Get segment name
segmentName = DimensionAttribute::find(DimensionAttributeValue::find(segment.parmDimensionAttributeValueId()).DimensionAttribute).Name;
//Get segment value (id of the dimension)
segmentValue = segment.parmDisplayValue();
if(segmentName =="BusinessUnit")            //Give the Dimension Name to that Dimension Value
{
return segmentValue;
}
}
}
}
return "";
}
Advertisements

Getting Dimension values from Default Dimension

From the below  we can get all the Default Dimension values:

Method 1:

DimensionAttributeValueSetStorage dimStorage;
Counter i;
str dimensionValue;
RefRecId defaultdim;
DirPartyName dimensionValue1;

defaultdim = 5637176154; //this number is basically from field DefaultDimension
dimStorage = DimensionAttributeValueSetStorage::find(defaultdim);

for (i=1 ; i&amp;lt;= dimStorage.elements() ; i++)
{
if(DimensionAttribute::find(dimStorage.getAttributeByIndex(i)).Name=='BusinessUnit') //Give the Attribute you want to get the value for.
{
dimensionValue = dimStorage.getDisplayValueByIndex(i);
dimensionValue1 = DirPartyTable::findRec(VendTable::find(dimensionValue).Party).Name;//To get the Vend Name
break;
}
}
info(strFmt(&quot;Business Unit: %1&quot;,dimensionValue));

Method 2:


DimensionAttributeValueSet dimAttrValueSet;
DimensionAttributeValueSetItem dimAttrValueSetItem;
DimensionAttributeValue dimAttrValue;
DimensionAttribute dimAttribute;
RefRecID dimensionSetRecID;
Name attributeName;
;
dimensionSetRecID = 5637176154; //this number is basically from field DefaultDimension
attributeName = 'BusinessUnit'; //put here the name of dimension
dimAttrValueSet = DimensionAttributeValueSet::find(dimensionSetRecID);

select dimAttrValueSetItem
where dimAttrValueSetItem.DimensionAttributeValueSet == dimAttrValueSet.RecId
join dimAttrValue
where dimAttrValue.RecId == dimAttrValueSetItem.DimensionAttributeValue
join dimAttribute
where dimAttribute.RecId == dimAttrValue.DimensionAttribute
&amp;amp;&amp;amp; dimAttribute.Name == attributeName;
info(strFmt(&quot;Business Unit: %1----%2&quot;,dimAttrValue.getValue(),dimAttrValue.getName()));

OR

Passing current record buffer


[SysClientCacheDataMethodAttribute(true)]
display DimensionValue BusinessUnit(InventJournalTrans _inventJournalTrans)//Passing current record buffer
{

DimensionAttributeValueSetStorage dimStorage;
Counter i;
DimensionValue dimensionValue;
RefRecId defaultdim;

defaultdim = _inventJournalTrans.DefaultDimension; //this number is basically from field DefaultDimension
dimStorage = DimensionAttributeValueSetStorage::find(defaultdim);

for (i=1 ; i&amp;lt;= dimStorage.elements() ; i++)
{
if(DimensionAttribute::find(dimStorage.getAttributeByIndex(i)).Name=='BusinessUnit') //Give the Attribute you want to get the value for.
{
dimensionValue = dimStorage.getDisplayValueByIndex(i);
break;
}
}
return dimensionValue;
}

OR

Adding the Code at Global Class


Eg: To use this method:
tempTable.DimensionValue= getDefaultDimensionValue("BusinessUnit",prodTable.DefaultDimension);
//To Get the default dimension value by using default dimension id
static str 50 getDefaultDimensionValue(str _dimName,DimensionDefault _defaultDimension)
{
 DimensionAttributeValueSetStorage dimStorage;
 CustTable custTable;
 Counter i;
 str returnValue;
 dimStorage = DimensionAttributeValueSetStorage::find(_defaultDimension);
 for (i=1 ; i<= dimStorage.elements() ; i++)
 {
 if(DimensionAttribute::find(dimStorage.getAttributeByIndex(i)).Name == _dimName)
 {
 returnValue = dimStorage.getDisplayValueByIndex(i);
 break;
 }
 }
 return returnValue;
}

Form Templates In AX

Below are the Form Templates widely in ax 2012.
Lets look at this now..

  • ListPage
  • DetailsFormMaster
  • DetailsFormTransaction
  • SimpleListDetails
  • SimpleList
  • TableOfContents
  • Dialog
  • DropDialog

Let us look at these in more details.

ListPage – A list page is a form that displays a list of data related to a particular entity or business object. A list page provides provisions for displaying data and taking actions on this data. Every module has at least a couple of list pages. List pages are further classified as primary and secondary list pages. A secondary list page will only display a subset of data from the primary list page. Example, CustTableListPage, VendTableListPage, ProjProjectsListPage. Best practice is to have ListPage as a suffix in the name of the form for all list pages.

DetailsFormMaster – This template is used for forms which displays data for stand-alone entities or business objects. Example, Customers, Vendors, Projects etc. If you look at the forms for these, i.e., CustTable, VendTable, ProjTable, their style property will be set to DetailsFormMaster.

DetailsFormTransaction – This template is used for forms which displays data for entities which have child records associated with it. In other words, if the data being displayed is header-lines type in nature, use this template. Example, Sales orders, Purchase orders etc. If you look at the style property of SalesTable, VendTable, their properties will be set to DetailsFormTransaction.

SimpleListDetails – This template is used to display primary fields in a list and detailed data in a tab page. This style is useful to view all records in a form and selecting a particular record will bring up their details. Example, MainAccount

SimpleList – This template is a very basic form which displays data in a grid. No extra or fancy detail is displayed. This style is best suited for forms where data being shown is not very detailed in nature or has limited fields. Example, AifAction.

TableOfContents – This template is the new style which should be adopted for all parameter forms in Dynamics AX 2012. Take a look at any parameters form and its style property will be set to TableOfContents. This style sets all the tabs as a hot link in the left hand side navigation pane. Clicking on the link will bring up the controls on that tab page. This style is a very neat and appealing UI design which is surely welcome.

Dialog – This template is used on forms which shows static data or are intended to capture some user input for further actions. These forms are mostly modal in nature and should be dismissed before any further actions can be taken.

DropDialog – This template is used for forms that are used to gather quick user inputs to perform an action. Drop dialog forms are generally attached to an action pane button. They appear to be dropping from the menu button when clicked.

Excel Import with Dialog

static void FixedAssetImport(Args _args)
{
    Num serialnumber;
    AssetTable assetTable;
    AssetId assetId;
    AssetGroupId assetGroupId;
    Dialog dialog;
    Filename filename;
    DialogField dialogFilename;
    //To filter the files while selecting
    container conFilter = ["Microsoft Excel 97-2003 Worksheet (.xls)" ,"*.xlsx"];
    
    //Excel
    SysExcelApplication application;
    SysExcelWorkbooks workbooks;
    SysExcelWorkbook workbook;
    SysExcelWorksheets worksheets;
    SysExcelWorksheet worksheet;
    SysExcelCells cells;
    COMVariantType type;
    int row=1;
    application = SysExcelApplication::construct();
    workbooks = application.workbooks();
    dialog = new dialog();
    dialog.caption("select a file");
    dialogFilename = dialog.addField(extendedTypeStr(FilenameOpen));
    //To filter the files while selecting
    dialog.filenameLookupFilter(conFilter);
    dialog.run();
    
    if(dialog.closedOk())
    {
        filename = dialogFileName.value();
    }
    
    try
    {
        workbooks.open(filename);
    }
    catch (Exception::Error)
    {
        throw error("File cannot be opened.");
    }
    
    workbook = workbooks.item(1);
    worksheets = workbook.worksheets();
    worksheet = worksheets.itemFromNum(1);
    cells = worksheet.cells();
    
    do
    {
        row++;
        assetGroupId = cells.item(row,1).value().bStr();
        assetId = cells.item(row,2).value().bStr();
        serialnumber = cells.item(row,3).value().bStr();
        
        if( assetGroupId && assetId && serialnumber)
        {
            select forupdate AssetGroup,AssetId,Recid from AssetTable
                                    where assetTable.SerialNum == "" &&
                                    assetTable.AssetGroup == assetGroupId &&
                                    assetTable.AssetId == assetId;
        
            if(AssetTable.RecId)
            {
                assetTable.SerialNum = serialnumber ;
                assetTable.update();
            }
            else
            {
                error(strFmt("Please check the values for AssetId:%1",assetId));
            }
            
        }
        else
        {
            error(strFmt("Please check the values for AssetId:%1",assetId));
        }
        
        type = cells.item(row+1, 1).value().variantType();
    }
    while(type != COMVariantType::VT_EMPTY);
    
    workbooks.close();
    application.quit();
    
    info("Operation/Processing Completed");

}

Business Unit Lookup in Form

Just add the below code in lookup() of StringEdit control in Form to get the Business Unit Lookup:

public void lookup()
{
Query query;
SysTableLookup sysTableLookup;
super();

//DimAttributeOMBusinessUnit is a View
sysTableLookup = SysTableLookup::newParameters(tableNum(DimAttributeOMBusinessUnit), this);

sysTableLookup.addLookupfield(fieldNum(DimAttributeOMBusinessUnit, Value));
sysTableLookup.addLookupfield(fieldNum(DimAttributeOMBusinessUnit, Name));

query = new Query();

query.addDataSource(tableNum(DimAttributeOMBusinessUnit));

sysTableLookup.parmQuery(query);

sysTableLookup.performFormLookup();

}

Query Ranges in X++

Expressions in query ranges

One of least understood but most powerful Axapta features is the so-called Expressions in query ranges syntax. This is not the same as simply using a QueryBuildRange object in a query and specifying criteria for a single field.

Introduction

This is a method of specifying ranges on queries which allows you to perform complex comparisons, and create complex join situations which would be impossible using the standard syntax.

Syntax

To use the special syntax, you should first add a range to your QueryBuildDataSource object in the normal way. Note that for this special syntax, it does not matter which field you use to add the range.

To specify the range value itself, certain rules must be followed:

  • The entire expression must be enclosed within single-quotes, notdouble-quotes
  • The entire expression must be enclosed in parenthesis (brackets)
  • Each sub-expression must be enclosed in its own set of parenthesis
  • For fields in the current table, simply the field name can be used
  • For fields in other tables, a prefix of the relevant datasource namemust be added. This is not always the same as the table name.
  • String values should be surrounded by double-quotes, and wrapped in a call to queryValue()
  • Enum values should be specified by their integer value
  • Date values should be formatted using Date2StrXpp()
  • Blank string like ‘ ‘ will not work as expected, use sysquery::valueEmptyString().

Examples

In the example below, we construct a query and add a single datasource.

The range is then added, using the DataAreaId field on each table. Any field can be used, but using an unusual one such as DataAreaId helps remind a casual reader of the code that it’s not a normal range.

query = new Query();

dsInventTable = query.addDataSource(tableNum(InventTable));

// Add our range

queryBuildRange = dsInventTable.addRange(fieldNum(InventTable, DataAreaId));

Given the above, the following are valid range specifications:

Simple criteria

Find the record where Item ID is B-R14. Take note of the single quotes and parenthesis surrounding the entire expression.

queryBuildRange.value(strFmt('(ItemId == "%1")', queryValue("B-R14")));

Find records where the Item Type is Service. Note the use of any2int().

queryBuildRange.value(strFmt('(ItemType == %1)', any2int(ItemType::Service)));

Find records where the ItemType is Service or the ItemId is B-R14. Note the nesting of the parenthesis in this example.

queryBuildRange.value(strFmt('((ItemType == %1) || (ItemId == "%2"))',

any2int(ItemType::Service),

queryValue("B-R14")));

Find the Record where the ItemId is NOT B-R14

 qbr.value(strFmt('!%1',"B-R14"));
  <strong>or
</strong> qbr.value(strfmt("!B-R14"));

Find records where the modified date is after 1st January 2000. Note the use of Date2StrXpp() to format the date correctly.

queryBuildRange.value(strFmt('(ModifiedDate &gt; %1)', Date2StrXpp(01012000)));

Find records where the Field is blank (null) or an empty string. For more see Sys::Query Docs

qbrStatement = this.query().dataSourceName("BankAccountTrans2").addRange(fieldnum(BankAccountTrans,AccountStatement));
//qbrStatement.value("!?*");//this is the old way that may not work in future versions of AX
qbrStatement.value(sysquery::valueEmptyString());//this is the new way 

Complex criteria with combined AND and OR clauses

Find all records where the ItemType is Service, or both the ItemType is Item and the ProjCategoryId is Spares. This is not possible to achieve using the standard range syntax.
Note also that in this example, we are using the fieldStr() method to specify our actual field names and again, that we have nested our parenthesis for each sub-expression.

queryBuildRange.value(strFmt('((%1 == %2) || ((%1 == %3) &amp;&amp; (%4 == "%5")))',
    fieldStr(InventTable, ItemType),
    any2int(ItemType::Service),
    any2int(ItemType::Item),
    fieldStr(InventTable, ProjCategoryId),
    queryValue("Spares")));

WHERE clauses referencing fields from multiple tables

For this example below, we construct a query consisting of two joined datasources (using an Exists join). Note that we specify the datasource names when adding the datasources to the query.
The ranges are then added, using the DataAreaId field on each table as described in the earlier example.

query = new Query();
dsInventTable = query.addDataSource(tableNum(InventTable), tableStr(InventTable));
dsInventItemBarCode = dsInventTable.addDataSource(tableNum(InventItemBarCode), tableStr(InventItemBarCode));
dsInventItemBarCode.relations(true);
dsInventItemBarCode.joinMode(JoinMode::ExistsJoin);
 
// Add our two ranges
queryBuildRange1 = dsInventTable.addRange(fieldNum(InventTable, DataAreaId));
queryBuildRange2 = dsInventItemBarCode.addRange(fieldNum(InventItemBarCode, DataAreaId));

Find all records where a bar code record exists for an item and was modified later than the item was modified.

In this example, we are using the range on the BarCode table. Therefore the unqualified ModifiedDate reference will relate to InventItemBarCode.ModifiedDate. The other field is a fully-qualified one, using the DatasourceName.FieldName syntax.

queryBuildRange2.value(strFmt('(ModifiedDate &gt; InventTable.ModifiedDate)'));

Note that if we had added our InventTable datasource using the following code

dsInventTable = query.addDataSource(tableNum(InventTable), "InventTableCustomName"); // Note that we are manually specifying a different datasource name 

then the query range would need to appear as follows

queryBuildRange2.value(strFmt('(ModifiedDate &gt; InventTableCustomName.ModifiedDate)'));

Conditional joins

We will modify our previous example slightly, to remove the automatic addition of relations for the join.

query = new Query();
dsInventTable = query.addDataSource(tableNum(InventTable), "InventTable");
dsInventItemBarCode = dsInventTable.addDataSource(tableNum(InventItemBarCode), "InventItemBarCode");
dsInventItemBarCode.joinMode(JoinMode::ExistsJoin);

// Add our two ranges
queryBuildRange1 = dsInventTable.addRange(fieldNum(InventTable, DataAreaId));
queryBuildRange2 = dsInventItemBarCode.addRange(fieldNum(InventItemBarCode, DataAreaId));

We can now use the query expression to specify whatever we like as the join criteria.

Find all records where either the ItemType is Service, or the ItemType is Item and a barcode exists. The join criteria is only applied in the second half of the expression, so all Service items will appear irrespective of whether they have a bar code. Again, this is not possible to achieve using the standard query ranges.

    queryBuildRange2.value(strFmt('((%1.%2 == %3) || ((%1.%2 == %4) &amp;&amp; (%1.%5 == %6)))',
    query.dataSourceTable(tableNum(InventTable)).name(), // InventTable %1
    fieldStr(InventTable, ItemType), // ItemType %2
    any2int(ItemType::Service), // %3
    any2int(ItemType::Item), // %4
    fieldStr(InventTable, ItemId), // ItemId %5
    fieldStr(InventItemBarCode, ItemId))); // %6 

Using the techniques above, it is possible to create queries with almost as much flexibility as using SQL statements directly.

Filter on array fields

    queryBuildRange.value(strFmt('((%1.%2 == "%4") || (%1.%3 == "%5"))', 
    queryBuildDataSource.name(),
    fieldid2name(tablenum(
), fieldid2ext(fieldnum(, Dimension), Dimensions::code2ArrayIdx(SysDimension::Center))), fieldid2name(tablenum(), fieldid2ext(fieldnum(, Dimension), Dimensions::code2ArrayIdx(SysDimension::Purpose))), "some dim2 value", "some dim3 value"));

Note: you must always specify the datasource name if you use Query Expression syntax to filter on array fields. See also Limitations section at the bottom of the page.

Using wildcards and comma-separated range values

Again, the previous example here was using standard syntax, not the special syntax using expressions. It’s not possible to modify the above examples to work with wildcards.
The above statement applies to AX versions < 5.0
AX 5.0 introduced solution to wildcards – while you still cannot directly use wildcards in ranges, now it supports the ‘LIKE’ keyword.

(AccountNum LIKE "*AA*" || Name LIKE "*AA*")