Introduction
It’s good to make a blogpost every now and again just to remember how to do it…
Here at iamJoshKnox blog, we LOVE reader feedback. Last week, Jared reached out with a question about using the excel add-in to return an excel list filtered for open sales orders.
What a great excuse for a video (and blogpost)! Watch the video above (or send your AI-bot to watch and comment for you, that’s your decision).
🔽[video summary powered by Loom]🔽
Overview
Step-by-step guide on how to filter and retrieve open sales orders from Dynamics 365 using the Excel add-in, in response to Jared’s inquiry. Addresses common issues and offers solutions for effectively managing sales order data.
Connecting Excel Add-in to Dynamics 365 6:06
- Open Excel and navigate to the Data tab.
- Select ‘Get Data’ and choose ‘From Online Services’.
- Enter your Dynamics 365 server name to connect.
- Sign in with your credentials to access the data.
Accessing Sales Orders Table 1:33
- In Dynamics 365, go to the Accounts Receivable module.
- Locate the ‘All Sales Orders’ form to access sales order data.
- Use the Excel add-in to download the ‘Sales Order Headers V4’ entity for the latest data version.
Filtering for Open Sales Orders 3:03
- To filter for open sales orders, right-click on the sales order table and select ‘Show Form Information’.
- Identify the data source and relevant fields, particularly the ‘Status’ field.
- Apply a filter in the Excel add-in to only include records where ‘Status’ equals ‘Open’.
Creating a New Excel Add-in Template 6:19
- Start with a new Excel workbook and connect to Dynamics 365 as described earlier.
- Add the ‘Sales Order Headers V4’ table to your workbook.
- Select the fields you want to display, such as:
- Sales Order Number
- Customer Account
- Status
- Refresh the data to load the filtered results.
Verifying Data and Performance 10:06
- After applying filters, check the number of rows returned to ensure only open orders are displayed.
- If the refresh takes longer than expected, verify if the legal entity filter is applied correctly to reduce the data load.