Populating a lookup list based on another

Discussions about Cross-site Lookup
Locked
Jaydius
Posts: 43
Joined: Thu Nov 12, 2015

15 Feb 2016

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.

User avatar
rostislav
Moderator
Posts: 364
Joined: Mon Oct 19, 2015

15 Feb 2016

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
Posts: 43
Joined: Thu Nov 12, 2015

15 Feb 2016

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
Posts: 43
Joined: Thu Nov 12, 2015

15 Feb 2016

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?

User avatar
rostislav
Moderator
Posts: 364
Joined: Mon Oct 19, 2015

16 Feb 2016

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
Posts: 43
Joined: Thu Nov 12, 2015

16 Feb 2016

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.

User avatar
rostislav
Moderator
Posts: 364
Joined: Mon Oct 19, 2015

17 Feb 2016

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
Posts: 43
Joined: Thu Nov 12, 2015

17 Feb 2016

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?

User avatar
rostislav
Moderator
Posts: 364
Joined: Mon Oct 19, 2015

18 Feb 2016

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

Locked
  • Information
  • Who is online

    Users browsing this forum: No registered users and 3 guests