Find Unique Records in Microsoft Excel

Find Unique Records in Microsoft Excel

Microsoft ExcelI’m not very good with Microsoft Excel. People often think that I am becuae I can concatenate data and write if statements and other formula, but, beyond this, my knowledge is very shallow.

There are things that I know you must be able to do, but have no idea how. As such, I often default to loading data into SQL Server to manipluate. When dealng with large quantities of data, I think this is still the best thing to do, but for smaller datasets, using Excel makes more sense.

There may be a number of other posts, covering fairly simple functionality in Microsoft Excel, that I post in future as reminders for myself.

The first of these Excel tips, is identifying unique data. In Excel this is actually quite simple (once you know how).

If I wanted to identify how many unique vendor ids I had in a dataset, I can do this in only a small number of steps.

Select the columns containing the data I want to search; in this case, the Vendor ID and Vendor Name columns. Select the DataSort & Filter section, click Advanced:

Microsot Excel

Continue reading “Find Unique Records in Microsoft Excel”

Microsoft Dynamics GP July Hotfix – 2018 Mid Year Tax Update Released

Microsoft Dynamics GPBack at the end of June, Microsoft released the Microsoft Dynamics GP July Hotfix – 2018 Mid Year Tax Update. I’ve delayed posting as there was a retraction and re-release of the hotfix due to some changes in Ontario tax and I decided to give it a little time to settle down.

There are a number of bug fixes in this release, two of which I reported to Microsoft:

Both of these fixes have been reported as fixed, so I will be giving this a test over the next few days, as I have a few clients waiting for these fixes.

Everyone will have their own notable fixes, but below is the list of items which I think will have relvance to my clients:

  • You may notice the Cash Receipts Inquiry Zoom window is cut off in certain areas, missing fields, after you upgrade to Dynamics GP 2018.
  • If a user’s workstations regional settings are set to the date format of DDMMYYY, transaction entry screens in web client does not reflect this date format. The main date in the top right corner does, but transaction entry screens do not. They show as MMDDYYY.
  • The VAT report was not including transactions that originated from General Ledger.
  • The VAT Report does not mark off transactions in the TX30000 table when regional settings are DATE/TIME format set to English-UK.
  • PM Payables Transaction Workflow A get/change operation on table ‘PM_Transcation_WORK’ failed.

For the full list of fixes, see the Dynamics GP Support and Services Blog blog for full details and download links for supported versions of Dynamics GP (2015, 2016 and 2018).

Download links for Microsoft Dynamics GP 2018 are below:

Enabling Email for All Master Records

Microsoft Dynamics GPIf when Microsoft Dynamics GP is implemented, you know that email documents is going to be used, the functionality can be enabled alongside the rest of the system, but for clients who were not, for example, using Purchase Order Processing from the start it needs to be switched on after the fact.

When first discussing it with the client, my initial thoughts were to use a macro to enable merged from the supplier list to step through the vendors/creditors and enable that way.

However, just before I started to explain this, I remembered a feature tucked away on the Vendors/Creditors navigation list. If you click the button, in the Modify section of the area pane, of two chevrons pointing to the right you get a pop-up menu containing an Email Settings option:

Creditors navigation list

Continue reading “Enabling Email for All Master Records”

Winthrop DC Now Distributing Products Directly

Winthrop DCAs announced back in May, from the 1st July 2018, Winthrop Development Consultants now directly and exclusively distributes all of its products.

Pricing remains the same (exception for Australia), but purchasing is handled directly by Winthrop DC and if you’re a partner, you will need to sign up as a partner with Winthrop DC (which is free to do) before purchasing for your clients.

One important point to note, is that only the latest builds of the software will automatically switch over to using Winthrop DC’s registration servers.

To ensure a smooth transition, please ensure all Winthrop products on your, or your clients, systems are updated on all servers and workstations before the current subscriptions expire. Upgrades can be installed over the top of existing builds, so there is no need to uninstall.

Full details of this changeover is available on the Winthrop DC site.

MVP Not Renewed for 2018-2019

Microsoft MVPWell, it seems that doing even more in this renewal period than I did to get the MVP award originally or keep it for three renewals, I have not done enough to retain it for the 2018-2019 year.

There seems to have been a mass culling this year, caused by Microsoft assigning more weight to some types of contribution than other types.

As a note, the renewal process is opaque with no guidance on how to get renewed, apart from being told to continue doing what you did to be awarded in the first place. With the apparent change in weightings, doing what you did is no longer enough if those contributions are now weighed less.

The Microsoft Dynamics GP element of the Business Solutions category itself saw five of fifteen MVPs removed; four no longer MVP and one moved to a new category.

Congratulations to all those MVPs who were renewed; for a list of those awarded for Dynamics GP, current MVP Jen Kuntz maintains a list, plus I have a list on my links page.

Neither of us have yet updated the pages, to allow people time to make their own announcements, but check those pages later in the week to see the updated lists.

Choose Which Icons Show in Windows 10 System Tray

WindowsAs Windows has progressed through the years, some settings have changed and been moved. One of the ones I struggle to find is how to set icons so that they always appear in the system tray. I’m sure this used to be done via right click on the system tray, but in Windows 10, the setting is buried in the Settings app.

In theory, it is possible to find it via a Start menu search, but I never remember the exact words to find it (or if I do they don;t seem to work until after I’ve found the settings via the Settings app.

A few weeks ago, I did a post on using the ms-settings URIs as shortcuts to quickly access the settings.

To quickly get to the relevant settings for choosing which icons show in the system tray, hit Win+R and typems-settings:taskbar:

Run ms-settings:taskbar

Continue reading “Choose Which Icons Show in Windows 10 System Tray”

Workflow Approval Request Error After Upgrade

Microsoft Dynamics GPI’ve assisted a few clients to upgrade to Microsoft Dynamics GP 2018 recently and have seen two different errors cause problems with Workflow.

As part of the upgrade process, database objects are deployed and various scripts run against them. This includes the wfdeployclrassemblies stored procedure which deploys the .NET Assemblies required by Workflow for such tasks as AD lookups and sending of emails.

However, I’m going to make the running of this stored procedure a standard, manual, part of the upgrade as I am finding that if it isn’t run after the upgrade is complete, that AD lookups don’t work until it is (this usually manifests in approvals failing to find a user to email or approvals failing if the user had the email from before the upgrade). I might was preempt and do it manually every time in order to give clients a smoother process. To run the stored proc, open SQL Server Management Studio and, against the system database, typically called DYNAMICS run the following (it’ll take a few minutes):

exec wfdeployclrassemblies

The other issue I’ve now seen more than once (and have also seen when upgrading some clients to prior versions), is that during the upgrade the Workflow managers are lost:

Workflow Maintenance

Continue reading “Workflow Approval Request Error After Upgrade”

DNS Overview for External Workflow 2.0 Email Notification Approval

Microsoft Dynamics GPSince its introduction, Workflow for Microsoft Dynamics GP has proven to be very popular and is a module I have spent a lot of time with. Workflow integrates with the Web Services for Microsoft Dynamics GP for the approval (or rejection) via email; there is a fair bit of complexity around this area, mainly in accessing the email actions from outside of the domain.

Dan Peltier of Microsoft has a good write up of what is required for DNS settings to allow for external access of approvals.

In the near future, I am going to be doing a small series on configuring web services to use https for approval/rejection, but will be written on the assumption you have working DNS records ready (DNS is very much not my specialist subject). Dan’s post is a good starting point if you need assistance in this area.

GP Transaction Search: Where Next?

Precipio ServicesThis post is part of the series on the new, free, GP Transaction Search window from Steve Endow of Precipio Services.

The GP Transaction Search is in in its first version; I think that Steve has made a very good start and I look forward to seeing the progress in future.

Steve isn’t allowing moss to grow, but is instead hard at work on version two, which includes new search windows for the General Ledger and Receivables Management modules as well as custom menus (rather than replacing the Transaction by Document menu) by using Visual Studio Integration Toolkit from Winthrop DC:

After version two comes out with these new search windows, I’d like to see additional windows created for Purchase Order Processing, Sales Order Processing and Inventory Control and, in longer term, other modules. The good news on this front, is that Steve has aspirations to create windows for additional modules as well.

The GP Transaction Search project is an open source one created by Steve Endow, but other people can get involved and contribute. If you find the project useful, consider seeing how you can be of assistance. I’ve already done this and have, since before the release of v1, been offering my assistance with testing and, more recently, with writing SQL queries.

Click to show/hide the GP Transaction Search Series Index

Removing Blank Lines from Microsoft Word

Microsoft OfficeI recently had to read a large Word document which had apparently been written by someone who doesn;t know how to use Microsoft Word. Intead of using paragraph spacing they had inserted blank lines.

I know in the scheme of things, this is a relatively minor gripe, but in a large document it is still annoying.

However, there is a simple way in Word to remove blank lines.

Using the Find and Replace tool, in the Find what box type ^p^p and in the Replace with box type ^p; hit Replace All and all double line breaks will be replaced with single line ones:

MS Word Find and Replace

To my eyes this makes the document far more readable which is important for a large one.