Recap about GP Tech Conference 2018

So this week was all about GP Tech Conference in Fargo, and sadly I couldn’t attend due to other scheduling conflicts.
However, some good friend & MVP’s have been there and reported their journey thru their respective blog posts.. Grab a cup of coffee (or Tea ūüôā ) or a glass of Wine and read thru them.. There are some nice topics covered and should you encourage to register for GPUG Summit in Phoenix this fall.
Advertisements
Categories: Dynamics GP, GP Tech Conference, News | Tags: , , | Leave a comment

Using GP Power Tools (GPPT) without VBA to run SQL code (part 1)

The Story behind this topic

Microsoft Dynamics GP Project Accounting is one of those wonderful module that can track Time & Materials for your resources. Until GP 2013R2 it was using the companion product Personal Data Keeper (aka PDK) that was integrated with the Business Portal (BP) for many years to allow end-users to quickly enter their Time & Expenses towards Project Accounting (PA) in GP. Though this no longer applies for newer GP versions like 2015 and up, the content of this blog post could still be used for other modules and I’m going to post another example with similar actions.

Recently the payroll master at the company I was working for was worried that if I wouldn’t be available one day to return a posted TS report for correction, he would be get in trouble, as much of his reporting was relying on the PDK transaction tables. You need to know that BP sends the data first to a set of PDKxxxxx tables in each GP company before they get posted to the PAxxxxx tables for financial transactions posting.

Those PDK tables do not have history tables, so everything remains forever in ‘work’ tables. The TimeSheet (TS) data uses one set of header & detail tables, whereas Employee Expenses (EE) uses another set of tables. The PA module in GP covers more transaction types than those two, but lets focus on TS transactions for now.

The TS header tables carries a status field that tells PDK the stage of each TS when it moves along from BP entry to PDK approval & processing, until PA posting. Sufficient to know that once a TS is submitted by an employee, the status is 5 (Submitted) and once it’s approved (7), processed (8) and finally posted (9) GP, it updated several times its value.

GP PA has a way to reverse a transaction that is posted in GP once it is posted (9), but due to a bug (or design flaw), the status of the TS itself never gets updated, thus forcing you to do this manually with a SQL script from the backdoor in the GP company DB. The transaction reversal in GP negates all amounts posted towards a project, but leaves you only with the option of submitting a new TS report, which due to reporting source coming from the PDK tables, would raise a concern as the data would just double up for an employee in the same time period, which of course is not good.

The same happens when a TS report has been processed (8) but not posted yet in GP, it is simply hold in a PA batch ready to post. Unfortunately, if you realize there was a mistake in the TS and you delete the batch, thinking you can just fix the TS & re-submit it, than you’re wrong. GP would flag (correctly) the TS as deleted (11) after deleting the PA batch and the corresponding transaction. But a TS with status 11 isn’t visible anymore in PDK to be managed.. thus having to grab back to SQL again.

How Can GP Power Tools  help me out here ?

Now you may ask, what does GP Power Tools (GPPT) have to do with PDK ? after all, GPPT is only available in GP, not in PDK..¬† It doesn’t really matter, as we want to use GPPT to fix the design flaw that prevents a proper status management of a returned TS report.

Well, here comes the handy part of GPPT called ‘Developer Tools’ which is one of the four modules that you can license in GPPT. If you haven’t purchased, I hope you’re going to change your mind after reading thru this blog post.

Basically the Developer Tools in GPPT allows for powerful scripting of various languages and setting triggers to act either on-demand or automatically, depending on your needs. The supported languages range from¬†Dexterity, Transact-SQL, Visual C# and Visual Basic.Net. The GPPT Dev tools allow even for writing custom functions for GP Report Writer fans, but that’s a topic for another time.

You got me hooked, where do I start ?

First, as with most GPPT Dev tools project, well you’re best to start by creating a GPPT Project ūüôā .. In GPPT, the project will hold together all the various components, the parameters, triggers, code & security for user / company assignment. This also simplifies the export / import of the project component to another GP instance, lets says when you’re the happy owner of a dedicated TEST environment and want to put your fully tested code into the PRODuction environment.

This can be done under : GP Power Tools Main page > Cards > Project Setup

ScreenCap 2018-08-06_190846

Tick the box “Current Project”, so that during the time you’re working on this, you’ll not have to lookup the project ID every time you open this form. For now the list in the lower section is empty, but it fill as we move forward. Once your project is completed, you’ll have a nice overview of all the elements and a double-click on one element will just open the corresponding form.

If you intend to export this project to an identical environment where your users & companies are the same, tick the other box “Transfer User and Company details”. During the export, GPPT will also include this information.

Lets continue with the easy stuff.. Next we’re going to define a trigger which will help firing the code that we need. Select from the GPPT Main page > Cards > Trigger Setup and enter the information like below.

ScreenCap 2018-08-06_190839

Pay attention to the details and select the PA module in GP for the trigger to be set on the PA Adjustment Timesheet entry form. At the bottom you can also define a GP Menu entry that the end-user will be able to see in the ‘Additional’ menu of the form.

ScreenCap 2018-08-06_190848

Next, we’re going to define the parameters that will be required for our TS report reversal. Remember that our goal is to make that posted TS visible and modifiable again thru PDK or BP. In order to do so, you’ll have to define a parameter field in GPPT to allow for the entry of the TS document number (something that looks like this ABCDE2008-TS-052618-1, the part being the Employee ID in GP).¬† Open the GP Power Tools Main page > Cards > Parameter Lists

ScreenCap 2018-08-06_190828

The TS report document number is field of 31 characters in the tables, but unless you use very long Employee ID’s, a field of type ‘String’ with max 30 chars should do the job.

You can click on the Preview button in the menu bar and get a glimpse of what the input prompt is going to look like :

ScreenCap 2018-08-06_190844

As this goal of this blog post is to show a simple step-by-step approach to start using the Dev Tools of GPPT, we’re not going to put a lot of validation into the input form, but it could be done for sure.¬†When closing the preview form, GPPT will prompt you to show that the created parameter actually works.

ScreenCap 2018-08-06_190802

Alright, I’ve the basics.. show me the meat!

Not so fast!.. Remember that we talked about SQL code ?  Before I had to implement this customization, I was using a T-SQL script to change the status of the TS report. This was looking similar to this :

UPDATE PDK10000
SET PDK_DOCUMENT_STATUS = 5
WHERE PDK_DOCUMENT_STATUS = 9 
	AND PDK_TS_NO LIKE 'ABCDE2008-TS-052618-1' 
	AND PAYR=2018

--> 2=saved, 3=returned, 4=ReSubmitted, 5=submitted, 
--> 7=Approved, 8=Processed, 9=Posted, 11=Deleted

The important part here is the PDK_TS_NO field where you match the current TS report you want to return. The payroll year field (PAYR) is optional, as it is very unlikely that you’re going to have 2 years with the exact same period beginning (the lat part of the TS doc number), but if you want to be safe, include it.

The TS status at the bottom are just comments to show you their real meaning within PDK and BP.

In Part 2 of this blog post, I’m going to show you how you can put that T-SQL code into work with the help of GPPT Developer Tools.

Stay tuned and enjoy the nice summer!
@GP_Beat

 

Categories: Dynamics GP | Leave a comment

Using the PowerShell MVP module

To all my Dynamics GP #MVP buddies out there.. this time of the year where you have to submit your activities to Microsoft, since they are now all due for March 31st as deadline (don’t wait the last minute!). This set of PowerShell scripts is going to help if you have a long list to enter.
Enjoy and looking forward to see most of you in Seattle from March 4th to 8th for the annual MVP Summit 2018.
Until next time..

>_

The renewal season is opened and it’s time to use the PowerShell module designed to interact with the Microsoft MVP websiteFrançois-Xavier Cat and I released last year when the Microsoft MVP Rest API was made available.

François-Xavier already published some very useful posts and demoed how to use the MVP module for:

This year, I only used the module to insert and review my contributions.
To start using the module, you basically need to install the module, import it and set your subscription key with the function. See first the following post from François-Xavier to achieve these steps.

Now I’d like to show you some code snippets I used along this contributions submission process.

First, I needed to find what contribution I submitted last year and filter out what PGI I attended.
To quickly do that I used:

I first get…

View original post 92 more words

Categories: Dynamics GP | Leave a comment

Remember User & Password not there in GP 2018?

Recently I had upgraded my TEST bed system from GP 2016R2 to 2018. After the upgrade I noticed that my auto-login feature was gone and though it bugged me, I had more important things to check before that ‘small’ annoyance..

A couple of weeks into the new release, and after I installed the Rockton Toolbox for GP that was just release last week which still requires you to claim the new registration keys from the support, I noticed myself that I need to dig into the issue with login & password not retained anymore.

2018-01-24_095646

As you can see from the login screen above, the check box and text for the feature are greyed out and can’t be selected.¬† I knew that from my past GP releases, that this was a setup that has to be enabled in the GP Setup under¬† “Administration >> Setup >> System >> System Preferences”

In GP 2013R2 this looked like this :

2018-01-24_095319

And this is in GP 2018 :

2018-01-24_094446

Looks pretty similar right ? aside of an added section at the bottom, the feature remains the same.  The thing is that checking or unchecking this box does actually two things :

