Pulling in the Full Note's Text from SY03900 through SmartList Builder

(0) Share

Report

Posted on by Community Member

Hello:

I'm using a SQL script to pull in open year journal entries' record-level notes from the SY03900 table.

Yes, I know that--instead of using a SQL script--this can ordinarily be done by clicking the Note Index field in the Year to Date Transaction Open table in SmartList Builder and choosing the "Show Note" checkbox in the SmartList Options window.

But, a lot of the notes for our journal entries have carriage returns. Simply choosing the Year to Date Transactions Open table in SmartList Builder forces SmartList to export the data in the Note field to Excel on multiple rows rather than only one.

Now, even though using my SQL script and exporting to Excel yields one row per note (which is what we want), the number of characters appearing for the Note field in both SmartList and in Excel is 255.

Is there a way of overcoming this character limitation, so that the full note's text will appear in SmartList and, therefore, after being exported to Excel?

Thanks!

John

*This post is locked for comments

All responses (6)

Answers (1)

Victoria Yudin 22,768 on at

Like (0)

Report

RE: Pulling in the Full Note's Text from SY03900 through SmartList Builder

John,

Excellent, glad you were able to get this working. Thanks for the follow up.
Community Member on at

Like (0)

Report

RE: Pulling in the Full Note's Text from SY03900 through SmartList Builder

Hi Again, Victoria!

Thank you! I just tested my "theory", in the reply that I just posted. And, it worked.

Specifically, since we do not have the Excel Refreshable Reports deployed in our production environment, I used the "Data...Connections" section of Excel and copied in the following T-SQL code:

select SY03900.TXTFIELD, * from AccountTransactions

LEFT OUTER JOIN

SY03900 ON

AccountTransactions.[Note Index] = SY03900.NOTEINDX

where [Journal Entry] = '1804498'

Excel, then, shows the full note text for the journal entry!

Again, thanks!

John
Community Member on at

Like (0)

Report

RE: Pulling in the Full Note's Text from SY03900 through SmartList Builder

Hi There, Victoria!

Thanks, for chiming in. I just looked at the AccountTransactions view in the TWO database. It shows Note Index and Note Index from Account Master. But, I don't see where it shows the note.

But, are you saying that if I modify the Excel Refreshable Report to tie to the SY03900 table and pull in that table's TXTFIELD then I will see the full note in one cell?

John
Verified answer

Victoria Yudin 22,768 on at

Like (0)

Report

RE: Pulling in the Full Note's Text from SY03900 through SmartList Builder

You could try a refreshable Excel report instead of SmartList, that should not have a 255 character limit.
Mick Egan 3,561 on at

Like (0)

Report

RE: Pulling in the Full Note's Text from SY03900 through SmartList Builder

John,

We faced a similar situation, where we needed at least 8 Columns with the 255 character limit for each Excel Column from a similar table holding text data.

It was fairly complex, where an SQL UserDefinedFunction was created using a Cursor to find the start position and end position, i.e. 0 -255, 256 + 255, etc. for each part of the Record_Note.

We then used an SQL View to retrieve the Data, and Case Statements with SUBSTRING for each of the 8 columns of records notes, this View was used in Smartlist.

It will certainly have your thinking cap with this one.

Mick
Community Member on at

Like (0)

Report

RE: Pulling in the Full Note's Text from SY03900 through SmartList Builder

We're on Excel 2010 (32-bit), by the way.