Lookup with filter on empty datefield

[Mapleleaf]

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]

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]

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?

[Dmitry Kozlov]

Hi,

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

[Mapleleaf]

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

[Dmitry Kozlov]

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]

It shows a "Loading Error".

[Mapleleaf]

It shows a "Loading Error".