I'm attempting to add the SNOTE table to a standard SL Crystal report. Apparently there is a KB article # 847735 but I can't find it in PartnerSource. Does anybody have that document or other information on adding the SNOTE table and note field to a report?
Thanks,
Scott
*This post is locked for comments
Hi Rick, thanks to you noticing the "select distinct" issue I was able to resolve the issue with the notes on the AR Invoice. On the Database menu, I unchecked the "Select Distinct Records" option and the report prints with the notes. Unchecking seems to have had no negative impact on the report, but I will continue testing to make sure it's OK. If I run into an issue with having that option unchecked, I will update the thread. I looked at a couple of other reports that already have notes and none of them had the Distinct Records option checked. It looks like this is just an odd situation with the AR Invoice report. Thanks again!
Scott
Hey Rick, thanks for all the efforts here. Curious to know what caused a working report to start generating the 421 error and if you've resolved it. My issues look like something to do with the ODBC data sources that Crystal utilizes and that's the angle I'm working now. Hope to know something later today.
Some more info: I copied the resultant SQL code from the report using Show SQL and pasted that into SQL Management Studio and attempted to run the query. It came back with an error stating that the selected field in not compatible with a select distinct construct. In looking at this further, the SNotetext field in the SNote table is defined as a text field and you cannot use the DISTINCT verb on a text field according to SQL Server. So, the issue is that this report, unlike most other reports that work with adding notes, is trying to use the DISTINCT verb.
Scott, I continued to play with some and was able to get the 421 error code but I am not sure what made that happen. I will continue to investigate and let you know if I find the cause/resolution.
Scott, I just modified that report to add the notes from the ARTran table and it worked just fine. I was able to add the note text field to the detail section and save the revised report in the usrrpt folder. When I launched the menu item, the Print Notes field showed and was enabled. So, the process as described works (not that it resolved your issue but, at least, you now know it should work).
What version of SL were you trying this on? I did my test on SL 2011 (just because that is what I had on that test computer).
Scott, it is my understanding that the Print Note option should enable on the ROI screen when the report definition includes the SNote table and the note text is a field in the report and the selected report format is the one with the note table included.
I have added notes to various reports such as the AR Period Sensitive Aging but have not tried the AR Invoice report. I will play around with that and see if I get the same issue and let you know.
Hi Rick, thank you very much for the reply. I have already reached the point of adding the SNOTE table to the report - AR Invoice 08760 - and that's not where my problem lies. When I run the report after adding the table, all is good. However, as soon as I drop the snotetext field onto the report I get the Crystal error "Failed to retrieve data from the database. Database Vendor Code 421." I've worked through this before and that's what I'm working on now. The join from ARTran.NoteID to Snote.nID is a left-outer join and the sNoteText field is in the detail section of the report. Also, I don't see how to activate the Print Notes check box to add that functionality although that is not a primary concern. Even though the Database Expert shows all the tables in the same local application database, I'm going to go through the Set Datasource Location steps just to rule that out. I was hoping to locate the KB article to see if there was anything useful there, but I can't find it anywhere. I'll keep plugging away. Thanks again for the reply.
Scott
Scott, this is pretty straight forward. You first need to select Database from the Crystal Reports menu and select Set Datasource Location. On that screen you need to change the database to your application database name. It may show in the list or you may need to use Create New connection to add it. For each table showing in the top panel you will need to locate that same table in the bottom panel and click the Update button so that Crystal Reports is pointing to your application database. Once you have complete this select Database again and select Database Expert. Drill down to the SNote table in your application database on the left panel and click the right arrow to add that table to the report. You will have to go through the dbo\tables layer to find SNote. Once the table is added in the right panel, click the Links tab and connect the table for which you have notes to SNote using the field NoteId from your table and the field NId from the SNote table. If the source table in your report uses a view instead of the actual source table you may need to modify that view or also add the source table to the report as a connection link to the SNote table by connecting the appropriate fields in the view to the source table fields under the Links tab. Once the SNote table has been added and linked, you can go to the report layout and add the sNoteText field to the report body. It is suggested that you add this field in a new detail section and suppress that section if RIPARAM("NotesOn") = "0". That way, records with no note attached do not take up blank space on the report.
You did not indicate what report you wanted to do this with so the above is just a general description of the process.
#1
André Arnaud de Cal...
292,516
Super User 2025 Season 1
#2
Martin Dráb
231,436
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (
