Behind the Dynamics GP Remember User and Password option

By Steve Endow

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Just another day in Dynamics GP paradise...

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

You can also find him on Google+ and Twitter

http://www.precipioservices.com

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

By Steve Endow


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


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

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

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

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

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

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

Second, use a SQL statement to update the class ID for the relevant inventory items.  Something like this:

UPDATE IV00101 SET ITMCLSCD = 'NEWCLASSID' WHERE ITEMNMBR IN ('WIRE100', 'WATCH', 'TOP100G', 'TRANS100', 'TRANSF100', 'TEST')

Your items will now be assigned to the new classID.

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

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

Click on Yes to roll down the changes.

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

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

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

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

You can also find him on Google+ and Twitter

http://www.precipioservices.com

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

My blog has moved!  Please visit the new blog at:  https://blog.steveendow.com/

I will no longer be posting to Dynamics GP Land, and all new posts will be at https://blog.steveendow.com

Thanks!



By Steve Endow


UPDATE: See comments at the bottom of this post for a resolution: Temporarily add the eConnect service account to the Local Administrator group.


UPDATE 2: I just worked with a customer where adding the eConnect domain user to the Local Administrator group still didn't resolve the issue.  Lacking any other ideas, the system administrator at the customer rebooted the server.  After the reboot, the eConnect error was properly written to the event log.  So it seems that in some environments, a server reboot may be required.


UPDATE 3:  I just built a new GP 2018 VM and encountered this eConnect permission error. After adding the eConnect windows user to the local Administrators group, I still got the logs error.  I then restarted the eConnect service, and that seems to have reloaded eConnect with the Administrator rights, and the error went away.  So, it could be that a server reboot may not be required, but a restart of the eConnect service is needed to refresh the permissions.


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

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

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

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

After running the GP 2015 integration a few more times, I eventually received an error I did recognize.

Error Number = 936  Stored Procedure= taPMDistribution  Error Description = Vendor number does not existNode Identifier Parameters: taPMDistribution

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

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

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

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

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

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

You can also find him on Google+ and Twitter

http://www.precipioservices.com

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

By Steve Endow

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

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

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

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

Good question.  I didn't know either.

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

So why is this happening?

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

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

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

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

My advice:  Specify the batch number.

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

Go figure!

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

You can also find him on Google+ and Twitter

http://www.precipioservices.com

Dynamics GP Integration Manager script to generate a fixed length number

By Steve Endow

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

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

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

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

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

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

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

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

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

You can also find him on Google+ and Twitter

http://www.precipioservices.com

Bug in Dynamics GP 2013 eConnect Employee Deduction import: taCreateEmployeeDeduction

By Steve Endow

Several years ago I developed a Payroll integration for a customer that transitioned from Dynamics GP HR to Kronos HR.  The client wanted to use Kronos for HR, but keep GP for Payroll.  This resulted in a rather complex integration that required Kronos to send over any type of employee, benefit, and deduction change so that GP payroll could have accurate information.

The integration has been in place for years and running fine.  Recently, the client had to make a change to some of their deductions to modify the Tax Shelter settings.

They had to check the box to shelter the deductions from FICA Medicare.  This FICA Medicare tax shelter option was added in GP 2013, and if you query the UPR40900 and UPR00500 tables, you'll see the new SHFRFICAMED field tacked on near the end of the tables.

After my customer modified their deductions to check the FICA Medicare sheltered check box, they noticed that imported employee deductions did not have the box checked.

If they manually entered the employee deduction in GP, the FICA Medical sheltered option would be checked, as expected.

So this seemed to be a bug in eConnect, and I had to start digging to find it.  I have found a few other eConnect bugs while developing this very complex payroll integration, so it didn't surprise me at all that there might be one more.

The eConnect stored procedure that handles the insert and update of employee deductions is taCreateEmployeeDeduction, so that was the likely culprit.

As far as eConnect procedures go, taCreateEmployeeDeduction is quite simple.  It has validation for the input parameters, and then it performs an insert or update on the UPR00500 table.

I reviewed the stored procedure to see how it handled the SHFRFICAMED field.  There were so few references to the field that I didn't see anything that looked incorrect.  All of the SQL for the SHFRFICAMED seemed to match the other tax sheltered options.  I couldn't see any invalid logic or problems with the script that would lead to the FICA Medicare option to not be set properly in UPR00500.

I tried to debug the stored procedure directly, but it would be a hassle to get it setup for debugging, so I took the low tech approach: debug logging.

I created a logging table called cstbDeductionTrace to record the values of several of the stored procedure parameters.  I then added this line at several points in the stored procedure.  This let me capture the procedure parameter values at various points in the proc to see when the SHFRFICAMED parameter got its incorrect value.

INSERT INTO cstbDeductionTrace SELECT '1 Before Pre', @I_vEMPLOYID, @I_vDEDUCTON, @I_vSFRFEDTX, @I_vSHFRFICA, @I_vSHFRSTTX, @I_vSFRLCLTX, @I_vSHFRFICAMED;

I then ran my import and saw this in the trace table.  And I was puzzled.