1. update a value in the DYNAMICS..SY01402 table to retain the status
2. Alter the flag in the DEX.ini file to make the option available at login time (remember, before you logged in, no DB access ! )

You can query the actual value to confirm this by running this SQL code :

SELECT *FROM SY01402 WHERE USERID = 'GLOBALUSER' AND syDefaultType = 71

Now this is were  the problem happens.. In my GP2013R2, this works as expected.. meaning that if the option has never been used before, checking the box in GP sets the value AND adds the following entry in the DEX.ini file :

RememberUser=TRUE

It doesn’t matter where it sits, as long as it’s in the DEX.ini and GP can read it out during the launch process, and it will make the ‘Remember User & Password’ available to check in the login screen. Keep in mind that GP can’t read any SQL table data before you’re actually login into the system, thus relying on the configuration file.

By default, this entry doesn’t exist in the DEX.in and is only added once you check the box in GP. During an upgrade process, a new DEX.ini is created within the new folder location of GP under the \Data sub-folder and many settings are set to default, but this one is not there.

@SteveEndow from Precipio Services had blogged about this in details here in 2015, but what he didn’t mentioned (or knew), is that there is another DEX.ini entry that could have a significant impact on the ‘Remember User’ feature:

EnablePerUserIni=TRUE

This feature was introduced with release of GP 2013 for the newly added web client to store individual user settings without having a common DEX.ini file.. Now the thing is that if this is set to TRUE, GP is going to store most of the regular DEX.ini entries in a SQL table, not in a flat file anymore. Each user ID in GP would get anywhere between 10 to 50 entries with the key field name & it’s corresponding value¬† See an example below from table DYNAMICS..SY01405 :

USERID  IniKeyName           IniKeyValue
dummy   Dictionary Version   18.00.0400
dummy   RememberUser         TRUE

@MarianoGomez too has blogged about the new values in the DEX.ini in 2014, but he wasn’t providing any technical details about how these two different entries would work together in the case of a shared and individual DEX.ini file..

What I got confirmed by Microsoft’s Daryl Anderson is that if the EnablePerUserIni=TRUE entry is present in the DEX.ini file, then GP will only look at the table entry for the RememberUser settings.. which brings us back to the old question: which came first ? the chicken or the egg ?

As I haven’t tested my GP 2018¬† setup on a workstation yet, but only on the server side, and if the web client is selected as an option , the entry EnablePerUserIni=TRUE is added to the configuration file and thus, it never gets a chance to receive the other parameter (RememberUser),¬† no matter how hard you try from the GP client side.

The only solution for now when this happens, is to edit the DEX.ini file, hunt down the entry EnablePerUserIni=TRUE, and switch the value to FALSE (or remove the entry completely if you don’t intend to use the web client). While you’re there, you can add at the same time RememberUser=TRUE, which will save you another login into GP before this works.

Finally a happy camper again and now I can check the box again on my login screen :-).
Enjoy your GP until next time,
@GP_Beat

2018-01-26_115758

 

Categories: Dynamics GP, System Administration | Tags: , , | Leave a comment

Online Payment Services gone !

