Thursday, August 29, 2013

How to create forms with related items in SharePoint 2010/2013 including Office 365

In this article I am going to demonstrate a new control of SharePoint Forms Designer called Related Items which has become available in version 2.7.5.

In the previous entries (1, 2) I described how to create forms with related items but that method requires much configuration including modification via SharePoint Designer. So users have to possess base skills in SharePoint configuration to follow instructions from those articles.

We looked into our customers requests and added a new control that allows to add and configure a list of related items directly in Forms Designer without using SharePoint Designer or configuration of web parts.

First, I have created lists linked by a single lookup column. Here you can use the standard lookup or our Cross-site Lookup column. Related items control works with both of them. Here are my lists:

  • Countries;
  • Offices: contains Country lookup column linked with Countries list;
  • Issues: contains Country and Office lookup columns linked with Countries and Offices lists accordingly;

In the Issues forms I configured cascading between Country and Office drop-downs. So, users can create issues and attach them to the specific office by selecting Country first and then Office.

Now I would like to place the list of Offices into Country form and filter it by the current Country. Also, it would be very useful to edit Offices list directly from the Country form.

Open Countries edit form in Forms Designer. You can find the new control 'Related Items' on the left side. I put it into the accordion section:

Related Items Control in SharePoint Forms Designer

In the properties window you can find Data Source property. Expand it by clicking the dots button. Here you can specify the list that you wish to display in your form, its view and lookup column for filtering. To display offices related to the current country I setup the following configuration:

Data Source Editor of Related Items control

Next, I will describe other properties of Related Items control.

Editable
If value is True, the related list is displayed with Add new button and a context menu for the items. So you can modify the list directly from the current form. It is good practice to set this property False for display forms and True for edit forms.

Save option If value is Recreate, the web part with the related items is resaved each time the form is saved. So if you wish to modify Related items with SharePoint Designer, you can change the value of Save option property to IgnoreIfExists, and Forms Designer will not resave Related Items component in the case it already exists on the form page even if you modified its properties.

View This is an extended property for advanced users. Here you can modify schema of related items view.

Ok, now let's see what we get:

SharePoint form with related items

Now, users can edit Offices directly in Country form. When the user clicks 'new item' they see Office form which contains Country lookup column:

SharePoint new form

Ok, now I wish to fill Country field in the new Office form automatically based on the parent form. Open this form in Forms Designer and configure its layout according to your requirements. Country field has to be in this form and we will prefill it with JavaScript. Here is my form.

Set css class for parent column

Please, note that I defined Css Class for Country field. I will use it in JavaScript to hide the field if the form has been opened from the parent form.

Open JS-Editor and place the following js-code there:

var parentID = fd.getSourceID();
if (parentID) {
    fd.field('Country').control().value(parentID);
    $('.country-field').hide();
}

As you can see, to get ID of the parent form I used a new function fd.getSourceID(). If you open a new form from the parent form this function returns ID of the parent item and null otherwise.

Next, if parentID is not null, I assign it to Country and hide Country field by selecting it with Css Class defined above.

Now, when the user adds Offices from Country form they have to fill Title field only. Country field is filled automatically and is not displayed in the form.

Great. Now I would like to show related issues in Offices forms. So, users will see only those issues which are related to their office. I would also like to see two views: all active issues linked to the current office and issues linked to the current office and assigned to the current user. So, open Forms Designer and put two related items into separate tabs and configure them to get data from Issues list. Set filtering by Office field and select the appropriate view: Active Issues and Assigned To Me. Actually, you can create your own views with filtration, grouping and styling and choose them in Data Source Editor.

Set view of related items

Also, I modified the new form of Issues the same way as Offices to prefill and hide Office field if the form is opened from the parent form. Here is the result:

SharePoint form with list of related items

As you can see, with the new version of Forms Designer it's very easy to add related items anywhere in the form, filter them by lookup field and modify directly from the form. Do not hesitate to leave your questions we will be very glad to answer all of them.

