You’re offline. This is a read only version of the page.
9,142
Hi
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
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
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.
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
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
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
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
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
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
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
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,240
Super User 2024 Season 2
#2
Martin Dráb
230,149
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (