RTF-Report - Custom SQL - Hyperlink in Notes-field

[Jonathan Lubach]

Hi,

for excluding reasons I have added a Custom SQL template fragment to my overall template. In this fragment I use the "Custom" section with corresponding to the SQL query.

In the custom section I use a field "CustomNotes" receiving the values from the query "SELECT notes from t_object where..."

I have the following problem using this technique:
If the notes contain a hyperlink to another element, in the database this looks like this:

Code: [Select]

This is a note with <a href="http://$element://{51E2DB11-6109-4ca7-94AA-
DA0345CE39C2}"><font color="#0000ff"><u>hyperlink</u></font></a> to another element.

Using the notes in a normal "Pkg" or "Element" section will format this in a correction way:
This is a note with hyperlink to another element.

Using the "Custom" section does only print the text right from the database field:
This is a note with <a href="http://$element://{51E2DB11-6109-4ca7-94AA-
DA0345CE39C2}"><font color="#0000ff"><u>hyperlink</u></font></a> to another element.

Is there any way to get around this misformatting?

Thanks!

Jonathan

[OpenIT Solutions]

Hi,

In custom SQL fragment you need to name the column 'note' or 'notes' - sorry can't remember which of the top of my head....

Regards,

Jon.

[Jonathan Lubach]

Hi Jon,

thanks for your reply. I tried this in my template but it does not work.

When using "t_package.Notes as Notes" the behaviour is equal to that described in my first post.
When using "t_package.Notes as Note" I get an error message from the JET engine saying there are reserved key words in the query.

I don't know if I made clear enaugh: I'm using the option "Custom Query" option in the "Document options" of the template creator.
When using this I have to use the "Custom"-section from the section selector.
In the custom section I cannot use the standard fields provided, e.g. let's say the "Pkg" or "Element".

I have to define my own fields and use them. E.g.:

Code: [Select]

select t_package.Name as CustomPackageName, t_package.Notes as CustomPackageNote, t_package.package_id from t_package
where package_id = #PACKAGEID# and package_id in
(select package_id from t_object where package_id = #PACKAGEID#
and object_type = "requirement")
Thanks!

Jonathan

[OpenIT Solutions]

You get an error with:

select t_package.Name as CustomPackageName, t_package.Notes as Note, t_package.package_id from t_package
where package_id = #PACKAGEID# and package_id in
(select package_id from t_object where package_id = #PACKAGEID#
and object_type = "requirement")

[Jonathan Lubach]

Hi,

I think your reply was a question?  

Yes, I get the following error from the JET-engine (I hope I found the correct translation, the message is displayed in german):

"Run-time error '3141': The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect."

If I replace

Code: [Select]

t_package.Notes as Noteby

Code: [Select]

t_package.Notes as MyNoteit works.

Jonathan

[OpenIT Solutions]

Does it work if you use ....

select t_package.Name as CustomPackageName, t_package.Notes as [Note], t_package.package_id from t_package
where package_id = #PACKAGEID# and package_id in
(select package_id from t_object where package_id = #PACKAGEID#
and object_type = "requirement")

[Jonathan Lubach]

Hi Jon,

just quickly executed this search in the model search and the result was:
The notes column is not visible :-/

At the moment I do not have the possibility to try this way in a template fragment...

I will come back if I tried it.

Thanks.

Jonathan

[KP]

just quickly executed this search in the model search and the result was:
The notes column is not visible :-/

There is a View Notes command in the toolbar for the Model Search. It gives you a choice of Hide Notes (which is default), Notes Preview, or Full Notes.

[Jonathan Lubach]

Hi KP,

thanks for the answer; I assume that is the difference between a custom-named column (e.g. "t_package.Notes AS MyCustomNotes") and the special "[Note]" column?

I will try that as soon as possible in my report fragment template.

Thanks!

Jonathan

[Henrik Possung]

I think I have found a workaround for this. I filter the elements I want with my SQL query  like this:

SELECT t_object.Name AS ElementName  
FROM t_object
WHERE …

Then in the custom field I use the ElementName, but instead of a custom field for notes, I use the generic “Element.Notes” (cut from some other template) :

custom >
{ElementName}      
{Element.Notes}
< custom

I’m not sure this solves you problem, but it seems to work fine in my case.

[Henrik Possung]

I just discovered that this is only valid if #OBJECTID# is not null, that is, if your model template are linking to a custom fragment from the element. In my case I can get the element notes, but not the package notes, doing like this.

Model Template:
package>
element>
{myCustomFragment}
<element
<package

myCustomFragment:
custom >
{ElementName}      
{Element.Notes}
< custom

In your case, you will get the #PACKAGEID# and therefore can get the  Pkg.Notes

Model Template:
package>
{myCustomFragment}
<package

myCustomFragment:
custom >
{PackageName}      
{Pkg.Notes}
< custom