Dynamics GP Year-End Payroll Audit List for W-2

It's that wonderful time of year for payroll administrators, where you need to validate employee information prior to sending out your W-2 wage reports. 

The SQL script below can either be ran via SQL or inserted into SmartList Builder as a SQL statement and used to build a SmartList. 

It will return Employee and Employee Address Information.

Run the following Script against each company Database:  


left outer join UPR00102


order by LASTNAME

Dynamics BC Top 10 Favorite Things #4-Viewing any Business Central Table in the Browser

Microsoft Dynamics 365 Business Central 
 My Top 10 Favorite Things
#4- Viewing any Business Central Table in the Browser

For all of those interested in viewing your Business Central data kept in the table versus the interface, I have a cool trick for you! I had to use it today to review some issues we were having with sales tax posting.

I needed to review the table where the data was being stored, as we have been integrating into the tables and review it against what was being manually entered for differences.

It is a very easy process to do this.

1. Open up your BC Environment via URL.
2. Go to the page that you are needing to review.
3. Click on the Question Mark in the upper right corner.
4. Choose help and support
5. Click on inspect pages and data
6. Find the table number on the page inspection page
7. In your current browser URL, find the place where your company name ends and there is a '&' in the URL. Remove everything to the right of your company name in the URL starting with the '&' symbol. 
8. Right after your company name add: &table=18
*replace the numbers with the table number found in inspection

Another cool thing you can do is move the table window to another window, so you can compare your environment to the table side by side. In order to do this, click on the open in another window symbol. 

Before URL:  https://businesscentral.dynamics.com/368c4b78-275b-413b-b945-590f23dace41/?bookmark=21%3bEgAAAAJ7BTEAMAAwADAAMA%3d%3d&node=0000233e-882d-0000-0c65-d300836bd2d2&page=22&company=Energy%20Logic&dc=0

With Table URL: https://businesscentral.dynamics.com/368c4b78-275b-413b-b945-590f23dace41/?bookmark=21%3bEgAAAAJ7BTEAMAAwADAAMA%3d%3d&node=0000233e-882d-0000-0c65-d300836bd2d2&page=22&company=Energy%20Logic&table=18

More information on this handy tool can be found here: 

Business Central -Team Members Capabilities in BC

Did you know there are team members licenses inside Dynamics BC?

These are limited users that can: 

  • Read anything within Dynamics 365 Business Central
  • Update existing data and entries in Dynamics 365 Business Central - existing data are records like customer, vendor or item records which are already created. Entries means entries on which it is specifically allowed from an accounting perspective to update specific information. (e.g. due date on customer ledger entries)
  • Approve or reject tasks in all workflows assigned to a user
  • Create, edit, delete a quote for Sales or Purchasing
  • Create, edit, delete personal information
  • Enter a time sheet for Jobs
  • Use PowerApps for Dynamics 365
At $8.00 a month, per user, that is quite a deal! 

Below is a video I recorded with a BC Team Member License to enter, print, email, submit a purchasing quote. Due to the demo tenant, email and workflow are not setup in the demo, but you get the point! 

Top Things to Not Miss at GPUG/ User Group Summit in Orlando

Summit is my favorite time of the year and I can't believe it is almost upon us. Here is my list of top things not to miss each year.

  • Welcome Reception for New Users- If you are a first-time attendee, this is a must attend! Get your questions answered and bond with other newbies. 
  • Expo Reception- Appetizers, Drinks, and Giveaways- Oh My! The first night of expo is the best one to attend while there are still free-giveaways available.Save room in your suitcase to bring all these fun little things home for yourself or the kiddos. 
  • Expo Hall- Visit my favorite top vendors and see what all the buzz is all about: EOne Solutions, Mekorma, Nodus, Avalara, Binary Stream, Jet Reports, DocLink, AvidExchange and so, so many more....
  • GPUG General Session- Here is where you learn the about the newest release of Dynamics GP, discover all the award winners each year, and Bob McAdam is always a great and entertaining host. 
  • Parties- There is always a party going on at Summit. This week there will be a few. If you need invites, let me know. I love me a good Summit party and GPUG knows how to party the best! 
  • Attend Other User Group Sessions- Your pass gets you among the other user group sessions. If you are dying to learn about the Power Platform, sneak over to some of their sessions and then come back to GPUG sessions for mine ☺
  • Come Early and Attend an Academy Class- I teach four academy classes before Summit even starts. Come Monday and Tuesday to learn early with the rest of us. There are so many great Academy Classes, it will be hard to choose. These are hands-on, instructor led sessions. Always rave reviews. 
  • Socialize at Meals- I love to sit at new tables with users I have never met. It is my favorite time to meet people from all over the country learning just as I am! 
  • Microsoft General Session- Best place to learn about all the new MS products and features that are happening. Always informative.
  • Snack Time- Don't miss snacks, sodas and coffee on the concourse mid-afternoon. I love me some snacks! 
  • Enjoy the Gaylord -Being at the Gaylord is always a treat. Stop and enjoy the beautiful scenery. 
Other Helpful Tips: Layer your clothes for cold conference rooms or HOT conference rooms. 
Bring a laptop so you can follow along. Buy the recorded session package. Bring COMFORTABLE shoes. Bring a refillable water bottle. 

Any questions feel free to message me! Can't wait to see you all in Sunny Orlando! 
Microsoft Dynamics 365 Business Central 
 My Top 10 Favorite Things
#3- Single Sign-On Logins & the Association with Office 365

Of all my favorite things in the world, remembering passwords and authentication questions is not one of them.
Do you ever get sick of logging in and out of programs all day long?
Microsoft understands this problem, which is why Dynamics 365 Business Central works with your Office 365 credentials and a single sign-on authentication.

What Does This Mean? 
When I log into BC and I remember my credentials, it authenticates me against my computer account which is associated with my Office 365 account.

Does This Mean I Must Have Office 365 to Run Dynamics Business Central? 
Yes, you must have
 Office 365 licenses to run Business Central.

Why the Association? 
Associating your Office 365 logins with Business Central allows for quicker login access, one single password for all Microsoft products, and easy security admin for your IT users. Your IT users grant access to Business Central through your Office 365 admin portal, the same way they would your Office 365 license is assigned.

This association allows you to access and utilize the same credentials to work with the Business Central adapters for Excel, Word, SSRS, Azure and Power BI. Thus allowing users to not have to login and logout of all these programs.

How Do I Grant Security Rights to BC Users? 
Once a user has access through the O365 Admin Portal, a security admin within the Business Central Site can grant user security rights. There is no need to key in the users inside Business Central, you can simply run the update users from Office 365 tool to see all users who have access in the portal.

Going forward, this one universal Microsoft single sign-on leans the Microsoft expansion of Office 365 and Dynamics 365 offerings towards a "One Microsoft World".

Microsoft Dynamics 365 Business Central 
 My Top 10 Favorite Things
#2- Filter Almost Anything

    One of my favorite things I feel in love with Business Central immediately was the automatic filters built into all the informational/list pages. Want to quickly filter a customer, vendor, or transaction? Try a filter. Want to filter and sort a column easily? Try a filter. Still confused? Add a filter, because filters are everywhere. 

   When I arrive on my customer page, I can select the filter button from the top of the list. Here I can filter the information down with multiple filters from the left pane. Even better, in the October release of BC, you will be able to save your filter preferences as a view that you can return to, time and time again. You can filter a range using the symbols (..) or filter by wild card using (*)
Filter the List

    If I just need to filter a specific column, I can do that as well. If I hover over a column name, I can see there is a filter button as well as the ability to sort ascending or descending. 

Filter a Column

     This same functionality also lives in the lists of transactions, such as general ledger entries as well. Here you can quickly filter by amount, account, debit, credit, etc... to get the results you need. Even better, you can select the page option and send your list to excel, using the view with Excel functionality built into BC. 

Filter Transactions

