Lookup column with date filter

[Nikita Kurguzov]

No results found is okay, we can work with that. The other error is worse. I can recommend switching Date Field template to Kendo, it will replace default window with a more convenient one, but it's up to you:

Kendo DatePicker Template.png (6.82 KiB) Viewed 7930 times

The fact that the date doesn't match the one that is selected in the other list shouldn't matter as both dates are converted to the UTC before comparison, so they should match if the same date is selected in both. If one of the fields is DateTime, not just Date, then there might be an issue.

[jacob]

Hi Nikita,

Switching the template to Kendo somehow did the trick. The filtering works as expected. I checked the field types and both were date only. Only thing i notice on loading the edit form is this message in the console:

Code: Select all

init.js?rev=l27kKb%2BMq%2FuMKrOMKLV%2B8w%3D%3DTAG0:1 GET https://xx.xx.nl/_layouts/15/plumsail/crosssitelookup/select2/select2_locale_nl-NL.js net::ERR_ABORTED

But again, now it works. If i can do anything to further investigate this issue then please let me know.

Thx for all your help!

[jacob]

Hi Nikita,

So i wanted to roll out the solution to the live environment and i think i found the original problem. At first, both the Agenda and the Documents list had the same field name for the date: Datum_x0020_overleg. Trying to find the issue you know i recreated the date field in Documents to Vergaderdatum. Obviously in the live environement it's still Datum_x0020_overleg, and after rolling out the code and adjusting the field names the filter wasn't working.

So it looks like if you have exactly the same field names in the two lists, the filtering isn't working. Only when i recreated the date column to Vergaderdatum en changing the code accordingly, the filter worked again. To recreate the colum in the live environment isn't really an option since i will loose all data.

Can we make this work with identical field names in the two lists?

[Nikita Kurguzov]

Dear Jacob,
There really is no issue with using the same name for columns, it should work just the same. Make sure that the names are correct and you replace them in all appropriate places in the code, but it should work like it did for different names. For example, in my environment, I've used two columns both named simply "Date".

[jacob]

Hi Nikita,

Can you look at following screenshot: https://www.dropbox.com/s/mp9jfk5ezb7n9 ... G.jpg?dl=0

Steps i took:
- create form with forms designer for Agenda
- create form with forms designer for Bijlagen
- create lookup field to Agenda

Screenshot shows that in fact it's a form from Forms Designer. Also shows the fd error again.
Code i have in lookup field:

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 = 'leeg';
  
console.log(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";
}

Do you see anything wrong?

[Nikita Kurguzov]

Dear Jacob,
While I can't see anything wrong with the code, I can see that the Lookup field looks like a regular Lookup, not like a Cross-site Lookup should look. If you've updated regular Lookup to be a Cross-site Lookup instead, you also need to open the form, delete the old Lookup field and add the new one again, then re-save the form, so it updates to an actual Cross-site Lookup field.

The error is not important in this case, it's caused by the fact that the form is opened in dialog mode, but it shouldn't affect the code of the Cross-site Lookup. If you want to be able to run tests from console, add the following code to JavaScript editor on the form:

Code: Select all

window.fd = fd;

[jacob]

Hi Nikita,

Just deleting the lookup field and adding it again, made it work. It's quite misleading though. If you initially look at the form (before the change) it looks like it has the right cross site lookup.

https://www.dropbox.com/s/q6r575tut8cdl ... 4.PNG?dl=0