D365FO enable lookup dimensions in excel add-in for standard and custom dimensions

If you have tried to enable dimensions as fields in excel add-in and checked the lookup of the dimensions, you would have stumbled upon the custom dimensions you would find that the lookup is getting all dimension values that are not standard or in other words, that are not in a separate entity.

This blog is about sharing a way of solving this issue. Thanks to Allah, this is done after a long tackling and debugging of this issue.

First of all, if you have not added your dimensions to Excel add-in, please follow this guideline created by Microsoft’s engineers: https://docs.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/financial/add-dimensions-excel-templates

After doing the above steps, let us begin our journey:

• In DimensionCombinationEntity and DimensionSetEntity that are mentioned in the above link, add a second relation to the dimensions that has a relation to DimmAttributeFinancialTagEntity as follows:

1. Relation type is: Related field fixed.
2. Related field: DimensionName
3. Value: the same name of your custom dimension.

Here are sample screenshots of both a standard relation and a custom relations created.

DimensionSetEntity

DimensionCombinationEntity

• Logically this should work and act like a filter to the dimension lookup result, but unfortunately trying to build the solution will throw errors that such kind of a relation is not allowed,

Luckily there is an option to ignore compile errors and proceed with the build all you must do is as follows:

1. Create an xml file named “CompileError.xml”.
2. You can follow the same file pattern below:

  CompileError
  
     
      Metadata
      Error
   AxDataEntityViewExtension/DimensionCombinationEntity.DimensionIntegration/Relations/Expense_Category/Constraints/RelationConstraintRelatedFixed1/RelatedField
      FieldTypeIsEnumOrInt
      Field type must be enum, int, or int64.
    
    
      Metadata
      Error
      AxDataEntityViewExtension/DimensionCombinationEntity.DimensionIntegration/Relations/Expense_Category/Constraints/RelationConstraintRelatedFixed1/Value
      RelationConstraintValueStrMalformed
      The value must be an integer or the enum value in the format: ::.
    
    
      Metadata
      Error
      AxDataEntityViewExtension/DimensionCombinationEntity.DimensionIntegration/Relations/Expense_Type/Constraints/RelationConstraintRelatedFixed1/RelatedField
      FieldTypeIsEnumOrInt
      Field type must be enum, int, or int64.
    
    
      Metadata
      Error
      AxDataEntityViewExtension/DimensionCombinationEntity.DimensionIntegration/Relations/Expense_Type/Constraints/RelationConstraintRelatedFixed1/Value
      RelationConstraintValueStrMalformed
      The value must be an integer or the enum value in the format: ::.
    
  

Or you can build the model this solution belongs to from VS -> Dynamics 365 -> Build models and select your model then build, this will generate an XML file with all the errors that has the same information you need to put it in “CompileError.xml” file.

After you are done creating this file, place it into the following path

• “K:\AosService\PackagesLocalDirectory\<Model name>\<Model name>\\AxIgnoreDiagnosticList”
• If the above didn’t work, then add the file in the solution “Metadata\<Model name>\<Model name>\\AxIgnoreDiagnosticList” folder in the source\repos folder
• Build the model, VS -> Dynamics 365 -> Build models.
• Now you should have the compilation successful.
• If you check general journal lines entity excel template and try to view the dimensions fields and the default dimensions fields, you will find that the dimensions will not have a lookup while the default dimensions fields will have a working properly lookups, now comes the final step below:
• Create a new public static class to host the event handler method such as below:

[PostHandlerFor(classStr(DimensionSegmentGenericLookupGenerator), staticMethodStr(DimensionSegmentGenericLookupGenerator, generateCustomLookupForDimensionSegment))]
    public static void Post_generateCustomLookupForDimensionSegment(XppPrePostArgs args)
    {
        OfficeAppCustomLookupRelationResult                                 customRelationLookup;
        ExportToExcelFilterTreeBuilder                                      builder;
        Microsoft.Dynamics.Platform.Integration.Office.FilterBinaryNode     filter;
        FieldName                                                           dimensionSegment,
                                                                            fieldName,
                                                                            relatedExternalFieldName;
        
        SysDictTable                                                        table;
        TableId                                                             tableId,
                                                                            relatedDimensionAttributeEntityId;
        SysDictField                                                        field;
        FieldId                                                             fieldId,
                                                                            relatedFieldId,
                                                                            relatedExternalFieldId,
                                                                            secLineRelatedExternalFieldId;
        SysDictRelation                                                     dimensionSegmentRelation;
        TableName                                                           relatedDimensionAttributeEntityName;
        Set                                                                 tableRelations;
        SetEnumerator                                                       enumerator;
        SysDictRelation                                                     currentRelation;
        str                                                                 publicEntityName;

        ;

        
        customRelationLookup    = args.getReturnValue();

        if(!customRelationLookup)
        {
            dimensionSegment    = args.getArg(identifierStr(_dimensionSegment));

            table               = new SysDictTable(tableNum(DimensionCombinationEntity));
            tableId             = table.id();

            field               = new SysDictField(tableId, table.fieldName2Id(dimensionSegment));
            fieldId             = field.id();

            tableRelations      = table.relations();
            enumerator          = tableRelations.getEnumerator();

            while (enumerator.moveNext())
            {
                currentRelation = enumerator.current();

                relatedFieldId  = currentRelation.lineTableValue(1);
                
                if (currentRelation.lines() > 1 && relatedFieldId == fieldId)
                {
                    dimensionSegmentRelation        = currentRelation;

                    secLineRelatedExternalFieldId   = currentRelation.lineExternTableValue(2);
                    break;
                }
            }

            if (dimensionSegmentRelation && secLineRelatedExternalFieldId)
            {
                relatedDimensionAttributeEntityId   = dimensionSegmentRelation.externTable();
                relatedExternalFieldId              = dimensionSegmentRelation.lineExternTableValue(1);

                relatedDimensionAttributeEntityName = tableId2Name(relatedDimensionAttributeEntityId);
                relatedExternalFieldName            = fieldId2Name(relatedDimensionAttributeEntityId, relatedExternalFieldId);

                if (relatedDimensionAttributeEntityName && relatedExternalFieldName)
                {
                    publicEntityName                = ExportToExcelMetadataCache::getEntity(relatedDimensionAttributeEntityName).PublicEntityName;

                    customRelationLookup            = new OfficeAppCustomLookupRelationResult();
                    customRelationLookup.addDeterminationField(dimensionSegment);
                    customRelationLookup.entityName(publicEntityName);
                    customRelationLookup.fieldName(relatedExternalFieldName);
                    
                    builder                         = new ExportToExcelFilterTreeBuilder(relatedDimensionAttributeEntityName);
                    filter                          = builder.areEqual(fieldId2Name(relatedDimensionAttributeEntityId, secLineRelatedExternalFieldId), dimensionSegment);
                    
                    customRelationLookup.filter(filter.ToString());
                    
                    args.setReturnValue(customRelationLookup);
                }
            }
        }
    }

Now you are absolutely done, build your solution and enjoy. Happy Daxing...