web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Sales Line Item Commen...
Microsoft Dynamics GP (Archived)

Sales Line Item Comment - Formatting Issues

(0) ShareShare
ReportReport
Posted on by Community Member

I am importing a large amount of invoices using Integration Manager into GP 2016 R2. The data resides in an Excel spreadsheet (sample below):

DocType DocDate DocumentNo. CustomerID BatchID CustomerPONo ItemNo UofM Quantity UnitPrice Comment 
Invoice 2/16/2018 11-111 100 TEST AB111 EA 1       550.00 Line 1
Line 2
Line 3
Line 4

When the import complete, the line item comment looks like this:

:3187.pic1.png

Ideally it looks like this: 

6266.pic2.png

Interestingly enough, if I open up the sale comment in picture 1, and add line breaks at the correct places and save it, GP retains the formatting. I just can not get the formatting of this comment field to work when I use an import. 

The field I am mapping to is under Item --> Item Detail --> Comment Text

4064.pic3.png

I have tried using Macro based import as well as using eConnect and I get the same result. Any assistance would be greatly appreciated. 

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    L Vail Profile Picture
    L Vail 65,271 on at

    Richard,

    The macro method should work. What was happening when you tried to populate it with a macro? The macro just for the comment would look similar to this:

    CheckActiveWin dictionary 'default'  form 'SOP_Item_Detail' window 'SOP_Item_Detail'
      MoveTo field 'Expansion Button 7'
      ClickHit field 'Expansion Button 7'
    NewActiveWin dictionary 'default'  form 'SOP_Comment_Entry' window 'SOP_Comment_Entry'
      TypeTo field 'Comment Text' , 'Entry 1'
      TNT_Event , '09003B012901000000000200005C06E238F0' # cntrl chr
      TypeTo field 'Comment Text' , 'Entry2'
      TNT_Event , '09003B012901000000000200005C06E238F0' # cntrl chr
      TypeTo field 'Comment Text' , 'Entry3'
      TNT_Event , '09003B012901000000000200005C06E238F0' # cntrl chr
      CommandExec dictionary 'default'  form 'SOP_Comment_Entry' command 'OK Button K_w_SOP_Comment_Entry_f_SOP_Comment_Entry'
    NewActiveWin dictionary 'default'  form 'SOP_Item_Detail' window 'SOP_Item_Detail'
    ActivateWindow dictionary 'default'  form 'SOP_Item_Detail' window 'SOP_Item_Detail'
      CommandExec dictionary 'default'  form 'SOP_Item_Detail' command 'Save Button_w_SOP_Item_Detail_f_SOP_Item_Detail'

    Leslie

  • Community Member Profile Picture
    Community Member on at

    Well, that would work if I only had 4 lines of data. We are using 11 lines of data. So the integration is utilizing the COMMENT TEXT field instead of Comment 1, Comment 2, Comment 3, Comment 4 fields.

    I apologize, my sample data was a little misleading.

  • L Vail Profile Picture
    L Vail 65,271 on at

    Hi Richard,

    My example just happened to have four lines. If you look back at the macro, you'll see it is not referring to the 4 parsed fields, but is pointing to the Comment 
    Text. If you want more lines, simply add more lines to the macro. Expansion Button 7 opens the Comment window.

      MoveTo field 'Expansion Button 7'
      ClickHit field 'Expansion Button 7'
    NewActiveWin dictionary 'default'  form 'SOP_Comment_Entry' window 'SOP_Comment_Entry'
      TypeTo field 'Comment Text' , 'Now is the time for all good men'
      TNT_Event , '09003B012901000000000200005C06E238F0' # cntrl chr
      TypeTo field 'Comment Text' , 'to come to the aid of their'
      TNT_Event , '09003B012901000000000200005C06E238F0' # cntrl chr
      TypeTo field 'Comment Text' , 'Country'
      TNT_Event , '09003B012901000000000200005C06E238F0' # cntrl chr
      TypeTo field 'Comment Text' , ' '
      TNT_Event , '09003B012901000000000200005C06E238F0' # cntrl chr
      TypeTo field 'Comment Text' , ' '
      TNT_Event , '09003B012901000000000200005C06E238F0' # cntrl chr
      TypeTo field 'Comment Text' , ' '
      TNT_Event , '09003B012901000000000200005C06E238F0' # cntrl chr
      TypeTo field 'Comment Text' , ' '
      TNT_Event , '09003B012901000000000200005C06E238F0' # cntrl chr
      TypeTo field 'Comment Text' , ' '
      TNT_Event , '09003B012901000000000200005C06E238F0' # cntrl chr
      TypeTo field 'Comment Text' , ' '
      TNT_Event , '09003B012901000000000200005C06E238F0' # cntrl chr
      TypeTo field 'Comment Text' , ' '
      TNT_Event , '09003B012901000000000200005C06E238F0' # cntrl chr
      CommandExec dictionary 'default'  form 'SOP_Comment_Entry' command 'OK Button K_w_SOP_Comment_Entry_f_SOP_Comment_Entry'

    You will need to be able to figure out how many lines are in each record's comment so that you have enough lines in your macro.

    Leslie

  • Suggested answer
    L Vail Profile Picture
    L Vail 65,271 on at

    Richard,

    In order to use a macro to import the individual lines in your comment boxes, you’ll need to break the comment apart and enter one line of data into cells going horizontally. First, import your Sales Orders using Integration Manager and then write a macro to go back and add the comments.

    Since you already have the comments in Excel, it will be easy to separate the data.

    1. Embed a delimiter in your comment string delimiting each line. Use the formula =SUBSTITUTE(C1,CHAR(10),"|")  change C1 to the address of your multi-line cell.
    2. Save the results of the formula to text (turn the formula into words)
    3. Using the Text to Columns function separate your string at the | delimiter.
    4.  Example 

    SOP#

    B1

    C1

    Changed to text

    Text to column 1

    Text to column 2

    Text to column 3

    INV023

    Item#

    Line 1

    Line 2

    Line 3

    Line 1 of data|Line 2 of data|Line 3 of data

     

    Line 1

    Line 2

    Line 3

    Using  Word, create your mail merge using the recorded macro.

    The relevant part of the macro would look something like this:

      TypeTo field  'Comment Text' , '<Line 1 of data>'

      TNT_Event , '09003B012901000000000200005C06E238F0' # cntrl chr

      TypeTo field 'Comment Text' , '<Line 2 of data>'

      TNT_Event , '09003B012901000000000200005C06E238F0' # cntrl chr

      TypeTo field 'Comment Text' , '<Line 3 of data>'

      TNT_Event , '09003B012901000000000200005C06E238F0' # cntrl chr

      TypeTo field 'Comment Text' , '<Line 4 of data>' 

      TNT_Event , '09003B012901000000000200005C06E238F0' # cntrl chr

      TypeTo field 'Comment Text' , '<Line 5 of data>' 

    Include all 11 of your columns  

    I think you could get your macro ready with the least amount of pain using the method above.

    Kind regards,

    Leslie

     

  • Verified answer
    L Vail Profile Picture
    L Vail 65,271 on at

    Hi again Richard,

    Having slept on it, and think the macro thing was a nice exercise, I do love macros, but let's abandon that idea. I think once you have the data formatted in the matter laid out above, you could use Integration Manager and just use a Field Script to update the table. I would try something like this:

    update SOP10202 set CMMTTEXT = 
       'This is line 1.' + CHAR(13)
     + 'This is line 2.' + CHAR(13)
     + 'This is line 3.' + CHAR(13)
     + 'This is line 4.' + CHAR(13)
     + 'This is line 5.' + CHAR(13)
     where SOPNUMBE = 'BKO1005' and LNITMSEQ = 16384

    You'll need to change it so that it's VBSCRIPT friendly, but the above worked in straight SQL.

    Since you told me each invoice only had one line, I hard-coded the line item sequence at 16384. You would also need to populate the COMMENT_1, COMMENT_2, COMMENT_3 and COMMENT_4 fields.

    I tried using CHAR(10) + CHAR(13), but the CHAR(10) wasn't necessary.

    In order to get the visual next to the comment ID on the SOP Item Detail Entry window, you'll need to update the FLAGS field on the SOP10200 and increase the value by 2.

    Hope this helps

    Leslie

  • Community Member Profile Picture
    Community Member on at

    Leslie, thank you for your reply. I utilized part of this response to resolve our issue. Essentially, we ended up putting a delimiter of && in the cell with the comment field everywhere we wanted a new line. Then we ran an after integration script to update the SOP10202 table to replace the && characters with Char(13):

    UPDATE SOP10202

    SET CMMTTXT = CONVERT(text,replace(convert(varchar(max),CMMTTEXT),'&&',char(13)))

    I appreciate your help!

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

#1
mtabor Profile Picture

mtabor 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans