Report Writer - RW_Token with Comment Text

(0) Share

Report

Posted on by Heather Roggeveen

9,146

I am wanting to integrate text into Dynamics GP into the comment field. I can only import into Comment Text. So the three separate lines that I want as a result need to go in as a single string.

I have seen other posts about then updating SQL to insert the carriage returns. I don't want users to have to do that.

It is no issue having it as a single string, but I wanted to show them as separate lines on the invoice.

So I delved into the document David Musgrave wrote a number of years ago that gave details of the RW functions. RW_Token seemed the right one to use. But my problem is that I get this error when using it over the Comment Text field:

To ensure it wasn't the structure of my calculation, I replaced the comment text field with the item description field and it worked perfectly. So this suggests to me it is something about the way the data is held in the Comment Text field.

Any one have any ideas?

Cheers

Heather

*This post is locked for comments

All responses (9)

Answers (1)

David Musgrave MVP ... 14,036 Most Valuable Professional on at

Like (0)

Report

RE: Report Writer - RW_Token with Comment Text

The Comment Text field is text datatype and cannot be used with the RW_Token() function I wrote for Dynamics 7.0.

You need to pass in a string field, variable or calculated field.

There are a number of RW functions I created to get data onto reports that avoid needing to link tables and parse text into lines of up to 80 characters (due to 80 character limit for string calculated fields). See

https://winthropdc.wordpress.com/2011/01/13/working-with-text-fields-in-report-writer/

Well done Chris on finding a working solution.

Also see

https://winthropdc.wordpress.com/2008/09/01/using-the-built-in-report-writer-functions/

Regards

David
Suggested answer

Midmichael 59 on at

Like (0)

Report

RE: Report Writer - RW_Token with Comment Text

I know this question is a few years old, but I came across researching a related issue, and may have found a solution - depending on the length of the tokenized text.

There is another RW function which helps with the SOP Comments text - RW_SOPHDRCommentText. This will retrieve the comments text from the sop doc, and return them as strings of max length 80.

This under Functions, User Defined, System and returns a String type. Usage is as follows:

FUNCTION_SCRIPT( RW_SOPHDRCommentText SOPType SOPNumber 80 1)

80 is the max number of characters it will return, and 1 is the first string block it parses from the Text field.

I created a calculated field using that called (C) LongCommentLine1

Then I created another calculated field using the RW_Token function to break (C) LongCommentLine1 into separate lines at each "|"

My new calculated fields - (C) CommentsTokenBroken1:

FUNCTION_SCRIPT( RW_Token (C) LongCommentLine1 "|" 1)

And

(C) CommentsTokenBroken2:

FUNCTION_SCRIPT( RW_Token (C) LongCommentLine1 "|" 2)

and so on...

Result at the bottom of the report:

Original text in comments field on invoice: "Test comments line1|Test comments line2|Test comments line3"

Printed at the bottom of the invoice:

Test comments line1

Test comments line2

Test comments line3

Again - the limit on the length of the returned strings is 80, and if there are carriage returns included, it will break on those as well.
Heather Roggeveen 9,146 on at

Like (0)

Report

RE: Report Writer - RW_Token with Comment Text

Hi Justin

Thanks so much for your help. I will take all this advice and look for the best solution. I may be able to use the learnings and extend what I put in e.g. Item Description and split from there.

I really appreciate the time you have taken on this.

Cheers

Heather
Verified answer

Justin Thorp 2,265 on at

Like (0)

Report

RE: Report Writer - RW_Token with Comment Text

Hi Heather,

I'm running into a whole host of issues trying to get this to work (especially after adding multiple line items on the invoice).

My initial testing was using a different report (Vendor List), then I added the Records Notes Master Table and the TextField in that table, and then placed my fields in the body section of the report.

For the invoice, I think we could get this to work if you were using the document level note, and then placed your fields in the RH section of the report. But, given that you are using the line item comments, I don't think we can get this to work (especially given how line comments get suppressed by default).

