Friday, January 29, 2016

Handy (Lazy) technique for multi-value string parsing with .NET

By Steve Endow

I find that I regularly need to parse strings that contain multiple logical values.

For example, you may have some employee data with a value like:

Steve.Endow-28-Operations
Christina.Phillips-362-Accounting

If you need to extract the first name, last name, and employee number, it can be tedious.  For the first name, you could locate the period in the value, then use a Substring function and get the left X characters until the period.

But then what about the last name?  You would need to locate the period, then locate the first dash, then get the characters between those two positions.  And then repeat this very tedious process for employee number and department.

Similarly, you are probably familiar with this type of multi-value string:

000-1300-00
100-6520-10

If you want the natural account in the second segment, you would normally do some tedious string parsing.  To do it properly, you can't assume that the account segment lengths will always be the same, so you need to first locate the dashes, then extract each segment relative to the dashes.

In short, this is a very common, but annoyingly tedious task.

I don't know why I didn't think of this sooner, and I'm sure this is obvious and widely used by programmers smarter than me, but it seems my laziness to think of a better solution finally overcame my laziness trying to avoid having to yet again parse a string using Substring--which I hate doing.

So I thought, well, we have a string with two or more values separated by a separator character.

If we pause for a moment, what does that sound like?

Perhaps like a delimited list?

And whenever you encounter a delimited list of values, what is a common way of parsing such values?  The Split function, of course.

So normally I would use Split to deal with values like:  27,56,78,90,12,34

You use string.Split(','), and you end up with a handy array.  So this is widely used when you have multiple independent values separated by the same delimiter.

But if we step back a bit, who cares what the values are and whether they are related, and who cares what delimiter is used?

In the case of our GL account string:

100-6520-10

We have three related values:  Segment 1, Segment 2, Segment 3.  Because these segments are related, and not a list of independent values, for some reason I never thought to use Split.  but if we think more abstractly and ignore that relation, the string is just a generic dash delimited list.

In which case, we can do:

string[] account = accountNumString.Split('-');

We can then reference account[0], account[1], and account[2] without having to count any character positions, locate dashes, or use Substring.

Moving on to our employee example:

Steve.Endow-28-Operations
Christina.Phillips-362-Accounting

Here, we have four values.  Two are separated by a comma, and three are separated by a dash.  Like the GL account, let's just pretend they are just delimited lists of values.

We can then use Split to extract specific values from our multi-value delimited string.

string firstName =  employeeInfo.Split('.')[0];
string lastName =   employeeInfo.Split('.')[1].Split('-')[0];
string employeeID = employeeInfo.Split('-')[1];
string department = employeeInfo.Split('-')[2];


Notice for the last name we're using a double split, which sounds like a gymnastic move.

Here we see it in action:


By the same token, rather than sending the GL account segments to an array, you can access the segments directly through Split.

string segment1 = accountNumString.Split('-')[0];
string segment2 = accountNumString.Split('-')[1];
string segment3 = accountNumString.Split('-')[2];


So no need to locate a separator, no need to count character positions, and no need to use Substring.

I'm assuming that this is a common practice for many, but I'm often late to the party, so sadly, this only occurred to me tonight.  It only took me around 30 years to figure it out.


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








How do I change the Dynamics GP Batch Approval password?

By Steve Endow

I received this question today so I thought I would post it for reference.  I've paraphrased it, but here is the essence of the question:

"Our accounting manager is out for the day.  I just tried to post a batch , but it is asking me for a Batch Approval Password and we don't know the password.  How can I change it?"

The batch approval passwords can be changed under Tools -> Setup -> Posting -> Posting. (Posting Setup Window)

You will want to make sure to select a Series and Origin before making any changes on the Posting Setup window.  Do not make changes to the window without those two fields populated.


Once you have selected the appropriate Series and Origin, you should see the "Require batch approval" box checked.  You can type a new password in the Approval Password box, then click OK.

After typing in a test password, I also noticed that the password is visible, so you can just check what the value is, and don't actually need to change it if you don't want to.


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



Tuesday, January 26, 2016

"This voucher number already exists" when trying to open a saved Payables Invoice

By Steve Endow

Tonight I'm testing some changes to a GP VS Tools AddIn that I developed.

I apparently had a bug in a line of the AddIn code that caused GP to crash while I was editing a Payables Invoice.


So I fixed the bug, recompiled, and opened GP again, clearing out my old login record.

Strangely, when I opened the Payables Transaction Entry window and clicked on the 'last' record button, nothing happened.  Using the First/Previous/Next/Last buttons wasn't bringing up my test invoice.  Odd.

I then tried the lookup button next to the voucher number field.  There was the invoice.  Hmmm.


But when I tried to select the invoice to open it, I received this message.


