Is there a way to Query a list and get the items to display as a choice in a field and attach that to a single line of text in Sharepoint in the form? I am trying to replace an info path form that uses Data connections.
Thank you
Jennifer
Dear Jennifer,
Sure, plenty of options here. You can use default lookup field, for instance.
If this doesn’t work, you can try JSOM - https://docs.microsoft.com/en-us/previous-versions/office/developer/sharepoint-2010/hh185007(v%3Doffice.14)
Alternatively, you could utilize SharePoint REST API - https://docs.microsoft.com/en-us/sharepoint/dev/sp-add-ins/working-with-lists-and-list-items-with-rest#working-with-list-items-by-using-rest
Hi Nikita,
I also need to query a additional list from within the form…
I’m really not a programmer, so I would appreciate if you would come up with some example how to do that…
Kind regards,
Zlatan
Dear Zlatan,
Any additional information on that? What exactly do you need?
What list do you need to access, what do you need to do with it, what permissions do users have to this list, etc…
Hi,
Sorry for lack of basic info… here’s the rather complex setup:
list:AR
- Requester (Person field)
- ACode (Lookup from list: ACode)
- DCode (Lookup from list: DCode)
- CCode (Lookup from list: DCode)
- other data
- other data
- …
list: DCode
- code
- description
list: CCode - code
- description
list: ACode - code
- description
list: EmployeeRegister
- Employee (Person field)
- ACode (Lookup from list: ACode)
- DCode (Lookup from list: DCode)
- CCode (Lookup from list: DCode)
Idea is that on the new list:AR form Requester, ACode, DCode, CCode fields are prepopulated based on querying the entries from list:EmployeeRegister using user currently logged on the Sharepoint.
But list:AR ACode,DCode,CCode fields must be editable…
Kind regards,
Zlatan
Dear @zmanowsky,
If I understand correctly, what needs to happen is: you open AR list New Form and it populated current user inside Requester field, plus populates ACode, DCode and CCode with values from Register list, which match the current user, correct?
This should be doable with pnpjs, which is included with Forms. Give me a bit of time, and I’ll write a short example for you, if this is what you need.
Dear @Nikita_Kurguzov,
you are quite right, still keeping in mind that ACode, DCode, CCode fields must remain freely selectable as they are indeed lookup fields to their respective lists…
Dear @zmanowsky,
You would need something like this:
fd.spRendered(function(){
fd.field('Person').ready().then(function(field) {
pnp.sp.web.currentUser.get().then(function(user) {
fd.field('Person').value = user.LoginName;
pnp.sp.web.lists.getByTitle("Register").items.filter("Person eq " + user.Id).get().then(function(items){
// see if we got something
if (items.length > 0) {
fd.field('ACode').value = items[0].ACodeId;
fd.field('CCode').value = items[0].CCodeId;
fd.field('DCode').value = items[0].DCodeId;
}
});
});
});
});
I’ve tested the code, it should work, but you might need some adjustments, especially when it comes to field names.
Dear @Nikita_Kurguzov,
thank you so much for your helpful example!
Indeed it works when checking against currently logged in user, but I run into problems when trying to do the same with by using Person field and entering other Sharepoint users as field did not yield actual userID I could use to query EmployeeRegister list which uses sharepoint person field…
Solution I came up with is based on use of Sharepoint REST API as described in:
and your example…
function GetUserId(userName, callback) {
/// get the site url
var siteUrl = _spPageContextInfo.siteAbsoluteUrl;
/// add prefix, this needs to be changed based on scenario
var accountName = userName;
/// make an ajax call to get the site user
$.ajax({
url: siteUrl + "/_api/web/siteusers(@v)?@v='" +
encodeURIComponent(accountName) + "'",
method: "GET",
headers: { "Accept": "application/json; odata=verbose" },
success: function (data) {
callback(data.d.Id);
},
error: function (data) {
console.log(JSON.stringify(data));
}
});
};
// START Fill 'Requestor' with current user
fd.field("Requestor").ready().then(function(field) {
fd.field("Requestor").value = _spPageContextInfo.userLoginName;
// getEmployeeData(_spPageContextInfo.userId);
});
/// END Fill 'Requestor'
/// GET DATA from EMPLOYEE_REGISTER
function getEmployeeData(person) {
pnp.sp.web.lists.getByTitle("Employee Register").items.top(1).filter("Employee eq '"+person+"'").get().then(function(result)
{
fd.field("Department").value = result[0]['DepartmentId'];
fd.field("Location").value = result[0]['LocationId'];
});
};
fd.field("Requestor").$on('change', function(value){
GetUserId(value.Key, function(results) {
getEmployeeData(results);
});
});
Hope it’ll be of use for others here…
Once again, thank you so much!
Z.
3 Likes
in your code, I can't seem to get the value of Persons (multiple)
fd.field('Approvers').value = items[0].Approvers;
Dear @danlim26,
The code for multiple selection would have to be modified significantly, so I'll need to know what exactly you want to achieve with your code, what are the exact requirements?
I need to query the another list and get the Approvers field. which is a Person Multiple.
function getApprovalID() {
pnp.sp.web.lists.getByTitle("Approvers1").items.filter("Title eq 'ID-11111').get().then(function (items) {
// see if we got something
if (items.length > 0) {
alert(items[0].Title);
alert(items[0].Id);
alert(items[0].Approvers);
//set current Approver field w/ the Approver value from Approvers1 list
fd.field('Approvers').value = items[0].Approvers;
alert('Good!');
} else {
alert('Approval Matrix not configured');
}
});
}
Dear @danlim26,
Please, try code like this:
pnp.sp.web.lists.getByTitle("Approvers1").items.select("Title", "Id","Approvers/Name").expand("Approvers").filter("Title eq 'ID-11111'").get().then(function (items) {
// see if we got something
if (items.length > 0) {
alert(items[0].Title);
alert(items[0].Id);
//save names to a separate array
var names = [];
for(var i = 0; i < items[0].Approvers.length; i++){
names.push(items[0].Approvers[i].Name);
}
//set current Approver field w/ the Approver value from Approvers1 list
fd.field('Approvers').value = names;
alert('Good!');
} else {
alert('Approval Matrix not configured');
}
});
1 Like
this one worked! thanks!
1 Like