There are a couple of ways to include images within CMS memo fields. The tempting one way is to copy and paste an image that has been inserted into a Word document into the CMS memo field. There are however serious problems with this method. The image is pasted in as RTF which converts the image into its binary code. Hence behind the scenes some 500,000 + numbers are inserted into the memo field per image. This causes serious problems with database size as your mdb will suddenly bloat dramatically. It will also cause problems with Crystal Reports as Crystal gives up when it comes across such a mass of data and any text entered after the image will be dropped from the report. Many other database processes also slow dramatically due to these huge fields.

Hence this is not recommended and some code is included below to identify where you may have images in your memo fields.

The preferred method is to use the LibraryLink module. As CMS7.5 now includes Crystal Report XI, linked images (i.e. using LibraryLink) can be inserted into reports. As there is not a standard name for your LibraryLink catalogue, it is not easy to create standard reports including images. If you want to avail of this, please contact us at cms@esdm.co.uk. More information will appear on this topic soon.

If you insert this code into the SQL Scratchpad (System Manager - Utilities) and run it, it will identify if you have RTF images in any of the CMS memo fields. We would recommend removing these. Be sure to copy ALL the blue text below into the SQL Scratchpad.

SELECT "Sites" as [Table], "Site Description"  as [Field], Sites.SiteName as [Details]
FROM Sites INNER JOIN SitesExtra ON (Sites.SiteCode = SitesExtra.SiteCode) AND (Sites.SiteCode = SitesExtra.SiteCode)
WHERE (((SitesExtra.Description) Like "*\pict*"));

UNION SELECT "Site Description" as [Table], "Heading Code" as [Field], Sites.SiteName & ": " & [PlanText].[HeadingCode] AS [Details]
FROM Sites INNER JOIN PlanText ON Sites.SiteCode = PlanText.SiteCode
WHERE (((PlanText.Text) Like "*\pict*"));

UNION SELECT "Features" as [Table], "Feature Description" as [Field], Sites.SiteName & ": " & [features].[featurenum] & ": " & [features].[featurename] AS [Details]
FROM Sites INNER JOIN Features ON Sites.SiteCode = Features.SiteCode
WHERE (((Features.FeatureDescription) Like "*\pict*"));

UNION SELECT "Objectives" AS [Table], "Vision" AS Field, Sites.SiteName & ": " & [features].[featurenum] & ": " & [features].[featurename] AS Details
FROM Sites INNER JOIN (Features INNER JOIN Objectives ON (Features.SiteCode = Objectives.SiteCode) AND (Features.ObjectiveNum = Objectives.ObjectiveNumber)) ON Sites.SiteCode = Features.SiteCode
WHERE (((Objectives.Description) Like "*\pict*"));

UNION SELECT "ObjectiveStatus" AS [Table], "Condition Details" AS [Field], Sites.SiteName & ": " & [features].[featurenum] & ": " & [features].[featurename] AS Details
FROM Sites INNER JOIN ((Features INNER JOIN Objectives ON (Features.SiteCode = Objectives.SiteCode) AND (Features.ObjectiveNum = Objectives.ObjectiveNumber)) INNER JOIN ObjectiveStatus ON (Objectives.ObjectiveNumber = ObjectiveStatus.ObjectiveNum) AND (Objectives.SiteCode = ObjectiveStatus.SiteCode)) ON Sites.SiteCode = Features.SiteCode
WHERE (((ObjectiveStatus.CurrentCondition) Like "*\pict*"));

UNION SELECT "Project Plans" as [Table],"Project Description" as [Field],  [sites].[sitename] & ": " & [projectplans].[projectcode] & "/" & [projectplans].[projectnumber] AS [Details]
FROM Sites INNER JOIN ProjectPlans ON Sites.SiteCode = ProjectPlans.SiteCode
WHERE (((ProjectPlans.ProjectDescription) Like "*\pict*"));

UNION SELECT "Annual Projects" as [Table],"Annual Project Summary" as [Field],  [sites].[sitename] & ": " & [projectplans].[projectcode] & "/" & [projectplans].[projectnumber] & " - " & [annualprojects].[financialyearstart] AS [Details]
FROM (Sites INNER JOIN ProjectPlans ON Sites.SiteCode = ProjectPlans.SiteCode) INNER JOIN AnnualProjects ON (ProjectPlans.ProjectNumber = AnnualProjects.ProjectNumber) AND (ProjectPlans.ProjectCode = AnnualProjects.ProjectCode) AND (ProjectPlans.SiteCode = AnnualProjects.SiteCode) AND (ProjectPlans.ProjectNumber = AnnualProjects.ProjectNumber) AND (ProjectPlans.ProjectCode = AnnualProjects.ProjectCode) AND (ProjectPlans.SiteCode = AnnualProjects.SiteCode)
WHERE (((AnnualProjects.AnnualSummary) Like "*\pict*"));

UNION SELECT "Annual Projects" as [Table],"Annual Project Report" as [Field],  [sites].[sitename] & ": " & [projectplans].[projectcode] & "/" & [projectplans].[projectnumber] & " - " & [annualprojects].[financialyearstart] AS [Details]
FROM (Sites INNER JOIN ProjectPlans ON Sites.SiteCode = ProjectPlans.SiteCode) INNER JOIN AnnualProjects ON (ProjectPlans.ProjectNumber = AnnualProjects.ProjectNumber) AND (ProjectPlans.ProjectCode = AnnualProjects.ProjectCode) AND (ProjectPlans.SiteCode = AnnualProjects.SiteCode) AND (ProjectPlans.ProjectNumber = AnnualProjects.ProjectNumber) AND (ProjectPlans.ProjectCode = AnnualProjects.ProjectCode) AND (ProjectPlans.SiteCode = AnnualProjects.SiteCode)
WHERE (((AnnualProjects.AnnualReport) Like "*\pict*"));

UNION SELECT "Annual Projects" as [Table],"Work Programme" as [Field],  [sites].[sitename] & ": " & [projectplans].[projectcode] & "/" & [projectplans].[projectnumber] & " - " & [annualprojects].[financialyearstart] AS [Details]
FROM (Sites INNER JOIN ProjectPlans ON Sites.SiteCode = ProjectPlans.SiteCode) INNER JOIN AnnualProjects ON (ProjectPlans.ProjectNumber = AnnualProjects.ProjectNumber) AND (ProjectPlans.ProjectCode = AnnualProjects.ProjectCode) AND (ProjectPlans.SiteCode = AnnualProjects.SiteCode) AND (ProjectPlans.ProjectNumber = AnnualProjects.ProjectNumber) AND (ProjectPlans.ProjectCode = AnnualProjects.ProjectCode) AND (ProjectPlans.SiteCode = AnnualProjects.SiteCode)
WHERE (((AnnualProjects.WorkProgramme) Like "*\pict*"));