I'd recommend going with your original findings of "I have seen other posts about then updating SQL to insert the carriage returns".

Justin
Justin Thorp 2,265 on at

Like (0)

Report

RE: Report Writer - RW_Token with Comment Text

Hi Heather,

Nice job so far :)

Using this type of logic on the SOP Invoice forms is rather complicated. It's also difficult to advise as I don't know of any existing modifications you've made.

Things to keep in mind:

1.) Since it's the line item comment you are dealing with, you probably want your VBA logic to be in the "Private Sub Report_BeforeAF(ByVal Level As Integer, SuppressBand As Boolean)" instead of the "Private Sub Report_BeforeBody(SuppressBand As Boolean)" (Because by default the line comments are in the footers and not the body section of the report).

2.) I presume you of course added the report to VBA. And then I presume you added your calculated fields to vba (your calculated fields Comment1Substring , Comment2Substring, Comment3Substring)? Additionally, you must add the Comment Text field (from Sop Line Comment Work Hist) to the F5 section and set it's "Display Type" to "Data", then add this field to VBA. Once that is done you would change your vba code to be this:

arr = Split(CommentText, "|")

instead of this: arr = Split(SOP_LINE_CMT_WORK_HIST.CommentText, "|")

Side note: Using fields that have a "Text" storage type in any footer section of a report is a pain

Good luck,

Justin
Heather Roggeveen 9,146 on at

Like (0)

Report

RE: Report Writer - RW_Token with Comment Text

Hi

Nothing like something completely new to make one feel like a rookie again.

So I took your vba and added it to my report and updated as below:

Private Sub Report_BeforeBody(SuppressBand As Boolean)

Dim arr() As String

' Parse string to array

arr = Split(SOP_LINE_CMT_WORK_HIST.CommentText, "|")

Comment1Substring = arr(0)

Comment2Substring = arr(1)

Comment3Substring = arr(2)

End Sub

Comment1Substring is what I named the field in Report Writer to return the value. SOP_LINE_CMT_WORK_HIST.CommentText is the report writer field that holds the information that I want to split. When I ran the report, I got a debug error saying "object required" and pointing to this line:

arr = Split(SOP_LINE_CMT_WORK_HIST.CommentText, "|")

Is there something I am missing?

I had a look at some other VBA reports that I knew one of our previous consultants had deployed and they all had some for of this at the top of them. Do I need to include this sort of section?

Dim cn As New ADODB.Connection

Dim rst As New ADODB.Recordset

Dim cmd As New ADODB.Command

Dim strSQL As String

Dim strFunctionName As String

Dim strContract As String

Dim lngOrd As Long

As I noted before, I am a complete rookie with vba - I can read it and having a fighting chance of editing it, but have never gone into writing it myself, so any guidance is greatly appreciated.

Cheers

Heather
Heather Roggeveen 9,146 on at

Like (0)

Report

RE: Report Writer - RW_Token with Comment Text

Thanks Justin

I have never actually tried to add vba to a report - I have modified vba that is already on a report - never started one myself. Another little challenge to add to what has already been an interesting week with this particular requirement. I will have a play and update you.

Cheers

Heather
Suggested answer

Justin Thorp 2,265 on at

Like (0)

Report

RE: Report Writer - RW_Token with Comment Text

Hi Heather,

See below a sample of the VBA you could use:

Private Sub Report_BeforeBody(SuppressBand As Boolean)

Dim arr() As String

' Parse string to array

arr = Split(TextField, "|")

Text1 = arr(0)

Text2 = arr(1)

Text3 = arr(2)

End Sub

Best Regards,

Justin
Justin Thorp 2,265 on at

Like (0)

Report

RE: Report Writer - RW_Token with Comment Text

Hi Heather,

I get the same error message and agree with your conclusion that the RW_TOKEN function doesn't work with fields that have a "Text" storage type.

Therefore I recommend using VBA to get the desired results.

Best Regards,

Justin