Friday, April 17, 2015

Microsoft's best kept secret: Office Online

By Steve Endow

Have you heard of Google Docs?  It's a set of fully browser based applications for editing documents.  Think of it as an online version of Microsoft Word that only runs in  your web browser.  There is also Google Sheets, which is a browser based version of Excel.  You can share files with people, control permissions, and best of all, multiple people can edit the same document online at the exact same time--a feature now generally referred to as "collaboration" or "online collaboration".

Google fans--the ones who use Android phones and spurn all-things-Microsoft, talk about how great Google Docs is compared to MS Office, and hey, it's free!  I know of many people that rag on Office as being old and clunky, because, gasp!, it requires an installation on a computer.

Here's one example.

I totally agree with this post--if it is referring to the desktop version of MS Word / MS Office.  It is a nightmare to get edits from several people and combine and merge them.  It simply wasn't designed to do that.

But did you know that Microsoft offers the same thing as Google Docs and Google Sheets?  A fully browser-based online version of MS Office that offers sharing, permissions, and online document collaboration.  And it is remarkably similar to the version of Office you have installed on your PC.

Really.  No joke.

It's called Office Online.  And it is INCREDIBLE.

In this screen shot, I'm editing a Word document using Word Online in two different browsers to demonstrate the live collaboration functionality.  I'm signed in to my Office 365 account in IE, but have shared the document and am also editing it in Chrome.

The changes made in one browser immediately appear to other users.  Just like Google Docs.

Yes, Microsoft does have paid plans for Office Online, so you may not be able to do everything you want with a free account.  And yes, understanding the plans and potential confusion with Office 365 is likely limiting adoption when compared to the 'everybody knows its free' approach with Google.

Here is a video discussing Office Online vs. Office 365:

But if you have ever used Google Docs or Sheets, the functionality doesn't compare to MS Office.  At least I don't think so--but then again, I hate the Gmail interface, while I know people who thing Gmail is the cat's meow, so that's apparently personal preference.

So it's a great tool, but there are obviously a few caveats.  If you don't want your documents in the cloud, or if your corporate policies do not allow documents to be stored online, then Office Online, and Google Docs as well, are not an option.  And if you use the free version of Office Online, then there are some limitations to the features available.

Admittedly, in my case, I'm still old school and am used to having files stored locally on my computers.  While I do store some files online in OneDrive, I do not use it as the sole or primary repository, so using Office Online is currently a limited tool for me--for the rare cases where I might need others to collaboratively edit a document.

But if you need to get input on a document or spreadsheet from several people and have the option of hosting the file online, at least temporarily, you can use Office Online, and you always have the option to download the file when you are done and remove it from the cloud.

And all of this functionality integrates well with the fantastic Office Mobile, which works with iOS, Android, and Windows mobile.

Give it a try, and spread the word that there is a better alternative to Google Docs! (IMHO)

Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

Thursday, April 16, 2015

Tax Account in Vendor Account Maintenance

So, have you ever wondered when the following account is ever used?  From Cards, Purchasing, Vendor, Accounts button...

This came up at a client this week, and got me to wondering.  As I have always entered tax accounts on the tax detail window, Setup, Company, Tax Details.  However, that is actually NOT required.  You can leave the tax detail account blank.  Yes. You can.

I bet you see where I am going with this, don't ya?  If....

  1. The tax account is populated on the tax detail, it will be used on a transaction for the tax liability
  2. If the tax account is blank on the tax detail, the system will look to the vendor tax account instead
So why would you want to have a tax account by vendor?  I think the best example I can think off is with use tax.  If you want to track those amounts by vendor in your GL (or by locality, setting each vendor in a location up with the same account instead of maintaining multiple tax details).

Total sidebar, here is a link to my favorite way to handle use tax in Dynamics GP without additional products.

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a senior managing consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.

Behind the Dynamics GP Remember User and Password option

By Steve Endow

Dynamics GP 2010 and GP 2013 have a "Remember user and password" option that allows you to save your GP login info so that Dynamics GP can login automatically.  When combined with the "Remember this company" option, a single click on the GP icon on your task bar will launch GP, login, and select a company.

A colleague was looking to enable this feature to perform some testing, but the Remember user and password option just wouldn't work.  I spent some time trying to figure out why it wasn't working for him, and in the process I had to find all of the places where GP stores the option and settings.  Since I went through that trouble, I figured I would document it all here for posterity.

If your "Remember user and password" option doesn't work, the setting doesn't save, or if the remember user option is always disabled, check these settings to see if one of them might be the problem.

