Changing the EntryType for Fields in an External Table

Hints, Tips and Tricks

Technical Hints Tips and Tricks that cover customization and development using Sage CRM. API usage and coding are covered.

Changing the EntryType for Fields in an External Table

  • Comments 13
  • Likes

Within Sage CRM it is very easy to link to an external table that exists in another database.  For example you may wish to link to a table such as ap_customer or ap_vendor in Sage 100cloud.  How the link can be created is described in the article "Connecting to an External ODBC database".

You can change the field definitions for columns of the external table within the Administration screens.

Changing the Caption and the EntryType is covered in the documentation.

In the list below the fields VendorType and State display their values in using images.  The web address field is rendered as a hyperlinked URL and the hyperlinked email address field will call Sage CRM's internal email client.

Changing a field to be displayed as a web address or to invoke the internal email client is very straight forward.

The change is just done clicking on the name of the field and then choosing the new Entry Type.  The Entry Type then determines the behaviour of the field in the interface.

Selection Lists

Changing field value to be displayed as a image is a little more complicated.  We must first set the fields up as selection lists and then make some changes to the list definition and images available in the Themes used by Sage CRM.

I am working with a table called ap_vendors.  The field "state" contains standard post office abbreviations for the different American States, e.g. AL, IL, TX etc.  The field "vendortype" contains either "B", "I" or "N" as its value.

The first thing that I did was to change the entry type for both fields to "Selection".

When you change the type you will get a screen that look like this:

For both fields I choose to "Create a New Lookup".  For the "state" field I entered a selection of the 50 states as a proof of concept .

Note:  The Caption Code corresponds to the value that will be held in the database table and the Caption is the translation that will be shown on the screen.

For the "vendortype" field I entered values that are stored in the database with expanded Captions.  

Note:  I have made an assumption that the codes B, I, N stand for "Business", "Individual" and "None".  I may be wrong!

By changing the field to a Selection list, data codes that might be hard for the user to understand now become much clearer within Sage CRM.

It also makes it less likely for users to make errors when entering values into screens when searching or editing.

Changing the List Definition

I need to make change to meta data to allow the fields when included in a list to display a graphic instead of a text value.

This is done in the definition of the List block.

In the image below you can see that the column "state" I have changed to value of "Show Select as gif" to be Yes.

But I need to add the 'gif' images that are to be used into the image folders.

I created "state" and "vendortype" folders under the Choices folder.

C:\Program Files\Sage\CRM\[installname]\WWWRoot\Themes\Img\ergonomic\Choices

Each folder contained the images that would be displayed.  And each image has a name has to be of type "gif" and correspond to the value held in the database. e.g.

C:\Program Files\Sage\CRM\[installname]\WWWRoot\Themes\Img\ergonomic\Choices\vendortype


  • B.gif
  • I.gif
  • N.gif

