Formatted fields in SQL Fragments

[Geert Bellekens]

In v12.1 it should be possible to use formatted notes in SQL fragments.
According to the help it says:

It is possible to force a field to be processed as formatted notes, by providing a column in this format:
    custom >
     {Name}
     <fieldname>-Formatted     (for .eap (JET) repositories, or <fieldname>.Formatted for other types of repository)
     < custom
In your custom SQL Query statement, you must use an alias matching the template field name; for example (in MySQL):
     SELECT ea_guid AS CLASSGUID, Object_Type AS CLASSTYPE, Name, Note as "Note.Formatted" FROM t_object
     Where Object_ID=#OBJECTID#

I tried to do that on .eap file, but it doesn't seem to work:
template:

Code: [Select]

custom>
{Description-Formatted}
<customCustom Query:

Code: [Select]

select c.[SourceCard] AS SMultiplicity, o.[Name] AS Source, c.[Name] AS ConnectorName,
c.[DestCard] AS TMultiplicity, ot.[Name] as Target, c.[Notes] AS [Description-Formatted]
from (( t_object o
inner join t_connector c on c.[Start_Object_ID] = o.Object_ID)
inner join t_object ot on ot.Object_ID = c.[End_Object_ID])                      
where o.Object_ID = #OBJECTID#
and c.[Connector_Type] in ('Association', 'Aggregation')
The in the output I only get the text Description-Formatted, not the formatted notes I was after.

A) what am I doing wrong?

B) Who decided to use a . notation that isn't compatible between JET and the SQL databases, and in doing so making my templates dependent on the database type?

C) Why is the documentation partially for Jet and partially for MySQL? Is it too much to ask to make a complete example for both?

Geert

[albert]

Hello Geert,

Any news about this problem ? Is it solved in .13 ?

Best regards,
Albert

[Eve]

Code: [Select]

select c.SourceCard AS SMultiplicity, o.Name AS Source, c.Name AS ConnectorName,
c.DestCard AS TMultiplicity, ot.Name as Target, c.Notes AS 'Description-Formatted'
from (( t_object o
inner join t_connector c on c.[Start_Object_ID] = o.Object_ID)
inner join t_object ot on ot.Object_ID = c.[End_Object_ID])                       
where o.Object_ID = #OBJECTID#
and c.Connector_Type in ('Association', 'Aggregation')
The square brackets don't wrap quotes around unknown terms.

[jop]

Hi Simon
I can't get this to work in .13

Template:

Code: [Select]

custom >
{ObjectNotes-Formatted}
< custom

SQL query:

Code: [Select]

SELECT TO1.Name AS Source, t_connector.Notes AS 'ObjectNotes-Formatted'
    FROM ((t_connector
           LEFT JOIN t_object AS TO2
                 ON t_connector.End_Object_ID=TO2.Object_ID )
           LEFT JOIN t_object AS TO1
                 ON t_connector.Start_Object_ID=TO1.Object_ID)
WHERE t_connector.End_Object_ID = #OBJECTID#
When I try to generate doc on this I get an error message.   Is it supposed to work?

thanks

[Geert Bellekens]

This works for me on an SQL-Server environment:

Template:

{Description.Formatted}

Query:

Code: [Select]

select c.[SourceCard] AS SMultiplicity, o.[Name] AS Source, c.[Name] AS ConnectorName,
c.[DestCard] AS TMultiplicity, ot.[Name] as Target, c.[Notes] AS [Description-Formatted], isnull(v.[Value],'TBD') AS Versioned, isnull(ts.[Value],'TBD')  AS Timesliced
from (((( t_object o
inner join t_connector c on c.[Start_Object_ID] = o.Object_ID)
inner join t_object ot on ot.Object_ID = c.[End_Object_ID])
left join t_connectortag v on (v.[ElementID] = c.[Connector_ID]
                                and v.[Property] = 'Versioned'))
left join t_connectortag ts on (ts.[ElementID] = c.[Connector_ID]
                                and ts.[Property] = 'Timesliced'))                       
where ot.Object_ID = #OBJECTID#
and c.[Connector_Type] in ('Association', 'Aggregation')
Geert

[Eve]

Hi Simon
I can't get this to work in .13

Template:

Code: [Select]

custom >
{ObjectNotes-Formatted}
< custom

SQL query:

Code: [Select]

SELECT TO1.Name AS Source, t_connector.Notes AS 'ObjectNotes-Formatted'
    FROM ((t_connector
           LEFT JOIN t_object AS TO2
                 ON t_connector.End_Object_ID=TO2.Object_ID )
           LEFT JOIN t_object AS TO1
                 ON t_connector.Start_Object_ID=TO1.Object_ID)
WHERE t_connector.End_Object_ID = #OBJECTID#
When I try to generate doc on this I get an error message.   Is it supposed to work?

thanks

I can't see anything that would prevent it from working, but I'm not a sql compiler and you haven't given details about the error or even mentioned what database you're using. What
I can say is that your SQL is unnecessarily complex (TO2 isn't used at all),

[jop]

Geert, thank you so much for taking the time to post that, the square brackets totally worked for me.

And thanks Simon M for the sql tips, obviously it's not my forte.

[jaimecerda]

Hello, I also have been trying to use this feature in Version 13.5.  Does anyone know if it has been fixed or if I should try another way?
Thanks!

[Geert Bellekens]

It works, but you have to tailor your query to the database you are using.

Geert

[jaimecerda]

I'm using the EAP file as repository, from all that i've read it seems this is not supported.  Have you've been able to make it work with eap repositories?

[Geert Bellekens]

Yes it works in .eap files.

I think you needed to use something like

Code: [Select]

o.Note as [note-formatted]in the query and use {note.formatted} in the template.

Geert

[jaimecerda]

It worked!!!
Thanks Geert, you rock!