Populating a lookup list based on another

[Jaydius]

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]

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:

Code: Select all

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):

Code: Select all

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]

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]

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]

Here's an updated version of the code:

Code: Select all

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]

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]

Take a look at this code:

Code: Select all

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]

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]

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:

Code: Select all

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);
});