Note: Each image should be 18x18 pixels.

  • Dear Jeff,

    This post is great, but I have an issue.

    Please consider that the external table field is numeric, and it has two possible values 0 or -1.

    In that case when I access to the fields data of linked database, I cannot change field type in this case.

    In connected view I created a field Yes/No based on 0/1 using case sentence, and defined that field as nchar(40), but when I access the fields data of linked database, also doesn't allow me to change to field type selection. It looks like only existing table text fields can be changed to selection type.

    Is there any way to bypass this issue?

    Thanks & Best Regards,

  • I have been thinking about this.

    You are correct that numeric fields can not be mapped to the entry type 'selection'.

    If the field had been an integer datatype they can be mapped to the entry type 'selection'.

    The numeric field in the external database can be cast or converted in a view to be either a text field (nchar/nvarchar etc) or an integer.  

    You can connect to the view in Sage CRM like a table and then use this view for lists.  I am assuming you need this for display (ReadOnly).

    Once the view field is known to Sage CRM you can change its entry type to selection.

  • Hi Jeff,

    I had tried your suggestion but appear not working. I tried again today (and surely because of IISReset or whatever) now I could change field type to selection. So now it's working nice!

    Thank you very much for your support!

  • Hi Jeff,

    I created a view and added a column called 'Action' with the option "Show Select as Gif" = YES. I Create 3 folders with GIF files inside on the paths u indicated but none of this seem to work. Do u have a clue of what i'm missing?


    •C:\Program Files\Sage\CRM\[installname]\WWWRoot\Themes\Img\default\Choices\Action

    •C:\Program Files\Sage\CRM\[installname]\WWWRoot\Themes\Img\color\Choices\Action

    •C:\Program Files\Sage\CRM\[installname]\WWWRoot\Themes\Img\neutral\Choices\Action

  • Do you get the red 'X's to indicate the image has not been found?

  • I have successfully added a custom table and have a WWW URL file path link similar to above which opens a PDF in another tab when I click on it. I would prefer to display a GIF (such as the paperclip) instead of the full URL as it is so long. So I want the same GIF for all the values. Is there a way to do this please? I could add a second field with a fixed value "Click Here" and display that but I don't know how to make this field clickable so that it invokes my WWW URL of the file path for that row. I am using 2017 R2.

    Thanks, Mark.

  • Is this the type of thing that you mean?

  • Hi Jeff and thanks for responding. No, sorry that's not what I meant. What I want to do is just show a GIF of the paperclip (for example) against each row of my data and when that GIF is clicked on I want to open the WWW URL in my filepath field for that row. This opens a PDF related to that row. So the GIF does not change but the filepath does. I want to hide the filepath as it takes up so much space on the list view and its actual value is not important to the user. Is this possible?

  • Mark

    Ah ha!  It is possible to control the hyperlinking of the list.  This article discusses the basic 'how to get to the column or row'

    A slightly more detailed article looks at controlling the tooltip of the cell

    You only need to find the cells in a column and change the text in between the anchor to point to your gif  <a>'this is the thing changed</a>

  • Thanks Jeff. I looked at those articles and some others I had found, discussed with some colleagues and I then came up with this solution which replaces the filenames with the paperclip icon. It uses a custom jump pointing to paperclip.js saved in the wwwroot/js/custom directory:

    crm.ready(function () {

    var contextInfo = crm.getArgs();

    //Set up Filenames List

    if (contextInfo.Act == "115") {

    //Identify the Filename Column

    var columnFilename = crm.grids(0).columnIndex("filename");

    //Loop through each Filename that is not blank

    crm.grids().filterWhere("filename", "ne", "").exec(function (index, key) {

    var rowIndex = $(key).parent().index() - 1;

    //get the html of the Filename cell

    var columnFilenameHTML = this.getCellHtml(rowIndex, columnFilename);

    // modify the HTML so the entry points to the system Paperclip image (T.gif)

    var strImage = columnFilenameHTML.substring(0,columnFilenameHTML.indexOf("EWAREVISITS")+13) + "<img alt=" " />" + columnFilenameHTML.substring(columnFilenameHTML.lastIndexOf(""));


    this.setCellHtml(rowIndex, columnFilename, strImage);




  • The strImage definition does not show correctly in my post, not sure if the number of quotes is throwing the web form. Here is it again:

    var strImage = columnFilenameHTML.substring(0,columnFilenameHTML.indexOf("EWAREVISITS")+13) + "<img alt=" " />" + columnFilenameHTML.substring(columnFilenameHTML.lastIndexOf(""));

  • It is the web form - I'll try again, the "img src" = should be followed by \ then " then " not just the one " shown here:

    var strImage = columnFilenameHTML.substring(0,columnFilenameHTML.indexOf("EWAREVISITS")+13) + "<img alt=" " />" + columnFilenameHTML.substring(columnFilenameHTML.lastIndexOf(""));

  • Thanks.

    If you send me the raw script I would love to create a post on your behalf.