Giving access to employee to certain folders within a library list using a multi-choice lookup

lso · July 10, 2020, 2:07am

Hello friends,
Any help would be greatly appreciated . We created a document library with folders (1st level) and other sub-folders (2nd level, etc.) and documents named Critical Projects (has ID and Name columns for each folder, etc). We then created a multi-choice lookup named CriticalProjects using that library's ID column to pull folders and document data for use in a SharePoint form named Entitlement form. The lookup won't allow me to use Name to pull folders data too.

I placed the CriticalProjects lookup field in the Plumsail Entitlement form and it lets me select muliple IDs to give some folders access to someone. It's showing all the folders and documents available but I would like it to only show the 1st level of folders to choose from through the ID. Also, I'm trying to show the IDs and Names of the folders with this lookup so we know what the names of those folders are along with ID number. Instead of the employee seeing the items in this folder it's sending me to the edit screen for this folder instead. Maybe it's because I'm testing it and I'm an Admin?

Is there a way to create this multi-lookup field (ProjectsCritical with ID + Name choices when selecting) that gives access to the 1st level folders of choice and for them to see the items inside instead of the edit screen?

I've tried https://plumsail.com/docs/forms-sp/how-to/lookup-view.html for an example and seem to be doing something wrong with my code.

JS-----------
fd.spBeforeRender(function() {
var template = '';
template += '';
template += '

#: data.LookupValue #

';
template += '

#: data.ProjectsCritical ? data.ProjectsCritical.Title : "" #';
template += '#: data.Name ? " - " + data.Name: "" #

';
template += '';
fd.field('Lookup').widgetOptions = {
template: template,
height: 400,
virtual: {
itemHeight: 100
},
dataSource: {
pageSize: 16
}
}
});

CSS-------
.k-dropdown .k-dropdown-wrap .k-input{
height: auto !important;
}

.lookup-col{
flex-direction: column;
margin-bottom: 5px;
padding-top: 10px;
margin-left: 10px;
}

.lookup-title{
margin-bottom: 3px;
font-size: 16px;
}

.lookup-name{
margin: 5px;
}

I tried using Name, ProjectsCritical/Id, ProjectsCritical/Title and ProjectsCritical/Name in the Extra Fields section for ProjectsCritical lookup field. I used ProjectsCritical in the Expand section. I get an error in the console when trying to run it. Sorry, I'm not very skilled with JavaScript so my code is probably really bad... Thank you.

mnikitina · July 10, 2020, 11:23am

Hello @lso,

You can use the code below to show only folders in the lookup field.

function filterLookup(category) {
    fd.field('Lookup').filter = "ContentType eq 'Folder'";
    fd.field('Lookup').widget.dataSource.read();
}

fd.spRendered(function() {
        fd.field('Lookup').ready().then(function(field) {
            filterLookup(field.value);
        });
});

Unfortunately, it is not possible to detect if the folder is 1st or 2nd level. The only thing that comes to my mind is that you can add a column in the document library called “FolderType” with 1st level and 2nd level options. When you create a new folder, you could set its type and filter the values in the lookup field by this field.

Instead of the employee seeing the items in this folder it's sending me to the edit screen for this folder instead. - not sure I understand you, could you please share more details about this.

What is the type of the ProjectsCritical field in the document library?

lso · July 12, 2020, 6:13am

Hi @mnikitina,

I tried the code but unfortunately it's not working for me. The lookup popup bar show rows of "Loading... " text and nothing ever loads.

For the employee being able to see the items in the folder but instead I'm getting the edits screen as I test it, here's a pic.

Here's the ProjectsCritical lookup when choosing multiple IDs.

This happens when I test the ProjectsCritical lookup and choose multiple ID numbers for different folders to try to give them access to see what's inside each folder. I'm not sure why I'm ending up at an edit screen for these folders or documents. For example this 385005 ID numbers is a folder with other folders and then documents inside, which is all inside a documents library named "Projects Critical". The ProjectsCritical is a lookup (connected to Projects Critical document library through ID) column used inside a SharePoint form I'm using Plumsail for named Entitlement Form.