Not that nobody hasn’t warned about the discontinuation of the Dynamics GP Online Payment Services (see official communication by Microsoft : https://community.dynamics.com/gp/b/dynamicsgp/archive/2017/11/09/discontinuation-of-dynamics-online-payment-services ), but it is still always a little disturbing when a service you counted on gets removed after several years.

I probably got the message too, but wasn’t really spending any thought about it, until I upgraded my TEST bed GP 2016R2 to 2018 in mid-December.. As I was comparing from the DYNAMICS.SET launch file, which modules I was missing (21 instead of 28), I tried to figure out where my modules where gone..

Using the nifty “Compare” plugin to my trusty Notepad++ editor, I pulled in the 2 launch file from GP 2016 & 2018..¬† The first ones were obviously missing ISV products, which I quickly looked up to find if there was any 2018 version yet available.. Out of a few add-ons we use in GP, only GP PowerTools (GPPT) & the eOne Solutions products are currently available with builds for 2018 to download.

And yes, I confirmed that GP Online Payment Services is no longer installed (and not available anymore) for 2018.¬† Which means that if you relied on this for accepting credit card payments in GP, you’ll have to turn to something else (ISV products) to replace that.

The reality of changes in life, not necessarily for the best (like abandoning the Business Portal / SharePoint tandem)..

Until next post, have fun with your GP,
@GP_Geek

Categories: Dynamics GP | Leave a comment

Canadian Payroll Year-End got wrong numbers for Quebec…

Every year-end it’s the same race for Dynamics GP customers that are using either the US¬† or the Canadian Payroll module in their system, they have to apply tax updates to account for the new deduction rates and personal credits. The US customers are lucky, because their tax updates are not tied to a full service pack, as Microsoft managed to put them in separate tax tables, thus a simple XML file import is doing the job.

Not so much luck for the Canadian Payroll users, as the tax rates & calculations are somehow not completely stored in tables, but also embedded into the core code of the module.. which makes the update only possible thru a full service pack to update the core code of GP (usually there are only 3-4 modules that got a new build number).

Fellow MVP Jen Kuntz has posted a brief note about this on her blog, with the links for the downloads.

The less funny part is that when the US tax updates get released somewhere around mid to end of November, the Canadian Government manages to release those new numbers only very late, sometimes in early December, but due to the singularity of the Province of Quebec, which has it’s own tax rules, those new rates get often delayed until mid-December.

Which brings me to the point that the poor GP development team in Fargo has to rush the code changes & testing often in as little as 1 or 2 weeks to get it out in time before the Christmas closing (or Year-end). Every year we make some bets about when it’s going to be released, not so happy to see preliminary tentative dates on Customer Source giving Friday December 22 as release date. This often leaves us with no spare time to TEST out the service pack and then apply it to our Production environment before closing shop for the holidays.

However, yesterday (Dec. 20st), the Canadian Payroll service pack 2017 for GP2013 was delivered as an early X-mas gift and we could install it & run some payroll tests.. Only to realize this morning when the Paymaster looked up the personal tax deduction for the employees to check if the rates were aligned with the official documents from CA & QC, and he told me that the values didn’t look good :-(.

2017-12-21_120836

According to the Year-End Tax update document itself (and confirmed with official documents), the tax credit for personal deduction should be 15’012$CAD in the Province of Quebec, whereas here we clearly see that the value is identical with the Canadian Basic Personal Amount. Somehow this value is not consistent in the Year-End documentation itself and should be corrected by Microsoft in the coming days. The code wasn’t updating the proper values..

If you have already run the payroll reset function, then all your employees would be currently set with the wrong value..

No panic ! There is a way to fix it with a quick SQL script that you can run against your GP company database where the Canadian payroll is used. Use the following code bellow to query your data and check the current amounts (Federal & Quebec)

-- Federal Amount
SELECT CPY10105.PBasicPersonalAmount
 ,CPY10105.PSpousalAmount
 ,CPY10105.P_Child_Amount
 ,CPY10105.PEmployeeID
 ,CPY10100.PLastName
 ,CPY10100.PFirstName
 ,CPY10100.PInactive
FROM CPY10105
INNER JOIN CPY10100 ON CPY10105.PEmployeeID = CPY10100.PEmployeeID
WHERE (CPY10100.PInactive = 0)

-- Provincial Amount
SELECT PQuebecMR19Base
 ,PQuebecMR19Line12
 ,PEmployeeID
 ,PFirstName
 ,PLastName
FROM CPY10100
WHERE Pinactive = 0

I’m not including inactive employees, as you may want to keep the rates of former employees, though you may want to review this on a case by case basis, as there might be employee on long-term leave (those get usually disabled to not get into the payroll run).

If the values that are returned are not correct, then you can fix them with the next script below:

UPDATE CPY10100 /*P_CPY_Master*/
SET PQuebecMR19Base = 15012.00000
 ,PQuebecMR19Line12 = 15012.00000
WHERE Pinactive = 0
 AND PQuebecMR19Base = 11730 --> last year's value
 AND PProvince = 'QC' --> do not update other provinces if you have

Also check out in the Employee card the spouse & child values at the Federal level, as some employees may want to claim the base amounts too according to the Federal form TD1(2018).

The Provincial amounts are defined in the TP-1015.3 which you can download the PDF from the link.

Happy Holidays and Happy New Year all.
Until next post..

Categories: Breaking News, Canadian Payroll, Dynamics GP, News, Year-End | Leave a comment

Why are my Project Timesheet Periods behind?

The initial situation

Are you using Project Accounting Timesheet (TS) entries in GP ? maybe you’re still on version 2013 & lower, which means you also use possibly Business Portal & PDK to manage and process the TS reports submitted by your employees.

If you’re wondering about the fact that after several years of TS report entry, your weekly period # has started to shift back.. and like in our company where GP has been implemented back in 2005, the shift currently is 2 full weeks .. Why you may ask ? just because every year isn’t equal in Calendar and doesn’t start exactly on Jan. 1st for your 1st day of reporting, which might be Saturday, Sunday or even Monday for some companies (not even counting for leap years).

In PDK this translates into showing the period #52 ending on Dec.  15th, this year, as shown in this screen capture:

2017-11-30_101739

After 12 years of weekly reporting

How do I fix it ?

Now someone would say that is an easy fix, as I’ve seen suggestions on a few forum posts or discussion, but it is not.. 1st place where they tell you to go is under “Project > Setup > Timesheet” and change the number of reporting periods per Year.. Alas, it doesn’t work like this, since PDK & GP is coded to ignore that value, when the option for field ‘Reporting Periods’ is anything else than Miscellaneous.

2017-11-29_123708

Original setup in GP

I’ve been poking around this setting long enough to confirm that this is possibly a bug, which wasn’t intentional I guess by Microsoft, but the truth is that the only purpose of that field is to provide a divider value for the Miscellaneous option.

2017-11-30_130254

(Note: the only time this Period # is actually validated by GP is during the posting of the TS batches)

 

In fact, when you setup and chose the option Miscellaneous the first time (before ever posting any TS report), GP will pop-up a message and claim that the value has to be greater than 0, but not bigger than 365 or 366 in case of leap years (actually saying it can’t be less than 0, but even zero is rejected).

2017-11-30_124758

How do I fix it ?

So, how to you gonna fix the shifting week issue ? The only field you have a control over, is the “First Date of Reporting Period 1”.. Every 5-6 years, when the calendar has shifted thru a whole week, your reporting period #1 might get out of sync and start in the previous year.. All you have to do is adjust the starting date to the new year, et voil√† !

2017-11-30_103841

With the First Date of reporting changed to 2016-12-31, Period #52 is correct in 2017.

2017-11-30_115452

It’s not until 2021 that you’ll have to worry again to change the start date ūüôā

 

WARNING !

Don’t do that while you have unposted TS reports in the system, since the period field in the table is set when a TS report is edited, but once submitted for approval, it doesn’t change anymore, and the last thing you want is to have a duplicate entry for the same period # in the current year (those are key fields for the PDK tables), ending with a xxxx-2 sequence report number.

Plan ahead of time your date change, and let all your reporting employees know that nobody should submit a new Timesheet into the new year, before you updated the First date of reporting for the next calendar year.

As always, it is warmly recommended to try out those settings first in a TEST company, and validate it with a couple of new TS reports to see if everything lines up correctly.¬† This shouldn’t affect the payroll periods setup, as those are managed in a different place, but if you post your TS reports directly to the US Payroll (which is an option in the PA TS Setup), you may want to check this out too.. As I don’t have a US Payroll setup in our companies, I wasn’t able to validate that aspect.

What if I have Timesheets started in the wrong period?

Two choices: either the TS report wasn’t processed in PDK and posted to GP, in which case you can simply return it to the user and make it edit to re-submit.. this is enough to update the Period field in the table after you updated the start date in the setup… or the TS was already processed and posted to GP, in which case you can run a SQL update to ‘switch’ those transactions from the new Period to an ‘extension’ of your 52 periods, this could be 53 or 54, depending how far you’re behind in the calendar.

TS Processed in PDK, but not posted in GP :
– update PDK10000 & PA10000 from the company DB

UPDATE PDK10000
 SET PAREPD = 54
WHERE PAREPD = 2 
and PAYR = 2017

UPDATE PA10000
 SET PAREPD = 54
WHERE PAREPD = 2 
and PAYR = 2017
and [USERID] = '*PDK*'
and BCHSOURC = 'PA_TS'

Note: GP will not post a TS batch if the period is greater then the current # in the PA TS Setup window and eventually throw an error. To bypass this, just go back to the setup window and change the number of periods for the year from 52 to 54 (or whatever your max value needs to be). Don’t forget to put it back after posting.

ScreenCap 2017-11-30_181108

TS Processed in PDK & already Posted in GP:
– update historical table PA30100 (there is not history for PDK as everything is kept in PDK10000)

UPDATE PDK10000
 SET PAREPD = 54
WHERE PAREPD = 2 
and PAYR = 2017

UPDATE PA30100
 SET PAREPD = 54
WHERE PAREPD = 2 
and PAYR = 2017
and [USERID] = '*PDK*'
and BCHSOURC = 'PA_TS'

But what if I want to change my Reporting Periods option ?

You can’t … at least not from the front-end GUI of GP.. Once it has been set and Timesheets have been posted into the system, this option is greyed out.

ScreenCap 2017-11-30_171117

During my research on the Internet, I came across a forum posting from Andy Sather at Microsoft, that provided some insight on the 2 values.. However, there was a typo in his answer, as he gave the value 9 for Miscellaneous, which is incorrect.. it should be 8 (I tested this in the Fabrikam company and looked up the value).

The only way to change it, is to grab to SQL Studio Management (or any tool to update a table value), and update the two fields driving the show. You’ll have to update two fields in table PA41801 from the company DB, which means repeat the process for each other company too.

-- Query content of PA Setup table
USE TWO
SELECT * FROM PA41801

-- Update PA Reporting Periods option
-- Refer to this community post for the details 
-- https://community.dynamics.com/gp/f/32/t/89410

UPDATE PA41801
SET PAreportingperiods = 3 -- bi-Weekly
 , PAnumofreportingperiods = 26 -- used when PAreportingPeriods = 8
Where PAreportingperiods = 2 -- Weekly
 and PAnumofreportingperiods = 52

I do not recommend the use of Miscellaneous in the Reporting Periods option, as PDK & GP don’t’ handle quite well fractions of weeks..For example, I tried to set the calendar year to use 53 or 55 weeks, but rather than dividing the 365 days in equal values, which is quite difficult when ending up with a decimal number ( 365 / 53 = 6.88 ), PDK & GP don’t round-up as you would expect, but rather just cut the numbers behind the decimal point..

Effectively, your period #52 is starting on Nov. 8th, finishing on Nov 14th, and your period #53 starts on Nov. 15h, but extends all the way until Dec. 31st.. not sure you want such  a long reporting period. Unless you have a very good reason to use a custom period setup, I suggest to stick with the regular settings from the list.

Hope this clarifies some of the mysteries around PA Timesheet periods setup and help you to fix some of the issues. Don’t forget to put a reminder every year-end to check your periods setup.

Until next post,
@GP_Beat

PS: I just noticed from a forum post on GPUG, that Equipment Log & Miscellaneous Log Setup suffer from the same issue, though in our company it was initially set to ‘Daily’ and uses therefore 365 periods/yr… which ended up to shift too after 13 years and we’re now already in period #21 on 2017-11-30, as it was never corrected in the past.

Update: There has been some interesting discussion around this topic on GPUG. Here’s is the link to the thread

 

Categories: Dynamics GP, PDK | Tags: , , , | Leave a comment

Get ready for  Year-end updates & sharpen the pencil! 

Howdy GPUGer’s

It’s this time of the year when all the leaves are on the ground (or almost) and the first ¬†snowflakes signals that Christmas is soon at the door too.¬† That’s when you know it’s time again to get ready for the year end processing, ¬†and most important, ¬†the tax tables updates.

The GP community south of the Canadian border  have it easy,  as usually they get their updates by mid- or end-November.

Our friend Terry Heley from Microsoft announced today the release for the US of the version for GP 2016.. 2013 & 2015 are scheduled to follow next week..

2017 US Year-End update released

Microsoft has also prepared a nice PowerPoint presentation for this YE 2017 prep, with tons of resources and links, even how to open a support case if you’re stuck and can’t find help over the holidays ūüôā

MS_YEU_2017

If you still struggle with your Year-End processes and don’t know where to start, then look no further and head over to the GPUG Collaborate where there is plenty of webinar recordings about the topic.

Happy Year-End preparation.
Until next time,
@GP_Beat

Categories: Dynamics GP, Year-End | Leave a comment

GP Power Tools build 22 hotfix available for download (May 02, 2017)

Good day GPUGgers !

As many of you may know (or not), I’m an avid user of GP Power Tools¬†(former Support Debugging Tool, aka SDT)¬†since the tools was launched in 2006, and recently worked very closely with David to bring fixes & enhancements to the product, as I’ve been running Windows 10 since a few months in production on my laptop, where I’ve GP 2013 R2 & GP 2016 R2 (in Test) clients installed.

Windows 10 having changed quite a few things in regards of screen management and multi-monitor setup, plus a few other small hindrance (like the invisible window frames), it has been quite an interesting journey in the past months.

Here are the changes that were brought into this update for the build 22:

Version 16.00 build 22 (Released 02-May-2017) Installer: 16.00.0022.4

  • ¬†¬†¬†¬†¬†¬†¬†¬† Updated Trigger Setup window to stop asking to reset script when changing Menu Trigger text.
  • ¬†¬†¬†¬†¬†¬†¬†¬† Updated Company Color Themes to use User Display Preference settings for Link Fields and Required Fields.
  • ¬†¬†¬†¬†¬†¬†¬†¬† Updated Company Color Themes to override User Display Preference color settings when needed for inverted themes.
  • ¬†¬†¬†¬†¬†¬†¬†¬† Added fix for Dynamics GP Bug where Apply button on User Display Preferences only works one time.
  • ¬†¬†¬†¬†¬†¬†¬†¬† Added additional checks to ensure correct window handle is used when repositioning and retitling windows.
  • ¬†¬†¬†¬†¬†¬†¬†¬† Update default values for Email, Logging and Administrator settings to enable recommended features for new installs.
  • ¬†¬†¬†¬†¬†¬†¬†¬† Adjusted window repositioning code when capturing window handle for 3rd party products to use foreground window method.

One of the important updates for me is about the handling of the windows, as I’m running a setup with 3 displays (Laptop LCD + 2 external 22″ monitors), and I think that nowadays this is a pretty common setup. Since the Windows position memory feature was introduced in build 22 by David, I had a few issues where my other application windows (Outlook, Office, Explorer) would randomly be snapped to the the lower right or left corner of the display it was showing, even it was not related to GP, and this specially annoying when you had a larger posting batch going on your GP client, as you couldn’t work with other windows without having them snapping away under your mouse :-).

Based on the feedback I provided to David, he was able to correct his code and now checks for the proper identification of the window’s title, and if that¬†title does not match the expected value, the code will stop the Automatic Window Position check (this can also happen with 3rd-party ISV products, though part of GP, they are not recognized as such).

Thanks¬†to a¬†feature¬†added a while ago, but not fully used yet,¬† it provides¬†¬†an additional DEX.ini settings that allows you to trace in detail¬†what’s going on with your GP forms while your client is active. That helped us to identify the issue caused by non-GP windows.

MBS_Debug_LogWinData=TRUE

Be careful to not leave that option on longer than it would be necessary for you to debug a problem with some forms in GP, because it will fill up the GPPT log quite quickly.

Each GP form/window entry would look something like this (extract from the GPPT log):

2017-05-05 09:44:07 : ** Start of Log **
2017-05-05 09:44:07 : Version: 12.00.0022, Last Modified: 02-May-2017.
2017-05-05 09:44:07 : Automatic Trigger Mode Trigger CPY_DIR_DEPOSIT Unregistered
2017-05-05 09:44:07 : Automatic Trigger Mode Trigger VENDOR_EFT Unregistered
2017-05-05 09:44:07 : ** End of Log  **

2017-05-11 08:02:24 : Automatic Window Position    : window UPR_Employee_MNT of form UPR_Employee_MNT.
2017-05-11 08:02:24 : Dynamics GP Application Pos  : (114,57), Content Offset: (202,167).
2017-05-11 08:02:24 : Dynamics GP Zero Position    : (316,224).
2017-05-11 08:02:24 : Primary Desktop Actual Size  : (1680,1050), not used if per monitor setting available.
2017-05-11 08:02:24 : Primary Desktop Work   Size  : (1680,1010), not used if per monitor setting available.
2017-05-11 08:02:24 : Monitor Desktop Actual Pos   : (0,0) - (1680,1050), Size: (1680,1050), Device: \\.\DISPLAY1.
2017-05-11 08:02:24 : Monitor Desktop Work   Pos   : (0,0) - (1680,1010), Size: (1680,1010).
2017-05-11 08:02:25 : Monitor Desktop Physical Size: (1680,1050), DPI Scaling: 100%.
2017-05-11 08:02:25 : Dexterity reports Window Pos : (9,33) - (656,489), Size: (647,456).
2017-05-11 08:02:25 : Windows reports Window Pos   : (332,257) - (981,771), Size: (649,514).
2017-05-11 08:02:25 : Windows Window Scaled Pos    : (332,257) - (981,771), Size: (649,514), use instead of Dexterity settings.
2017-05-11 08:02:25 : Windows Window Border Offset : (7,3).
2017-05-11 08:02:25 : Adjusted Dynamics GP Zero Pos: (323,224), adjusted horizontal position for border offset.
2017-05-11 08:02:25 : Updated Dexterity window Pos : (9,33) - (658,547), Size: (649,514).
2017-05-11 08:02:25 : Dexterity Window + Offset Pos: (332,257) - (981,771), Size: (649,514).

Quite interesting and helpful when you’re an administrator and trying to figure out what’s going on with your GP client, especially if you run that on a shared environment like Citrix or Terminal Server.

If you’re already a GPPT subscriber, go and download the latest build 22 from the Mekorma site and install it. If you’re not a GPPT user yet, I encourage you strongly to grab a copy and ask for the free 30 Days trial key. You’re going to love it.

Until next time, have a great Day!

@GP_Beat

Categories: Citrix - TS, Dynamics GP, GPPT - GP Power Tools, System Administration | Tags: , , | Leave a comment

A remove range operation on table ‘SY_Current_Activity’ failed accessing SQL data

error1

You’ve probably encounter this error message on rare occasions in Dynamics GP, but be prepared for seing an increase with GP 2015 & up.

Recently I was working on a case with a customer that upgraded last fall (2016) from GP 2010 to GP 2015, and ever since the upgrade, the users who access GP over Citrix exclusively (no local GP clients) had randomly issues with pop-ups denying them access to some of the 50 GP companies.. The corporation has office mainly on the east-coast and also over in Asia, thus making the maintenance more difficult with the 13-hours timeshift, as the system is busy most of the 24h in a day.

error2

However, as they also use tons of 3rd-party ISV products and some customization, it was hard to pinpoint the culprit / faulty module. At some point we thought that it was the “inactivity” timer of the Rockton Toolbox that was causing this, but it made no sense to me, especially as the companies were not in the user’s access list.. Disabling the feature in RSTB was only a temporary solution, as you can guess that with so many companies, comes also a lot of users and more than often they don’t exit GP properly, leaving their session run on the Citrix server.. doubled down with the fact that there was no timeout set for inactive sessions, it wasn’t helping either. On top of that, the company is using FastPath for SSO and as I discovered later, FP does also have a feature for ‘inactivity’ timeoout.. As both were enabled (FP & RSTB), it becomes really though to diagnose the source of the problem.
We tried to enable the DexSQL tracing with the help of GP Power Tools, but as it occured only randomly, it was not a solution, since all users accessed GP thru 2 Citrix servers, the logs were growing fast (because enabled to all users) and it was slowing down the system.

By early 2017, we had a confirmation from Rockton that it might have been a bug related to the Toolbox ‘inactivity’ timeout feature, and thus applied the latest build with big hopes. Alas, only a few days later, the problem was back again with some users out of Asia reporting the pop-ups.. Strangely it occured mostly at Asia’s business hours (during night on east-coast) or very early in the morning on Eastern Time. My suspiscion started towards some maintenance job that would kill user sessions without further validation, but couldn’t find any tangible hints. I tried also by all kinds of back-end manipulation of the ACTIVITY table to reproduce the behavior and get the error message, as I suspeced the shared DEX.ini¬† to be causing this (DEX.ini stores the last company ID when a user exits GP).

At the end, as I had a conversion with my good friend & fellow MVP¬†David Musgrave on how I could potentially enable single user tracing, without implementing indivual user DEX.ini config files, he suggested to have a look at the ACTIVITY table from the DYNAMICS DB, and check for potential triggers that could fire up at a bad time. Low & behold, it turned out that starting with GP 2015, Microsoft implemented new feature in GP that required a trigger on the ACTIVITY table to proceed with some clean-up work on company DB tables, which whne happening under a context when users were logged into another company at the same time to which they didn’t had access, would cause the infamous pop-up to show on screen.. confusing the users about the company they didn’t access to.

Read the details in David’s blog post about the underlying details and a (temporary) resolution. For now, the trigger was simply disabled on the ACTIVITY table, to confirm the source of the problem. Further action is required to resolve the issue on a long term basis with Microsoft and/or 3rd-party providers.

https://winthropdc.wordpress.com/2017/03/08/delete-or-remove-range-errors-on-sy_current_activity-table-after-gp-2015-r2/

 

Categories: Citrix - TS, Dynamics GP, GPPT - GP Power Tools | Leave a comment

Blog at WordPress.com.

Dynamics GP Tips and Traps For Users and Developers

Talking about Great Plains, Dexterity, SQL, and some C#

smay772

A fine WordPress.com site

Tom Roush's Blog

Musings on Life, Lessons, and Laughter...

Belinda Allen, Microsoft MVP Business Solutions

Things I've learned from years of working with Microsoft Dynamics GP.

David Musgrave's Winthrop Development Consultants Blog

This blog will keep you up to date with Winthrop Development Consultants and David’s experiences, tips and tricks as a developer and consultant in the Microsoft Dynamics GP community.

Gilbert Quevauvilliers - BI blog

My learnings and findings in the world of Business Intelligence

Build Numbers

Build numbers and version history for various products

Rambles of a non-IT person

Business development work to make lives easier

Dynamics GP - Learn & Discuss

Victory is directly proportional to Hardwork

Darryl Bajaro

- Codes, Processes and Tutorials

Nizamudeen

Keep Smile Always

The Dynamics GP Geek blog

My contribution to the never ending Dynamics GP journey

Q Factor's Blog

Microsoft Dynamics GP in a nutshell

Blog on IT

for when IT Fails...

Peerlyst.com

Product-centric social site for tech pros. Get unbiased product ratings based on your peers firsthand experience. Connect with new peers to get the real story about products and services.

www.gillianhorgan.com

Gillian Horgan: An extraordinary eye on the world

Victoria Yudin

Ramblings and musings of a Dynamics GP MVP

alldynamicsgp

All Dynamics GP Information