Get items from list "B" assigned to the person in a people picker in list "A"

[Alexander Bautz]

I got a question from Charlie which was basically like this:

You have a List (A) where you have set up the SPJS-Lookup solution. In this list you have a people picker “Requester”.

You have another list (B) where there is a people picker “AssignedTo”.

You want the filter to read the currently selected “Requester” from the item in list A, and search list B for records assigned to this user.

To do this, you must set up the CAML property of the “argObj” like this:

"CAML":"<Where><And><IsNotNull><FieldRef Name='Title' /></IsNotNull><Eq><FieldRef Name='AssignedTo' LookupId='TRUE' /><Value Type='User'>"+String(spjs.dffs.beforeProperties["Requester_ID"])+"</Value></Eq></And></Where><OrderBy><FieldRef Name='Title' Ascending='TRUE'/></OrderBy>"

In this example, you must change the FieldInternalNames “AssignedTo” and “Requester” to match your fields names. Please note that the “_ID” suffix on the “Requester” must also be used for the new people picker you use in stead.

Alexander

[Charlie Bell]

Recap for anyone trying to use this.. What im trying to do is Build a ticket system which ties to an asset system allowing user to submit ticket and select equipment assigned to that user so tickets will then be shown on asset page for monitoring problem devices.
So what i want is to enter the requester on new ticket which will then populate a dropdown of the SN of the equipment assigned to the requester..

Question I used the above CAML in the misc tab custom JS in the filterObj is this the correct spot i notice you say argObj?
still getting basically that same error not returning any data.
Where you mention _ID does this mean i need to change the people picker for the requester? is this why im getting the null data

[Alexander Bautz]

Hi,
This is the complete setup for SPJS-Lookup where I have used the CAML in my previous post:

	var argObj = {
		"fieldToConvertToDropdown":["SPJSLookup1"],
		"optTextFieldInternalName":"Title",
		"listName":"Tasks",
		"listBaseUrl":"/DFFS",
		"sortFieldName":"Title",
		"filterObj":{
			"on":true,
			"folder":"",
			"CAML":"<Where><And><IsNotNull><FieldRef Name='Title' /></IsNotNull><Eq><FieldRef Name='AssignedTo' LookupId='TRUE' /><Value Type='User'>"+String(spjs.dffs.beforeProperties["Requester_ID"])+"</Value></Eq></And></Where><OrderBy><FieldRef Name='Title' Ascending='TRUE'/></OrderBy>", // Full CAML query. If used, the rest of the filterObj settings are disregarded
			"fin":"",
			"isLookup":false,
			"operator":"Eq",
			"filterVal":""
		},
		"dropDownDefaultvalue":"...",
		"addYouOwnValue":{
			"on":false,
			"linkText":"Write your own value"
		},
		"addToExternalList":{
			"on":true,
			"customFunction":null,
			"linkText":"Add new item",
			"saveNewItemText":"Save new item"
		},
		"debug":true
	};	
	spjs.lookup.init(argObj);

Regarding the people picker field name:
My people picker has FieldInternalName “Requester”, but the “beforeProperties” object has a separate property containing the ID of the person in the people picker. This is stored in the “Requester_ID” attribute. If your people picker is named “MyPeoplePickerField”, you must use “MyPeoplePickerField_ID”.

I hope this clarifies it a bit.

Alexander

[Charlie Bell]

Man his is tuff so im still getting the same thing no data.. I just created a new list with 3 columns Title, AssignedTO, AssetNumber i have populated the list with 2 records assigned to me.. I have a ticket that is saved andmy name is in the Requester field…
here is my code… `var argObj = {
“fieldToConvertToDropdown”:[“Equipment”],
“optTextFieldInternalName”:”Title”,
“listName”:”Equipment”,
“listBaseUrl”:”/Intranet/IT/”,
“sortFieldName”:”Title”,
“filterObj”:{
“on”:true,
“folder”:””,
“CAML”:”<Where><And><IsNotNull><FieldRef Name=’Title’ /></IsNotNull><Eq><FieldRef Name=’AssignedTo’ LookupId=’TRUE’ /><Value Type=’User’>”+String(spjs.dffs.beforeProperties[“Requester_ID”])+”</Value></Eq></And></Where><OrderBy><FieldRef Name=’Title’ Ascending=’TRUE’/></OrderBy>”, // Full CAML query. If used, the rest of the filterObj settings are disregarded
“fin”:””,
“isLookup”:false,
“operator”:”Eq”,
“filterVal”:””
},
“dropDownDefaultvalue”:”…”,
“addYouOwnValue”:{
“on”:false,
“linkText”:”Write your own value”
},
“addToExternalList”:{
“on”:true,
“customFunction”:null,
“linkText”:”Add new item”,
“saveNewItemText”:”Save new item”
},
“debug”:true
};
spjs.lookup.init(argObj);`

Here is the debug..` [LookupInTextField: Debug]
1.10
ListName: Equipment
Query: <Where><And><IsNotNull><FieldRef Name=’Title’ ></FieldRef></IsNotNull><Eq><FieldRef Name=’AssignedTo’ LookupId=’TRUE’ ></FieldRef><Value Type=’User’>undefined</Value></Eq></And></Where><OrderBy><FieldRef Name=’Title’ Ascending=’TRUE’></FieldRef></OrderBy><OrderBy><FieldRef Name=’Title’ Ascending=’TRUE’/></OrderBy>
ViewFields: ID,Title
Query returned 0 items.
Current field value: “”, was NOT found in the available values returned by the query. The field value has been cleared. `

