CODEWITHSUNDEEP
ms-accessenterprise-architect
vascobnunes
vascobnunes

Reputation: 213

Missing text from <memo> field in table in report

I have multiple models in Sparx Enterprise Architect in file-based, i.e. using MS Access.

I'm using a custom template to populate a table with data from object's properties, including some with <memo> fields.

This is the query I'm using in the template fragment:

SELECT 
    obj.object_id,
    obj.Stereotype,
    objp.Property as Prop,
    switch(objp.Value = '<memo>', objp.Notes, objp.Value LIKE '{*}', 
           NULL, 1=1, objp.Value) AS Val,
    (SELECT tobj2.ea_guid & tobj2.Name 
     FROM t_object tobj2 
     WHERE tobj2.ea_guid = objp.Value) AS [Obj-Hyperlink]
FROM 
    t_object obj 
INNER JOIN 
    t_objectproperties objp ON (obj.object_id = objp.object_id)
WHERE 
    obj.object_id = #OBJECTID# 
    AND obj.Stereotype = 'Data-Stream' 
    AND objp.Property NOT IN ('isEncapsulated')
ORDER BY 
    objp.Property ASC;

enter image description here

I found that the when these fields are longer than 249 chars I get an error message when generating the reports and the cell in the generated table is simply empty. This is also noticeable with a query:

enter image description here

This is the error I'm getting:

Error Processing xml document: an invalid character was found in text context"

Is there any workaround to enable including the <memo> fields' data with more than 249 chars in the reports?

Any help is much appreciated.

Upvotes: 1

Views: 181

Answers (1)

vascobnunes
vascobnunes

Reputation: 213

I've found a workaround for this by joining two queries with a "Union all". The first query will handle the non-memo fields with the switch function and the second one the memo fields without the switch function.

    select 
     obj.object_id, 
     obj.Stereotype, 
     objp.Property as Prop, 
     objp.Notes AS Val, 
     (
      SELECT 
       tobj2.ea_guid & tobj2.Name 
      FROM 
       t_object tobj2 
      WHERE 
      tobj2.ea_guid = objp.Value
     ) AS [Obj-Hyperlink] 
  from 
     t_objectproperties objp 
     left join t_object obj on (obj.object_id = objp.object_ID) 
  where 
     obj.object_id = #OBJECTID# 
     AND obj.Stereotype = 'Data-Stream' 
     AND objp.Property NOT IN ('isEncapsulated') 
     AND objp.Value = "<memo>" 
  UNION ALL 
  SELECT 
     obj2.object_id, 
     obj2.Stereotype, 
     objp2.Property as Prop, 
     switch(
      objp2.Value LIKE '{*}', NULL, 1 = 1, objp2.Value
     ) AS Val, 
     (
      SELECT 
        tobj2.ea_guid & tobj2.Name 
      FROM 
        t_object tobj2 
      WHERE 
        tobj2.ea_guid = objp2.Value
     ) AS [Obj-Hyperlink] 
  FROM 
     t_object obj2 
     INNER JOIN t_objectproperties objp2 ON (obj2.object_id = objp2.object_id) 
  WHERE 
     obj2.object_id = #OBJECTID# 
     AND obj2.Stereotype = 'Data-Stream' 
     AND objp2.Property NOT IN ('isEncapsulated') 
     and objp2.Value <> "<memo>" 
  order by 
     3 asc;

Thanks a lot @geertbellekens for your comment which was crucial to find this solution.

Upvotes: 1

Related Questions