lookup filter per view

rss

Posted by Eax - Nov 30 ’15 at 04:14

Hello guys,

I have a question : is it possible to get a filtering lookup not by items but by a view?

i have 3 Colums : Reporting Period - Start Date - End Date.

In my custom view, my Start Date is less than or equal at [Today] & End Date is equal or more than at [Today].

In my lookup, i want to show all concerned Reporting Period.

Is it possible?

Best regards,

  • rostislav
    rostislav
    Moderator
    Member for: 4 years 7 months 2 days
    #1 by rostislav Nov 30 ’15 at 07:29

    Hi,

    No, unfortunatelly it's not possbile. You'll have to replicate the conditions you use in the view in the request query. It's not hard. Here's the basic idea of what you'll need to do:

    function (term, page) {
    
      if (!term || term.length == 0) {
    
        return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField}&$filter=(Id ge 2) and (Id lt 5)&$orderby=Created desc&$top=10";
    
      }
    
      return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField}&$orderby={LookupField}&$filter=((Id ge 2) and (Id lt 5)) and startswith({LookupField}, '" + encodeURIComponent(term) + "')&$top=10";
    
    } 

    Notice the $filter variable. What we've done is added is a condition that tells the query executor to return items with Id value in between 2 (inclusive) and 5 (exclusive).

    Check the following link for more information:

    https://msdn.microsoft.com/en-us/library/office/fp142385.aspx

  • Eax
    Member
    Member for: 4 years 1 month 8 days
    #2 by Eax Nov 30 ’15 at 12:28

    [#1]: Hi,

    Thanks

    I try it but "Loading Error".

    This is my code :

    function (term, page) {

    if (!term || term.length == 0) {

    return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField}&$filter=(Date_x0020_de_x0020_d_x00e9_but ge 'datetime') and (Date_x0020_de_x0020_fin le 'datetime')&$orderby=Created desc&$top=10";

    }

    return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField}&$orderby={LookupField}&$filter=((Date_x0020_de_x0020_d_x00e9_but ge 'datetime') and (Date_x0020_de_x0020_fin le 'datetime')) and startswith({LookupField}, '" + encodeURIComponent(term) + "')&$top=10";

    }

     

    Something wrong ?

    Best regards,

  • rostislav
    rostislav
    Moderator
    Member for: 4 years 7 months 2 days
    #3 by rostislav Dec 1 ’15 at 05:43

    [#2]:

    If you want to use datetime to do your filtering, then the $filter variable would look something like this:

    ...$filter=(Date_x0020_de_x0020_d_x00e9_but ge datetime'" + today.toISOString() + "') and...

    where today is a JavaScript Date object. You'd be able to create it anew like this:

    var today = new Date();

    or retrieve it from a Date/Datetime field like this:

    var today = fd.field('DateTimeField').control('getDate');

    More about date objects:

    http://www.w3schools.com/jsref/jsref_obj_date.asp

  • Eax
    Member
    Member for: 4 years 1 month 8 days
    #4 by Eax Dec 2 ’15 at 03:43

    [#3]: Thanks,

    This is my message error :

    {"error":{"code":"-1, Microsoft.SharePoint.Client.InvalidClientQueryException","message":{"lang":"fr-FR","value":"L'expression \u00ab\u00a0(Date_x0020_de_x0020_d_x00e9_but ge datetime')function toISOString() {    [native code]}(Date_x0020_de_x0020_fin le datetime')\u00a0\u00bb n'est pas valide."}}}

     

    And this is my code actually :

    function (term, page) {
    var today = new Date();

    if (!term || term.length == 0) {

    return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField}&$filter=(Date%5Fx0020%5Fde%5Fx0020%5Fd%5Fx00e9%5Fbut ge datetime')" + today.toISOString + "(Date%5Fx0020%5Fde%5Fx0020%5Ffin le datetime')&$orderby=Created desc&$top=10";

    }

    return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField}&$orderby={LookupField}&$filter=((Date%5Fx0020%5Fde%5Fx0020%5Fd%5Fx00e9%5Fbut ge datetime')" + today.toISOString + "(Date%5Fx0020%5Fde%5Fx0020%5Ffin le datetime')) and startswith({LookupField}, '" + encodeURIComponent(term) + "')&$top=10";

    }

     

  • rostislav
    rostislav
    Moderator
    Member for: 4 years 7 months 2 days
    #5 by rostislav Dec 2 ’15 at 08:09

    [#4]:

     

    Use the following code:

    ..
    if (!term || term.length == 0) {
    	return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField}&$filter=(Date_x0020_de_x0020_d_x00e9_but le datetime'" + today.toISOString() + "') and (Date_x0020_de_x0020_fin ge datetime'" + today.toISOString() + "')&$orderby=Created desc&$top=10";
    }
    	return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField}&$orderby={LookupField}&$filter=(Date_x0020_de_x0020_d_x00e9_but le datetime'" + today.toISOString() + "') and (Date_x0020_de_x0020_fin ge datetime'" + today.toISOString() + "') and startswith({LookupField}, '" + encodeURIComponent(term) + "')&$top=10";
    .. 

     

  • Eax
    Member
    Member for: 4 years 1 month 8 days
    #6 by Eax Dec 2 ’15 at 09:00

    [#5]: Thank you !

Displaying 1 to 6 of 6 messages