Login / Register  search  syndication  about

          George Jensen's Blog

George Jensen on Reporting, Business Intelligence, Crystal Reports, Visual Analyzer, and more.

Working with the Picture Object in Crystal

We have had several inquires lately on how to dynamically change images in a report based on report data.  

For example you have pictures of products that you want to display in a report.  One way to accomplish this is to add an image with each product in a huge segmented report and have Crystal sort things out through suppression rules.  This is a valid solution but the report is going to become very unmanageable as your inventory grows and than changes.  The best case is to store the picture in a central folder and call the image into your report based on the data.

With SalesLogix you have the ability to store attachments to records which can be be documents, spreadsheets, pdf's, or pictures.  This provides a perfect area for managing images.  But - the standard Products area in SalesLogix does not have the attachment capability.

I came up with an easy work around that will allow you to create a Product Report that displays the correct picture that you maybe able to relate to a report that you are working on.

I am using the evaluation database for a SalesLogix version 7.5.4 system.  This database is owned by a company called "Phoenix Computers, Inc."  and comes with product records we can use for our example.

The first thing I need is some pictures of some of the products.  I searched on Google Images for photos of a some common products that are already in the database.  I found images for the  "BlackBerry", the "Dell Latitude", the "Dell Optiplex", and the "MacBook Pro".  The pictures are all stored as .jpg

I went to the Phoenix Computers, Inc account record and added the pictures to the Attachment Tab.  You could also create a dummy account for this purpose if you want but whereever you decide to put the attachment we will need the record name.

 

Above are the images in the attachment tab, notice the file description (Attachment column) matches the Product Name.

Now we can build the report.  Open up Crystal and make your connection to the database.  In the base report I am just going to pull in the PRODUCT.NAME and PRODUCT.FAMILY fields.  For the purpose of this report I am going to set the Record Selection formulas to {PRODUCT.NAME} in ["BlackBerry","Dell Latitude","Dell Optiplex", "MacBook Pro"] which are our target products. 

 I am going to add two sub reports. The first is for pricing which will use the PRODUCTPROGRAM table.  I will link the sub report to the base report using the PRODUCT.PRODUCTID (base) and PRODUCTPROGRAM.PRODUCTID (sub report).  For this example I brought in the PROGRAM and PRICE fields. 

 

The second sub report will be the one that contains the picture.  The ATTACHMENT table is the primary table for this sub report and stores all attachment data for the entire database so to help narrow down the data I will include the ACCOUNT table. Using the ACCOUNT.ACCOUNT field we can create a record selection formula that limits the Attachments to the Account record where I am storing the images.  Here is that formula:

 {ACCOUNT.ACCOUNT} = 'Phoenix Computers, Inc.'

The next step in this report is to make formula field that provides the path to the Images using the data in the Attachment table.  The ATTACHMENT.DESCRIPTION field gives us the name of the image as we entered and is the link we will use to match to the image to the records in the Product table.  The other field is the ATTACHMENT.FILENAME fields which provides the unique name SalesLogix gave the image when we entered it into the database.  The images are really stored in a shared foler and the path for the folder is set in the SalesLogix Administrator under the Systems/Office tab.  If you double click on your office name in the list you will get the following screen.

At the bottom you find the Attachment Folder path.  You will also need the name of the server where the folder is located.  For my example I have a server name of w2003-dts.  With this information I am able to create a formula field called "Path".

The formula inside the field is (using my example) : '\\w2003-DTS\Sage_SalesLogix_Demonstration_Tools\Demo_Database\Documents\'+{ATTACHMENT.FILENAME}

Next go to the Crystal Insert menu and select "Picture" from the list that appears.  Immediately a search box comes up which will allows you to search for a default image.  This default will display when the report cannot find an image at the path you provided.  I like to use a screen shot with a white background and the text of "Picture Not Available". 

Place this picture in the details section of the sub report.  Than, suppress all other section of the report.  Here is what my "picture" sub report looks like so far.

 

Now, to get the correct picture to display based on the product name listed in the report, right click on the image and select "Format Graphic" from the list that appears.  Navigate to the Picture Tab and find the field labeled "Graphic" toward the bottom of the tab and select the button to the right, the Formula Workshop Editor appears.

Double click on the formula field labeled "Path", Save and Close the editor.

Finally we need make the link to the base report.  If you remember we described each attachment with name of the product it represents.  This allows us to match the data in the sub report to the main report using PRODUCT.NAME (Base) to the ATTACHMENT.DESCRIPTION (Sub-Report). 

When I run my report I see the images are displaying next to the product information.

 

So, by using just one picture object we are able to call different images and match them to correct product.

Very cool.  Thanks for reading!

geo

 

What's This?
  
Bookmark and Share

About George Jensen

   George Jensen is a Senior Developer for Customer FX Corporation.


Related Content
   Infor CRM (formerly Saleslogix) Web- Dependency Lookup OnChange Event Not Working Correctly
In the Saleslogix 8.1 web client the dependency lookup still has a problem with the OnChange event. 
Posted on Nov 26, 2014 by Kris Halsrud to Kris Halsrud's Blog
 
   Confirming Actions With an "Are you sure?" Prompt in Infor CRM (Saleslogix)
I'm not a huge fan of bugging end users. I figure, if an end user clicked something, he or she meant
Posted on Nov 18, 2014 by Ryan Farley to Ryan Farley's Blog
 
   Infor CRM (formerly Saleslogix) Statement of Direction
Learn more about the future of Infor CRM. Recently I received my login information for the Infor Partn
Posted on Nov 13, 2014 by Brianna Ojard to The Inbox
 
   Xbar for InforCRM (formerly SalesLogix) Installation Issue
After installing Xbar 1.1 on a Windows 2008 R2 server the Xbar application was not available in Outlook 2
Posted on Nov 11, 2014 by Mark Duthoy to SalesLogix Support
 
   Programmatically Using Security Roles and Secured Actions in Infor CRM (Saleslogix)
In most places in the Infor CRM (Saleslogix) web client, it's pretty easy to use security roles. You
Posted on Nov 07, 2014 by Ryan Farley to Ryan Farley's Blog
 
Comments

No Comments

Leave a Comment

(required)  
(optional)
(required)  
Add
All contents Copyright © 2014 Customer FX Corporation
Customer FX Corporation
2324 University Avenue West, Suite 115
Saint Paul, Minnesota 55114
Tel: 800.728.5783

  Follow @CustomerFX on twitter
Follow the best news, tips, and articles
  Subscribe to Customer FX on youtube
Watch SalesLogix tutorial videos from Customer FX
Login / Register