The change from COM and DTC to a Windows Service took a little while to understand, but I've adjusted and am now pretty comfortable with the service and how to troubleshoot it.
Using eConnect within Visual Studio is very similar, except to a few changes to the final call to eConnect, but that wasn't difficult to accomodate.
But now I've run into a change that has me stumped for the moment. It's minor, but as with many "minor" things, it's an annoyance that just seems to be challenging me to figure it out and find a solution.
When importing notes into GP using eConnect 9 and 10, I have previously used "\r" or vbCr (carriage return) to produce line breaks in the note text. Worked like a charm.
When I had to send a note line break using GP 10 Web Services, I figured out that you need to send the UTF-8 escaped version of carriage return, which is & #13. (I've had to add a space to prevent the blog publishing from thinking it is HTML)
Great, problem solved, mission accomplished, non-issue.
But then comes eConnect 2010. When testing an integration that I upgraded to 2010, I noticed that my notes now had the vertical bar in the note instead of a line break. Hmmm.
I tried \r, \n, and \r\n, but all produced the same result. So then I tried & #13, but that just put the literal text of "& #13" in the note!
Just to make sure I wasn't going crazy, I queried the text of a GP note with manually entered line breaks and was able to confirm that "\r" is still the line break being used and stored in SQL.
So now I'm stumped. I need to do more testing with \r to see why that doesn't seem to work, and how that value is being stored in the database when it is saved by eConnect 2010. I'm guessing it is escaping the character somehow and that is causing the problem, but I haven't had time to trace the SQL or query the resulting note text to figure out what character is being stored.
If any eConnect 2010 gurus out there know the answer, post a comment and impress us!
UPDATE: So I have done some more testing, and what I'm seeing with notes created using eConnect 2010 is pretty strange. I'm still reviewing my import to make sure that I don't have a bug in my code, but here is what I'm seeing.
When I assign text to the eConnect customer NOTETEXT field, I am making sure that the text contains "\r" for line breaks. When I view the serialized XML just prior to sending it over to eConnect, there are line breaks in the XML.
But after the note is saved and I query the TXTFIELD value from the SY03900 table, I am seeing "\n" characters for line breaks, which is causing the display problem in GP.
Here is the value being passed to eConnect:
City: Las Vegas\rPostal Code: 3658\r\r\rLas Vegas\r3658
And here is what I am querying from SY03900 after it is saved:
City: Las Vegas\nPostal Code: 3658\n\n\nLas Vegas\n3658
So it seems that somehow, for some reason, eConnect is replacing \r with \n in Notes.
Steve Endow is a Dynamics GP Certified Trainer and Dynamics GP Certified Professional. He is also the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.
http://www.precipioservices.com
Hi Steve
ReplyDeleteWe are having the same issue with our POS system integration via eConnect to GP10. But, where yours is with the Notes, our issue is in the Comment ID text. I am not that technical but if you come up with a solution to eliminate the Bar, please post adn I can pass onto our developer.
Can you answer this question - How can I achieve a carriage return in a Comment Field (SOP transaction in this case) using IM10 integration into GP10? For example on Comment line 1 we want to have an address, Line 2 to equal Rental Address Code and Line 3 the applicable month.
Cheers
Brent
Hi Brent,
ReplyDeleteI believe you will want to use VBA on the field to combine the three fields you mention. For the line breaks, you will want to use the carriage return value, vbCr, or Ascii 13.
Thanks,
Steve
I had a similar issue with the Webservices for GP 2010 as well, which related is built on top of econnect framework.
ReplyDeleteMy issue was with Service Calls in a c#.net Web App. I first created a Stored procedure in Microsoft SQL.
CREATE PROCEDURE sp_FixNotes
@DRID int
AS
UPDATE sy03900
SET txtfield = STUFF(convert( varchar(8000), txtfield ),
PATINDEX( '%' + '_RtnHere_' + '%' , txtfield ),
DATALENGTH( '_RtnHere_' ),
'' + CHAR(13) + CHAR(10) + '' )
WHERE txtfield LIKE '%' + '_RtnHere_' + '%'
and dex_row_id = @DRID
This allows me to simply pass in the Dex_row_id of the record i want to replace and i can call the procedure directly from my c#.net code.
execute sp_FixNotes 'DEXROWIDHERE'
In my application i only have to specify "_RtnHere_" in my code with the stringbuilder object and then loop through the procedure in order to update all occurances of it on the SQL side.
Stringbuilder example - C#.net
StringBuilder s = new StringBuilder(svccallnotes);
s.Replace("\r\n", "_RtnHere_");
You can choose to replace the _RtnHere_ with whatever you like, it is not very likely that this will get entered in by mistake.
This seems to work pretty well, but MS stated not to have a release for this naturally through econnect and WS until SP3 for GP2010. Couldn't wait that long... i hope that this helps you as well....
Nicholas Cole - Saratoga Technologies
Hi Nicholas,
ReplyDeleteGreat idea for a workaround. I guess I was hoping that there was some trick to getting the carriage returns to work in 2010.
But it seems the consensus is that this is a bug or issue with 2010, so as you indicated, it looks like we're going to have to live with it for a while.
Thanks for the code sample!
Steve
I am having the same issue. Instead of using eConnect directly, I actually write an extension to GP Web Services with my own Note objects, XSLT, and sprocs. To "fix" this issue, I changed my sproc to update the text of the note to replace CHAR(10) with CHAR(13).
ReplyDeleteGP only likes to see CHAR(13) to properly write out the notes, and eConnect only seems to want to put CHAR(10) into the DB. Since I have the implementation I do with the sproc I can control, I am able to do a SQL REPLACE on it and get the desired result!
Econnect has the stored procedures that load the data into GP.
ReplyDeleteEach stored procedure has a pre and a post procedure.
When I create a transaction entry and add a note, I just added this to the taSopHdrIvcInsertPost stored procedure:
DECLARE @TheNoteIndex numeric(19,5)
SELECT @TheNoteIndex = NOTEINDX
FROM SOP10100
WHERE SOPNUMBE = @I_vSOPNUMBE
AND SOPTYPE = @I_vSOPTYPE
update sy03900
set txtfield = replace(cast(txtfield as varchar(max)),'_VBCRLF_' ,char(13))
where noteindx = @TheNoteIndex
ahhh i like that :) thx
ReplyDeleteespecially for pointing my attention to
taSopHdrIvcInsertPost