Wednesday, June 15, 2011

Sharepoint LookUp field issue when created using List Template


Usually when we create a list using a "List Template". The lookup field doesnot take the reference. The blog will show you how to reproduce the issue and resolve it.


How to produce this issue.



  • We have created 2 lists "Gender" and "Actors". Gender is going to be a lookup field in actors. After filling some data both the list will look like shown below.



  • Save both list as a template with the content included. In this current example, i have given the names as "Template Gender" and "Tempate Actors".

  • Download the stp files from the Site Settings > List Templates.

    • Create another site. Upload the tempates Site Settings > List Templates.

    • If you don't want to create another site than delete both list from current site.



  • Once the template gets added. Use the template to create a list. In the below given images "Gender" List comes up fine but "Actors" List is not having anything in the "Gender" column field.


Solution



  • Download the "Template-Actors.stp" file from "List Templates".

  • Rename the file and change the extension of the file from ".stp" to ".cab".

  • Once you have the extension as ".cab", extract the "Manifast.xml" file.

  • Get the List ID of newly created "Gender".

  • Replace the old value List Id in Lookup of Gender to New List ID

    • Old

    • New



  • After making the changes save the xml file.

  • To recreate a CAB file open "Microsoft Visual Studio". Create a "CAB Project". In this case i have created "Template-Actors".(We can also use makecab.exe instead of visual studio.)

  • Add the file Manifast.xml to the project.

  • Build the cab project. Go to debug\bin folder you will find "Template-Actors.cab".

  • Rename the file to "Template-Actors.stp".

  • Upload the file to "List Template".

  • Create a list using new template.

  • The gender selections will now show up.


