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.

One sign of a really bad API

By Steve Endow

The last several months, I've been working with a third party product that integrates with Dynamics GP.

While the product sometimes technically works, it is absolutely riddled with bugs.  These aren't subtle bugs where you need to perform 26 steps with specific data in perfect sequence to trigger them.  These are glaring, obvious, brick-wall-in-the-middle-of-the-road bugs that will cause an error with the simplest data entry example.  In 10 minutes I found a half dozen bugs in the product--and that was on just 1 window.

The product has a web service that serves as the back end to their Dynamics GP integration--so their custom GP windows call the web service, rather than calling DLLs.  That web service also serves as the API if you want to integrate with their product.  Great, makes sense.

After working through numerous bugs in the product, we finally got it working and I got my integration working with it as well.  All seemed peaceful.

Until one day we noticed that some records that were sent to the product through my integration weren't in the database.  Records existed in GP, but didn't exist in the third party product tables.

After being puzzled for several minutes, we eventually tried to manually enter the same data into the product window in GP.  We entered the necessary data and clicked on Save.

So the problem wasn't my integration per se, it was that this particular data was failing to save.  We looked at the data, pondered for a minute, and saw that the data in one of the fields was relatively long--51 characters.

Curious, we made that value shorter and clicked Save.  The save was then successful.  I then opened the diagnostic logs for the web service and saw that it had logged an error.

String or binary data would be truncated
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

I then checked the SQL table, and sure enough, the field length was 50 characters.  Our 51 character field value was triggering the error.

Developers should immediately recognize the issue, and alarm bells should be ringing.

This tells us that the developer of the web service API is not validating input values or lengths in their API when they attempt to save data to their own tables.  So if a field is 50 characters and I submit 51 characters, their method will fail with a SQL exception.

Digging through the log file, it looks like they are building SQL statements by concatenating a string.

INSERT INTO SomeTable (TransactionType, Amount, TransactionTime, IsProcessed, CurrencyId, InvoiceNum, PurchaseOrderNum, SubmittedBy, CompanyId, CustomerId, CompanyName, AgreementId)
VALUES (1,10.99,1,'3/23/2015 2:37:08 PM',2,1,1, 'STDINV3588', '', '1', 'sa','Test Company','','-1','TI TESTCO100',2,2,'Test Company', 0)

While there might be some rare situations where I build a SQL statement like this, I do it reluctantly as an exception, and I never do it with user generated data.  The potential for errors from unvalidated data is bad enough, not to mention issues like SQL injection.

What they should be doing is something like this:

SqlParameter[] sqlParameters = new SqlParameter[2];

sqlParameters[0] = new SqlParameter("@CustomerID", System.Data.SqlDbType.VarChar, 15);

sqlParameters[0].Value = gpCustomerID.Trim();

sqlParameters[1] = new SqlParameter("@CompanyID", System.Data.SqlDbType.Int);

sqlParameters[1].Value = gpCompanyID;

In this example, my code is building parameters for a SQL command and is defining data types and maximum value lengths.  Customer ID must be a VarChar of no more than 15 characters, and Company ID must be an integer.  This doesn't handle all of the potential problems of someone submitting a 20 character customer ID (which should be caught and handled earlier in the process), but it at least prevents a SQL truncation exception.

This isn't rocket science and isn't a developer secret.  It's pretty basic .NET development.

After finding this issue, I had to modify my integration code to truncate input values before submitting them to the web service API.

All because the developer of an API for a "commercial" software product that is being sold to Dynamics GP customers for thousands of dollars doesn't know how to validate inputs.  And the "QA department" clearly didn't do a whole lot of testing.

Developer, please pick up the chalk and start writing.

I make coding mistakes all the time, but this clearly isn't a typo or a small mental lapse.  It's a fundamental design issue in the code that correlates well with all of the bugs we have found in the product.

On the plus side, this developer is making me feel like a coding genius.

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