Finding Data has never been easier in Dynamics BC! 

    Microsoft Dynamics 365 Business Central My Top 10 Favorite Things #1

    Microsoft Dynamics 365 Business Central 
     My Top 10 Favorite Things
    #1- Built-In Excel Capabilities- Refreshing and Publishing Data

         As a 20 year Microsoft Dynamics GP veteran, it was hard to wrap my mind around cloud software the past few years. I have been so entrenched in SQL Servers, RDP, Workstation installs, config files, and the works. As the on-premise accounting software sales started to decline, I started to look towards the future of Dynamics Software. Last year I was on a mission to not only learn Business Central but also be able to do demos and pre-sales, just like I could with Dynamics GP. It was a huge feat and I am still learning things weekly. This blog series will focus on my top 10 favorite things so far for Business Central. This week is my first post and will focus on the built-in Excel capabilities for Dynamics BC. 

          One of my favorite things I feel in love with for BC, is the capabilities of pushing and pulling data directly from Excel. Most of the windows inside BC have a page tab, which allows you to either export to excel or edit in excel. That is right: Editing in Excel, cool! 

          You can edit and create master records (customers, vendors, items, accounts, etc...) as well as transactions. You can also create and refresh your financial statements in Excel as well. This is a huge benefit when you need to quickly add customers, vendors, items, journal entries etc.. or make quick edits to your data. Even better is the fact that you can save your excel spreadsheet somewhere on your network, open in Excel, and have the same capabilities as pulling from the system. 

    Here is an example of how it works: 

    Open the Customers Page, Select Page drop down: 

    Choose Edit in Excel: 

    Select Enable Editing: 

    Sign in to the Connector on the right: 

    Add or Edit a Record: 

    Go Back to BC,  Refresh and see your results! 

    Here is a sample of what a Refresh-able Balance Sheet out of the Box looks like. 
    With a little Editing, You can make it your own. 

    Until Next Time...

    Formatting Phone Numbers in SQL Reporting Services

    Every time, without fail, I need to create a phone number on an SSRS report for a client and get stuck with phone formatting. Every time I am searching forums. I thought it would be awesome if it was in some amazing blog that I could visit and copy from each time.

    So... here it is, if not for myself, then for others:

    In Report Builder or visual studio, add your phone number field to the report. 

    Right click on that text box where you added the field and click on expression. 

    Clear the formula box and type in the expression below.

    Formula to Use: 
    =IIF(Fields!Phone.Value Is Nothing, Nothing, IIF(IsNumeric(Fields!Phone.Value), Format(Val(Fields!Phone.Value), "(000) 000-0000"), Fields!Phone.Value))

    Note: My field is actually called Phone. If your field is not called phone, you will need to replace it with your phone field name. (Like: Fields!E_T_PHONE_HOME.Value)

    Then click on OK at the bottom of the expression box.

    Run your report.

    Until next time.

    Verifying Email Messages Sent from Business Central

    In Dynamics GP, you could always track if an email was sent by visiting the sent box of your inbox after sending the emails. 

    After much research, this does not occur with Dynamics BC. When setting up email, you set up one universal email account and therefore it does not prompt you for credentialing when sending emails. This leaves the user to wonder where will you find if an email was sent to a customer or vendor and how do you resend it?

    Enter- Interaction Log Entries in Dynamics Business Central

    In the search box, look for Interaction Log Entries. 

    The first checkbox will show you if the interaction was cancelled. The second will show you if the attempt failed. 

    If you go to Actions in the taskbar, you can several other options for the interactions. 

    If a user needs to create an interaction in 365 for Sales/BC, they can do that from this window. You can also evaluate the interaction as well. 

    If the user did not get the email, from here you can resend. 

    To clean up the entries, you can also remove cancelled entries. 

    Need to create a task to remind yourself to contact the customer or vendor again? You can do that from here as well. 

    You can setup different interaction templates in the interaction template setup. 

    Pretty cool stuff. 

    You can read more about these entries here: 

    Dynamics Business Central- My Favorite Things #6- Direct Integration with D365 for Sales

      Microsoft Dynamics 365 Business Central   My Top 10 Favorite Things #6- Direct Integration for Dynamics 365 Business Central and Dynamics ...