Quick and Dirty Checklist for Modifying Word Templates

I was asked recently for the basic steps on how to modify Word templates. They didn't need step-by-step instructions, just something to help them along the way. Here's the list I came up with. I'm sure something is missing; I trust you will correct me where I'm wrong. As I told my colleague, this is the best I could do waiting for the train.

Modifying Word Templates can be kind of tricky. I've accumulated the steps and put them together in a list. Of course, before you try any of this you should back up your database, your existing Word templates as well as your Reports dictionaries.

1. Modify the Report Writer report such that it is properly printing (without errors) the values you are wanting to print on your Word template. Be careful of what section you place your new fields, it matters. If the Report Writer report doesn't print clean, you can't move on and expect to succeed.

2. Change your Alternate/Modified Forms & Reports ID so that you are pointing to your modified report.

3. Print your modified report. In the report destination window, there is a field 'What Type'. Here you select the option between Template and Standard. Choose standard, don't hit OK yet.

4. In the Destination Area, check both Screen and File.

5. Browse to a place you want to save your file, say the desktop, and change the file format to .XML

6. The Report Writer work is done, now you have to address your Word Template. I'm going to presume you have templates turned on and you have the template we're modifying set up as the default template for your company.

7. Open the Template Maintenance window and find the template you want to modify.

8. Once you have it in the window, click the Modify button at the top of the window.

9. Word will open to a layout version of your report. Save this on your desktop.

10. Click on the Developer Tab in Word's ribbon bar/tool bar.

11. If you do not have a Developer tab, turn it on using Word Options.

12. Over to the far right you'll see the Dynamics logo - the colorful one, click it. It says field list under it. If you do not have the Dynamics logo button, install the Word Addin from the GP install media.

13. When you click on Field List, a pane opens up on the left side of the window. At the top of that pane is XML Resource

14. Click on the down-pointing chevron and select SOP Blank Order Form.

15. Back to the Developer Section, select Remove Source. Accept the warning.

16. With the old source gone, now you need to add a new source.

17. Click on Add Source and select that XML file you created from Report Writer.

18. Put the new fields on your template and make any other changes you want.

19. Save the Word Document to your desktop and don't forget its name.

20. Close Word.

21. Go back to GP and open the Report Template Maintenance window if it isn't already open.

23. Select the Template Name of the report you modified and click on the green plus sign near the top.

24. Select the Word Document you just saved after your modifications.

25. Click OK when it tells you you've already got a template with this name.

25. Double-check your assignments and print your newly modified report.

Live the Dream!
Leslie

The Customer is its own Parent

Recently I've read some posts regarding National Accounts. There are a few items I wanted to note.

1. Once you have posted a transaction between the parent and the child - the child cannot gain independence again, nor can it run off to a different parent.
2. On the Customer Card, the parent needs to have itself as its Parent Customer ID - making them a parent in the National Accounts Maintenance window will automatically accomplish this. If you import the children into the National Accounts master - don't forget this detail.
3. If the children are all removed from the parent, the parent will still have itself as its own parent.
4. If you want the former parent to become a child of another customer, you will not be able to do it until you take away its Corporate Customer ID (which is its own ID)
5. The only way I know of to fix the former parent problem, is to use SQL and remove the record from the RM00105 table (the RM National Accounts Master)

Moments after I posted the above, I received a note from Brenner Klenzman at WilloWare Software  about a neat customization you can get from them. Here's his story:

 

" As an ISV, a VAR is a National Account for us [Willoware], with their Customers (end users) being the children in the National Account.

However, there is often more than one VAR/partner involved with a single customer.  So one customization allows us to assign any VAR as the “parent” on any given invoice.  Essentially there is a Primary Parent, and an unlimited number of Secondary Parents.

Also, since customers often change VARs, another customization allows changing the Primary Parent (i.e. the National Accounts Parent)."

I hope this helps someone who may be struggling with National Accounts.

Live the dream!

Leslie

 



VKonnact - a Fresh Look at Data Integration

I recently looked at a relatively new product on the market for integrating data into Dynamics GP. It's called VKonnact and it uses eConnect for the backend but shields the user from all of the eConnect complexities. While I haven't finished my review yet, what I've seen so far is very interesting. It's more user friendly than other products in the channel and has a user interface that looks nearly identical to the GP windows you are used to seeing. If you are thinking about purchasing an integration solution, definitely give this one a look. More to follow once I finish my review.
Live the dream!
Leslie

~internal~ Windows have no place in security

Have you ever wanted to use security to disallow the system Setup Checklist? You access this window using the Microsoft Dynamics GP button and then Tools > Setup > Setup Checklist. As it turns out, you cannot turn it off. Any window resource that has the name ~internal~ is not available to the security model. Now, if you hacked the dictionary (NOT recommended and a violation of your license agreement) and changed the object's name, you could then get to it, but that's not a good idea.

I had a bright idea once and thought I could use Modifier to move all of the fields off of the window. That worked fine, except it wasn't available to select in the Alternate/Modified Forms and Reports window (such a catchy name). I read a GPUG forum post from Jeff Martin that said you could include the Setup Checklist window in a VBA project, so I set out to see if I could get it to work with VBA. Sure enough, you can cause the window to open invisibly, but that doesn't really solve the problem. The reason for this post is to publish a tidbit revealed to us by David Musgrave of Winthrop Development. He said that "part of the issue with the checklists window is that the one window is used for all checklists from all series". I did not know that. Another mystery solved. Thank you David.

Live the dream!
Leslie

Welcome to Conexus SG Harry Lee