A coworker who knows more JavaScript than me tried to help and we tried this to pull another field along with ID in the ProjectsCritical lookup, which is called "Name". So I wanted the ID and Name fields to popup in the lookup but we have not been successful. We tried some console tests.

fd.spBeforeRender(function() {
console.log('Hello');
var froggy = fd.field('ProjectsCritical').extraFields;
console.log(froggy);
console.log('Hello2');
//console.log(data);
fd.field('ProjectsCritical').widget;
//display Extra Field Name, if it is available
var tmp = '#: data.LookupValue # #: data.Name ? " $" + data.Name : "" #';
fd.field('ProjectsCritical').widgetOptions = {
template: tmp,
valueTemplate: tmp
}/
});

I put "Name" in the Extra Fields section and "Name" in the Expand section as well. I may be doing this part wrong.

The console shows that there is a Name field but we never get anything to popup in the ProjectsCritical lookup when testing. Seems that no data is being relayed.

Here's a pic of the Projects Critical document library:

Here's a pic of the ProjectsCritical lookup:

Sorry, I this may be a lot of information because I'm trying to do multiple things. I have really appreciated all your help. Hopefully I can get a few of these things to work. Thank you.

mnikitina · July 13, 2020, 2:15pm

Hello @lso.

1. Regarding the filtration by the content type. Please check the console for the errors and share its screenshot.
Also, try to comment out all other code and use only this code:

function filterLookup() {
    fd.field('LookupDocs').filter = "ContentType eq 'Folder'";
    fd.field('LookupDocs').widget.dataSource.read();
}

fd.spRendered(function() {
        fd.field('LookupDocs').ready().then(function() {
            filterLookup();
        });
});

If it doesn't work, make sure that you are not using the custom content type for the folders.

2. Do you mean you click on the lookup value in the list view? In this case, by default, you are redirected to the display form of the item.

3. To display the Id and the name of the document in the lookup field do the following:

enter FieldValuesAsText to Expand property
enter FieldValuesAsText/FileLeafRef to Extra Fields property

Add this code to JavaScript editor to customize lookup's template:

fd.spBeforeRender(function() {
    var template = '';
    template += '<span class="lookup-col">';
    template += '<p class="lookup-title"> #: data.LookupValue # </p>';
    template += '<p class="lookup-desc"> #: data.FieldValuesAsText ? data.FieldValuesAsText.FileLeafRef : "" #';
    template += '</span>';
    fd.field('LookupDocs').widgetOptions = {
        template: template,
        height: 400,
        virtual: {
            itemHeight: 100
        },
        dataSource: {
            pageSize: 16
        }
    }
});

lso · July 14, 2020, 1:56am

Hi @mnikitina,

#3–Thank you, the lookup template code worked!

#2–Yes, I believe it is the display form view of the item. Is there a way to send the click from the lookup value to a view of the items (folders and/or documents) in that ID folder?

#1–I think this code is working now, thank you! When I first click in the lookup search bar everything pops up but when I start to type in a number for the folder ID it shows only folder IDs (and Name fields now) instead of documents like PDFs and Excel spreadsheets.

mnikitina · July 14, 2020, 2:52pm

Hello @lso,

You can use Document Sets instead of Folders. Thus, you can design Display form for this content type and set up redirection to any page using window.location.href
Do you mean that lookup does not filter on form load?

lso · July 14, 2020, 11:33pm

OK, I see. I'll look into this. Thank you, @mnikitina!
Yes, lookup does not filter on form load until I start to type a number. By default all file types pop up in the lookup after initially clicking in the form field.

Another question, after I assign ID folders to someone, is there a way to automatically give them access to view this folder through the lookup choices? Currently, I need to manually give them access through SharePoint for them to see the folder/document. The lookup seems to just give them the link to the item, currently in display form view, but if they don't have access they will be denied here. I then need to manually give them access through SharePoint.

mnikitina · July 15, 2020, 7:18pm

Hello @lso,

You can also try out List or Library control to display specific documents and folders on the form. You can filter documents dynamically using CAML query. This can be an alternative to redirection to a specific folder and granting access to each user separately.
Also, you can have a look at Plumsail Actions that can grant/remove permission to folders automatically. Please find the example here.
Ok, we have been informed about this problem and already investigating it. As a workaround, you can update the filter function, reading datasource twice resolves the case:

function filterLookup() {
    fd.field('LookupDocs').filter = "ContentType eq 'Folder'";
    fd.field('LookupDocs').widget.dataSource.read();
    fd.field('LookupDocs').widget.dataSource.read();
}

lso · July 21, 2020, 2:15am

Hi @mnikitina,

Unfortunately, adding the extra datasource reading line doesn't seem to work for me. It still doesn't automatically filter for folders only once click on the lookup form load field.

We are currently trying to filter for the first level of folders now instead through the ProjectsCritical lookup. We created a column named Level1 in the Projects Critical document library and set the first level of folders to "Yes" for the Level1 column. We didn't set the other second level/sub-folders or documents to anything and left Level1 column blank. Then trying to filter the ProjectsCritical lookup in our Entitlement Form to only pull up only the first level of folders with Level1 = Yes. I combined some new code with the other filtering code but I'm doing something wrong.

function filterLookup() {
var levelfilter = encodeURIComponent('Yes');
fd.field('ProjectsCritical').filter = "Level1 eq '" + levelfilter + "'";
fd.field('ProjectsCritical').filter = "ContentType eq 'Folder'";
fd.field('ProjectsCritical').widget.dataSource.read();
fd.field('ProjectsCritical').widget.dataSource.read();
}

fd.spRendered(function() {
fd.field('ProjectsCritical').ready().then(function() {
filterLookup();
});
});

Any help in this first level folder filtering lookup would be greatly appreciated.
Thank you.

mnikitina · July 21, 2020, 4:46pm

Hello @lso,

We published a fix. Now you can use refresh() instead of widget.dataSource.read().
Filter is based on a OData query. If you want to filter data based on two parameters, the filter should be the following:

fd.field('ProjectsCritical').filter = "Level1 eq '" + levelfilter + "' and ContentType eq 'Folder'";

You can find more information about OData query here.

Please try out this code:

function filterLookup() {
var levelfilter = encodeURIComponent('Yes');
fd.field('ProjectsCritical').filter = "Level1 eq '" + levelfilter + "' and ContentType eq 'Folder'";
fd.field('ProjectsCritical').refresh();
}

fd.spRendered(function() {
fd.field('ProjectsCritical').ready().then(function() {
filterLookup();
});
});

lso · July 21, 2020, 11:51pm

Hi @mnikitina,

Thank you again for the code. For some reason it's not filtering for me still with the new code. ProjectsCritical lookup still pulls up all documents and sub-folders on form load:

Here's the Projects Critical document library with the Level1 Yes/No column:

I've only changed the first level of folders to Yes in the library and left other sub-folders/documents blank in Level1 column. Do you think I need to index the Level1 column or something else for filtering to work? Maybe it's also worth mentioning that this Projects Critical library has over 390,000 items. I wonder if that has something to do with it.

mnikitina · July 22, 2020, 12:22pm

Hello @lso,

Yes, if the source lists have more than 5000 items, you need to index the source list's columns that you intend to filter. You can find more information on how to configure lookup fields pointing to large lists with more than 5000 items here.

lso · July 27, 2020, 7:41pm

Hi @mnikitina,

Thank you for all the assistance! I've indexed our columns. I think there was something wrong with our code for the Level1 column since it is a Yes/No boolean field. It wasn't working so we ended up just creating another single text line column and labeling that first set of folders with the same text. It seems to be working fine now using a text column instead of the boolean type. I'm still not sure why the folders only filter wasn't working on form load but it's okay now since we just show the first level of folders.

mnikitina · July 28, 2020, 7:12am

Hello @lso,

To filter lookup values by Yes/No boolean field you need to use this expression:

//Yes/No field is equal to false
fd.field('LookupFieldName').filter = "YesNoColumName eq 0";

lso · July 28, 2020, 4:04pm

Hi @mnikitina,

I'll test this code out as well. Thank you! You've been of great help.