23 comments:

  1. Thank you for detailed explaination for resolving the issue :)
    great post..

    We can resolve the GUID issue in some other way too.Instead of giving explicit GUID's to the list property
    we can give Lists/ListName which will make the template independent of GUID incase we have to use the same template on different sites and the ListName would remain the same everywhere :)

    Thanks and Regards

    ReplyDelete
    Replies
    1. How Would You Do That Please, Im In A Scenario In Which I Have To Create Lists Programmatically From A Template With Lookup Field.

      Thanks In Advance,

      Delete
    2. To create a list programatically is very simple. You add a FeatureReceiver to the project. In the feature activation event you can create the list programatically. The below given code should help you out.

      public override void FeatureActivated(SPFeatureReceiverProperties properties)
      {
      SPSecurity.RunWithElevatedPrivileges(delegate
      {
      SPWeb _web = ((SPSite)properties.Feature.Parent).RootWeb;

      _web.AllowUnsafeUpdates = true;
      SPListCollection lists = _web.Lists;

      lists.Add("Sample", "", SPListTemplateType.GenericList);

      SPList _list = _web.Lists[ListName];
      _list.OnQuickLaunch = true;
      _list.Update();

      #region Lookup - RelatedID

      SPList _RelatedList = _web.Lists.TryGetList("Sample Related List");
      if (_RelatedList != null)
      {
      string _RelatedColumnName = _list.Fields.AddLookup("Title", _RelatedList.ID, true);
      SPFieldLookup _RelatedColumn = (SPFieldLookup)_list.Fields.GetFieldByInternalName(_RelatedColumnName);

      _RelatedColumn.LookupField = _RelatedList.Fields["ID"].InternalName;
      _RelatedColumn.Indexed = true;
      _RelatedColumn.RelationshipDeleteBehavior = SPRelationshipDeleteBehavior.None;
      _RelatedColumn.Update();
      }

      #endregion

      #region Default View
      SPView _defaultView = _list.DefaultView;
      _defaultView.ViewFields.Add("RelatedID");
      _defaultView.Update();
      _list.Update();
      #endregion
      });
      }

      Delete
  2. Yes we can do that but this was just when I started on SharePoint and I was keen to know how this can be resolved so finally got this way to get this.

    ReplyDelete
  3. Thanks for the solution. Here I i resolve the issue by using object model.

    http://sasikumarreddyv.blogspot.in/2012/02/lookup-fields-issue-in-lists.html

    ReplyDelete
  4. Holy Cow, this is very clever, but kind of complicated for a "built-in" feature :)

    Thank you very much for this. Worked like a charm!

    ReplyDelete
  5. Very good post!

    What I found in my case is that the items in the source list have to be exactly the same on both sides (e.g. exporting site collection and importing site collection). Because I had some modifications in the destination site collection, and the lookup values were messed up because the IDs were not the same anymore. So I had to import this list including values too.

    Also for the Lookup Fields wich are site columns the Param value to replace is List and not SourceID as I tried first. This is the value without the accolades. I didn't changed the WebId in my case, so I guess this is less important?

    Thanks

    ReplyDelete
    Replies
    1. @Anonymous - Actually it just came out of my mind to try this one so I did it. I was not sure that I will find this kind of stuff. But coming on your question, I think it should be all good!

      Delete
  6. Good solution!
    But if we create new list and then recreate lookUP field is simple

    ReplyDelete
  7. @Anonymous,

    I guess the data will be lost in that case in the look up. I only did this if someone gives us a list template with data in it.

    Thanks,
    Maulik Dhorajia

    ReplyDelete
  8. Hi Maulik,

    Thank you for publishing this, but when I attempt to make this change (using Sharepoint Server 2010) this does not work for me. My list that I have converted to a list template has two lookup fields.

    I have exported the other two lookup fields and created the new list from it and that works great (and I see the data in them). When I modify the STP of the list that points to the other lists as follows, they do not properly connect and they are showing as blank. Do you have any suggestions? I need to move this content from one site to another and am running out of ideas. Thanks.

    ReplyDelete
  9. Hi Maulik,

    Please disregard my previous message. I found the issue. I was moving from a subsite to the root site, and when doing that, you also need to update the WebId attribute in the manifest as well as what you described above. I used this web site as assistance on the WebId attribute:

    http://blog.johnsworkshop.net/moving-lists-with-lookup-columns-inside-your-site-collection/

    Once that was done, I followed your same procedures and it properly linked the lookup lists. Thanks!

    ReplyDelete
  10. This is very great post. Thank you very much. It saved my precious time.

    ReplyDelete
    Replies
    1. That's why I wrote it. I wasted a lot of time when I was new.

      Delete
  11. This can resolved the lookup problem, but there is another issue which is the item previously created by different users. But after importing list with content all the items created by field value updated with the admin account. Any idea how we can resolved that.

    ReplyDelete
  12. Hi, excellent instructions, however I have an issue with self referring list. I have a lookup field that refers to the same list. How to solve this issue avoiding recreating filed itself manually and again inserting data? There's no list id, before creating a list.

    Thank you in advance

    Thanks

    ReplyDelete
    Replies
    1. We could restore parent/child list template first. Then run powershell script to update parentListID for lookup field in Child list. Here is the script

      if(-not(
      Get-PSSnapin | Where { $_.Name -eq "Microsoft.SharePoint.PowerShell"})
      ) {
      Add-PSSnapin Microsoft.SharePoint.PowerShell
      }
      #Define variables
      $SiteUrl = "http://abc.com/sites/collaboration";
      $ListName = "";
      $parentListName = ""
      $fieldName = ""

      $site = New-Object Microsoft.SharePoint.SPSite($SiteUrl)
      $web = $site.OpenWeb()

      $list = $web.Lists[$ListName];
      Write-Host "List Name: " $ListName
      Write-Host "List count: " $list.ItemCount

      $lookup = $list.Fields[$fieldName]
      Write-Host "Lookup field Name: "$lookup.Title

      $parentList = $web.Lists[$parentListName]
      Write-Host "Parent List Name: " $parentList.Title " and ID =" $parentList.ID

      $schema = [XML] $lookup.SchemaXml

      $schema.Field.SetAttribute("List", "{" + $parentList.ID + "}")
      $lookup.SchemaXml = $schema.OuterXml


      $lookup.Update($true)

      $web.Dispose();
      $site.Dispose();

      Delete
  13. HI, I have question "How to convert .stp file to .cab file?"
    Thanks.
    Samadhan.

    ReplyDelete
    Replies
    1. Rename the file and add .cab to the file. Once you are done remove cab from the name. Simple.

      Thanks,
      Maulik Dhorajia

      Delete
  14. Awesome post, i saw a lots of post for the same prob, but didnt find solution in such a simple and detailed manner, thanks Maulik for sharing it, you rock

    ReplyDelete
  15. This post was really helpful, thanks.

    ReplyDelete
  16. We could restore parent/child list template first. Then run powershell script to update parentListID for lookup field in Child list. Here is the script

    if(-not(
    Get-PSSnapin | Where { $_.Name -eq "Microsoft.SharePoint.PowerShell"})
    ) {
    Add-PSSnapin Microsoft.SharePoint.PowerShell
    }
    #Define variables
    $SiteUrl = "http://abc.com/sites/collaboration";
    $ListName = "";
    $parentListName = ""
    $fieldName = ""

    $site = New-Object Microsoft.SharePoint.SPSite($SiteUrl)
    $web = $site.OpenWeb()

    $list = $web.Lists[$ListName];
    Write-Host "List Name: " $ListName
    Write-Host "List count: " $list.ItemCount

    $lookup = $list.Fields[$fieldName]
    Write-Host "Lookup field Name: "$lookup.Title

    $parentList = $web.Lists[$parentListName]
    Write-Host "Parent List Name: " $parentList.Title " and ID =" $parentList.ID

    $schema = [XML] $lookup.SchemaXml

    $schema.Field.SetAttribute("List", "{" + $parentList.ID + "}")
    $lookup.SchemaXml = $schema.OuterXml


    $lookup.Update($true)

    $web.Dispose();
    $site.Dispose();

    ReplyDelete