proper model for side storage in SharePoint?

Question

We'd like to build a custom type - or simply add custom columns to an existing type - that can be displayed in regular lists (or document library) without requiring a special page or grid view. This column retrieves complex/structured data from an external service and exposes it as the column's "value". I realize that this will prevent some column features from working properly (ie. you can't sort on it, search over it, etc) but that's okay so long as the column can integrate into the display UI.

As a relative newcomer to SharePoint, I'm having trouble building a model or determining the "best practices" for such a field. I've seen plenty of articles about building calculated/computed fields, but there are a few basic issues with my usage of it:

1. I can't make a service call for each item in a list view (and for perf reasons, wouldn't want to) so if I want to give this column a list view, I need to store a "cached" copy of the service data. But SP only allows me to store a single text value in my column, without any easy way to keep structured data. I could serialize it in some way, but can't figure out how I could possibly define a RenderPattern that correctly split out the structured data for display. Is there a better way to store structured data in a custom field? I suppose that I should just store the little bit of text I'll be displaying in the list, though that does prevent me from storing other useful info in the column value.

2. In the item's full view, it's okay to use a service call to retrieve the full structured information. But I'd like to use a local cache to store the data I've already retrieved, for performance reasons and to remove the dependency on the external service. Presumably this should even be persistent - but where should I store it? Is there already a good persistent cache for SP?

3. Assuming the cache from #2 is completed, I need a primary key to store/retrieve the cached data for a particular row in the list. But as far as I can tell, list items don't have such a key - just a list ID (guid) and ordinal position. That means that deletion of a row above could break what used to be my "invariant" key. Is there a better ID I can use for list items?

Any help or pointers are much appreciated - thanks! Steve

Answer 1

I've never actually tried this myself, but have you tried using the BDC to populate items from your external data store into a list, and reference your BDC list as a lookup column in your second list?

Check out this BDC Blog. It talks about how to tie BDC data to a list. Presumably, you'd be able to use that as a lookup value in your second list.

If you're unfamiliar with the BDC, take a look at this article series for more information on what it is, and how you can leverage it.

Cheers, Adam

Answer 2

You could create a custom type (like this) that stores the cache for the structured data in the list as an xml and render it with a custom control.

The issue is how are you going to update the cached data in the list regularly.

Creating a custom field control to go with the type will allow you to customise the input and rendering. You can create quite complex field input screens in this way.