CSL filtering on multiple columns

Discussions about Cross-site Lookup
Locked
User avatar
Nigel Hertz
Posts: 9
Joined: Tue Nov 10, 2015
Contact:

25 Jan 2017

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.

User avatar
Dmitry Kozlov
Site Admin
Posts: 1524
Joined: Thu Jun 07, 2012

25 Jan 2017

Hi,

That's possible with the Cross-site Lookup. Please, read the post
http://spform.com/forms-designer- ... office-365

User avatar
Nigel Hertz
Posts: 9
Joined: Tue Nov 10, 2015
Contact:

25 Jan 2017

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.

User avatar
Nigel Hertz
Posts: 9
Joined: Tue Nov 10, 2015
Contact:

25 Jan 2017

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.

User avatar
Nigel Hertz
Posts: 9
Joined: Tue Nov 10, 2015
Contact:

25 Jan 2017

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.

User avatar
Dmitry Kozlov
Site Admin
Posts: 1524
Joined: Thu Jun 07, 2012

25 Jan 2017

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";
}

User avatar
Nigel Hertz
Posts: 9
Joined: Tue Nov 10, 2015
Contact:

25 Jan 2017

Brilliant, thanks Dmitry. I'll give that a try now.

Locked
  • Information
  • Who is online

    Users browsing this forum: No registered users and 8 guests