CSL filtering on multiple columns

rss

Posted by Nigel Hertz - Jan 25 ’17 at 02:50

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
    Dmitry Kozlov
    Admin
    Member for: 5 years 10 months 26 days
    #1 by Dmitry Kozlov Jan 25 ’17 at 06:15

    Hi,

    That's possible with the Cross-site Lookup. Please, read the post:

    http://formsdesigner.blogspot.com/2013/07/cascading-drop-downs-with-cross-site.html

  • Nigel Hertz
    Nigel Hertz
    Member
    Member for: 3 years 9 months 7 days
    #2 by Nigel Hertz Jan 25 ’17 at 06:19

    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
    Nigel Hertz
    Member
    Member for: 3 years 9 months 7 days
    #3 by Nigel Hertz Jan 25 ’17 at 07:25

    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
    Nigel Hertz
    Member
    Member for: 3 years 9 months 7 days
    #4 by Nigel Hertz Jan 25 ’17 at 07:34

    [#3]: 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
    Dmitry Kozlov
    Admin
    Member for: 5 years 10 months 26 days
    #5 by Dmitry Kozlov Jan 25 ’17 at 10:41

    [#4]: Hi Nigel,

    Here is just a sample. You have to use your own field names (internal names):

    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
    Nigel Hertz
    Member
    Member for: 3 years 9 months 7 days
    #6 by Nigel Hertz Jan 25 ’17 at 10:43

    [#5]: Brilliant, thanks Dmitry. I'll give that a try now.

Displaying 1 to 6 of 6 messages