Get Financial Dimension using default dimension through x++ code and Sql in Dynamics AX #D365forOps

Below is the X++ code snippet for getting the Financial Dimensions using Default Dimension by using X++ code:

private str getFinancialDimensionName(Name _dimAttrName, DimensionDefault _defaultDimension)

    {

        DimensionAttribute dimAttr;

        DimensionAttributeValueSetStorage dimStorage;

        DimensionAttributeValue     dimAttrValue;

        Common                      common;

        DictTable                   dictTable;

        select firstOnly dimAttr where dimAttr.Name == _dimAttrName;

        if(dimAttr)

        {

            dimStorage = DimensionAttributeValueSetStorage::find(_defaultDimension);

            select firstonly dimAttrValue

            where dimAttrValue.RecId == dimStorage.getValueByDimensionAttribute(dimAttr.recID)

            join dimAttr

            where dimAttr.RecId == dimAttrValue.DimensionAttribute;

            if (dimAttrValue)

            {

                dictTable = new DictTable(dimAttr.BackingEntityType);

                common = dictTable.makeRecord();

                if (common.TableId)

                {

                    select common where common.(dimAttr.KeyAttribute) == dimAttrValue.EntityInstance;

                    return common.(dimAttr.NameAttribute);

                }

            }

        }

        return "";
}

Where _dimAttrName = name of the dimension attribute like Department, Location etc

and _defaultDimension = value of the defaultDimension like value in DefaultDimension field of table HcmEmployment.

Below is the code snippet to get the department and location in Sql using default dimension.

select dimensionFinancialTag.description from  DefaultDimensionView

join dimensionFinancialTag on dimensionFinancialTag.VALUE = DefaultDimensionView.DISPLAYVALUE

    where DefaultDimensionView.DefaultDimension = 5637144783

      and DefaultDimensionView.Name = 'Location' 

select DisplayValue, dimAttributeOMDepartment.NAME from defaultDimensionView

 join dimAttributeOMDepartment

            on dimAttributeOMDepartment.Value = defaultDimensionView.DisplayValue

        where defaultDimensionView.DefaultDimension = 5637144783

            and defaultDimensionView.name = 'Department'

For Any Question feel free to Comment.

Thanks,

Zahid

Renaming Database Name using Query in SQL Server

Sometimes if we try to rename the database in our development or any other environment we got the error message that this database is in use by other process and we cant rename it.

So first we need to check if any user is not using that database and try to rename it. If still we got the same error then might be some services using that database.

Instead of investing time to investigate which service is causing the issue to rename the database you can simply write the below query to rename your database.

ALTER DATABASE oldatabasename SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

ALTER DATABASE olddatabasename MODIFY NAME = newdatabasename

ALTER DATABASE newdatabasename SET MULTI_USER WITH ROLLBACK IMMEDIATE;


Thanks,
Zahid

Virtual Fields in Data Entity in Microsoft Dynamics 365 For Operations

Some times we need to populate fields on Data Entity based on some calculated X++ logic instead of binding them directly from table.

To do this below are the some simple steps.

1. right click your fields node of your data entity and than select the string Unmapped field.

note: you can select other type of umapped field if your field is not string.

2. After that go to the properties of that field set the name , label , extended data type and is computed field to no.

After that right click your data entity and than override the postLoad() method.

and in that postload method you can write your X++ logic to populate that field.

Now after that once you rebuild your code and try to export data entity you will see this field will be populated in data entity.

Note: Data entities are saved as view on back end in sql and the main drawback of using virtual field is that you can see them in sql.You can only see when you use that entity with in Dynamics Domain.

Thanks,
Zahid

Getting Dimension Values from Financial Dimensions in Dynamics 365 For Operations

I got one tasks of report development in which I have to show the Financal Dimension and its Dimension Value in a lookup on report parameter.

To do this the one I have to grab all the Dimension Values against the Financial Dimension to show in a lookup.

Below is the navigation for Financial Dimension Form in D365:

General Ledger -> Chart of Accounts -> Dimension -> Financial Dimensions.

The form for Financial Dimension is.

As you can see in the above image there is a dimension value button when you click on it a new form will open containing all of the dimensions against that selected Financial Dimension.

So the question is how can we get the Dimension Values based Financial Dimension by using query.

Below is the code for you:

class RunnableClass1
{       
    /// <summary>
    /// Runs the class with the specified arguments.
    /// </summary>
    /// <param name = "_args">The specified arguments.</param>
    public static void main(Args _args)
    {   
        DimensionAttribute              dimAttr;
        DimensionAttributeViewContract  dimAttrViewContract;
        Query                           q;
        QueryRun                        qR;
        QueryBuildDataSource            qbds;
        Common                          common;

        dimAttr = DimensionAttribute::findByName("Department");


        dimAttrViewContract =  DimensionAttribute::getViewContract(dimAttr.RecId);

        q = new Query();

        changecompany (dimAttr.company())
        {
            qbds = q.addDataSource(dimAttrViewContract.parmViewId());
            qbds.addSortField(dimAttrViewContract.parmValueFieldId(),SortOrder::Ascending);

            // Apply ranges on language ID fields.
            DimensionAttribute::addTranslViewRangesToBackingEntityQuery(dimAttr.RecId, qbds);

            if (DimensionCache::instance().dimensionAttributeHasCategorization(dimAttr.RecId))
            {
                if (dimAttr.Type == DimensionAttributeType::MainAccount)
                {
                    qbds.addRange(dimAttrViewContract.parmCategoryFieldId()).value(queryValue(LedgerChartOfAccounts::current()));
                }
                else if (dimAttr.Type == DimensionAttributeType::CustomList)
                {
                    qbds = qbds.addDataSource(tablenum(DimensionAttributeDirCategory), 'DimAttDirCat');
                    qbds.relations(true);
                    qbds.joinMode(JoinMode::InnerJoin);
                    qbds.fetchMode(QueryFetchMode::One2One);
                    qbds.addLink(dimAttrViewContract.parmCategoryFieldId(), fieldnum(DimensionAttributeDirCategory, DirCategory));
                    qbds.addRange(fieldnum(DimensionAttributeDirCategory, DimensionAttribute)).value(strfmt('%1', dimAttr.RecId));
                }
            }
        }

        qR = new QueryRun(q);
        while(qR.next())
        {
            common = qR.get(dimAttrViewContract.parmViewId());

            info(strFmt("%1 %2",common.getFieldValue(dimAttrViewContract.parmValueFieldName()), common.getFieldValue(dimAttrViewContract.parmNameFieldName())));
        }
    }

}

For more Details Below is the link:

https://community.dynamics.com/ax/b/hellodax/archive/2015/07/07/get-all-financial-dimension-value-and-description-from-dimensionattribute

Thanks,
Zahid

Open Table Browser in other than Dat Company in Dynamics 365 for Operations

Some time we want to open the table browser in other than DAT company in Dynamics 365 for operations.

To open table browser in other company below is the link that contains the full description:

https://community.dynamics.com/crm/b/microsoftdynamicscrmsolutions/archive/2016/01/25/open-table-browser-with-different-legal-entity-in-ax-7


Thanks,
Zahid. 

Resolved: The field with ID '21' does not exist in table 'smmContactPersonEntityCopy'.

One day one of my Integration Teams was trying to insert records from CRM to Dynamics 365 for Operations using the integration tool i,e Logic apps.

There logic app was configured correctly and they were successfully able to fetch the records from CRM but when they were trying to insert the records in D365 for operations they were getting the following error:

 "An error has occurred.  Error executing code: The field with ID '21' does not exist in table 'smmContactPersonEntityCopy'.\r\nclientRequestId: 4f0ca82f-ff64-46a9-9066-ff62cea0b036" 

I investigated the issue in D365 for operations.Actually they copied the standard AX entity i,e ContactPersonEntity to create the new one smmContactPersonEntityCopy.

I created the job with in D365 for operations to insert the records into the same entity.

After running the above job I was also getting the same error that I have stated above.After some  debugging I found out that the error was on the init value method of my entity i.e

smmContactPersonEntityCopy.initValue();

 When I looked into that method and found out that it was throwing exception on number sequence generation.

After some analysis I found out that the other developer has copy the standard AX entity i.e smmContactPersonEntity and created new Entity with ame smmContactPersonEntityCopy and he forgot to update the refrences in code.

So as you can see in the above image Its smmContactPersonEntity I have change this to smmContactPersonEntityCopy and after that I rebuild the project and I run my job which was successfully able to insert the records in that entity and my logic app was also working fine.

Below is the updated code:

Thanks
Zahid.

Development Approach in Dynamics 365 For Operations

The are two ways of development approach in D365 one is Extension Development Approach and Overlayering development approch.

1. Extension Development Approach.
2. Overlayerring Development Approach.

Extension Development Approach:

This Approach is use if you want to create new objects or you don’t want to customize the existing standard AX functionality given by Microsoft.

In extension development approach you can enable or disable the controls of standard ax objects and you can do what you want with the new objects that you have created.

Benefit of using the Extension Development approach is  that It would be easier for you to do the future upgrades but sometimes we might need to customize the existing standard AX object based on our requirement. in this  case we use Over layering development Approach.

To do the extension development you first need to create the model and under that model add new objects or refer to the existing objects to enable and disable some of their controls.

Below are the steps for working in extension development approach.

Open Visual Studio Click Dynamics 365 -> model management ->  Create Model

A new wizard will open where you give name and other details of the model.

Now click next.

In the above screen you have the option to use the extension development approach or the over layering development approach.

If you choose the 'Create new package' option than it will be the extension development approach.

And if you choose the 'Select existing package' than you have to select the existing model which you will be customizing that would be the over layering development approach.

If you select 'Create new Package' and then click next than you have the option to select the existing package if you want to use objects of that model. (note: you can only enable or disable control of existing AX objects but you cannot customize it)

Like this.

When you click next final screen will appear where you can check the checkbox Create new project so once you click next a new dialog will appear when u can create new project or u can uncheck the checkbox model will created and then you can create the project manually.

After creating the model In the above image I right click the standard AX form i.e cust table.

Now I have the option of either customize it or create extension. As you can see on the right pane I have use this form as customize and extension both (just for example).

It is because my module is over layering development approach. If my module would be total new I,e extension development approach then I won't be able to customize that form I extend that form.

Now I will show you the properties of the form that I have customize in which I have full authority to change any property.

and below is the properties of the same form which I extended.in which I can change only some properties not all of them.

Feel free to comment if you have any questions.

Thanks,
Muhammad Zahid.