Populating a lookup list based on another

rss

Posted by Jaydius - Feb 15 ’16 at 02:06

Im using the great Cross-Site Lookup on my site.  I have two seperate Cross-Site Lookup dropdowns:

  • Equipment
  • Component

I want to be able to populate the Equipment dropdown (which is set to multipule values) based on choices made in the component dropdown.  Each component is related to a piece of equipment.  I want to easily be able to fill the 'parent' value from the chosen component into the Parent field (Equipment).

Im currently displaying the relationship within the Component Dropdown, displaying the component name on the left, with the related equipment on the right of the dropdown.

 

Thanks for taking the time to read my question.  I hope theres a possible solution.

  • rostislav
    rostislav
    Moderator
    Member for: 7 years 10 days
    #1 by rostislav Feb 15 ’16 at 05:14

    You'll need to do two things:

    1. Request additional information (equipement title and id) in the component lookup. To do this, go to "Manage lookups", select the component lookup, expand "Advanced settings" and edit the Request items code in the following way:

    function (term, page) {
      if (!term || term.length == 0) {
        return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField},Equipment/Id,Equipment/Title&$expand=Equipment/Id,Equipment/Title&$orderby=Created desc&$top=10";
      }
      return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField},Equipment/Id,Equipment/Title&$expand=Equipment/Id,Equipment/Title&$orderby={LookupField}&$filter=startswith({LookupField}, '" + encodeURIComponent(term) + "')&$top=10";
    }
    

    Notice the additional Equipment/Id and Equipment/Title columns inside "select" and "expand" parameters. Note that your internal names may differ, so be sure to check that.

    2. Add an onchange handler to the Component lookup. To do this, go to Forms Designer and add the following JavaScript code to the form (press the JavaScript button at the top):

    fd.field('Component').change(function(){
    	var title = fd.field('Component').control('data').Equipment.Title;
    	var id = fd.field('Component').control('data').Equipment.Id;
    	//Equipment is a multi-value select, this line of code will rewrite any existing value in it. If you want to add a value to the box, rather than overwrite it you'll have to retrieve the existing array from the lookup field and add a new object to it
    	fd.field('Equipment').value([{Id: id, Title: title}])
    });
    

    Again, be sure to check that the internal names you're using are correct.

  • Jaydius
    Member
    Member for: 6 years 6 months 4 days
    #2 by Jaydius Feb 15 ’16 at 02:18

    [#1]: Thank you so much for the prompt reply.  As ill be wanting to add to the existing values in the Equipment box, how would I change the code you've provided to deal with this?

    Really appreciate your help on this.

     

     

  • Jaydius
    Member
    Member for: 6 years 6 months 4 days
    #3 by Jaydius Feb 15 ’16 at 06:21

    Also testing the code you provided gave me the following error:

    Uncaught TypeError: Cannot read property 'Title' of undefined

     

    Could it be caused by the Component form field allowing multiple choices as well?

  • rostislav
    rostislav
    Moderator
    Member for: 7 years 10 days
    #4 by rostislav Feb 16 ’16 at 05:21

    [#3]:

    Here's an updated version of the code:

     fd.field('Component').change(function(){
    	var components = fd.field('Component').control('data');
    	var equipmentPieces = [];
    	for (var i = 0; i < components.length; i++){
    		var title = fd.field('Component').control('data')[i].Equipment.Title;
    		var id = fd.field('Component').control('data')[i].Equipment.Id;
    		equipmentPieces.push({Id: id, Title: title});
    	}
    
    	fd.field('Equipment').value(equipmentPieces);
    });
  • Jaydius
    Member
    Member for: 6 years 6 months 4 days
    #5 by Jaydius Feb 16 ’16 at 02:22

    [#4]: This works well, thanks.  The only issue I have is, if items have been added directly to the equipment field, is there any way to stop the function from removing them each time a new component is added?

    Ideally the function would only add or remove items from the equipment field relating to chosen components.  It wouldnt affect manually added items to the Equipment field.

  • rostislav
    rostislav
    Moderator
    Member for: 7 years 10 days
    #6 by rostislav Feb 17 ’16 at 06:11

    [#5]:

    Take a look at this code:

    var equipUserAdded = [];
    fd.field('Equipment').change(function(ev){
    	if (ev.added) {
    		equipUserAdded.push(ev.added);
    	}
    	else if (ev.removed) {
    		equipUserAdded = equipUserAdded.filter(function(e) {
    			return e.Id && e.Id != ev.removed.Id;
    		});
    	}
    });
    
    fd.field('Component').change(function(){
    	var components = fd.field('Component').control('data');
    	var equipmentPieces = equipUserAdded.slice();
    	for (var i = 0; i < components.length; i++){
    		var title = fd.field('Component').control('data')[i].Equipment.Title;
    		var id = fd.field('Component').control('data')[i].Equipment.Id;
    		equipmentPieces.push({Id: id, Title: title});
    	}
    
    	fd.field('Equipment').value(equipmentPieces);
    });
     

    You can just use it, however it's not perfect in that, in the following scenario it doesn't work as expected:

    1. Add two pieces of equipment by hand

    2. Add two components that are related to the two pieces of equipment

    3. Remove both pieces of equipment

    4. Remove one component

    5. Observe, that the piece of equipment related to the remaining component is readded.

     

    For this to be fixed you'll need to reword the component change handler the same way I've reworked the equipment change handler, i.e. using the event object together with added and removed properties.

  • Jaydius
    Member
    Member for: 6 years 6 months 4 days
    #7 by Jaydius Feb 17 ’16 at 07:47

    [#6]: That works exactly how I wanted.  Thank you so much for your help.  You're a genius!

     

    One final question though, when editing an existing record, the function clears anything already stored in the equipment field.  Is there any way to ensure the existing stored values in the field are not lost when editing a record and adding more components?

  • rostislav
    rostislav
    Moderator
    Member for: 7 years 10 days
    #8 by rostislav Feb 18 ’16 at 06:28

    [#7]:

    I couldn't reproduce your issue, however, as it is the code wouldn't work on an edit form. Try this version, maybe it'll fix your problem too:

    var equipUserAdded = fd.field('Equipment').control('data');
    fd.field('Equipment').change(function(ev){
    	if (ev.added) {
    		equipUserAdded.push(ev.added);
    	}
    	else if (ev.removed) {
    		equipUserAdded = equipUserAdded.filter(function(e) {
    			return e.Id && e.Id != ev.removed.Id;
    		});
    	}
    });
    
    fd.field('Component').change(function(){
    	var components = fd.field('Component').control('data');
    	var equipmentPieces = equipUserAdded.slice();
    	for (var i = 0; i < components.length; i++){
    		if (fd.field('Component').control('data')[i].Equipment) {
    			var title = fd.field('Component').control('data')[i].Equipment.Title;
    			var id = fd.field('Component').control('data')[i].Equipment.Id;
    			equipmentPieces.push({Id: id, Title: title});
    		}
    	}
    
    	fd.field('Equipment').value(equipmentPieces);
    });
    
Displaying 1 to 8 of 8 messages