Consultant Tools Series: Coordinating Across Multiple Time Zones

Have you ever made a mistake trying to figure out times in different time zones? It happens to me all of the time--I forget whether a state is in Mountain or Central or Eastern. Or I accidentally add an hour instead of subtracting. Clearly not rocket science, but definitely an error prone process.

I've found two tools (so far) that help me keep track of time zones.

The first is FoxClocks, an Add-On for the Firefox web browser. This utility is essential for helping me coordinate with people in other time zones and countries, since I can quickly glance at my browser and know what time it is in any city or country around the world.



FoxClocks has served me well for several years now, but what is often challenging is coordinating a meeting with people in multiple time zones.

I recently had to coordinate a conference call with attendees from Alaska, California, Colorado, Ontario Canada, and Australia. Talk about mind bending.

After looking around for a solution, I thought of my good friend Excel. It appears that there are several ways to use Excel to help calculate the time across multiple time zones. I chose one of the simplest methods.

If you enter a date time value in a cell, you can then create a formula to add or subtract hours from that cell to produce a time zone offset.

So if your tentative meeting time in cell A1 is 3/3/2010 11:00 am Pacific time, in cell B1 you can use the formula =A$1$ + "3:00" to get Eastern time. There are several other much fancier techniques to calculate time zones in Excel, but this approach was plenty adequate for my needs.

Here is a sample of what my spreadsheet looks like:



The green cells are the manually entered values, allowing me to enter the desired meeting time in both Mountain time and Alaska time and see how it affected the times in all of the other time zones.

And these are the formulas behind the values:



With my simple approach, I had to manually adjust the offsets that were used on row 5 and row 7, but it was simple enough to do.

The downside to my low-tech approach is that it doesn't automatically account for daylight saving time. I'm assuming there are ways to automatically handle DST, so maybe I'll deal with that when the time change rolls around.

Happy teleconferencing!

Copy and Paste doesn't work between RDP session and local workstation

Updated: Based on a reader comment about a local drive connection not being required for copying and pasting text, I have updated this post. I previously was able to resolve text copy and paste issues by adding a local drive connection, but apparently that should not be required. Sometimes I have been able to restore text copy and paste by reconnecting to the RDP session, but many times that doesn't work.

Here is a post on the Terminal Server team blog that describes the problem in great detail, and actually points out that there are two different versions of the issue. If you can't copy from the server to your local computer, that is potentially a different problem than not being able to copy from your local computer to the server. In the latter case, disconnecting and reconnecting may resolve the problem. But in the former case, you may need to kill the rdpclip.exe process, then restart it.


If you work alot on remote client servers using Remote Desktop Connection, you have likely encountered a situation where you couldn't copy and paste between your computer and the remote machine. Sometimes copying files does not work, and sometimes copying text doesn't work either.

Many years ago, this was sometimes due to the "rdpclip" utility not being properly installed. But with newer versions of Windows, installing rdpclip should not be required.

So, if you can't get copy and paste to work with your RDP session, here are two things to check.

Open the RDP connection properties.
Select the Local Resources tab
Make sure that Clipboard is checked



Even though Clipboard is checked, copy and paste may still not work properly.

If that is the case, click on the More button at the bottom of the Local Resources tab.

Try checking the box next to one of your local drives. In some cases, this has resolved problems I have had copying text. And it appears that a local drive connection is required to copy and paste files to and from the RDP session.



If you are still unable to copy and paste, you can try terminating the rdpclip.exe process on the server and restarting it.

If you have verified both the settings on your RDP connection and tried to restart rdpclip, then it could be that the server has disabled clipboard mapping or drive mapping in the Terminal Services Configuration utility.



In that case, you can try and inquire with the system administrator to see if you can have those options enabled.

I'm guessing there are probably a few other reasons why copy and paste will not work, but these are the ones I've run into most frequently.

GP 9 Windows Won't Open or Error After Importing Package Files

Here's a fun Wednesday morning GP brain teaser.

I have a copy of a client's GP 9 Dynamics and Company databases setup in a development environment. I have developed several Modifier & VBA mods for this client, so I open GP to make some changes to one of the mods.

GP appears to work properly, but when I try and open the Sales Transaction Entry window, where I have some VBA code, nothing happens. No error, no window, nothing. When I try and open the Customer Address Maintenance window, where I have both Modifier and VBA changes, I get an "Unhandled script exception EXCEPTION_CLASS_FORM_MISSING" error.

Very odd. Nothing in the Knowledge Base seemed to specifically address this issue, although there were some references to security being the culprit.