Can you believe it?! Harry Lee, Redbeard himself, a fellow Microsoft MVP, has joined the fine people at the consulting firm of Conexus SG in Dallas, TX. I didn’t recognize him without his hat, and he didn’t recognize me without my cape, but know for sure that Conexus SG now employees one of the finest Dynamics GP consultants on the planet.

If you are in the Dallas, TX area and need help with Dynamics GP, be sure to check with Conexus SG. Of course, you should check with me first.

Live the dream!
Leslie

Macros and Scrolling Windows

I did a session on GP Macros today at the Amplify conference in Anaheim. Questions came up regarding scrolling windows that I did not have time to cover in the short 1-hour session. I'm posting this example as an aid to understanding how scrolling windows work. The macro language will only deal with the number of rows that are visible in the scrolling window. When you open the SOP Entry window there are 8 lines visible in the scrolling window. When you add information to the scrolling window you need to include the command ScrollByLine down scrollwin 'Line_Scroll' at the bottom of the 8th line. Every line after line 8 is still considered line 8 because there are still only 8 lines visible. Below is a partial example macro of what it looks like to add more than 8 items to the SOP Entry scrolling window:

CheckActiveWin dictionary 'default'  form 'SOP_Entry' window 'SOP_Entry'
  ClickHit field 'SOP Type' item 4  # 'Invoice'
  MoveTo field 'Document ID'
  MoveTo field 'SOP Number'
  MoveTo field 'Customer Number'
  TypeTo field 'Customer Number' , 'AARONFIT0001'
  MoveTo field 'Customer Name'
  MoveTo field 'Primary Shipto Address Code'
  MoveTo field 'Document Date'
  MoveTo field 'Batch Number'
  TypeTo field 'Batch Number' , 'AUG31'
  MoveTo field 'Location Code'
  MoveTo field 'Customer PO Number'
  MoveTo field 'Currency ID'
  TransLinePrepare scrollwin 'Line_Scroll'
# 
  MoveTo line 1 scrollwin 'Line_Scroll' field 'Item Number'
  TypeTo line 1 scrollwin 'Line_Scroll' field 'Item Number' , '128 SDRAM'
  MoveTo line 1 scrollwin 'Line_Scroll' field '(L) Dropship'  # 'FALSE'
  MoveTo line 1 scrollwin 'Line_Scroll' field '(L) Unit Price'
  MoveTo line 1 scrollwin 'Line_Scroll' field '(L) Extended Price'
  TransLinePrepare scrollwin 'Line_Scroll'
#
  MoveTo line 2 scrollwin 'Line_Scroll' field 'Item Number'
  TypeTo line 2 scrollwin 'Line_Scroll' field 'Item Number' , '1-A3261A'
  MoveTo line 2 scrollwin 'Line_Scroll' field '(L) Dropship'  # 'FALSE'
  MoveTo line 2 scrollwin 'Line_Scroll' field '(L) Unit Price'
  MoveTo line 2 scrollwin 'Line_Scroll' field '(L) Extended Price'
  TransLinePrepare scrollwin 'Line_Scroll'
#
  MoveTo line 3 scrollwin 'Line_Scroll' field 'Item Number'
  TypeTo line 3 scrollwin 'Line_Scroll' field 'Item Number' , '128 SDRAM'
  MoveTo line 3 scrollwin 'Line_Scroll' field '(L) Dropship'  # 'FALSE'
  MoveTo line 3 scrollwin 'Line_Scroll' field '(L) Unit Price'
  MoveTo line 3 scrollwin 'Line_Scroll' field '(L) Extended Price'
  TransLinePrepare scrollwin 'Line_Scroll'
#
  MoveTo line 4 scrollwin 'Line_Scroll' field 'Item Number'
  TypeTo line 4 scrollwin 'Line_Scroll' field 'Item Number' , '128 SDRAM'
  MoveTo line 4 scrollwin 'Line_Scroll' field '(L) Dropship'  # 'FALSE'
  MoveTo line 4 scrollwin 'Line_Scroll' field '(L) Unit Price'
  MoveTo line 4 scrollwin 'Line_Scroll' field '(L) Extended Price'
#
  TransLinePrepare scrollwin 'Line_Scroll'
  MoveTo line 5 scrollwin 'Line_Scroll' field 'Item Number'
  TypeTo line 5 scrollwin 'Line_Scroll' field 'Item Number' , '1-A3261A'
  MoveTo line 5 scrollwin 'Line_Scroll' field '(L) Dropship'  # 'FALSE'
  MoveTo line 5 scrollwin 'Line_Scroll' field '(L) Unit Price'
  MoveTo line 5 scrollwin 'Line_Scroll' field '(L) Extended Price'
  TransLinePrepare scrollwin 'Line_Scroll'
#
  MoveTo line 6 scrollwin 'Line_Scroll' field 'Item Number'
  TypeTo line 6 scrollwin 'Line_Scroll' field 'Item Number' , '128 SDRAM'
  MoveTo line 6 scrollwin 'Line_Scroll' field '(L) Dropship'  # 'FALSE'
  MoveTo line 6 scrollwin 'Line_Scroll' field '(L) Unit Price'
  MoveTo line 6 scrollwin 'Line_Scroll' field '(L) Extended Price'
  TransLinePrepare scrollwin 'Line_Scroll'
#
  MoveTo line 7 scrollwin 'Line_Scroll' field 'Item Number'
  TypeTo line 7 scrollwin 'Line_Scroll' field 'Item Number' , '1-A3261A'
  MoveTo line 7 scrollwin 'Line_Scroll' field '(L) Dropship'  # 'FALSE'
  MoveTo line 7 scrollwin 'Line_Scroll' field '(L) Unit Price'
  MoveTo line 7 scrollwin 'Line_Scroll' field '(L) Extended Price'
  TransLinePrepare scrollwin 'Line_Scroll'
