Calculate a new content type field

Question

This is my first post ever here after many years of finding solutions to my issues. I'm rather new to SharePoint, though not a complete beginner.

Here is what I want to achieve:

• I want to create a list, which will be filled by users
• This list will contain an Identifier text field, we'll call it strId
• This list must contain a field, that's a value retrieved from an oracle database using strId as a parameter

I already did some BDC Models but this seems to be different, as the list elements do not come from a database, just one column does.

I thought about creating a content Type with 2 site columns in it, one with the strId and the other would do the calculation, but I can't seem to be able to do it.

Can anyone help on this matter ?

Answer 1

I would suggest the following:

• create external content list : (eg: T_Investigator)
• create new custom list (eg: ExternalDisplay)
• add your strid as a single line of text
• add the external content list as a lookup (eg: external_id), and add the field you wish to see as well (eg: firstname, lastname)
• create a sharepoint solution with an eventreceiver with onAdding/onUpdating for the ExternalDisplay list. On adding / updating, you can overwrite the 'external_id' field with the new value in your strid

code:

using Microsoft.SharePoint;

namespace SOEventReceiver.ExternalTest
{
    public class ExternalTest : SPItemEventReceiver
    {
        public override void ItemAdding(SPItemEventProperties properties)
        {
            string strid = (properties.AfterProperties["strid"] ?? string.Empty).ToString();
            if (!string.IsNullOrWhiteSpace(strid))
            {
                properties.AfterProperties["external_id"] = strid + ";#" + strid;
            }
            base.ItemAdding(properties);
        }

        public override void ItemUpdating(SPItemEventProperties properties)
        {
            string strid = (properties.AfterProperties["strid"] ?? string.Empty).ToString();
            if (!string.Equals(strid, properties.ListItem["strid"] ?? string.Empty))
            {
                properties.AfterProperties["external_id"] = strid + ";#" + strid;
            }
            base.ItemUpdating(properties);
        }
    }
} 

with elements.xml pointing to the url of your list

<?xml version="1.0" encoding="utf-8"?>
<Elements xmlns="http://schemas.microsoft.com/sharepoint/">
  <Receivers ListUrl="Lists/ExternalDisplay">
      <Receiver>
        <Name>ExternalTestItemAdding</Name>
        <Type>ItemAdding</Type>
        <Assembly>$SharePoint.Project.AssemblyFullName$</Assembly>
        <Class>SOEventReceiver.ExternalTest.ExternalTest</Class>
        <SequenceNumber>10000</SequenceNumber>
      </Receiver>
      <Receiver>
        <Name>ExternalTestItemUpdating</Name>
        <Type>ItemUpdating</Type>
        <Assembly>$SharePoint.Project.AssemblyFullName$</Assembly>
        <Class>SOEventReceiver.ExternalTest.ExternalTest</Class>
        <SequenceNumber>10000</SequenceNumber>
      </Receiver>
  </Receivers>
</Elements>

Sharepoint automatically makes the changes since the Lookupfield value has changed, and the external columns are displayed.

The advantage here would be that sharepoint does the main magic, it also doesn't matter which kind of lookup list is behind this (external/sharepoint).

A disadvantage is that the external_id field could also be changed, which wouldn't update the strid field, and you would get inconsistent data. This you could however, also block inside the updating statement