Lookup column with date filter

[jacob]

Hi,

I have two lists. One is called Agenda with meeting items. The second one is called Documents. When i add a record in Documents i created a lookup column to the title column in Agenda. This works fine. When the user is adding a record in Documents he also has to provide a date. This date also exists in Agenda. I want to filter the results in the lookup column with the date the user entered. I've got this:

Code: Select all

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

At the moment i'm not getting any results. Can you help me with this?
Thx in advance.

Kind regards,
Jacob

[Nikita Kurguzov]

Dear Jacob,
Please try something like this, it should work and show all results by default and if date is selected, the results should be filtered:

Code: Select all

function (term, page) {
  var vergaderdatum = fd.field('Datum_x0020_overleg').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},Datum_x0020_overleg&$orderby={LookupField}&$filter=startswith({LookupField}, '" + encodeURIComponent(term) + "') and " + vergaderdatum + "&$top=10";
}

[jacob]

Hi Nikita,

Thx for you response. After applying your code it kept searching and i didn't get any results. After some debugging i found that it already stops working on the first line: var vergaderdatum = fd.field('Datum_x0020_overleg').value();

I verified the internal name which is correct. Even recreated the date column to see if it made any difference.
Any idea why this is happening?

[Nikita Kurguzov]

Dear Jacob,
Not sure why that would be the case, the same code works as intended for me. Try to open the form and run the following code from the console:

Code: Select all

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

Try to first select date and then run the code, and try to run it if no date is selected.
You can also run the following code from the console:

Code: Select all

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

[jacob]

On loading the form i'm getting this in the console:

VM857:1 Uncaught ReferenceError: fd is not defined
at <anonymous>:1:1
(anonymous) @ VM857:1
init.js?rev=l27kKb%2BMq%2FuMKrOMKLV%2B8w%3D%3DTAG0:1 GET https://xx.xx.nl/_layouts/15/plumsail/c ... e_nl-NL.js net::ERR_ABORTED

The lookup column however looks fine when i don't add a date in the from. On adding a date and click the drop down i'm getting this:

VM1028:1 GET https://xx.xx.nl/overleg/test/_api/web/ ... 1559106284 400 (Bad Request)

The dropdown now says: Loading failed.

Just running the first line in the console as you suggested results in the same message as above:
VM1201:1 Uncaught ReferenceError: fd is not defined
at <anonymous>:1:1

To see if it made any difference i changed the name of the column in the Document library. So currently the code looks like this:

Code: Select all

function (term, page) {

  var vergaderdatum = fd.field('Vergaderdatum').value();
  if (vergaderdatum)
  vergaderdatum = "Vergaderdatum'" + 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";
}

[Nikita Kurguzov]

Dear Jacob,
The form needs to be opened and saved in Forms Designer, then this part of the code will work. fd is a Forms Designer manager global variable, it allows easy access to the fields on the form.

[jacob]

Hi nikita,

I noticed this. At first i just used the standard form but now i'm already using a form with Froms Designer. However, i just changed the setting in the library to not use a dialog windows. Now the fd error is gone. The Loading failed remains with this error in the console:

plumsail.csl.jquery.js:5 GET https://xx.xx.nl/overleg/test/_api/web/ ... 1561397528 400 (Bad Request)

In the form i entered a date 1-4-2018. In the error i see a date 2018-01-03??

[jacob]

Sorry, the full error message is:

Code: Select all

plumsail.csl.jquery.js:5 GET https://xx.xx.nl/overleg/test/_api/web/lists('ec3237d0-e1df-4b4f-ae24-55f8ac22ee25')/items?$select=Id,Title,Datum_x0020_overleg&$filter=Vergaderdatum%272018-01-03T23:00:00.000Z%27&$top=10&_=1521561397528 400 (Bad Request)

[Nikita Kurguzov]

Dear Jacob,
Didn't you change the name of the field to Vergaderdatum? But in select, you still have Datum_x0020_overleg, perhaps this is what gives the error. Try this instead:

Code: Select all

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

[jacob]

Hi Nikita,

Hi changed the column in the library Documents to Vergaderdatum. The date column in Agenda is still Datum_x0020_overleg. The users adds a new document and enters the date in Vergaderdatum. I want to use Vergaderdatum to filter the Datum_x0020_overleg column in Agenda. The column Datum_x0020_overleg is the right one, because if the user doesn't enter a date, i get all results back from Agenda.

I notice something which i said earlier. For testing purposes, i changed the lookup column to Datum_x0020_overleg to see which values i get back. The dates i get back do not correspondent with the dates that are in the table. The dates i get back is the date in Datum_x0020_overleg minus one day.

So for example: in the list Agenda i have a date 2018-06-01 in the Datum_x0020_overleg column. If i set the lookup column to the same list and the same column, the date 2018-05-31T22:00:00Z is returned. Could this be the problem?