Page 1 of 1
CSL filtering on multiple columns
Posted: 25 Jan 2017
by Nigel Hertz
Hi, I'm creating a new form, and it has a few lookup fields on it. I need one of them to be able to be filtered on the selections in TWO others. Is this possible? Unfortunately my javascript leaves a lot to be desired.
There are 3 columns - Stream, Region and Contract.
Stream has Corporate, Private, Other
Region has North, South, Central, Head Office
Contract has about 500 rows, but each one belongs to ONE specific Stream and Region (a contract is unique to a combo of stream and region)
I want to be able to select one (or more) streams and one (or more) regions, and have all the contracts against those selections populate in the selection box.
Re: CSL filtering on multiple columns
Posted: 25 Jan 2017
by Dmitry Kozlov
Hi,
That's possible with the Cross-site Lookup. Please, read the post
http://spform.com/forms-designer- ... office-365
Re: CSL filtering on multiple columns
Posted: 25 Jan 2017
by Nigel Hertz
Hi Dmitry,
Yes - I can get it working with one lookup, however I need to figure out a way of filtering against 2 different lists. So if someone selects A and B from list 1 and 2, only the items in list X show that have properties of 1 and 2.
If 1 and 7 are selected, then list X should show only items with 1 and 7.
The lookup needs to show values based on the selections of 2 lists, not 1.
Re: CSL filtering on multiple columns
Posted: 25 Jan 2017
by Nigel Hertz
Hi Dmitry, I've just re-looked at that other thread. I didn't scroll down far enough - my apologies. I didn't see the last comment in the thread where it talks about 2 lookup filtering. I'll give that a try now.
Re: CSL filtering on multiple columns
Posted: 25 Jan 2017
by Nigel Hertz
No, that didn't work, as it's not what I need.
I need to filter field 3 based on the selections of field 1 AND 2.
Re: CSL filtering on multiple columns
Posted: 25 Jan 2017
by Dmitry Kozlov
Hi Nigel,
Here is just a sample. You have to use your own field names (internal names):
Code: Select all
function (term, page) {
// Getting the selected lookups
var lookup1Id = fd.field('Lookup1').value();
if (!lookup1Id) {
lookup1Id = 0;
}
var lookup2Id = fd.field('Lookup2').value();
if (!lookup2Id) {
lookup2Id = 0;
}
// Filtering by the selected lookups
if (!term || term.length == 0) {
return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField},Lookup1/Id,Lookup2/Id&$orderby=Created desc&$expand=Lookup1/Id,Lookup2/Id&" +
"$filter=Lookup1/Id eq " + lookup1Id + " and Lookup2/Id eq " + lookup2Id + "&$top=10";
}
return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField},Lookup1/Id,Lookup2/Id&$orderby={LookupField}&$expand=Lookup1/Id,Lookup2/Id&" +
"$filter=startswith({LookupField}, '" + term + "') and Lookup1/Id eq " + lookup1Id + " and Lookup2/Id eq " + lookup2Id + "&$top=10";
}
Re: CSL filtering on multiple columns
Posted: 25 Jan 2017
by Nigel Hertz
Brilliant, thanks Dmitry. I'll give that a try now.