Notice that the first four tax sheltered parameter values start out as NULL.  Only after the procedure pulls default values do they get set to 1, which makes sense.

But the SHFRFICAMED parameter starts out with a value of 0 rather than NULL.  And because the stored procedure is designed to only load default values for NULL parameters, the value never gets set properly--it stays at 0.

Huh.

So...why was the parameter being passed in with value of zero rather than NULL?

I wondered whether the eConnect serialization might be sending an invalid value.  I checked the XML I was sending to eConnect, and it didn't have a SHFRFICAMED node.

I then checked the eConnect documentation...and guess what.  The documentation doesn't even list the SHFRFICAMED field as a parameter.  And the eConnect object doesn't have a FICA Medicare property available in Visual Studio.  It looks like Microsoft forgot to add it to eConnect 2013 entirely.  So serialization didn't seem to be the culprit.

I then stared at the stored procedure SQL again.  And then it dawned on me.

If serialization wasn't adding the values for the tax sheltered fields in the XML, then it also wasn't passing values to the stored procedure.  Which means that the parameter values in the stored procedure were coming from...the parameter default values!

This seems a little obvious in hindsight, but when you are tracing and reverse engineering someone else's code, many things are far from obvious.

So I jumped to the top of the stored procedure to check the parameter default values...and sure enough, there was the culprit.

SHFRFICAMED was being defaulted to 0.  But what were the other tax sheltered parameters being defaulted to?

As I guessed, and as my trace data indicated, the other parameters were being defaulted to NULL.  Someone had typed the wrong default value for the new FICA Medicare parameter.  Bad Laama!

So why does a NULL vs. 0 value matter?  Well, the stored procedure is designed to only pull default values from the master deduction if the parameter is NULL.

@I_vSHFRFICAMED = CASE WHEN @I_vSHFRFICAMED IS NULL

                        THEN SHFRFICAMED

                        ELSE @I_vSHFRFICAMED

Since a 0 was being defaulted, the proc code thought that the value was being passed in, and didn't change it or override it.

Changing the parameter default value from 0 to NULL fixed the issue and resolved the issues with my deduction import.

I looked at the taCreateEmployeeDeduction on GP 2015 RTM and it has the same issue--an incorrect default value for the @I_vSHFRFICAMED parameter.  So the bug still exists there.

I checked the help file for GP 2015 eConnect, and guess what--the title page still says "eConnect 2013 R2", and also doesn't list the SHFRFICAMED field, so it seems that eConnect for GP 2015 has not added the FICA Medicare tax sheltered field.  Fun times ahead.

I'll be submitting this to Microsoft to see if they are aware of the issue, but I don't expect a fix for GP 2013, and I'm not holding my breath for a fix for GP 2015.  Fortunately it's fairly easy to correct the stored procedure, but it's a hassle to have to remember to update the proc after service packs and upgrades, as those can replace eConnect procedures.

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

You can also find him on Google+ and Twitter

http://www.precipioservices.com

Clearing Out Old Uninterfaced Fixed Assets GL Activity

It's not unusual for Fixed Assets to get implemented after the initial project for Dynamics GP.  But lately, I have had a few different clients who went live with Fixed Assets but never actually went live.  What do I mean? They started using the module, but for whatever reason (e.g., lack of confidence in the GL accounts used, issues with how items were calculating, not understanding the process) they did not use the GL interface to pass the journal entries from Fixed Assets to the General Ledger.






Of course, if there are issues with the accounts being used, or the calculations, or understanding the process, those need to be addressed. But once they are, what do you do with all of that uninterfaced activity (considering it might be using incorrect accounts)?  Now, if you are lucky-- their won't be much of it and you can just run the GL interface (Routines-Fixed Assets-GL Posting) and delete the resulting batch.  But as luck would have it, I have had a couple instances where there was SO MUCH ACTIVITY that it was taking hours and hours and hours to for the interface to run.  So at the worst, it was locking up the machine and at the best, it was annoying to have to deal with.


So what to do?  Well, let's just update those records in the database so that they think they were previously interfaced.  This approach is surprisingly easy because there is just one table involved, FA00902.






If you do a select on that table, you will see that it contains all of the GL activity records and it has columns for GL information.

• INTERFACEGL stores a 1 if the record is to be interfaced to the GL
• GLINTTRXDATE, GLINTDATESTAMP both store 1/1/1900 until the record has been interfaced to the GL, and then these dates are updated
• GLINTBTCHNUM stores the batch name (FATRX000...) created in the GL

So, with these fields in mind, the following script would update and set the records as interfaced.  Now, keep in mind, you might want further restrictions in your WHERE clause on the FAYEAR or FAPERIOD which are also in the table.




--Confirm records to be updated
SELECT * FROM FA00902 WHERE INTERFACEGL=1 AND GLINTBTCHNUM=' '




--Mark all records as interfaced to GL
UPDATE FA00902 SET GLINTRXDATE='whatever date you want', GLINTDATESTAMP='whatever date you want', GLINTBTCHNUM='CLEAROUT' WHERE INTERFACEGL=1 and GLINTBTCHNUM=' '


As always, please make sure that you have a backup and use the script above to first validate what will be updated before actually doing the update. Happy updating!


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