#
  MoveTo line 8 scrollwin 'Line_Scroll' field 'Item Number'
  TypeTo line 8 scrollwin 'Line_Scroll' field 'Item Number' , '128 SDRAM'
  MoveTo line 8 scrollwin 'Line_Scroll' field '(L) Dropship'  # 'FALSE'
  MoveTo line 8 scrollwin 'Line_Scroll' field '(L) Unit Price'
  MoveTo line 8 scrollwin 'Line_Scroll' field '(L) Extended Price'
  TransLinePrepare scrollwin 'Line_Scroll'
  ScrollByLine down scrollwin 'Line_Scroll'
#
  MoveTo line 8 scrollwin 'Line_Scroll' field 'Item Number'
  TypeTo line 8 scrollwin 'Line_Scroll' field 'Item Number' , '1-A3261A'
  MoveTo line 8 scrollwin 'Line_Scroll' field '(L) Dropship'  # 'FALSE'
  MoveTo line 8 scrollwin 'Line_Scroll' field '(L) Unit Price'
  MoveTo line 8 scrollwin 'Line_Scroll' field '(L) Extended Price'
  TransLinePrepare scrollwin 'Line_Scroll'
  ScrollByLine down scrollwin 'Line_Scroll'
#
  MoveTo line 8 scrollwin 'Line_Scroll' field 'Item Number'
  TypeTo line 8 scrollwin 'Line_Scroll' field 'Item Number' , '128 SDRAM'
  MoveTo line 8 scrollwin 'Line_Scroll' field '(L) Dropship'  # 'FALSE'
  MoveTo line 8 scrollwin 'Line_Scroll' field '(L) Unit Price'
  MoveTo line 8 scrollwin 'Line_Scroll' field '(L) Extended Price'
  TransLinePrepare scrollwin 'Line_Scroll'
  ScrollByLine down scrollwin 'Line_Scroll'
#
  MoveTo line 8 scrollwin 'Line_Scroll' field 'Item Number'
  TypeTo line 8 scrollwin 'Line_Scroll' field 'Item Number' , '1-A3261A'
  MoveTo line 8 scrollwin 'Line_Scroll' field '(L) Dropship'  # 'FALSE'
  MoveTo line 8 scrollwin 'Line_Scroll' field '(L) Unit Price'
  MoveTo line 8 scrollwin 'Line_Scroll' field '(L) Extended Price'
  TransLinePrepare scrollwin 'Line_Scroll'
  ScrollByLine down scrollwin 'Line_Scroll'
#
  MoveTo line 8 scrollwin 'Line_Scroll' field 'Item Number'
  TypeTo line 8 scrollwin 'Line_Scroll' field 'Item Number' , '128 SDRAM'
  MoveTo line 8 scrollwin 'Line_Scroll' field '(L) Dropship'  # 'FALSE'
  MoveTo line 8 scrollwin 'Line_Scroll' field '(L) Unit Price'
  MoveTo line 8 scrollwin 'Line_Scroll' field '(L) Extended Price'
  TransLinePrepare scrollwin 'Line_Scroll'
  ScrollByLine down scrollwin 'Line_Scroll'
#
  MoveTo line 8 scrollwin 'Line_Scroll' field 'Item Number'
  TypeTo line 8 scrollwin 'Line_Scroll' field 'Item Number' , '1-A3261A'
  MoveTo line 8 scrollwin 'Line_Scroll' field '(L) Dropship'  # 'FALSE'
  MoveTo line 8 scrollwin 'Line_Scroll' field '(L) Unit Price'
  MoveTo line 8 scrollwin 'Line_Scroll' field '(L) Extended Price'
  TransLinePrepare scrollwin 'Line_Scroll'  
  ScrollByLine down scrollwin 'Line_Scroll'
#
  MoveTo line 8 scrollwin 'Line_Scroll' field 'Item Number'
  TypeTo line 8 scrollwin 'Line_Scroll' field 'Item Number' , '128 SDRAM'
  MoveTo line 8 scrollwin 'Line_Scroll' field '(L) Dropship'  # 'FALSE'
  MoveTo line 8 scrollwin 'Line_Scroll' field '(L) Unit Price'
  MoveTo line 8 scrollwin 'Line_Scroll' field '(L) Extended Price'
  TransLinePrepare scrollwin 'Line_Scroll'
  ScrollByLine down scrollwin 'Line_Scroll'
#
  MoveTo line 8 scrollwin 'Line_Scroll' field 'Item Number'
etc., etc., etc.,

I hope this helps someone struggling with scrolling windows.
Kind regards,
Leslie

Insert or Delete Lines in Advanced Financial Analysis

Frank Heslin recently revealed a tip about the AFA module that few people have run across. Back in the old days when AFA was our only way to create financial statements, there was more information floating around about how to use it. Today, not so much. With the latest versions of GP that come with the ‘ribbon’ menus, you will be hard-pressed to figure out how to insert or delete lines into an AFA financial report. Frank provided the following tip on the GPUG collaborate forum “change your  'Window Command Display' under User Preferences to 'Menu Bar', then restart GP, and you will see the option to insert or delete under the 'Edit' menu.  Keep in mind that this change is global for you, so if you prefer the 'Action Pane' or 'Action Pane Strip', you'll have to toggle this setting as needed.”
Thanks Frank!
Until next post,
Leslie

National Accounts Step Children

Thanks to Steve Erbach (aka Sparkly Steve)  and Lauri Romaniuk for this idea! The question came up on the forum as to whether you could change the National Account for a customer whom has already been used in a transaction with it’s parent. I thought the only way you could do it was with a bit of file surgery, but Steve pointed out a better way.

