Lookup with filter on empty datefield

Discussions about Cross-site Lookup
Locked
Mapleleaf
Posts: 29
Joined: Tue Oct 25, 2016

25 Nov 2016

built a lookup which filters the results by a datefield and others. This works fine and shows results where upto is greater or equal today.

Is it possible to combine this with an or operator and filter the field upto that it shows empty upto fields as well?

The query should show all data where upto is ge today or empty.


return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField},FG/Id,upto&$orderby=Title asc&$expand=FG/Id&$filter=FG/Id eq " + FGId + " and (upto ge datetime'" + today.toISOString() +"')

YuriyMedvedev
Moderator
Posts: 33
Joined: Wed Sep 21, 2016

28 Nov 2016

Hi! Try use this filter:

"{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField},FG/Id,upto&$orderby=Title asc&$expand=FG/Id&$filter=(FG/Id eq " + FGId + ") and ((upto ge datetime'" + today.toISOString() + "') or (Date eq null))"+...

Mapleleaf
Posts: 29
Joined: Tue Oct 25, 2016

03 Jan 2017

Thanks for the reply and help.

This did not work. Could it be that SP2013 uses some kind of date and not "null" to save an empty date-field?

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

03 Jan 2017

Hi,

We have tested the solution in SP2013, it works. Could you provide your request?

Mapleleaf
Posts: 29
Joined: Tue Oct 25, 2016

04 Jan 2017

Some Field-Names have changed but this works:

function (term, page) {
// Getting the selected Fachgebiet
var FGId = fd.field('Fachgebiet').value();
if (!FGId) {
FGId = 0;
}

// Filtering by the selected Fachgebiet
if (!term || term.length == 0) {
var today = new Date();
today.setHours(3);
return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField},Fachgebiet/Id,bis&$orderby=Title asc&$expand=Fachgebiet/Id&$filter=Fachgebiet/Id eq " + FGId + " and (bis ge datetime'" + today.toISOString() +"')&$top=30";
}
return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField},Fachgebiet/Id,bis&$orderby={LookupField}&$expand=Fachgebiet/Id&$filter=startswith({LookupField}, '" + term + "') and Fachgebiet/Id eq " + FGId + " and (bis ge datetime'" + today.toISOString() +"')&$top=30";
}

This does not work:

function (term, page) {
// Getting the selected Fachgebiet
var FGId = fd.field('Fachgebiet').value();
if (!FGId) {
FGId = 0;
}

// Filtering by the selected Fachgebiet
if (!term || term.length == 0) {
var today = new Date();
today.setHours(3);
return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField},Fachgebiet/Id,bis&$orderby=Title asc&$expand=Fachgebiet/Id&$filter=(Fachgebiet/Id eq " + FGId + ") and ((bis ge datetime'" + today.toISOString() +"') or (bis eq null))&$top=30";
}
return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField},Fachgebiet/Id,bis&$orderby={LookupField}&$expand=Fachgebiet/Id&$filter=startswith({LookupField}, '" + term + "') and (Fachgebiet/Id eq " + FGId + ") and ((bis ge datetime'" + today.toISOString() +"') or (bis eq null))&$top=30";
}

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

05 Jan 2017

Hi,

I have just tested the second snippet and it worked fine. What do you mean by 'does not work'? - It does not return any items or shows an error? Our support team can assist you, if you provide temporary access to the site. It will take 100 support minutes:
http://spform.com/buy

Mapleleaf
Posts: 29
Joined: Tue Oct 25, 2016

06 Jan 2017

It shows a "Loading Error".

Mapleleaf
Posts: 29
Joined: Tue Oct 25, 2016

06 Jan 2017

It shows a "Loading Error".

Locked
  • Information
  • Who is online

    Users browsing this forum: No registered users and 4 guests