Lookup column with date filter

Discussions about Cross-site Lookup
User avatar
Nikita Kurguzov
Posts: 888
Joined: Mon Jul 03, 2017

21 Mar 2018

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

jacob
Posts: 27
Joined: Thu Nov 30, 2017

21 Mar 2018

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

User avatar
Nikita Kurguzov
Posts: 888
Joined: Mon Jul 03, 2017

21 Mar 2018

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.
Cheers


User avatar
Nikita Kurguzov
Posts: 888
Joined: Mon Jul 03, 2017

21 Mar 2018

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?
Cheers


User avatar
Nikita Kurguzov
Posts: 888
Joined: Mon Jul 03, 2017

21 Mar 2018

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.
Cheers

jacob
Posts: 27
Joined: Thu Nov 30, 2017

21 Mar 2018

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.

User avatar
Nikita Kurguzov
Posts: 888
Joined: Mon Jul 03, 2017

21 Mar 2018

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

Code: Select all

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

jacob
Posts: 27
Joined: Thu Nov 30, 2017

21 Mar 2018

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"

Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 1 guest