Here’s the scenario. Astor Suites has Aaron Fitz as it’s child. Astor Suites sent Fabrikam a check that was used to pay one of Aaron’s invoices. The transaction was recorded and posted using the National Accounts feature in the Cash Receipts window. As with so many relationships, Aaron Fitz fell out of favor with Astor Suites and Aaron Fitz ran away and joined up with Adam Park Resort. Now we want to change Aaron Fitz’s National Account to reflect Adam Park Resort as the parent. We also want to remove Aaron Fitz from the National Account of Astor Suites.

If you simply go into the National Accounts screen and attempt to remove Aaron Fitz from Astor Suites, you cannot. You are greeted with the error message ‘You can’t remove this child customer ID. Apply records exist between the parent customer ID and this child customer ID.’

Here’s how Steve told us we can make the change. I ran through this example and it worked! Be aware, however, that those previously posted parent/child transactions are no longer properly represented in your transaction history. The financial information is OK, but there is no longer a trail back to the previous parent. Here’s Steve’s process:

1. Create a new Customer with the correct National Account and all the same attributes as the existing Customer. (including the name and address information)
   
2. Use PSTL Customer Combiner to move transactions to the new Customer.
   
3. Delete the old Customer now that it has no transactions. (this will happen automatically)
   
4. Use PSTL Customer Modifier to change the Customer Number of the new Customer to be the same as the old Customer.

Here are a couple of links to the original forum threads:
http://bit.ly/1NtJx3f
http://bit.ly/1slC1ya

Thanks everyone!
Until next time - Leslie Vail

Technorati Tags: National Accounts,PSTL

The Corrector by Reporting Central

From the folks who brought us The Closer, we now have The Corrector. I spent some time this morning with Gianmarco Salzano gsalzano@reporting-central.com and Clark A. Patterson cpatterson@reporting-central.com looking at a new product called The Corrector. This new product is very nice, and worth a look if you find yourself needing to correct distributions often. It makes correcting posted AP transactions easy while providing an audit trail for the change.
Let’s say you post some AP transactions, but later realize they were distributed to incorrect GL accounts. With The Corrector, you can simply click on the Correct button and adjust the distribution to point it to the correct account. The correcting journal entry is automatically created and linked to the original transaction.

I thought this made the process of correcting a distribution seamless and provided an audit trail as to what was done and why it was done. It was so much easier than trying to figure out what correcting entry went with which transaction (we’ve all been there at one time or another). It’s a small piece of functionality that makes correcting posting transactions much easier. It doesn’t change the original transaction, it provides a means for you to easily correct the distributions using a correcting journal entry that is linked to the original entry. I liked it.
The price was good and it was very easy to use. Take a look at http://reporting-central.com/the-corrector-for-ap-distribution/.
Kind regards,
Leslie

What?! Referential Integrity Checked on a Clear Data Table.

I learned something today from Lawrence Reid at ConexusSG that impacted the Clear Data file maintenance utility. They set up some National Accounts and then wanted to clear the Customer Master table. Once you create National Accounts, none of the customers with children will be cleared. After you run the utility you will get an error on your report that states:

You can't clear data in the parent table RM Customer MSTR until you have cleared data in the child table RM National Accounts Master.

Wow! I’d never seen that before. It does delete all of the customers with no ‘child’ companies, but any customers with children are left intact.

Until next time,

Leslie Vail

Upgrade from GP 2010 to GP 2013 R2

Question: How do you upgrade from GP 2010 build 2390 to GP 2013 R2

Answer: The documentation from Microsoft that delineates the upgrade path from GP 2010 to GP 2013 R2 is missing the current build of GP 2010 (as of 3/16/2015). The document from Microsoft can be found here: https://mbs.microsoft.com/partnersource/northamerica/support/hot-topics/HOT_TOPIC_MDGP2013Upgrade#UpgradePaths.

The line missing is how to upgrade to GP 2013 R2 from 11.00.2390. The answer is that you have to upgrade to 12.00.1826. The build numbers for both GP 2010 and GP 2013 R2 are achieved after installing the January 2015 Hotfix. Thanks go out to Paul Degneau, at Standard Process www.Standardprocess.com, for suggesting this to me.

Enjoy!

Leslie

Values for SY01402 - System User Defaults

I needed to research the settings in the System User Defaults table recently, so I took out my list of what the syDefaultType field translated to. The table below shows the value and its definition. These constants are from the GP2013R2 build

syDefaultType	Definition
1	SCBARAUTOHIDE
2	SCBARWHICHSIDE
3	SCBARWIDTH
4	SCBARICONSIZE
5	SCBARAUTOOPEN
6	UNDERLINED_PROMPTS
7	COLORSCHEME
8	BACKGROUND
9	TOOLBARBUTTONS
10	MESSENGER
13	REMINDERS
14	MAPPOINT
15	FIELDSECURITY
16	COMMISSIONS
17	VENDORLISTSORT
18	VENDORLISTINACTIVECB
19	CUSTLISTSORT
20	CUSTLISTINACTIVECB
21	ITEMLISTSORT
22	ITEMLISTINACTIVECB
23	SOPITEMMISSINGCB
24	POPITEMMISSINGCB
25	EMPLISTSORT
26	EMPLISTINACTIVECB
30	AUTOCOMPLETE
31	PRINTONEPOPERADDRESS
40	HRVETSNAICSNUMBER
41	LISTWINDOWSIZE
42	NAVBAR_BIGBTNCOUNT
43	SOPMANAGESOPTYPE
45	CPRCONVERSION
46	SQLREPORTING_TARGETLOC
47	SQLREPORTING_RPTMGRLOC
48	SQMOPT
49	SHAREPOINTSERVERURL
50	LOCKEDTOOLBARS
51	REGKEY
52	HOMEPAGEMETRIC
53	HOMEPAGEREMINDERS
54	HOMEPAGEOUTLOOK
68	MFG_MODULE_ENABLED
69	REMINDERSCUES
70	REMEMBEREDCOMPANY
71	REMEMBERUSERENABLED
72	SHOWALLMETRICS
80	RPTTEMPLATES_ENABLED
82	RPTTEMPLATES_ALLOWSTDORTEMPLATE
83	POSITIONCONTROL
84	FACTBOX_ROTATIONFREQUENCY
85	FACTBOX_IMAGESIZE
90	ISSHAREPOINTONLINE
91	EMAIL_SERVER_TYPE
92	EMAIL_PROTECT_WITH_PASSWORD
93	EMAIL_PASSWORD_RADIO
94	EMAIL_PROTECT_PASSWORD
95	PHONEHOME
96	SLAHWINDOW
97	WEBCLIENTSQLLOGIN
1493	SMARTLIST
22001	MBS_DEXPROFILE
22002	MBS_SQL_LOG

