In today’s fast-paced business environment, the ability to think outside the box and find innovative solutions is crucial. One such example is how we recently addressed a customer’s requirement in Dynamics 365 for Finance without resorting to customizations.

 

By harnessing the power of OData entities and Power queries in Excel, we were able to deliver a real-time, efficient solution that exceeded our customer’s expectations.

 

The Challenge

 

In essence our customer needed a way to summarize weights based on commodity codes for consolidated packing slips, a task that traditionally might have required custom development.

 

However, we aimed to achieve this using the standard tools available within Dynamics 365 for Finance, avoiding the time and cost associated with customizations.

 

The Solution

 

We turned to OData entities and Excel, leveraging their capabilities to create a seamless and efficient process. Here’s how we did it:

 

1. Utilizing OData Entities

 

OData (Open Data Protocol) entities in Dynamics 365 for Finance allow for easy access to data. We identified the relevant OData entities that contained the packing slip and commodity code information we needed.

 

–  To see all publicly available entities add the keyword /data in the URL

In that list we have identified the entities:

CustPackingSlipTransBiEntities and ProductCategories

 

– To check if the entity provides all fields needed, add the entity name in the URL

 

2. OData Feed in Excel

 

OData Feed in Excel allows us to connect to these entities and manipulate data further within Excel.

 

– To expose the entity data in Excel, copy the URL with the entity name and maybe add some filters to reduce the data load, like:

 

https://xxxxxx.operations.dynamics.com/data/CustPackingSlipTransBiEntities?$filter=dataAreaId eq ‘xyz’  (indicating to fetch only data from ‘xyz’ legal entity).

 

– Paste that URL in:

 

 

3. Power Query in Excel

 

Power Query is a powerful tool within Excel that enables data transformation and combination. We used Power Query to merge two queries from the OData entities, effectively summarizing the weights based on commodity codes on the consolidated packing slips.

 

4. Real-Time Data Extraction

 

By connecting Excel to the OData entities, we ensured that the data extraction was real-time. This means that any updates in Dynamics 365 for Finance were immediately reflected in our consolidated packing slips.

 

 

The Outcome

 

The result was a streamlined process that met the customer’s requirements without any customizations. The real-time data extraction ensured accuracy and timeliness, and the customer was extremely satisfied with the solution.

 

 

Conclusion

 

This experience highlights the importance of thinking creatively and utilizing the tools at our disposal. By leveraging OData entities and Excel, we can achieve powerful results without the need for custom development.