Lookup column with date filter

[Nikita Kurguzov]

Dear Jacob,
This might be part of the problem, but not the critical one, at least the filtering should still work, even if dates do not match. The dates do not match, because of the time difference. When you transform date to ISO format, like we do in the code, it automatically switches timezone from your local one to UTC (aka GMT), that causes time to go over midnight and date to change. This can be fixed, but for now, it's more important to ensure that the filtering works properly.

If you didn't change the name in the Source List, then please correct the code to keep using the original name:

Code: Select all

function (term, page) {
  var vergaderdatum = fd.field('Vergaderdatum').value();
  if (vergaderdatum)
    vergaderdatum = "Datum_x0020_overleg eq datetime'" + new Date(vergaderdatum).toISOString() + "'"; 
  else
    vergaderdatum = '';
  
  
  if (!term || term.length == 0) {
    return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField},Datum_x0020_overleg&$filter=" + vergaderdatum + "&$top=10";
  }
    return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField}&$orderby={LookupField},Datum_x0020_overleg&$filter=startswith({LookupField}, '" + encodeURIComponent(term) + "') and " + vergaderdatum + "&$top=10";
}

[jacob]

The errors are gone!

I entered a date 1-4-2018 which exists in Agenda. I get the message: No results.
If i enter the date 31-3-2018 which does not exist in Agenda, the search seems to hang and the console gives the following error:

Code: Select all

jsinitializer.ashx?web=%2Foverleg%2Ftest&list=8050dd86-2d99-432b-9a3a-111158d178b5&field=Agenda_x0020_item&el=cslAgenda_x0020_item&key=csl_init_cslAgenda_x0020_item.js&rev=1521620504828:16 Uncaught RangeError: Invalid time value
    at Date.toISOString (<anonymous>)
    at Plumsail.CSL.CrossSiteLookup.retrieveItemsUrl (jsinitializer.ashx?web=%2Foverleg%2Ftest&list=8050dd86-2d99-432b-9a3a-111158d178b5&field=Agenda_x0020_item&el=cslAgenda_x0020_item&key=csl_init_cslAgenda_x0020_item.js&rev=1521620504828:16)
    at m.fn.init.url (plumsail.csl.crosssitelookup.js:1)
    at plumsail.csl.select2.js:22

[Nikita Kurguzov]

Dear Jacob,
Okay, that is strange. Try to select the same date, get the same result from the Cross-site Lookup and enter the following code into the console:

Code: Select all

var vergaderdatum = fd.field('Vergaderdatum').value();
if (vergaderdatum)
  vergaderdatum = "Datum_x0020_overleg eq datetime'" + new Date(vergaderdatum).toISOString() + "'"; 
else
  vergaderdatum = '';
  
console.log(vergaderdatum);

What will the result look like? Send us a screenshot.

[jacob]

Did you mean this?

https://www.dropbox.com/s/35i55l4t7tc0r ... l.PNG?dl=0

[Nikita Kurguzov]

No, this is good, I understand that it says "No results." Can you reproduce the actual error you've told me about and then use the same code?

[jacob]

Here you go:

https://www.dropbox.com/s/exy3rcpwbuqgc ... 2.PNG?dl=0

[Nikita Kurguzov]

Dear Jacob,
Okay, I see. Can you tell me if you select the date in the window or manually enter the date? Could it be that month and day are in the wrong spot? Try to enter 3-31-2018 instead.

[jacob]

I entered the date manually. Just now i tried the same date picking it from the calender and its throws exactly the same errors.
If i try the format 2018-3-31 (manually) it just says no results found.

[Nikita Kurguzov]

Okay, I see. Try to run just this command from console when you get this error:

Code: Select all

fd.field('Vergaderdatum').value();

[jacob]

Sorry just noticed other format: 3-31-2018 also gives No results found.

If i pick the date 31-3-2018 and run the above console command i get this:

Code: Select all

fd.field('Vergaderdatum').value();
"31-3-2018"