SP2010 Relational Lists - User Objects show "#VALUE!" instead of value

Question

I know I'm just doing something wrong here but after I create a SPFieldLookup object, attaching to content type, and then adding to list, all looks fine. I then add an item to the parent, which in turn creates the child item adding the required data for association purposes. When I try to view the child item i get the following error

I use the correlation id to view the specifics...

Failed to cache field with id"{56c7f784-102c-4039-a1fe-2b4749095f36}", overwrite=0
   Leaving Monitored Scope (EnsureListItemsData). Execution Time=30.0065
    System.Runtime.InteropServices.COMException: Cannot complete this action.  Please try again.   
     at Microsoft.SharePoint.Library.SPRequestInternalClass.GetListItemDataWithCallback2(IListItemSqlClient pSqlClient, String bstrUrl, String bstrListName, String bstrViewName, String bstrViewXml, SAFEARRAYFLAGS fSafeArrayFlags, ISP2DSafeArrayWriter pSACallback, ISPDataCallback pPagingCallback, ISPDataCallback pPagingPrevCallback, ISPDataCallback pFilterLinkCallback, ISPDataCallback pSchemaCallback, ISPDataCallback pRowCountCallback, Boolean& pbMaximalView)    
     at Microsoft.SharePoint.Library.SPRequest.GetListItemDataWithCallback2(IListItemSqlClient pSqlClient, String bstrUrl, String bstrListName, String bstrViewName, String bstrViewXml, SAFEARRAYFLAGS fSafeArrayFlags, ISP2DSafeArrayWriter pSACallback, ISPDataCallback pPagingCallback, ISPDataCallback pPagingPrevCallback, ISPDataCallback pFilterLinkCallback, ISPDataCallback pSchemaCallback, ISPDataCallback pRowCountCallback, Boolean& pbMaximalView)

Basically I created the SPFieldLookup as normal with AllowMultipleValues = false. I then add/associate all of my secondary fields that I want to view. This has also always worked and after viewing the Children list items and the associated related fields of the Parent, all of the fields of type "User" and "Note" w/ RichText=true come back as "#VALUE!". The parent contains the correct information but the children only see the fields of type "Text" and "DateTime" correctly. The ones in question always show the #VALUE!. I'm sure it would display other "single value" field types as well but those are my only examples that I am using at this point.

So I tried setting the secondary fields I associate to the SPFieldLookup with the AllowMultipleValues = true. This is when I started receiving the error message and the field id in the error matches the SPFieldLookup that I have created. So my question is, setting all I've said aside for a sec, how do I get children list items to view parent data of type "User" or possibly of type "URL" without the calculation error of #VALUE!?

          foreach (KeyValuePair<string,string> _columnSet in _secondaryColumnDict) {
            string _secondaryColumn = _impWeb.Fields.AddDependentLookup(_columnSet.Key,_primaryColumn.Id);
            SPFieldLookup _secondaryCol = (SPFieldLookup)_impWeb.Fields.GetFieldByInternalName(_secondaryColumn);

            _secondaryCol.LookupField = _columnSet.Key;
            _secondaryCol.Title = _columnSet.Value;
            _secondaryCol.AllowMultipleValues = true;
            _secondaryCol.AllowDeletion = true;
            _secondaryCol.ShowInListSettings = true;
            _secondaryCol.ShowInVersionHistory = true;
            _secondaryCol.ShowInViewForms = true;
            _secondaryCol.ShowInNewForm = true;
            _secondaryCol.ShowInEditForm = true;
            _secondaryCol.ShowInDisplayForm = true;
            _secondaryCol.Update(true);
          }

Answer 1

You need to define a relationship. Please review the Relationship section of your column settings and select Enforce relantionship behavior check box and select restrict delete or cascade delete. Hope it helps!