I then check Advanced Security and try switching back to the standard GP windows. No dice, still have the problems.

So then I delete the Dynamics.vba file and Forms.dic to remove my customizations. GP then worked fine, windows would open, no errors.

As soon as I import just one of my packages again, the problems return.

My intuition is telling me that it isn't the mods themselves, as they are quite basic, and should never prevent windows from opening. So my thought is that the problem is somehow related to security. I just can't tell what the problem might be.

So I try to open the SOP Transaction Entry window again. Nothing. I then launch SQL Profiler to see if any queries are being performed as I open the window.

When I run the trace, I see that there are 6 queries. One is a select against Dynamics SY02000, which is the Security Restrictions table. Hmmm.

So I run the query manually: EXEC DYNAMICS.dbo.zDP_SY02000SS_1 1, 'sa', 0, 2, 390

I then review the records in SY02000. It's been an eternity since I had to look in this GP 9 table, and everything is referencing dictionary resource IDs, so I don't know what any single record may mean.

And then I have a thought: What if, because I restored the client's Dynamics database, there are extra or invalid security restriction records that are causing GP to get confused?

Hmmm. How would I find such records?

I created a new GP user, "steve", and gave it access to the client's database without assigning a Class ID. I then queried SY02000 for both 'sa' and 'steve' to compare the record counts. 'steve' should have only default security records, so if my theory is right, I should see a difference.

Sure enough, sa had 29 more records than steve. So then I ran this self-join query to identify those extra records:

SELECT sy1.*, sy2.USERID
FROM DYNAMICS.dbo.SY02000 sy1
LEFT OUTER JOIN DYNAMICS.dbo.SY02000 sy2 ON sy2.CMPANYID = sy1.CMPANYID
AND sy2.DICTID = sy1.DICTID AND sy2.RESTYPE = sy1.RESTYPE
AND sy2.RESID = sy1.RESID AND sy2.USERID = 'steve' AND sy1.CMPANYID = 1
WHERE sy1.USERID = 'sa' and sy1.cmpanyid = 1 AND sy2.USERID IS NULL

I felt it was worth a shot to try and remove those records to see what happens. So I backed up the Dynamics database, and then ran this delete statement:

DELETE FROM DYNAMICS.dbo.SY02000 WHERE DEX_ROW_ID IN
(
SELECT sy1.DEX_ROW_ID
FROM DYNAMICS.dbo.SY02000 sy1
LEFT OUTER JOIN DYNAMICS.dbo.SY02000 sy2 ON sy2.CMPANYID = sy1.CMPANYID
AND sy2.DICTID = sy1.DICTID AND sy2.RESTYPE = sy1.RESTYPE
AND sy2.RESID = sy1.RESID AND sy2.USERID = 'steve' AND sy1.CMPANYID = 1
WHERE sy1.USERID = 'sa' and sy1.cmpanyid = 1 AND sy2.USERID IS NULL
)


I then reimported my packages into GP, and presto magic, everything worked fine again.

Definitely one of the more obscure problems I've run into, but resolving it before 8am was satisfying!

Uninstalling or Disabling Manufacturing: Dictionary Not Loaded Error

If, for some strange reason, you have to uninstall or disable the Manufacturing module, here are the steps to eliminate an error when opening the Account lookup window. If you try and open the window, you get the error "Cannot access this form because the dictionary containing it is not loaded".

Once the Manufacturing module is removed, it seems that Security retains one reference to an alternate version of the Account lookup window, so you need to go into security and change the reference back to SmartList.

These are the instructions for GP 9, which is where I had to solve the issue.

To disable Manufacturing:

1. Make a backup of your Dynamics.set file
2. Remove the two lines referencing the Manufacturing module from Dynamics.set (346 and Manufacturing)
3. Remove the three lines referencing the Manufacturing dictionaries (ICONMFG.DIC, ICONFRMS.DIC, and ICONRPTS.DIC)
4. Decrease the module count at the top of the file by one
5. Save the set file (or do a Save As if you want a separate version)
6. Launch GP with the new set file
7. Open Advanced Security (Tools -> Setup -> System -> Adv Security)
8. Change the view to Alternate/Modified/Custom
9. Select the company that has the issue in the upper right
10. Select the users or groups in the lower right
11. Expand Manufacturing
12. Expand Forms
13. Expand Financial
14. Expand Accounts
15. Choose SmartList
16. Repeat for any other users or groups
17. Click on Apply

Poor Dynamics GP Prospects

UPDATED: Mea Culpa. Updated to reflect my incorrect interpretation of the Customers and Prospects lookup window. The error message still exists, but my interpretation is that typical use of Prospects will not trigger the error.