51 comments:

  1. This is really great... Is there any way that we can pull down additional fields this way as well? Specifically We have a Parent-Child-Grandchild type of situation.

    The Grandchild has both a lookup to the child and a lookup to the parent.

    When you create the Grandchild item from the Child display form, we can set the lookup to the child (the level right above) using the method stated, but cannot set the lookup to the Parent (2 levels above). Our current 2010 solution from www.sharepointaid.com DOES support this, but they do not have a 2013 solution.

    ReplyDelete
    Replies
    1. Thank you for your feedback. You can obtain this functionality via custom JS-code. For example, you can use SPServices or REST-api to request parent ID. Also you can pass it as additional URL paramter to the grandchild new form. Our support service can help you with this task. Please, write them to support@spform.com

      Delete
    2. Did you ever get a solution for this using spforms? We have the exact same requirement and just purchased spform.

      Thanks in advance for any help.
      Dave

      Delete
  2. This is a great new feature and thanks for the nice explanation. I was wondering if there is a way to do column filtering from the related items view (as can be done by SharePoint natively). For example, in the Active Issues tab above, I would like to filter on Priority (just imagine there are 20 items listed with a mixture of 4 different priorities). Thank you! :-)

    ReplyDelete
    Replies
    1. Thank your for your positive feedback. Sure, you can use grouping, sorting and filtering features of SharePoint views. Just configure the view you need via SharePoint UI and choose it in Data Source of Related Items control in Forms Designer.

      Delete
  3. Thanks for the response. I understand your reply and know it can be done that way but what I really want to be able to do is for the user to do their own filtering the way that's provided by SharePoint natively (by clicking the down array of the column title and selecting the filter). Does this not work in your solution? Thank you!

    ReplyDelete
  4. Any news here? I'm on a time crunch as my trial period ends very soon and this issue is an important factor for purchase of your product. Thank you!

    ReplyDelete
    Replies
    1. Hello Renee,
      We are working on this issue. I am not sure that we will resolve problem with filtering soon but we will enable sorting functionality by the next week. So, your users will be able to sort list by priority or any other column right on the form page. I will keep you informed. Thank you.

      Delete
    2. Please, download and install the latest version from our website: http://spform.com/download

      Delete
  5. Dmitry, I had an idea/input on the filtering/sorting issue with "Related Items" in SharePoint Form Designer. I noticed that if you use the out of the box "Related Items" view filtering would not work if the related item lookup referenced the Title field. Basically when you went to sort or filter all the items would disappear from the related web part.

    I had this same issue in SP2010 and had to link the lookup field on the child to the ID column on the Parent. Once I linked it on the Parent's ID, all the filtering/sorting worked fine.

    Hopefully this makes sense. We are willing to link our relations on the ID instead of a text field if this would help you guys to get the sorting/filtering working 100%.

    ReplyDelete
    Replies
    1. A video of how this works out of the box: http://screencast.com/t/9R3sZN4Oi

      Delete
    2. Hello Justin,
      I am glad to announce a new release of Forms Designer which supports filtering and sorting of related items. Please, download it from our website and set Refresh property of Related Items control to Asynchronous and Render to Server.

      Delete
  6. This works great, however when our related list is too long we get a more items link at the bottom. When we click the more items link we are getting all list items and not those related to our parent list item. Is this a bug, config or feature by design?

    ReplyDelete
    Replies
    1. It depends on list type and view which you have selected in Related items settings. Also, try to install the latest version of Forms Designer and set the following settings of Related items: Refresh in 'Asynchronous' and Render in 'Server'.

      Delete
  7. Hi Dmitry.

    This is very useful feature, but it does not work with parent lookup fields with multi-word names. If I, for example, have a field named "Countries Of The World", instead of "Country", this javascript snippet does not work.

    Any workaround, or do I need to rename all my columns :D?

    ReplyDelete
    Replies
    1. Hello,
      Thank you for your question. In Forms Designer's JavaScript Framework you have to use internal names of your fields instead of their titles. Most probably, the internal name of your field is Countries_x0020_Of_x0020_The_x00. So, please, try the following code:
      var parentID = fd.getSourceID();
      if (parentID) {
      fd.field('Countries_x0020_Of_x0020_The_x00').control().value(parentID);
      $('.country-field').hide();
      }

      You can find more info about internal names in the following thread:
      http://social.msdn.microsoft.com/Forums/sharepoint/en-US/75ca6fab-56f3-4bf4-aae0-2d29821778a2/how-to-get-internal-names-of-columns-in-sharepoint-lists?forum=sharepointdevelopmentlegacy

      Delete
    2. This worked, thanks a lot.

      I have another question though :).

      Can I retreive some other value from the parent form, except the ID?

      Delete
  8. Hi again Dmitry.

    I'm sorry for repeating myself, but it looks as you have not seen my last reply, so I decided to add as a new comment.

    My new questions is:"Can I retreive other field values from the parent form"?

    ReplyDelete
    Replies
    1. I'm sorry for grammar and typo errors. Typed it while doing other stuff at the same time... :D

      Delete
    2. Hi,
      You can pass them as additional columns of your lookup field. Go to lookup column settings and choose additional fields which will copied from the parent list with lookup value. Then you can place these columns onto your form and use them via JS-framework.

      Delete
  9. Hi Dimitry,

    I modified the "Add new entry" Link in the parent form to submit another field value to the child form.
    Then I put following code in the JS-Editor:
    var customer = fd.getUrlParam(window.location.href, 'Customer');
    if (customer) {
    var feld = fd.field('FCM_x0020_Customer');
    feld.value(customer);
    }
    But it seems that the code is executed before the the form designer put the fields to the document, so the "var feld" is empty.
    I also tried to put the code in $(window).load(function() {}); but even then it seems to be to early.
    Is there any way to wait until every field ist completly rendered?

    ReplyDelete
    Replies
    1. Hi Rinden,
      User's code from JS-editor is executed after initialization of all fields and other Forms Designer objects. Please, make sure that query string contains Customer parameter.

      Delete
    2. Hi Dmitry,

      The debugger shows me that customer is set correctly to the id but when i step into the fd.field function i can see that this._fields is an empty array with the length of 0. So my first thought was that the function is called too early. It happens in IE11 and Firefox.

      Delete
    3. _fields collection is filled by request, so, before the first call of fd.field() method it will stay empty. Please, make sure that the internal name of your field is FCM_x0020_Customer and that this field is located on the form.

      Delete
    4. This comment has been removed by the author.

      Delete
    5. Hi Dmitry,

      Thank you for your help. I have my solution now. In the parent form I added following Code to modify the Link:
      $(window).load(function() {
      var link = $('a:contains("Add new contact")');
      var customerID = encodeURIComponent(fd.field('Customer').value());
      var customerName = encodeURIComponent(fd.field('Customer').control('data')['Title']);
      if (link && customerID) {
      var script = link.attr("onclick");
      script = script.replace("')","&CustomerID=" + customerID + "&CustomerName="+ customerName +"')");
      link.attr("onclick",script);
      var target = link.attr("href");
      target = target + "&CustomerID=" + customerID + "&CustomerName="+ customerName;
      link.attr("href",target);
      }
      });

      And in the child form I added following code to set the lookup field:

      var customerID = fd.getUrlParam(window.location.href, 'CustomerID');
      var customerName = fd.getUrlParam(window.location.href, 'CustomerName');
      if (customerID) {
      var feld = fd.field('FCM_x0020_Customer');
      feld.value({Id: customerID, Title: customerName});
      feld.readonly(true);
      //$('.customer-field').hide();
      }

      The problem was that when I only set the ID, the LookupControl did not get the title automatically so I had to set it manually.Your way would have worked too because after saving the new entry the lookup column was saved correctly even when the title was empty. But I wanted that the user can see what the value of the lookup field is.

      Thank you again

      Delete
  10. what about if I want to add 2 or more parentID ?

    I tray to use this :



    var licID = fd.getSourceID();if (licID) { fd.field('licID').control().value(licID); $('.licIDhid').hide();}

    var agremntID = fd.getSourceID();if (agremntID) { fd.field('agremntID').control().value(agremntID); $('.agremntIDh').hide();}

    but it faild !
    It shows the same ID for both feild agremnt and lic

    ReplyDelete
  11. Hi Dimitry.

    I have managed to get the related content working, so that when I add new documents or files into the Documents app, they show up, but I have a problem when I add the files in a folder that is above the root of the documentation...

    If I add a file into a folder in the Documents app, and relate them to the correct contact, they won't show up... If I upload them direcly to the root of the Documents app, they show up. Any idea how to get this function to work?

    ReplyDelete
    Replies
    1. Hello Remi,
      Please, run Forms Designer, choose the Related items control containing related documents and expand its View option. Add Scope="RecursiveAll" attribute to View element:

      <View Scope="RecursiveAll">
      ...
      </View>

      Save the form. Now, documents from all folders will be displayed on the form.

      Delete
  12. Hello,
    When the child is a document library, the above method does not pass the look up value automatically, i guess since 2 windows open, one to upload the document and then the new form with the look up value. Is there a way around it?
    Thanks,
    kat

    ReplyDelete
    Replies
    1. If you open an edit form of a document in a dialog, you can obtain ID of the parent item from the parent window URL. Put the following code into the edit form:
      var parentId = GetUrlKeyValue('ID', window.top.location);

      Delete
    2. Hi Dimitry,
      I think you misunderstood my question (or i didn't get your answer ;)). What i am asking is that instead of 'Offices' list that u are having in the above example, we have a documents library, with a lookup field to Countries for instance. When i followed the same procedure as above, i get first to upload the document and then the NewForm of my document library opens up with 'Country' field not being auto-populated.
      Thanks,
      Kat

      Delete
    3. This comment has been removed by the author.

      Delete
    4. I found this post of yours that describes how to do this but with drag and drop: http://formsdesigner.blogspot.ca/2014/04/related-documents-with-support-of-quick.html. Can we also use this functionality when user uses the "Add new Item" to upload?

      Thanks,
      Kat

      Delete
    5. Hi Katerina,
      Yes, I understood you and the solution described in my previous comment will work for your case. First, documents don't have New forms. As soon as you upload a new document, its edit form opens. So, you have to put JavaScript which gets the parent items ID:
      var parentId = GetUrlKeyValue('ID', window.top.location);

      And assign it to a lookup field of the uploaded document as described in the original post.

      Delete
  13. Hi Dimitry,
    I have one more question (we are in evaluation stage of your application) i read that it supports external content types, is it possible to auto fill those fields (similar to what u did for the look ups above)? For example, i have a list in SharePoint to manage a product and the data of customers exists in another external SQL DB. If i create external fields (or lookups to an external list) of the contact info of customers in my product SharePoint list, would it be possible to auto-fill them in similar to the process above?
    Thanks,
    Kat

    ReplyDelete
    Replies
    1. Hi Katerina,
      Yes, it's possible with help of JavaScript but the syntax differs. Please, find example in the following post (External Data section):
      http://formsdesigner.blogspot.ru/2013/04/getting-and-setting-sharepoint-form.html

      Delete
    2. Thank you Dimitry,
      It works :)
      In my Newform i used:

      fd.field('Title').change(function(){
      var StudentIDval = fd.field('Title').value();
      fd.field('extStudentID').value({key:'__bkc1000300', text: StudentIDval});
      });
      $('.extStudentID-field').hide();

      So i pass the value that user entered in StudentID field (internal: Title) to the look up field that holds studentid in the external list (extStudentID). The information(e.g. address, telephone fields etc) i present them as the additional fields in my external look up column (extStudentID). This way they are filled in automatically (auto-populated) when i set the value of extStudentID to the value that user entered in StudentID field.

      Thanks,
      kat

      Delete
  14. Dimitry,
    When I add first "child" Item from "mother" item, it's not opens in Dialog window, but open in current tab, after save it redirect to first item in "mother" list not in current.
    When I add second and more items it work.

    what is the reason of this?

    ReplyDelete
    Replies
    1. Unfortunately, I couldn't reproduce the issue. Do you use the Related items control? What are the values of the Refresh and Render properties of the Related items?

      Delete
  15. Is it possible to make items listed in the related items list downloadable only. I'd like users to only have the option to download a copy.

    ReplyDelete
  16. on occasion the parent ID is not written to the related item list. Anyway around this?

    ReplyDelete
    Replies
    1. What version of the Forms Designer are you using (you can check in the Forms Designer UI in the bottom-right corner)? What field type is giving you problems - normal SharePoint lookup or Cross-Site lookup?

      If you're using FormsDesigner 3.0.1 and cross-site lookup field - try downloading the latest versions of FormsDesigner and Cross-Site lookup. (You may need to deactivate and activate again these features in site settings and clear your cache for the new versions to be picked up).

      Delete
  17. Ever since I updated to 3.01 this does not work. I use standard lookup field. Worked great prior. Tried to recreate you example again but it didn't work either.

    ReplyDelete
    Replies
    1. The how-to uses the previous version of Forms Designer. In 3.01 in the data source editor you now have 'Filter by form field' instead of 'Filter by lookup field', so adjust your setup accordingly: the form field needs to be the current list's field (say, Title), and the List field needs to be the lookup field, say, Country.

      Delete
    2. Can you use this method with a document library as the related item? Any differences if you can?

      Delete
    3. Sure, you can. Moreover, you can turn quick upload on and populate some fields automatically:
      http://formsdesigner.blogspot.com/2014/04/related-documents-with-support-of-quick.html

      Delete
  18. How about showing items related to a 'multi lookup value' - i.e. how to pass an array to the filter?

    ReplyDelete
    Replies
    1. Hi Joseph,
      You can filter the related items by multi-lookup field if the source field is a single lookup and the target field is multi-lookup.

      Delete
  19. This comment has been removed by the author.

    ReplyDelete
  20. Hi

    I am stuck in an scenario , please can somebody help me out

    I have one parent list and child list bind by order number.

    While creating new item in parent list, I want to create new item in child list also.So if I enter order number in parent list it should show in child list drop down and value should be set to what is there in parent list order number. and this is should happen in one save button

    ReplyDelete

Note: Only a member of this blog may post a comment.