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).
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.
Stop! Look up. Do you have more browser tabs open than you can count?
You might have a problem.
In fact, you might have several problems…but the one that this blogpost can help you with is managing too many tabs. Introducing: vertical tabs.
Microsoft Edge – Turn On Vertical Tabs
By default, internet browsers usually put tabs horizontally across the top of the application. In Microsoft Edge, you can “Turn on Vertical Tabs” to display tabs vertically, down the left side of the application.
Horizontal Browser Tabs
Tab Actions Menu
Vertical Browser Tabs
When toggling between lots of tabs, I prefer this approach because vertical tabs have a fixed width, making them easier to read (as well as click).
Instead of using ChatGPT through the web browser, you can use Microsoft Edge to access it as a desktop web app. I find this to be useful for frequently used websites because the application is always available when I pin it to my toolbar instead of getting lost among all the other browser tabs I may have open.
OpenAI (the ChatGPT company) is working on a Windows desktop app. It’s currently in trial mode and only available to ChatGPT plus customers.
Recently, I was loading an updated file into a SQL table. Before proceeding, I wanted to compare it with the original SQL table to identify data differences. This can be time consuming, but I knew just who to turn to for help: my friend Chatty-G.
The Prompt:
Both tables had the same column structure, making the comparison straightforward. Using SQL Server Management Studio (SSMS), I right-clicked on my SQL table and selected “Top 1000 rows” to easily get and copy the column names from my table.
I then pasted the select statement from my first table, and the name of the second table into ChatGPT and explained what I wanted in 2 poorly worded sentences:
The Performance:
Then, within 30 seconds, ChatGPT returned the ~100 line comparison CTE below:
…
The Process:
I copied the code into SSMS and executed it. No errors!
The Postscript:
The CTE here is not particularly complex. However, ChatGPT generated error-free code faster than I could type it. And if I had typed it, there certainly would have been an error to two to debug. This task is a good candidate for AI because it is relatively straightforward to verify the accuracy of the answer provided. I can easily see this causing trouble if used to “solve” problems that are not easily verified.
There are higher level questions I could ask about the data. Is this the way I want to verify the changes? Maybe I should return mismatched values instead of a binary identifier. If I needed that, I could update the structure of the code myself or better prompt ChatGPT in that direction.
A last thought is that this was a two-step process, but I can see the compounding advantages coming as Copilot and other AI companions get baked into the core of IT and Data products.
In Excel, to create a calculated column in a pivot table, you need to add a Calculated Field. Here’s how you can do it:
1. Click anywhere inside the pivot table to activate the PivotTable Tools on the ribbon. 2. Go to the “PivotTable Analyze” Tab. 3. Click on “Fields, Items & Sets” and select “Calculated Field”.
4. In the Calculated Field dialog, enter a name for your new field.
5. In the Formula area, define the calculation you need, using the fields from your pivot table.
For instance, if you want to calculate the Net Amount as Credit – Debit
6. Click “Add” and then “OK” to create the field.
This field will now appear as part of your pivot table, and you can use it like any other field, dragging it into your table layout as needed.
Fun fact: you can screen share or schedule a meeting from a Teams group chat.
It’s hidden in the ellipsis next to the add person icon. I don’t think this was always the case.
There are multiple ways to talk with multiple people in Teams. You can create a Team. You can create a Channel. You can create a Private Channel. You can create a Shared Channel. You can embed a Whatsapp group chat as a website tab inside a Team created specifically for that purpose, where only you are a member.
The pros and cons of these can, and in fact does, fill a book (though the book doesn’t explore that last scenario in detail).
Group chats have fewer features than Teams, or channels within Teams, but sometimes a group chat is the best, lightweight tool for getting things done. It’s nice to know they’re good for quick communication and also easily enable sharing screens or organizing a meeting within the group if necessary.
Recently came across this issue while using a manuscript template (fancy!)
Sections
An advanced feature of word is you can carve your document into sections. Each section has it’s own page number sequence. These number sequences can be formatted to continue from the previous section, or start at any given number.
Using sections allows you to do fancy(!) page number things like use roman numerals in your pre-manuscript section and regular numerals in your regular manuscript section.
It is easier to see these sections if you enable show/hide paragraph marks (CTRL+*).
Format Page Numbers
To set the page number format, right click the page number in the desired section and click Format Page Numbers.
In this video, we look at a simple example of how Power Query can supercharge your reporting abilities for values brought into excel via the data connector.
I’ve found Power Query to be a great tool for efficiently combining data that lives across two different D365 data entities. It is a fantastic tool for data migration or analysis in general.
Some Concepts:
Pivot Tables
Refreshing Data
Connecting Data to Power Query
Merging Two Data Sources in Power Query
D365 Relationship Between Projects, Project Contracts, and Sales Currency