One of many Dynamics GP features that doesn't seem to get much attention is the Sales Prospect Maintenance window.

Sitting quietly under Cards -> Sales -> Prospects (or Sales -> Cards -> Prospects, depending on how you prefer to navigate), the Sales Prospect Maintenance window allows you to enter prospects that are stored separately from your customer records.

The reason I went on this bumpy journey is because I want to integrate an external CRM system with Dynamics GP. Rather than send CRM contact records into GP as full fledged customers, I want to use Prospects, which will allow the accounting staff to review and validate the prospects before they are converted to real customers. Similarly, I want to transfer any transactions from CRM into GP as quotes, so that the transactions can be reviewed and then converted to orders.

So, back to Prospects.

Customers are stored in the RM00101 table, while Prospects are, interestingly, stored in the SOP00200 table. You'll notice that Prospects don't have as many fields as a customer. Although there are the full address and contact fields, there is no Address ID field, and there are no fields for salesperson, territory, or any of the fields found on the Customer Options window. However, there is a Class ID field, which uses the same classes as customers, which aids with consistent data entry of a few fields.



One use for Prospects is to simply track your potential customers by recording their contact information in GP, like a very basic CRM system, or in my case, as a conduit for receiving contacts from a CRM system. But GP also allows you to create sales quotes using prospects instead of customers, allowing you to avoid cluttering your real customer list. If the Quote gets transferred to an Order, you can then transfer the Prospect to a Customer.

To create quotes for prospects, you will need to make sure that you have enabled the option in SOP Setup. Under Tools -> Setup -> Sales -> Sales Order Processing (in GP 10), click on the Sales Document Setup button, then select Quote.



In the Sales Quote Setup window, select a quote ID, and then verify that the Use Prospects box is checked. Next, make sure that "Transfer Quote to Order" is checked, and a Order ID is selected. Save your Quote ID and then close the setup windows.

Now that you are on the edge of your seat with excitement, you need to first make sure you have some Prospects entered in GP. Go to Cards -> Sales -> Prospects and if you don't already have some records, create one or two. For demonstration purposes, I would recommend using a Prospect ID starting with "A" or "1". In either case, make a note of the first Prospect ID.

Next, open the Sales Transaction Entry window and choose Quote. Select the Quote ID that you verified earlier, then tab to let GP assign a document number. Once you get to the Customer ID field, open the lookup window.



UPDATE: Reader "RS" pointed out my mistake with my original post. By default, the "Customers and Prospects" window displays Customers, which is why Prospects are not listed. If you click on the "down arrow" in the blue "View" bar (that says "View: all Customers; by Customer ID"), you can change it from All Customers to All Prospects. This will switch the list to Prospects without any error.

From the "View" drop down list in the lookup window, select All Prospects.



If the prospect is new, you can enter a new Propsect ID. You will be asked if you want to add a new Customer or Prospect. Choose Prospect.



Clicking on the Prospect button handily opens the Sales Prospect Maintenance window, where you can enter the Prospect information and then save the record and close the Prospect window.

Back on the SOP Quote window, if you click on the Customer ID lookup again, you should be greeted by an error:



Unhandled script exception: Illegal address for field 'Prospect ID' in script 'ASI_LU_Customer_Lookup_Scroll_Fill_After'. Script terminated.

This error is discussed in the dust covered KB Article 863781, for GP 8.0, and "is scheduled for a service pack". Lovely. Interpretation: Don't hold your breath for a fix.

In my testing, I have to click on the OK button for the error message exactly 12 times (one error message per record that is displayed on the lookup window), and then it finally goes away, and then you get a list of Prospects.

Once you have selected your Prospect and enter your quote, go ahead and transfer the quote to an order.

The "Prospect to Customer" window should appear, which will transfer the prospect info to a new customer record. This window gives you the option to change the customer ID before it is created, in case your prospect IDs use a different scheme than your customer IDs. Once you click on OK, the Customer Maintenance window will open, pre-populated with all of the prospect information. If you selected a Class ID for your prospect, all of the values provided by the class will also populate. Pretty snazzy, right?

Before you save your new customer, you will need to enter an Address ID, since Prospects do not have address IDs. Once you save your customer and close the window, return to the Prospect to Customer window and click OK. Your Quote will then transfer to an Order. Presto!

So now that you have your new Customer and Order, what happens to the Prospect? Well, GP does not automatically delete or remove the prospect once it is transferred to a customer, so you need to manually delete it if you want to get rid of it.

So now that you know, show your GP Prospects some love. (and lobby the GP dev team to fix that error)

