Using a field in the list to filter a lookup

[dominique.beaudin]

I am trying to filter a list on a page by a value in the list.

For example: I edit a record where the ContractorEquipmentID = 234

In the list I want to populate (to select equipment), I want to only choose the items that belong to contractor is 234 - I have tried following examples for cascading lookups, but maybe this is different? I get a "failed to load" message when I attempt it.

the fields that link the lists together is ContractorEquipmentID (on both sides)

What am I doing wrong?

thanks in advance!!!!

function (term, page) {
// Getting the selected contractore
var countryId = fd.field('ContractorEquipmentID').value();
if (!countryId) {
countryId = 0;
}

// Filtering by the selected country
if (!term || term.length == 0) {
return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField},ContractorEquipmentID/Id&$orderby=Created desc&$expand=ContractorEquipmentID/Id&$filter=ContractorEquipmentID/Id eq " + countryId + "&$top=10";
}
return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField},ContractorEquipmentID/Id&$orderby={LookupField}&$expand=ContractorEquipmentID/Id&$filter=startswith({LookupField}, '" + term + "') and ContractorEquipmentID/Id eq " + countryId + "&$top=10";

[Nikita Kurguzov]

Dear Dominique,
The code looks correct, at least at the first glance. What you need to make sure is that you use the correct InternalName for both the lookup field on the form, and the field in the list that you have a lookup to. Is it identical in both cases or is it different? Also, is it a lookup in both cases?

Code: Select all

function (term, page) {
// Getting the selected contractor
var contractorId = fd.field('INTERNAL_NAME_OF_CURRENT_FORM_FIELD').value();
if (!contractorId) {
  contractorId = 0;
}

// Filtering by the selected country
if (!term || term.length == 0) {
  return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField},INTERNAL_NAME_OF_LOOKUP_ITEM_FIELD/Id&$orderby=Created desc&$expand=INTERNAL_NAME_OF_LOOKUP_ITEM_FIELD/Id&$filter=INTERNAL_NAME_OF_LOOKUP_ITEM_FIELD/Id eq " + contractorId + "&$top=10";
}
return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField},INTERNAL_NAME_OF_LOOKUP_ITEM_FIELD/Id&$orderby={LookupField}&$expand=INTERNAL_NAME_OF_LOOKUP_ITEM_FIELD/Id&$filter=startswith({LookupField}, '" + term + "') and INTERNAL_NAME_OF_LOOKUP_ITEM_FIELD/Id eq " + contractorId + "&$top=10";
}

InternalName of a field can be checked in Forms Designer when you open a form:

InternalName.png (4.54 KiB) Viewed 6914 times

Or in List Settings, when editing a column in URL:

InternalNameColumn.png (17.04 KiB) Viewed 6914 times

If both Internal Names are correct, but the code still doesn't work, please, open a browser's console and when you see an error in the Cross-site Lookup field, you'll see an error in the console. Please, send us a screenshot of it.

[dominique.beaudin]

The internal names are definitely correct. the first ContractorEquipmentID is a field on the form. The second is a lookup. I did try both as a lookup to test that out, but it should be a single value.

[Nikita Kurguzov]

Dear Dominique,
I can see some JS errors for Related Items code, something is not working right. But in regards to Cross-site Lookup, only the last error is of interest. It contains the REST API URL, which you can try to open on your own in another browser tab and see if it will return any results - you can also try to manipulate it, to achieve the desired effect.

I would also recommend posting it here, so we can take a closer look at the actual URL as well.

RestURL.png (78.37 KiB) Viewed 6911 times

[dominique.beaudin]

Thank you so much for the help

Oddly, I am getting this message
<m:error xmlns:m="http://schemas.microsoft.com/ado/2007/0 ... s/metadata">
<m:code>-1, Microsoft.SharePoint.SPException</m:code>
<m:message xml:lang="en-US">
The field or property 'ContractorEquipmentID' does not exist.
</m:message>
</m:error>

I triple checked and the field does exist. I even created a test lookup table with that field and I still get the error.

The internal name on the list that is being "looked up" is attached.

[dominique.beaudin]

I tried this using the built in Field title and get the same result... so strange!

[Nikita Kurguzov]

Dear Dominique,
Can you show us the URL you are using? Preferably, as text, so we can copy and paste it. Finally, are there a lot of items in the Source List? I doubt it, but there can be issues if there are over 5000 of them.

[dominique.beaudin]

no it's not over 5000- this is a test list so it's tiny. this list only has 2 columns, ContractorEquipmentID and Title (plus other SharePoint Created columns)- in this case the value for "title" is 217111111

https://hnirisk.sharepoint.com/HNIDataH ... 1148525330

[Nikita Kurguzov]

Dear Dominique,
For Title, this code will not work(FIELDNAME/Id is used only for lookups, as well as expand), you can try this instead:

Code: Select all

https://hnirisk.sharepoint.com/HNIDataHub/_api/web/lists('{8001d0af-a5c6-4d7f-9d78-d25c0093ce58}')/items?$select=Id,Title&$orderby=Created desc&$filter=Title eq 'Test'&$top=10

Should retrieve all items with Title == "Test".

As for the other field, I'd recommend inserting the following URL into the browser and see what it returns:

Code: Select all

https://hnirisk.sharepoint.com/HNIDataHub/_api/web/lists('{8001d0af-a5c6-4d7f-9d78-d25c0093ce58}')/items?$select=Id,ContractorEquipmentID/Id&$expand=ContractorEquipmentID&$orderby=Created desc&$top=10

If it still gives an error, most likely the field is not a lookup.

If it does give the result, please select a different Display Field for the vin Cross-site Lookup. It does not support other lookups as Display Field, just like a regular lookup doesn't. This might be the reason of the error.

[dominique.beaudin]

That last post you sent gave me the right clues!!! I got it working!! Thank you!!!!!