Adding filters to custom fields lookup in excel add-in D365FO

In many cases while building an entity we face an issue with the lookup of fields not showing data as expected, or sometimes, there is no direct relation that can solve the lookup to the end user needs. In such case, we must create a custom lookup method for our field and there are two ways to do this as follows:

• Relation custom lookup method (depends on relation between entity and another).
• Result custom lookup method (depends on adding results into a custom list of results).

as an example of a relation custom lookup method, let us have a quick look at the below article about how to initiate the relation first and see the result in excel, afterwards we can push forward with our adventure.

In the below article we can see that a relation is created so that the field can return lookup results. https://community.dynamics.com/365/financeandoperations/b/how-tos-for-dynamics-365fo-and-hints-by-vinit-goyal/posts/how-to-enable-lookup-for-a-field-in-open-in-excel-for-dynamics-365-finance-and-operations

Now in some cases the result is too common to be specific to our case, thus we need to narrow down the results shown, to do that we have to the below steps:

• Go to the entity desired -> create a new method -> name it lookup_<Field name>
• Write the below on top of your method replacing brackets and its content ~~ with “” and a name:

[SysODataActionAttribute(~Name of method in metadata~ false),
SysODataCollectionAttribute("_fields", Types::String),
SysODataFieldLookupAttribute(~Field name~)]

• Method declaration should be as below:

public static str lookup_~Field name~(Array _fields)

• Now what we are going to need in the method are the below objects:
  1. OfficeAppCustomLookupRelationResult
  2. ExportToExcelFilterTreeBuilder
  3. Dynamics.Platform.Integration.Office.FilterBinaryNode
• As an example, find below body of a method:

publicEntityName                = ExportToExcelMetadataCache::getEntity(~Entity name~).PublicEntityName;

customRelationLookup            = new OfficeAppCustomLookupRelationResult();
customRelationLookup.addDeterminationField(~Field name~);
customRelationLookup.entityName(publicEntityName);
customRelationLookup.fieldName(~Related field name~);

builder                         = new ExportToExcelFilterTreeBuilder(~Related entity name~);
filter                          = builder.areEqual(fieldId2Name(~Related Entity Id~, ~Related field Id~), ~Filter value~);

customRelationLookup.filter(filter.ToString());