Dynamics GP Multi-currency ISO Code

By Steve Endow

If you have ever setup Multi-Currency in Dynamics GP, you may have noticed a field called "ISO Code".

It is located on the Currency Setup window, and is a required field. So any time you create a new currency ID, you have to enter an ISO Code. Values are defined by the International Organization for Standards (ISO), and are typically three letter values, like USD, CAD, AUD, etc.

It looks like the ISO organization charges for their standards document, but Wikipedia appears to have a decent listing.

The GP Help file explains that the field is a three letter code, but in typical frustrating fashion, offers no explanation as to where or when the field value might be used.

Well, I recently had a crash course on the topic, and learned of two areas where it is used.

First, it is apparently used by FRx. In this particular situation, a client had transactions for the same currency in GP using two different Currency ID values (long story). Rather than try and convert the data so that all of the Currency ID values matched, Microsoft support offered a workaround for FRx reporting: Set the ISO codes for both currency IDs to the same value. So if Currency ID USD and Z-US$ have the same ISO code, FRx will think that the transactions / data use the same currency.

Okay, so that workaround may have solved that problem, but then we uncovered a second place where the currency ISO code is used: GP Web Services.

I've never had to do a multi-currency integration using Web Services, so I never looked at the currency fields. But if you check the help file, you will notice that GP Web Services asks for an ISO Code, and not a Currency ID for integrated transactions.

So what happens when you have two currency IDs setup in GP that have the same value for the ISO code? Flip a coin!

It appears that Web Services grabs one of the two (either randomly or based on a table index or some sort order). Needless to say this isn't a good thing. In this case, it grabbed the one currency record that did not have rate tables setup, so the web service import failed with an error explaining that it could not find a current rate.

So if you are working with Multi-Currency, you now know at least two places where the ISO code matters.

I'm sure there is plenty of history behind the addition of the ISO Code field, but at this point the documentation is minimal and the inconsistent use of currency ID vs. ISO code makes for some fun times.

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

You can also find him on Google+ and Twitter

http://www.precipioservices.com

Consultant Tools Series: Darkfield Laser Mouse

If you are a roving consultant, you've probably built up a set of tools, technologies, and tricks to help you work more efficiently in all sorts of situations. For the mobile consultant, you have probably had to work at a free desk during the rare visit to the office, or at random workplaces at client sites, airports and airplanes, and endless coffee shops in between. You may have had to pull files off of a floppy disk (remember those?), open an Access 2.0 database, open a 2GB text file, use VLookup on a giant Excel file, connect to a client VPN from a rental car in a parking lot, you name it. My favorite was having to sit on a milk crate and rest my laptop on a broken office chair while working at a client.

Well, I've certainly had to field alot of random requests in random places over the years, so I thought I would occasionally share some tools, tips, and tricks that I've accumulated.

This first note is about wireless mice. I've used 2 or 3 wireless mice with my laptops over the last 5 years, both Logitech for some reason. The first one was good, but had poor battery life. The second one had good battery life, but an annoying scroll wheel that was difficult to click as a center mouse button. And I remember I had one that would "fight" with any other wireless mouse in the room, moving the cursor on other computers, and vice versa. All have had an annoying USB 'dongle' that stuck out of my laptop that I was always afraid I would lose.

Those optical / laser mice typically worked fine and did the job, but have you ever been at a client who had a glass conference room table? Or any table with a smooth, shiny, monochrome surface? I had two clients who had glass tables, and I often work at a coffee shop with tile tables outside.

Other than using the frustrating touchpad on my laptop, my only solution was to find a piece of blank paper, stick the edge under the rubber feet on one side of my laptop to keep it from moving, and use that as a make shift mouse pad. (wow, remember mouse pads??)

Enter the Logitech Darkfield Laser mouse. This is the answer for the mobile consultant who must mouse on any surface imaginable. I don't know if any other mouse manufacturers are making a competing product--if so, Logitech must have better marketing.

I finally broke down and bought one recently--the Anywhere MX model. Based on my tests, it's pretty darn amazing.

I just grabbed a picture frame off the wall--literally. It works fine right on the glass. I've only used it a few times, but so far it seems to work on any surface, even the bottom of a DVD and my laptop LCD display.

And a very nice touch is that they reduced the size of the USB dongle to a tiny little 1/4" nub that can stay in your laptop USB port at all times.

I'm still getting used to the shape, buttons, and odd scroll wheel, but the new laser does seem like a pretty neat innovation.

Mouse away.

Project Accounting Timesheet Recalc Version 2 Released