Enjoy!
Leslie

How to disable "Checking for newer builds" message in Rockton software products

Given the well-deserved popularity of Rockton Software’s (http://www.rocktonsoftware.com/) products, such as ‘SmartFill’ and ‘The Dynamics GP Toolbox’, it has come to my attention that it checks for newer builds each time you sign in. Kind of like the ‘Welcome to Dynamics’ trill from years ago, this can get annoying. Fortunately, Rockton has addressed this issue and published the following KB article to address it.

Jim Moore, at Wesco Valve and Manufacturing Company (www.sswesco.com) was kind enough to send me the article below: 

Issue:
Disabling the "Checking for newer builds..." message. Note: This setting is user-specific.

Option 1.

To disable this feature, go through the following steps:

1. Click on the Help icon in the upper-right corner of GP.
2. Select Rockton Software Support at the bottom of the list.
3. On the menu, click Settings >> Rockton Software Preferences.*
4. Under the User-specific settings section, bring up the individual user for whom you want to change the setting.
5. Unmark the Enable check for newer builds at the login for each user that doesn’t want to have that process run.

Note: These settings are stored in a table in the DYNAMICS database, so you don’t need to do this at each workstation.

*Step 3, the path may vary. It may be Rockton Software Support | Preferences | Rockton Global Settings.
Note: All users will be marked by default but the check for newer builds will only appear for PowerUsers and users assigned to the Administrator role for Rockton Software products within Security. If you use Auditor, then it is based on those users you marked as Admins. in the Auditor System Settings window.
Option 2.
To do a mass update on the table that the information is stored in, you can run the following script in SQL Management Studio.  You can then view what the values are for that particular setting in the RS_Global_Setting_Value column (1 = Enabled and 0 = Disabled)**. Be sure to have a backup made prior to running the script.

USE DYNAMICS

UPDATE RSIGLOBL SET RS_Global_Setting_Value = 0 WHERE RS_Global_Setting_Key = 'EnableNewerBuildCheck'

More Information:
When you install any Rockton product after turning this off, you will receive a prompt at the end of the install to re-enable the setting if it has been disabled. 
**It is recommended that you leave at least one person enabled, so that they are notified when a new build is available.

Reshuffling your Account Structure

You come to work one morning and find a note on your desk saying that you need to change the structure of the account number. You need to add a segment to the beginning to keep track of the Company number. Then you need to rearrange the existing segments to better match reporting requirements. Well, you have heard that is not something you can do ‘out-of-the-box’. All of the documentation says you cannot do it and your partner says you cannot do it. Microsoft even says you cannot do it. Your partner tells you that you need to buy ‘Changer’ from Corporate Renaissance Group http://www.crgroup.com. Truth is you CAN do it. This article is intended to provide a step-by-step instruction on how to accomplish your directive ‘out-of-the-box’.

Setting the stage

Let’s take Fabrikam, Inc. (Fabrikam) as our example company – everybody knows good ole’ Fabrikam. You need to change the account format from its current format to a new format. The old and new formats are shown in the table below:

Old format	New format
Division – Account – Department XXX – XXXX - XX	Company – Department – Division – Account X-XX-XXX-XXXX

Breaking your job down into simple steps, here is what you have to do: 1. Add a new single character segment to the front of the account
2. Move the Division segment to the third segment
3. Move the Department segment to the second segment
4. Move the main Account segment to the end.
Looks like a piece of cake. However, wait, you cannot add a segment to the front, you can only add one to the end. Another complication is that you cannot rearrange existing segments. Obviously, the plan needs to change. With that in mind, you take a different approach. You decide to change the lengths of the existing segments, add a new four-character segment to the end and then import the accounts back in with the configuration you want. Here is your new plan: 1. Shrink the first segment to one character from three characters.
2. Shrink the second segment from four characters to two characters.
3. Expand the third segment from two characters to three characters.
4. Add a four-character segment to the end.
5. Rearrange the numbers so that the account segments hold the correct values.
6. Import the accounts back in.
7. Re-label the segments.
Unfortunately, you cannot get past the first step. The system will not allow you to shrink the length of a segment. What to do?

Solving the problem

What follows is a step-by-step solution for changing your account structure. This is a complete list of the steps that are explained below: Step 1 – change to an Interim Account format
Step 2 – launch Excel and set it up to accept your current accounts
Step 3 – retrieve your current accounts from GL00105
Step 4 – select the returned data
Step 5 – copy the returned data
Step 6 – paste the data into Excel
Step 7 – prepare your ‘after’ columns
Step 8 – build your ‘after’ accounts
Step 9 – prepare your Excel spreadsheet to create the PSTL source
Step 10 – create the import file
Step 11 – open the PSTL tool
Step 12 – perform the import
Step 13 – confirm the structure of the new account numbers
Step 14 – commit the conversion
Step 15 – shrink the format
Step 16 – recreate the account index table (GL00105)
Step 17 – check your Inventory Setup and Site ID windows
Step 18 – final step – Reconcile

---

Step 1 – change the existing Account format Administration | Setup | Company | Account format

Beginning Format	Length	Interim Format	Length
1 – Department	3	1 – Department	3
2 – Account	4	2 – Account	4
3 – Division	2	3 – Division	3
		4 – Segment4	4

You need to do it this way because you cannot shrink (yet) the existing segments, but you need to provide enough space for the final configuration. You will adjust the lengths to the desired size much later in this process. Step 2 – launch Excel and set it up to accept your current accounts Set the first five columns to a Text format. This step will prepare Excel to accept a ‘Paste’ of your existing chart of accounts without stripping off the leading zeros. Your worksheet should look substantially similar to this: Step 3 – retrieve your current accounts from GL00105 Open SQL Studio Manager and run the following SQL script: SELECT actindx
, actnumbr_1
, actnumbr_2
, actnumbr_3
, actnumbr_4
, actnumst
FROM gl00105
Step 4 – select the returned data Click in the upper left-hand corner of the results to select the entire contents. Your result set should look substantially similar to this: Step 5 – copy the returned data Right click on the upper Left-Hand corner and select ‘Copy with Headers’. Step 6 – paste the data into Excel Switch over to Excel and ‘paste’ into cell A-1. Make sure Excel did not strip off your zeros. If they are, return to step 2 and start over. These will be your ‘before’ numbers and your spreadsheet should now look substantially similar to this: Step 7 – prepare your ‘after’ columns Take the ‘before’ columns and copy them into ‘after’ columns. Be sure to skip a column between the two groups. THIS IS IMPORTANT. You are going to need to use columns for formulas and text cells will not accept formulas. I like to add some rows to the top to keep track of what I’m doing. Below is an image showing the rows I add to the ‘after’ columns. Step 8 – build your ‘after’ accounts Next, you need to build the new account in the structure you want. This is where you rearrange the columns and create the ‘after’ accounts that you will be using with the PSTL tool. More about that later. I create an Excel formula that will piece together the account segments in the ‘after’ ACTNUMST column. For Fabrikam, I used this formula in column M: • =TRIM(I5)&"^^-"&TRIM(J5)&"^^-"&TRIM(K5)&"-"&TRIM(L5) The ‘after’ columns of my spreadsheet now look like this: I used the carrot symbol to show where the spaces need to go. If you do not provide spaces to fill up the account segment length, your ‘new’ account will not come out right. Without the spaces, the new account will just fill the account field one character after another. For example, the first ‘after’ account above would resolve to 300-0001-100- . That is not what you want. We will remove the carrots in an upcoming step. Step 9 – prepare your Excel spreadsheet to create the PSTL source The PSTL tool’s import source requires a text file with two columns. It needs the accounts as they exist today in the first column. I call this the ‘before’ column. The second column contains the new account numbers. I call this the ‘after’ column. Remember, you still need blank spaces in the ‘after’ column to populate the account master properly. We will remove the spaces later when we shrink the segments. Insert a column next to the ‘before’ ACTNUMST column. Copy the ‘after’ ACTNUMST column and paste the values (not the formulas) into the new empty column. To paste the values, select the Paste as illustrated below: Your spreadsheet should look substantially similar to this: Step 10 – create the import file This step will create the file you will eventually use as the import file for the PSTL tool. Launch Notepad. Copy just the ACTNUMST columns (both ‘before’ and ‘after’). Do not copy the headers, copy only the numbers. Paste it into Notepad. There will be a lot of space between the two columns; that’s OK. Save the file to a place and using a name you will remember. I’m naming this one OldAcctNewAcct.txt Replace the “^” characters with empty spaces. To do this, select CTRL+H in Notepad. The Replace window will open. In the ‘Find what’ field put the ^ symbol. In the ‘Replace with’ field put a single space. The Replace window should not look substantially similar to this: Click the Replace All button. Save the file Step 11 – open the PSTL tool Open the PSTL tool main window. If you are using GP2013, you will have a button in the lower section of your Navigation Pane that says ‘PSTL’. It will look substantially similar to the image below: Select the ‘Main Setup Window’ from the next window that opens. From the ‘Main Setup Window’, select the ‘Acct Modifier/Combiner’ radio button and then click the ‘Next’ button. You will find the ‘Acct Modifier/Combiner’ tool in the ‘Financial Tools’ section as illustrated below: Step 12 – perform the import Select the ‘import’ radio button and then click the ‘Validate’ button. The window is illustrated below: On the next screen, select the .txt file you created in an earlier step. The system will run through the validation. If you see any account numbers going by, your validation has failed. Print the report at the end of the process. Once it shows no invalid accounts, you are good to go. But wait, just because your ‘before’ column was valid doesn’t mean your ‘after’ column resulted in a desirable format. Step 13 – confirm the structure of the new account numbers Before hitting the ‘Modify’ button, go back to SQL Studio Manager and check the structure you are about to import. A table named ‘TACHANGE’ holds the before and after numbers. I only worked with four segments, so I ran the query below to check my format: SELECT actnumbr_1
, actnumbr_2
, actnumbr_3
, actnumbr_4
, omnesa_1
, omnesa_2
, omnesa_3
, omnesa_4
FROM tachange
The results of my query looked like this: The ‘after’ accounts are stored in the Omnesa columns. These columns correspond to the account segments. This result shows that the accounts will be modified appropriately. If I did not have the spaces in the right place, my import might verify, but the results could look like this: Clearly, this is not what I want. Go back to the drawing board and remake your import text file. Try the validation again and check the results. Do not import your changes until this table displays exactly what you want the new accounts to look like. Step 14 – commit the conversion Once you are happy with the new account numbers, click on the ‘Modify’ button. You will probably be presented with three dialogs during the course of the conversion. Each one is illustrated below, along with the appropriate response. Select OK Select No Select OK. A report will then print showing you the before and after results. Your report will look substantially similar to this: Step 15 – shrink the format Even after the conversion, we still have those blank spaces in our account segments. This step is the magic of the process. You perform a system reconcile on the Account Format Setup table to get rid of the empty spaces and shrink the length of the account segments. Administration | Utilities | System | Reconcile After the reconcile, check your Account Format window to make sure you have the expected results. Change the segment names and make sure the appropriate segment is selected as the Main account. Fabrikam’s window looks like this: Step 16 – recreate the account index table (GL00105) I know it has been a long road, but you are not there yet. Almost, but there are just a few things you need to do. Even after you change this account structure using the steps above, the ACTNUMST field in the Account Index master is not correct. Fabrikam’s looks like this: As you can see, there are still spaces in the account number. My thanks to Mike Lupro for suggesting this easy fix. You’ll need to go back to the PSTL module and use the System tool named ‘Toolkit’. Select the ‘Toolkit’ radio button and click ‘Next’. Mark the radio button next to ‘Rebuild GL00105’ and click ‘Next’. Click the ‘Rebuild GL00105’ button on the next window and the rebuild will begin. Once the rebuild is complete, you will get a dialog similar to this: You’re close, but you’re not done yet! Step 17 – check your Inventory Setup and Site ID windows Go to your Inventory Setup window and check the ‘Segment ID for Sites’ field. If the segment in that field is not the right segment, you will need to change it at the SQL level. Change the field IV40100.ACSGFLOC to the correct segment number. Next, be sure to open each of your inventory sites and make sure the Location Segment values are correct. If you need to change them, go to IV40700.Location_Segment and change it to the appropriate value. Step 18 – final step – Reconcile The final leg of this journey is to reconcile your Financial module. Financial | Utilities | Financial | Reconcile Reconcile every year, open or closed. If you are using any other products that use your account segments in a similar way, you will need to check them and potentially change their values. Finally, run the Reconcile utility in the Financials module against all of your years. Financial | Utilities | Financial | Reconcile

Enjoying the resolution

As you lean back and admire your work, pick up a tall one and bask in the glow of doing something when ‘they said it couldn’t be done’ Enjoy Leslie

Modifier Quick Tip for Fixed Assets - Don’t check that box!

 

This came up recently on the forum. A user had modified the ‘Asset User Data’ window. The window previewed perfectly in Modifier, the Modified/Alternate Forms and Reports ID was correctly set, and there was even a period before the window title on the window. However, the window still looked the same as it did when before the modification. What’s going on here?

What’s going on has to do with your choices in the Company Setup window for Fixed Assets. One of the Option items is a checkbox for ‘User Data Auto Format’. If this box is checked, any Modifier changes you make are overridden by the system, and the window displays as it did before it was modified.

The moral of this story is - if you want to modify the Asset User Data window, be sure to uncheck the ‘User Data Auto Format’ checkbox.

Enjoy!

Leslie

David Musgrave’s blog has moved

Please visit David at: http://winthropdc.wordpress.com/

David may have left Microsoft, but he hasn’t left the Dynamics GP stratosphere. In fact, this incredible resource is available for you to engage on one of your GP projects! Imagine hiring the best there is!

Welcome back David!

Going Beyond 5 Decimals

Recently I was asked how to get Dynamics GP to calculate a unit price using more than five decimals. They were tired of constantly dealing with the few pennies of rounding differences.

Multicurrency to the rescue!

Since Multicurrency uses more precision when doing the translation, you can set up a new currency that will use many more decimals. Here’s how it went.

The problem:

The actual unit price of an item was $0.00491256. The system could only use the first five decimals, so they ended up with a price of .00491. If they were to sell 10,000 of these, the extended price would be $49.10 instead of the correct price of $49.13. Sure, GP can easily dispense with that extra 3 cents, but it’s a pain.

A solution:

Create a new currency (I’ll call it $NEW) with an exchange rate of .000001 set to multiply. Enter the sale using the $NEW currency. When the currency is translated into the functional currency, the desired value of $0.00491256 is used as the unit price.

When entering the transaction using the $NEW currency, you would use a unit price is $4,912.56. When the price is translated into USD, you end up with this: $4,912.56 * .000001 = .00491256. Problem solved.

Just as an aside, changing the unit of measure schedule was not a viable alternative in this case.

Until next time!

Leslie

Product ID and Product Name

The table below lists the various products that are commonly available on the Dynamics GP install DVD media. I got this list originally from the Dynamics GP worksheet prepared by Microsoft.

Product ID	Product
0	Dynamics
1	Dexterity
258	Project Accounting
309	Fixed Assets
346	Manufacturing
414	Human Resources
949	Field Service
1042	Interfund Management
1045	Revenue Expense Deferrals
1058	Cashbook (Bank Management)
1150	Scheduled Installment
1157	Collections Management
1235	Safe Pay
1428	Electronic Reconcile
1493	SmartList
1632	Cash Flow Management
1838	Technical Service Tools
1878	Excel-Based Budgeting
1911	HRM Solution Series
2150	Payment Document Management
2199	Business Activity Statement
2277	Purchase Order Enhancements
2416	Control Account Management
2547	Enhanced Commitment Management
2788	Enhanced Intrastat
2992	CopierSeries
3096	VAT Daybook
3104	Advanced Security
3180	Analytical Accounting
3258	Encumbrance Management
3278	Report Scheduler
3830	SmartList Builder
4067	ML Checks
4421	Grant Management
4522	Payroll Integration to Payable
4612	Advanced Go Tos
4621	Analysis Cubes for Excel
4933	Certification Manager
4955	Employee Health and Wellness
5597	HITB Report
5967	Fixed Assets Enhancements
5982	Export Financial Data
6499	Dynamics Online
6831	Date Effective Tax Rates
7131	Canadian Payroll
8811	Direct Debits & Refunds

If you look in the ‘upgrade’ section of Customer/PartnerSource, a worksheet is available that includes the products and their various build numbers.

Until next time!

Leslie

LOADING STORED PROCEDURES - Create_Requester_Procs_cursor

Technorati Tags: gp 2013,upgrade,cursor error

Recently I did an upgrade from GP2010 to GP 2013 R2 (build 1745) and ran into a variety of problems.

I thought I’d share them here, in the event someone else is faced with the same issues.

Some background on the upgrade, we switched about everything you could:

 

1. Switched SQL Server physical machines.
2. Moved from a physical machine to a virtual server.
3. The names of the servers, and the SQL server changed.
4. Moved from Server 2008 to Server 2012.
5. Moved from SQL 2008 R2 to SQL 2014.
6. Moved from GP 2010 to GP 2013.
7. Moved an eConnect web commerce application.
8. Moved AVALARA’s web service.
9. Several 3rd party products were involved.

After several attempts to do things that did not solve the problem, the following DID solve the problem. My thanks goes out to fellow MVP Richard Wheeler who gave me the tip to shrink the transaction log.

1. Restore the databases to the new server
2. Execute the Upgrade scripts suggested by Microsoft
1. Account_Framework_Validation.sql
2. Check_Workflow_Status.sql
3. eConnect_Out_Setup.sql (this is really not necessary)
4. Invalid_Records_POTables.sql
5. Payroll_Work_Tables.sql
3. Truncate the eConnect_Out_Setup table
4. Shrink the transaction log of the target databases:
1. DBCC SHRINKFILE ('DB_Log.ldf', 1), etc;
5. Install Dynamics 2013 on the new Server
6. Update the tables using DynUtils
7. Import the eConnect_Out_Setup table records that were previously removed in step 3.

You most likely do not have to complete all of the above steps. But it worked for me, so I stopped.

I had done several upgrades before this one and never had a problem. I guess I just got lucky this time .

Until next time,

Leslie

Modified SOP Entry window

The SOP Entry window pictured above has been modified to bring the Description up to the first line. This made the window quite wide, but my goal was to get as much of the description visible as possible. The expanded view looks almost the same as the original, except I added the Item Code field as can be seen below:

If you would like a copy of this modified window for yourself, you can download the package file containing this window here: https://app.box.com/s/z2sujz9pu62jbybhxb4b

This is a GP2013 R2 window.

I welcome any suggestions you all may have to spiffy this up.

Until next post,

Leslie

Technorati Tags: Modifier,Modified Window,SOP Entry Window

Record the Bar Code in the Item Code field in Inventory

 

We’re all looking for fields we can use for something else without bringing the system down. Inventory is a popular area where we never seem to have enough fields. How many times have you looked through that Item Maintenance window looking for a field you can ‘re-purpose’. I always use the Item Code field. It’s a 15 character string field, but it’s not visible on the window. It’s been in the table for eons, but the powers that be never exposed it on the window. Well, we all have Modifier now, so that’s a quick and easy way to reveal the field .

Here’s my window - I’ve got Item Code in a field above Item Class, but you can put it anywhere you like.

That’s my tip for today!

Until next time,

Leslie

Technorati Tags: Inventory,Modifier,Bar Code

Modified RM Aged Trial Balance–Summary

The RM summary aged trial balance is too busy. Too many lines per customer. If you just want one line per customer, this is the report for you.

The modified AGED TRIAL BALANCE WITH OPTIONS – SUMMARY:

The original AGED TRIAL BALANCE WITH OPTIONS – SUMMARY:

I know it’s hard (perhaps impossible) to read the images, but you can clearly see how much cleaner the modified report looks.

You can download the modified version of this report here:
Modified RM Summary ATB-1 line per customer

Until next post

Leslie

Modified Payables Transaction Entry Distribution

I modified the Payables Transaction Entry Distribution window so the Account Number appears on the first line of the scrolling window. I also added the Currency ID field to the second line of the scrolling window because the second line looked pretty desolate and  needed something else. There were no other fields in the distribution table (PM10100) that looked interesting.

This modification was done on GP 2013 R2. You can download a package file for this modified form here: Modified Payables Transaction Entry Distribution

Enjoy!

Leslie

Modified GL Transaction Entry Window - Account Description on First Line

Forever I’ve wanted the account description on the first line of the GL Transaction Entry window. Well now I have it! It took me all day to create, but at least now it exists. I shouldn’t be the only one who benefits from my labor, so I’m going to make it available to you.

NEW GL Transaction Entry window:

Changes Made to GL Transaction Entry window:

Fields added to the upper right-hand quadrant:

• Last User ID
• Last Date Edited
• Originating Document Information
• Series
• Originating Posting Date
• User Who Posted

Fields added to the third line of the scrolling window: 

• Originating Master ID
• Originating Master Name
• Transaction Type
• Original Voucher/Payment Number
• Original Document Number

The screenshot below shows the new window with the changes highlighted:

This modification was done on GP 2013 R2. If you want a copy of this window for yourself, you can download the package file here: Modified GL Transaction Entry window

Until next post!

Leslie Vail