Filtering DropDowns in ListControls

Dario_Chiga · May 5, 2022, 8:38am

Dear Plumsail Team

I wanted to ask about some filtering configuration and if it's possible to implement this:

Basically i have a List, in which i have implemented a Form. Within the Form i added controls which display other Lists.

This is for example on of the Lists shown in the form.

So now what i want the user to do is, when creating a new Item, to select an Analysis-Reference item which is a lookup Column to the other List implemented in the Form.

Is it possible to filter this DropDown and only show LookUps from the Analysis-List, that are connected to the Gap-ID?

The DropDown is in the Review List. and the List is shown in the Gap-List form.

Margo · May 5, 2022, 9:05am

Hello @Dario_Chiga,

I'm sorry, I didn't fully understand your idea.

The Analysis-Reference drop down that you want to filter is on the parent form. Is that right?

And you want to filter it by Gap-ID. How users select Gap-ID? Or is it predefined?

Dario_Chiga · May 5, 2022, 11:44am

yeah it was quite difficult actually to explain what i want to achieve

let me try again with some more pictures:

I have a List GAP Analysis.

Within this List i implemented a Form and in this Form i used the Control "List or Library" to show two other Lists. (1st the big table, 2nd the small Review List at the bottom)

Both lists, have a lookup field for the original Item in the Gap Analysis List.

so when i create an item in the Review List i want the field "Analysis-Reference" to only show the items which have the same "Gap-Analysis Reference" of the original Item.

As of now it just shows every existing item of this big table list which also includes other items which do not have the link to the original Gap-Analysis Item.

Margo · May 5, 2022, 12:24pm

Hello @Dario_Chiga,

If you want to bind GAP Analysis list and two other lists, you need to set up parent child relationship. For this:

Make sure that child lists have lookup that points to GAP Analysis list.
In the Plumsail designer, select List or Library control >> Data Source and select the Lookup field that points to GAP Analysis list.

Thus, on the form users will see only items related to the currently opened item. It works even on a new form.

You can find more information about parent-child relationship here.

Dario_Chiga · May 9, 2022, 7:43am

Yes i know. If you look at the first entry of this post here there is a diagram which shows the relations of the list.

What i want to achieve is, that when creating a new item in one child list, it should show me only entry of the other child list which has the same parent in one lookup column

Margo · May 9, 2022, 9:34am

Hello @Dario_Chiga,

So do you want to filter Review list when a new item is created in Analysis List? Is that correct?

You can detect when a new list item is created in one control using the code:

fd.control('Control1').$on('change', function(changeData) {
    if (changeData.type === 'add') {
        alert('New Item has been added);
    }
});

And you can filter list dynamically using the code:

fd.control('Control1').ready(function() {
    fd.control('Control1').filter = '<Eq><FieldRef Name="Title"/><Value Type="Text">Test</Value></Eq>';
    fd.control('Control1').refresh();
});

You can combine these two code to filter one control when user adds new item using another control.

Dario_Chiga · May 9, 2022, 9:43am

i don't think that's possible.
Probably because i cannot adress a column within a control.

And the Detection is not needed as well, because the Field i try to filter already has a lookup on this list. so it displays all the items without problem.

maybe this picture will show the intention a little bit better of what i'm trying to achieve.

So the "Analyses" List is filled out before the Review List.
And in the Review List, one should be able to create a task and link it to the "Analyses-Item" which should be reviewed.

So i try to filter the "Analyses-Reference" Field in the Review list to show only the Items of the "Analyses" which have the same "Gap-Analysis-Reference"

Margo · May 9, 2022, 10:58am

@Dario_Chiga,

I'm sorry, but I still have questions.

So, basically, you want to filter the Review list by two fields. Is that correct?

What do you mean? Do you want to filter Analyses-Reference Lookup within Review list by Gap-Analysis-ID field?

Dario_Chiga · May 9, 2022, 11:42am

Exaclty. When creating a new Item in Review List, the Analyses-Reference LookUp Field should only show the items which have the same GAP-Analysis-ID

Margo · May 10, 2022, 5:43am

@Dario_Chiga,

Thank you for clarification!

As I see from the screenshot, the List or Library control is in inline editing mode. In this case, you can use the code below. Please update field names in the code before testing.

fd.spRendered(function() {
    fd.control('SPDataTable1').$on('edit', function(editData) {
        //filter Lookup field by parent field value
        editData.field('AnalysesRef').filter = "GapID/Id eq '" + fd.field("GapID").value.LookupId + "'";
        editData.field('AnalysesRef').useCustomFilterOnly = true;
    });
});

Find more information in Manipulate fields in inline editing mode of List or Library article.

Dario_Chiga · May 10, 2022, 12:51pm

Okay this looks very promising and easy to achieve.
Just some small questions about the field names i should be using.

Did i assign them correctly ?

Margo · May 11, 2022, 4:59am

Hello @Dario_Chiga,

Yes, everything looks correct, except of the parent item ID. Use this code to get the item id:

fd.itemId

Dario_Chiga · May 12, 2022, 5:59am

var ID = fd.itemId;
editData.field('Analysis_x002d_Reference').filter = "Gap_x002d_Reference/Id eq '" + ID + "'";
editData.field('Analysis_x002d_Reference').useCustomFilterOnly = true;

like this ?

Dario_Chiga · May 12, 2022, 7:52am

Nevermind. I had to change it to dialog because there was a request for the analysis-field to be multiple choice.
so i could achieve my wish to filter them based on the gap analysis request with the filter function in the forms designer.

Thanks for the help. i will definitely use this code in the future when inline editing should be mandatory.

Dario_Chiga · July 25, 2022, 1:52pm

Hello

Same problem different approach.

How can i filter a LookUp Column based on string it contains ?
I do not have specific ID's or such but just know the string it should containt.

function filterLookup(){		       
    // setting filtration
    var v = 'QA'
    fd.field('Approver_x0020_1').filter = "Title substringof '" + v + "'";
    fd.field('Approver_x0020_1').widget.dataSource.read();
    fd.field('Approver_x0020_1').refresh();
}

//filter lookup when form opens
fd.field('Approver_x0020_1').ready().then(filterLookup);

and in the Field i added the extra field "Title"

Margo · July 26, 2022, 5:57am

Hello @Dario_Chiga,

The correct syntax for checking if field value contains string is this:

fd.field('Approver_x0020_1').filter = "substringof('" + v +"', Title)";

The queries are based on OData, learn more here.