By default, the Remember user and password option is not enabled.

You have to enable the option at the system level.  Open Tools -> Setup -> System -> System Preferences and check the Enable Remember User option, then click OK to save the setting.

When you save this option, it updates a record in the DYNAMICS..SY01402 table.


When the option is checked, the SYUSERDFSTR value will be 1.  When the option is unchecked, the value is 0.

So, once you enable that option, you will then have the ability to check the box the next time you launch GP and login.

When you enter your username and password, and then click OK, Dynamics GP creates two registry entries.  Mariano has a post discussing the entries here.

So at this point, you are all set to have GP remember your username and password.

But wait a minute.  How can that be?  How will the GP client know whether you have checked the option to Remember user and password?

Or, what if, despite doing all of these things like my colleague, the Remember user option still doesn't work and is disabled every time GP is launched?

There is the setting in SY01402, of course, but we double checked that and it was correct..  And, if GP hasn't logged in yet, it can't connect to the database, so it can't read the setting from SY01402.  So that wasn't the issue.

The GP client could be reading the registry entries to decide whether or not to login with saved username and password, but the registry entries were present on my colleague's computer and it still didn't work.

So where else does GP save settings besides the database?  Of course the answer is the tricky Dex.ini file.

After reviewing the Dex.ini file, we found the RememberUser setting in my Dex.ini file.  When my colleague checked his Dex.ini file, the RememberUser line was not present for some reason.

When he added the RememberUser=TRUE line to his Dex.ini, the Remember user feature started working.

Just another day in Dynamics GP paradise...

Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

Monday, April 13, 2015

How to assign a new Dynamics GP Item Class to existing inventory items and roll down class settings

By Steve Endow

UPDATE: A reader contacted me to let me know that the process outlined below may not work properly if you are using Dynamics GP Manufacturing.  Some manufacturing tables contain the item class ID, so the process below does not account for those.  Also, updating the item class ID via SQL may not properly trigger certain updates in manufacturing.  The reader suggested that the macro approach, while more tedious to setup, is probably a safer method.

I was asked if there was a way to create a new Inventory Item Class, apply that new class to a lot of existing inventory items, and roll down the new item class settings to those inventory items.

Obviously you can open an individual inventory item, change the class ID, and when prompted, roll down the class settings to that single item.  But how do you do this in bulk for 50 or 100 or more items?

I searched around to see if anyone had a good solution, and I found a few potential workarounds, such as using a macro, but none of the options sounded very appealing.

After thinking about it for a few minutes, I came up with this solution.  It seems to work, but there may be a particular scenario that it doesn't handle, or some fields that it doesn't update.

Here's my approach to assigning a new item class to a bunch of existing inventory items and rolling down the new class settings to those items.

First, create the new inventory item class and populate as few fields as possible.  I'll explain why below.

Second, use a SQL statement to update the class ID for the relevant inventory items.  Something like this:
Your items will now be assigned to the new classID.

Next, open your new inventory class and edit all of the fields that you need to set.

When you click on Save, you will be prompted if you want to roll down the changes.

Click on Yes to roll down the changes.

After rolling down the changes, open several of the inventory items to confirm that all of the settings rolled down properly.

In my testing, if I only changed one or two fields on the new item class, even though I clicked Yes to roll down the changes, the field changes to the class did not roll down to a test item.  I didn't perform additional testing to see why the small change didn't roll down, or which fields were, or were not, included in the roll down.  But I did test changing several fields, and after I rolled those changes down, they did update the items properly.

It seems like this should work, but as I mentioned earlier, there may be some caveats.  So give it a try and let me know if you find any issues or ways to improve the process.

Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

Dynamics GP 2015 eConnect error: The source was not found, but some or all event logs could not be searched.

By Steve Endow

After upgrading an eConnect integration to Dynamics GP 2015, I received the following strange error message:

Exception type:Microsoft.Dynamics.GP.eConnect.eConnectException
Exception message:The source was not found, but some or all event logs could not be searched.  To create the source, you need permission to read all event logs to make sure that the new source name is unique.  Inaccessible logs: Security.
Stack Trace:   at Microsoft.Dynamics.GP.eConnect.ServiceProxy.CreateTransactionEntity(String connectionString, String xml)   at Microsoft.Dynamics.GP.eConnect.eConnectMethods.EntityImportImplementation(String connectionString, String sXML, Boolean isTransaction)   at Microsoft.Dynamics.GP.eConnect.eConnectMethods.ProcessEntityImport(String connectionString, String sXML, Boolean isTransaction)   at Microsoft.Dynamics.GP.eConnect.eConnectMethods.CreateTransactionEntity(String connectionString, String sXML)

