Once you have been working with Sage CRM for a little while you will notice that the primary keys (unique ID fields) have been suppressed from the interface. By that, I mean that you can't refer to them directly in Lists and Screens.

This is normally fine as the keys job is only to allow Sage CRM to key track of everything but those keys don't normally have any value to a business as they automatically increment to ensure integrity. Every now and then, however, a customer may want the Opportunity ID or another primary key to be displayed as they are used to treating this (from perhaps a previous system) as actually an 'Intelligent' field that imparts a business purpose.

I have never felt very comfortable about doing that personally and that is why the Case and Opportunity tables make use of the 'StoredProc' datatype to generate the alternate keys. But that is deviating from the subject.

So how could the customer get to see the Opportunity ID in a list or screen? See the screen shot below:

To get a unique ID to display in a list (and indeed stop it display with a thousand separator) all you need to do is change the list blocks underlying view to include an alias column for the uniqueid. In the example above I changed the view vListOpportunity that is used to get the data for the OpportunityList screen.

[code language="sql"]
oppo_opportunityid as oppo_displayid
[/code]

I actually converted the integer into a string because I wanted to avoid thousand separators appearing in the number.

[code language="sql"]
CONVERT(varchar, oppo_opportunityid) AS oppo_displayid
[/code]

The new derived field can be treated like any derived field. In my example I used the "Derived Field" component that can be downloaded from the community. You will need to be a member of the Developer Program to access that component. The component allowed me to add additional metadata to make sure that the field looked just as I wanted it.

But as soon as the view with the new derived field is saved then the field is available in the list block.

This derived field is then available in reports and anywhere else you wish to use it.