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 firstly 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.

27 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. 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