Based on a customer request, I have added new functionality to my Project Accounting Timesheet Recalc Utility and have released Version 2.0.

The PA Timesheet Recalc utility is useful for Dynamics GP customers who use Project Accounting Timesheets for salaried employees.

The salaried employees have fixed payroll costs, but if they work more or less than the standard number of hours in a given pay period, the project timesheet costs posted to the general ledger will not match the actual payroll expenses for the employee.

Some companies ignore this discrepancy, some companies post a summary adjustment to correct the difference, and others try and manually adjust the costs on each Project Accounting timesheet to try and get the total to match the employee payroll amount. Each of these has its own challenges that make them less than ideal.

The PA Timesheet Recalc utility adjusts the unit costs on PA timesheets so that the amounts match the employee payroll exactly. It averages the unit cost based on the hours entered on the timesheet, handles the rounding issues caused by the average cost calculation, and also recalculates the timesheet distributions.

The initial version of PA Timesheet Recalc was designed for companies whose employees submitted a single timesheet for the entire pay period. In this situation, the total timesheet cost would always equal the employee pay, plus any overhead.

However, some companies have employees submit multiple timesheets during the pay period. For companies that wish to have the timesheets routed and approved by different managers responsible for different projects, employees may have to submit a separate timesheet each day for each project.

To accomodate this requirement, PA Timesheet Recalc Version 2 can now recalculate multiple timesheets for a given employee for any date range. For example, if an employee submits 20 timesheets for the period of January 16 - January 30, the utility summarizes all timesheets to determine the appropriate average cost and recalculates all of the timesheets. The user is able to specify the date range, and select multiple employees to include in the recalculation process.



PA Timesheet Recalc is definitely a niche product, but for those companies that are using Project Accounting Timesheets and trying to get their project costs to match their payroll expenses, it can be a significant time saver.

Since it is somewhat difficult to explain and understand, I've posted an updated video demo of the product.

Tracking down a GP 10 security task that is granting unwanted access to a window

I don't spend a whole lot of time in GP 10 security. I appreciate the design of the new security model, but I find configuring the security roles and tasks tedious, so I'm thankful I don't have to deal with it often.

GP 2010 does have some enhancements that will make configuring GP security easier, but for those still on GP 10, you are limited in your ability to research certain security settings.

I recently fielded a question about GP 10 security where the user was looking to remove access to specific SmartLists, but he was unable to determine which security Task in his Role was granting the access. He had unchecked all of the tasks that seemed obvious, but the SmartList was still visible to the user.

After poking around, I saw that the SmartLists were not assigned via separate, discrete Tasks--they were assigned to Tasks that provided access to other types of objects.

Below is one approach to figure out which security Tasks grant access to specific GP objects. In my example, I'm trying to figure out which security Tasks provide access to the Accounts and Multidimensional Analysis SmartLists, but you can use the same technique to track down almost any other GP object.

There may be other ways to figure this out, but this is what I came up with, which was quick and simple.

1. Create a new empty security Task (I called mine "1TEST")
2. Assign it to category Financial
3. For Product, select SmartList
4. For Type, select SmartList Objects
5. For Series, select SmartList Objects
6. Check only the Accounts and Multidimensional Analysis checkboxes
7. Save the Task

Run this SQL statement against the "Security Assignment Task Operations" table:

SELECT * FROM DYNAMICS..SY10700 WHERE SECURITYTASKID = '1TEST'

This will list the security IDs for those specific SmartLists. In my TWO database, they are 1 (Accounts) and 14 (Multidimensional), with a security resource type = 1000 (presumably SmartLists).

Now, query the security task table again to see what other tasks are granting access to those two SmartLists:

SELECT * FROM DYNAMICS..SY10700 WHERE SECRESTYPE = 1000 AND SECURITYID IN (1, 14)

In my TWO database, that shows me that the following Tasks are providing access to those SmartLists:

1. CARD_0101 (Accounts)
2. CARD_0102 (Accounts and Multidimensional)
3. INQ_FIN_001 (Accounts)
4. INQ_FIN_002 (Accounts and Multidimensional)


This quicky tells me all of the Tasks that provide access to those SmartLists, so now I can go back to my Role and determine how I want to remove access.

I could uncheck all four of the Tasks, which will do the trick, but it may also remove access to windows that the user should be able to access.

Another approach would be to modify those Tasks to remove access to the two SmartLists. This would also work, but any other users that rely on those Tasks to view the Accounts and Multidimensional SmartLists would lose access as well. In that case you can create a new tasks that gives access to those SmartLists, and add that task to a new or existing role for users that need access.

Clear as mud?