That part that stood out was "Inaccessible logs: Security".  This reminded me of some errors I've seen where an application is trying to write to a windows Event Log that doesn't exist, that it is unable to create, or that it doesn't have permissions to write to.

I looked all of the logs Windows Event viewer, but couldn't figure out what was causing the error.

After running the GP 2015 integration a few more times, I eventually received an error I did recognize.
Error Number = 936  Stored Procedure= taPMDistribution  Error Description = Vendor number does not existNode Identifier Parameters: taPMDistribution

Once I fixed the vendor ID in my test data file, the integration ran fine and I didn't receive the strange "The source was not found" message again.

I then installed the integration on the customer's workstation, and once again, the "The source was not found" error message appeared.  After a few more tests, we received a standard eConnect error message, fixed the data, and the integration ran fine.

My guess is that there is some type of issue with the eConnect 2015 install or the process that creates or writes to the eConnect event log when the first error is encountered.  The error appears once, and then things get straightened out in the log, and subsequent errors are logged properly.

Since the error does go away and appears to be a one-time event, I believe that you can safely ignore this message.  Unfortunately it is confusing for users, but if you remember to warn them in advance, or are able to recognize the error and explain the issue to users, it shouldn't be an issue.

And here's some GP geek trivia:  Did anyone notice that the error message refers to "eConnect12"?  Even though the internal version number for GP 2015 is version 14?  

Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

Wednesday, April 8, 2015

Why specify a Batch Number when importing bank transactions using eConnect?

By Steve Endow

A few weeks ago I delivered an automated eConnect integration to a client that imports vendors, AP vouchers, GL journal entries, and bank transactions.  This integration replaced several manual Integration Manager imports.

The new eConnect import worked well, but recently the client noticed that they had lots of CMTRX general ledger batches piling up, whereas before they just had one CMTRX batch in the GL for the bank transactions that they imported with Integration Manger.

The fact that the GL batches were piling up tells us, incidentally, that they have their Bank Transactions set to Post To, but not Post Through.  But they had set the option to Append GL transactions to an existing batch in the Posting Setup window.

So why is eConnect creating a separate GL batch for every Bank Transaction?

Good question.  I didn't know either.

On my Dynamics GP test machines, I normally have all of my batch types set to Post Through, as I don't want to deal with piles of GL batches after testing.  I speculate that most GP customers also use Post Through, so I don't see too many issues like this for customers that only use Post To.  So that explains why I would have never noticed the issue of lots of GL batches generated by my eConnect import.

So why is this happening?

I checked my eConnect code to see if there was some configuration option that controlled how the Bank Transactions were posted to the GL, but didn't see anything.  At first.

After some poking around, I finally noticed, to my surprise, that the eConnect Bank Transaction document type has a BACHNUMB parameter!

And even more surprising, the eConnect help file actually explains that the field is for the GL batch number.  Since Bank Transactions don't have batch numbers, I would have never thought to look for this field.

This is a pretty unusual feature for eConnect--but then the Bank Transaction, which doesn't use batches, is also a bit of an unusual transaction for Dynamics GP.  Rather than rely on the Bank Transaction posting settings to determine the GL batch name, eConnect lets you specify a batch number.  If you don't specify a value, it will create a new batch for every single bank transaction of you are using Post To.

My advice:  Specify the batch number.

Once I assigned a batch number of "CMTRX", all of the transactions posted to a single GL batch.

Go figure!

 Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

Dynamics GP Integration Manager script to generate a fixed length number

By Steve Endow

There are some situations when importing data into GP where you want to have a consistent or fixed length to a number.

Say you are importing Employees, and your source data file has numeric employee IDs such as 123 or 2345.  But suppose that you want all employee IDs to be a consistent 5 digits, with leading zeroes, in Dynamics GP.  So instead of 123, you want 00123, and instead of 2345, you want 02345.

Here is an Integration Manager field script to "pad" the Employee ID with leading zeroes.  It uses the VB Script Right function, which lets you easily pad a field value with leading zeroes.

CurrentField = Right("00000" & SourceFields("SourceName.EmployeeID"), 5)

This could also be used if you want to prefix an alpha value at the beginning of a document number.

So if you were importing invoices, and the data file had invoice 4567, and you wanted "INV00004567", you could do something like this:

CurrentField = "INV" & Right("00000000" & SourceFields("SourceName.DocNum"), 8)

That produces a consistent 8 digit invoice number, to which you can prefix "INV".

Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter