CSL filtering on multiple columns

[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.

[Dmitry Kozlov]

Hi,

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

[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.

[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.

[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.

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

[Nigel Hertz]

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