Um, ya, of course the voucher number already exists!  It's a saved voucher!

So apparently the GP crash caused some problem that is preventing GP from opening the voucher.

To start my detective work, I closed the Payables Transaction Entry window, and I checked DEX_LOCK, SY00800, and SY00801.  Nothing.

Then I opened the window again and traced the SQL activity that occurred when I selected the saved voucher.  Nothing obvious that I could decipher.

I then queried PM00400 and saw a small oddity--voucher 680 had a blank record, and I hadn't attempted to save it.  It wasn't obvious if that might be related, but it does look like an invalid record.


I also noticed that the REBILL batch to which the invoice belongs has a transaction count of 2, even though there is only one transaction in the batch.  I've done lots of testing and had several GP crashes, so I am guessing the transaction count was already wrong.


So, as a last resort, I entered a new voucher so that I could compare the data for that new record to my problem voucher.

And that's when I saw an issue.


Voucher 680 had a document status of 0.  Even though the voucher had been previously saved, when GP crashed, it somehow caused the document status for the voucher number to be set to 0 in the PM Keys table.

I can proudly say that I have crashed GP hundreds, nay, thousands of times with my test code, and have never seen this issue.

So I set the DCSTATUS value to 1 and tried opening the voucher.  Nope.  Same error.

So then I queried PM10000 to see if I could find any odd values.

I found that the PSTGSTUS field was indicating that the voucher was being edited.  Which makes sense.


After setting the posting status back to 20, I was finally able to open the voucher again.

That was a fun waste of 30 minutes.

It'll teach me to never have a bug again.  My first and only bug.


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










A Dynamics GP error all consultants should know: Data dictionary memory allocation error

By Steve Endow

Because I work a lot with Post Master Enterprise, which occasionally requires manual adjustments to the Dynamics GP Dynamics.SET file, I pretty consistently get a question about this error message.


It is totally understandable when customers ask me what this means.  I don't expect them to be familiar with the arcane details of a proprietary configuration file that they normally wouldn't edit.

But I am surprised when a Dynamics GP partner asks me what this error means and how to fix it.

When I started as an employee at a Dynamics GP partner many years ago, the first day that I was introduced to GP, one of the first things the manager showed me was the Dynamics.SET file.  It's pretty fundamental to GP, so it's good to understand what it is and how it works.

For that reason, there are many, many blog posts and articles that discuss the SET file, so I'm not going to write another one.  Here is one that I found quickly that covers the basics.

http://www.interdynbmi.com/blog/microsoft-dynamics-gp-dictionary-set-file

If you are a GP consultant and you don't know how to edit a SET file and diagnose SET file issues in your sleep, you really need to spend a few minutes to get up to speed.

The Data dictionary memory allocation error shown above is very easy to fix.  It indicates that the module count listed on the first line of the SET file does not match the number of modules listed in the SET file.  So if you have to manually edit the SET file to remove one module, you need to decrease that module count by one.  If you get this error message, it's a 10 second fix.

Learn it.  Live it.  Love it.

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





Thursday, January 21, 2016

Dynamics GP Company Posting Accounts: Sales-Sales vs. Inventory-Sales

By Steve Endow

Although I have worked with Dynamics GP for over 10 years, because my focus is on customizations, integrations, SQL, and development related areas, I don't consider myself an "Expert" when it comes to Dynamics GP application functionality.

I have taken the MS exams, been certified, been an MCT, and am now a Dynamics GP MVP, but even now, I would still not say I'm a Dynamics GP application expert.  I'm constantly reminded that there are so many nooks and crannies in GP and its many modules that I know there are lots of features and options that I haven't explored, or haven't done so in years and have forgotten about.

Today I received such a reminder.

I was troubleshooting an eConnect integration that wasn't getting the default sales account out of GP for some reason. While poking around in the Company Posting Accounts Setup window, I noticed something.  This is probably obvious to the Dynamics GP application experts out there, but since posting accounts aren't on my Top 10 Exciting Things List, it's one area where I haven't paid too much attention.

This is the Posting Accounts Setup window, displaying the Sales accounts.


Pretty straightforward, right?

And this is the Posting Accounts Setup window, displaying Inventory accounts.  Exciting stuff, I know.


If you haven't fallen asleep yet, you may have noticed that subtle bright red circle around the sales account.

I can understand a Sales-Sales account.  But an Inventory-Sales account?  What's up with that?

Does the inventory module hit sales?  Does an Increase Adjustment affect Sales?  A Variance transaction?  A Transfer?  In-Transit Transfer?  I wouldn't think so.

So why does the Inventory module have a Sales posting account?

And then it dawned on me.  Or at least I had a guess.

If you go into Tools -> Setup -> Sales -> Sales Order Processing, there is an option called "Posting Accounts From".  Your choices are Item or Customer.