[Charlie Bell]

If i change to use current logged in user id it works..

<Eq><FieldRef Name='AssignedTo' LookupId='TRUE' /><Value Type='User'><UserID/></Value></Eq>

that will show me the equipment assigned to me so its something with the requester people picker

[Alexander Bautz]

Sorry for the delay – I have had quite a lot of backlog on emails. I have looked at your code snippet above, and it seems that you have not changed the fieldinternalname to match the one you use. In my example, I used “Requester”, but this must be changed to the actual field you want to pull the information from.

Alexander

[Charlie Bell]

Requester is the name of my people picker on the service ticket. so it should read the requester and then look in the equipment list and match AssignedTo in that list..

when you say pull the information from are you meaning that what ever is in requester will populate the dropdown?
i thought the below code would populate the dropwodn with the title
“fieldToConvertToDropdown”:[“Equipment”],
“optTextFieldInternalName”:”Title”,
“listName”:”Equipment”,
“listBaseUrl”:”/Intranet/IT/”,
“sortFieldName”:”Title”,

And the CAML would read my current form and get the ID from the requester Field and the look in Equimpnet for that name in the AssignedTo column

[Alexander Bautz]

Hi,
It seems I have mistakingly assumed that the people picker was filled when the form loaded, and not being set manually in the form.

Can you confirm that the picker is set by the user?

If so, how do you plan to “fill” the dropdown? – by a button, or by “change” of the PP?

Alexander

[Charlie Bell]

Hey, So it actually does get popualted when you load new form with the logged in User ID ( still does not work) But i would like it to update the dropdowns when there is a change on the PP..

i even tried on the edit form where Requester field had name and was saved prior to trying to get to dropdown.

[Alexander Bautz]

Hi,
Hopefully, this code example will provide a working solution. Add the below code to the Custom JS, or the CEWP for the DFFS frontend.

Please note that you must change the FieldInternalName the function is called with in the bottom line, and you must change the parameters in “spjs.lookup.init” in the “fill” function in the top.

Alexander

var spjs_lookup_ppTrigger = {
	"fill":function(fin){
		var ppID = spjs_lookup_ppTrigger.getSelectedPeoplePickerID(fin);
		if(ppID !== false){
			spjs.lookup.init({
				"fieldToConvertToDropdown":["SPJSLookup1"],
				"optTextFieldInternalName":"Title",
				"listName":"Tasks",
				"listBaseUrl":"/DFFS",
				"sortFieldName":"Title",
				"filterObj":{
					"on":true,
					"folder":"",
					"CAML":"<Where><And><IsNotNull><FieldRef Name='Title' /></IsNotNull><Eq><FieldRef Name='AssignedTo' LookupId='TRUE' /><Value Type='User'>"+ppID+"</Value></Eq></And></Where><OrderBy><FieldRef Name='Title' Ascending='TRUE'/></OrderBy>", // Full CAML query. If used, the rest of the filterObj settings are disregarded
					"fin":"",
					"isLookup":false,
					"operator":"Eq",
					"filterVal":""
				},
				"dropDownDefaultvalue":"...",
				"addYouOwnValue":{
					"on":false,
					"linkText":"Write your own value"
				},
				"addToExternalList":{
					"on":true,
					"customFunction":null,
					"linkText":"Add new item",
					"saveNewItemText":"Save new item"
				},
				"debug":false
			});
		}
	},
	"data":{
		"pp":{}
	},
	"init":function(fin){
		setInterval(function(){
			var a = spjs_lookup_ppTrigger.ppChanged(fin);
			if(a){		
				spjs_lookup_ppTrigger.fill(fin);
			}
		},2500);
	},
	"ppChanged":function(fin){
		var a = spjs.utility.getFieldValue({
			"fin":fin,
			"key":"loginName"
		}), b = false;
		if(spjs_lookup_ppTrigger.data.pp[fin] !== String(a[0])){
			spjs_lookup_ppTrigger.data.pp[fin] = String(a[0]);
			b = true;
		}
		return b;
	},	
	"getSelectedPeoplePickerID":function(fin){
		var a, b, c = false;
		a = spjs.utility.getFieldValue({
			"fin":fin,
			"key":"loginName"
		});
		if(a.length > 0){		
			b = spjs.utility.userInfo(a[0]);
			c = b.ID;	
		}
		return c;
	}
};

spjs_lookup_ppTrigger.init("PeoplePicker2");

[Tony Bowes]

Thankyou!

I managed to get the people picker field to successfully filter another list. Initially had it working with the original CAML, but my field is autofilled on the form from another DFFS form, so I changed to the second method.

A point to note on the second method, the spjs.lookup.init needs to be changed to your own SPJS-Lookup initialisation, APART FROM the “+ppID+” in the middle of the CAML. I had left mine as the original post’s CAML (the “+String(spjs.dffs.beforeProperties[“Requester_ID”])+” part) and had undone all the extra work put in to make it actively pick up the new people picker field’s value.

[Author]

Posts