This option is pretty obvious.  When you create a sales transaction, you can default the posting accounts from either the line item or the customer.



So continuing with my Sales Account example, if I enter a SOP Invoice, the default account can come from the line item or my customer.

But what if either my customer or my item doesn't have a Sales account?  Well, that's where the company posting accounts come in.

For some reason I thought that if either the customer or item default sales accounts were left blank, GP would use the company Sales posting account.  As in Sales -> Sales.

Since the SOP Setup account options are labeled as "Item" and "Customer", and not "Inventory" and "Sales", I just assumed the fallback would be the single company Sales -> Sales account.

Nope.

As you can probably guess by now, if you choose "Item" in SOP setup, the Sales account will default from the Inventory -> Sales company posting account.  If you choose "Customer" in SOP setup, the Sales account will default from the Sales -> Sales posting account.  Apparently someone deemed it necessary to have two different fallback default company posting accounts.

In my wildest dreams of Dynamics GP posting accounts, and I have many, I probably would not have thought of that particular configuration requirement to have both a Sales and Inventory default sales posting account.  But then again, I'm no Dynamics GP application expert.

And so there ya go, my daily dose of humility in the never ending journey of learning an ERP system.

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











Tuesday, January 12, 2016

Dynamics GP Error: Selected company is not configured as a company of the current system database

By Steve Endow

Today I created a new test company in my development environment and then restored the TWO database into my new TEST database.  I dutifully ran the GP refresh company script and got a few strange errors, but eventually ran the script successfully.

But when I logged into GP and selected my new Test company, I received this error:


The selected company is not configured as a company of the current Microsoft Dynamics GP system database.  You must resolve the database configuration to log in to the company.


I've never seen this error before, so I didn't know where to start.  I Googled the message, and after digging around, I finally found a GP partner post that discussed the issue and mentioned that the problem was with the SY00100 table in the new company database.  The record in that table should reference the Dynamics GP System database, not the Company database.

Sure enough, the SY00100 table in my new Test company had its own INTERID value of TEST, instead of the DYNAMICS system database.  I corrected the value in SY00100 and was able to login.

Turns out that Tim Wappat documented this issue in detail a year ago, but I didn't see it in the Google search results.

Here is Tim's post from January 2015:

http://www.timwappat.info/post/2015/01/21/Automated-restore-of-Live-Company-to-Test-Company-in-GP2013-CHANGES

And I now see that Jen Kuntz also documented the issue in August 2015:

http://kuntzconsulting.ca/2015/08/restore-issue-on-gp-2015-drove-me-nuts/


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





Monday, January 4, 2016

Locate the Dynamics GP record associated with a Note's Document Attachment

By Steve Endow

I received a request tonight that I thought was interesting.  I'm guessing it's not a common request, but I found it intriguing, so I thought I would post it.

Dynamics GP 2013 R2 added a feature called Document Attachment, or Doc Attach.  This feature was an upgrade to the rather old OLE Notes feature in GP, and provides basic file attachment functionality throughout GP.

So that's great and all, but since I don't personally use Doc Attach, one detail that I never knew about was that the Attach feature in the Dynamics GP Note windows also uses Document Attachment.


In the screen shot above, I have the Customer Maintenance window open, then I clicked on the Note button, and you can see a Document Attach button available.

Okay, cool, that makes sense--might as well use Document Attach consistently everywhere.

So if you attach a document to the customer note, you're essentially attaching the document to the customer, right?

Well, not really.

If you use the Note document attach feature, you are attaching the document to the Note, not the Customer.  And then the Note is associated with the Customer record.  There is no direct link between the Note document attachment and the Customer.

Which leads to the request I received this evening.

"I see a document attachment record in the CO00105 table, but I can't tell what GP record it is associated with. Can you help me find the GP record?"

Here are two sample records from the CO00105 table, showing two document attachments.


The first record is for a document attached directly to a customer record using the Attach button on the Customer Maintenance window.  That record clearly tells us that the document is associated with customer ID AARONFIT001.  Easy peasy lemon squeezee.

But the second record is for a document attached to the Customer Note window.  But if you only look at the data in the CO00105 table, you wouldn't know that.  You can see a reference to "System\Notes", which you could eventually deduce means that the document is attached to a Note.  But what is DOCNUMBER "0000048D"?

From the second record, you can't tell which Note window in GP was used to attach the document, and you can't tell which GP record the document, or the Note, is associated with.

And this is where the fun begins.

Let's tackle the mysterious 0000048D value.  That value is not actually a Dynamics GP document number--it's a hexadecimal version of a number.  Why the developers chose to use hex is beyond me--I'm guessing they had a reason, but I'm not yet convinced it was a good enough reason.

You can convert the hex number to decimal through a site like this one:

http://www.binaryhexconverter.com/hex-to-decimal-converter

So we learn that 48D is equal to 1165.  Okay, so what does 1165 mean?

That value is the Dynamics GP Note Index value.  So if you query the SY03900 Note table, you can look up the Note to which your document is attached.

SELECT * FROM SY03900 WHERE NOTEINDX = 1165


Okay, wonderful.  So now we know that our document was attached to Note 1165.  But you'll see that the SY03900 table doesn't really tell us anything more than the CO00105 table did.  We see the note, but in typical Dynamics GP cruel fashion, we can't tell which GP record the Note is associated with, and we can't even tell which type of record it's associated with.  Nothing.  Zilch.  Nada.

So we know the Note Index value, but we have no idea which record has been assigned to that note index.  Lovely.

So here is where things get even funnerer.  Super fun.  And kludge filled.

If you are familiar with the Dynamics GP NOTEINDX field, you know what when you create a new record in GP, a NOTEINDX value is typically assigned to the record, even if you don't actually create a note for that record.

So somewhere in my Dynamics GP company database is a record that has a NOTEINDX value of 1156.  But I don't know which type of record, and therefore I don't know which table it might be stored in.

So, how in the world do we search the entire company database to find the record that has our Note Index value of 1156?

Well, I'm a big fan of the INFORMATION_SCHEMA views.  If you don't know about these views, go learn about them pronto, as they will save your bacon at some point.

Using the information schema views,you can use this query to see all of the tables that have a NOTEINDX field.

SELECT c.* FROM INFORMATION_SCHEMA.COLUMNS c
LEFT OUTER JOIN INFORMATION_SCHEMA.TABLES t ON t.TABLE_NAME = c.TABLE_NAME
WHERE c.COLUMN_NAME = 'NOTEINDX' AND t.TABLE_TYPE = 'BASE TABLE'
ORDER BY c.TABLE_NAME


You then learn that around 189 tables in each Dynamics GP company database have a NOTEINDX field.  

I don't know about you, but I'm not about to query 189 tables.  I'm super lazy, and that right there sounds like a lot of work.

So how can we magically query the entire database, and more specifically, just those 189 tables, and find the one record that has a Note Index value of 1156?

Very good question.  I didn't have an answer either.

But I pulled out a big roll of duct tape and a ball of twine, and I cobbled together this messy little gem.  It isn't going to win any beauty contests, but it took me about 10 minutes to cobble together and was the simplest query that produced the cleanest results.  There are likely a dozen ways to do this in a much fancier way, but this inquiry was non-billable, so good enough worked just fine.

Of course you will need to replace the highlighted 1165 value with the specific Note Index you are looking for.  And if you are the ambitious type and plan to use this more than a few times, you could get super fancy and turn this into a stored procedure that accepts the Note Index value as a parameter.


DECLARE @noteindx nvarchar(20)
DECLARE @tablename nvarchar(20)
DECLARE @sqlstring nvarchar(MAX)
DECLARE @param nvarchar(100)

SET @noteindx = '1165'

DECLARE notecursor CURSOR FOR
SELECT c.TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS c
LEFT OUTER JOIN INFORMATION_SCHEMA.TABLES t ON t.TABLE_NAME = c.TABLE_NAME
WHERE c.COLUMN_NAME = 'NOTEINDX' AND t.TABLE_TYPE = 'BASE TABLE'

OPEN notecursor
FETCH NEXT FROM notecursor INTO   @tablename

SET @sqlstring = N'SELECT ''' + @tablename + ''' AS TableName, NOTEINDX, DEX_ROW_ID FROM ' + @tablename + ' WHERE NOTEINDX = ' + @noteindx + CHAR(13);

WHILE @@FETCH_STATUS = 0
BEGIN
       SET @sqlstring += N' UNION SELECT ''' + @tablename + ''' AS TableName, NOTEINDX, DEX_ROW_ID FROM ' + @tablename + ' WHERE NOTEINDX = ' + @noteindx + CHAR(13);
      
       FETCH NEXT FROM notecursor INTO @tablename
END

CLOSE notecursor
DEALLOCATE notecursor

EXEC(@sqlstring)



This script queries a list of tables that contain the NOTEINDX field and then builds a SQL statement to query them all.  It then displays the results in a simple and tidy format.


And behold.  The query tells us that there are two tables with a NOTEINDX value of 1165.

SY03900 is in the list, of course, because it is the Note table.  But the other result, RM00101, tells us that the note is associated with a customer record.

So, given this information, I can query the RM00101 table.

SELECT NOTEINDX, * FROM RM00101 WHERE NOTEINDX = 1165


And with that, we have found the record associated with our mystery attached document.


So there you have it--tracing a document, attached to a note, back to the Dynamics GP record to which the note is associated.

I told you it was fun!


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