Cross-site lookup - 'Loading Failed' error

[kevyn]

The URLs are as follows:

column 1 is:

beta/_layouts/15/FldEdit.aspx?List=%7B44B4850F-54F9-46F5-AA7D-F35E9CDD2C2D%7D&Field=Project_x0020_Number

column 2 is:

beta/_layouts/15/FldEdit.aspx?List=%7B44B4850F-54F9-46F5-AA7D-F35E9CDD2C2D%7D&Field=Title

Therefore my new code is:

function (term, page) {

if (!term || term.length == 0) {

return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField}&$orderby=Created desc&$top=10";

}

return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField}&$orderby={LookupField}&" +

"$filter=startswith(Project_x0020_Number, '" + encodeURIComponent(term) + "') or " +

"startswith(Title, '" + encodeURIComponent(term) + "')&$top=10";

}



However I still get the same error - is there anything you can suggest please?

[Dmitry Kozlov]

You cannot sort the result set by calculated columns, so, please, use the following snippet:

Code: Select all

function (term, page) {
    if (!term || term.length == 0) {
        return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField}&$orderby=Created desc&$top=10";
    }

    return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField}&$orderby=Title&" +
    "$filter=startswith(Project_x0020_Number, '" + encodeURIComponent(term) + "') or " +
    "startswith(Title, '" + encodeURIComponent(term) + "')&$top=10";
}

[kevyn]

This is excellent - thank you so much. My final question - how would you go about adding a 3rd column? (thank you!)

[Dmitry Kozlov]

Do you mean that you need to filter the result set by three fields? If so, here is the code:

Code: Select all

function (term, page) {
    if (!term || term.length == 0) {
        return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField}&$orderby=Created desc&$top=10";
    }

    return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField}&$orderby=Title&" +
    "$filter=startswith(Project_x0020_Number, '" + encodeURIComponent(term) + "') or " +
    "startswith(Title, '" + encodeURIComponent(term) + "') or " +
    "startswith(field3, '" + encodeURIComponent(term) + "')&$top=10";
}

[kevyn]

That's what I thought, but it doesn't seem to have any effect (the dropdown only includes 2 fields)


function (term, page) {

if (!term || term.length == 0) {

return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField}&$orderby=Created asc&$top=10";

}



return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField}&$orderby=Title&" +

"$filter=startswith(Project_x0020_Number, '" + encodeURIComponent(term) + "') or " +

"startswith(Client_x0020_ID, '" + encodeURIComponent(term) + "') or " +

"startswith(Title, '" + encodeURIComponent(term) + "')&$top=10";

}

[Dmitry Kozlov]

If you need to display all three fields in the result set, you should join them into a single calculated column or modify the 'Item format' template as described in out blog:
http://spform.com/office-365/sett ... office-365

[kevyn]

I'm sorry I thought we already established that a calculated column would not work? Your suggestion in did not seem to work either... If we can figure out how to get 3 columns into the results set, then we will purchase a licence

[kevyn]

Please ignore my previous post - I fixed the issue now by adding hte 3rd column to the calculated column. My mistake. Thanks for all your help

[Dmitry Kozlov]

You can display a calculated column in the drop-down but you cannot search by this field. That is why we built a complex condition in the 'Request items' template. So, if you need to display multiple fields at once, you need to create a calcualted column that joins these fields and select it as a display column in the Lookup Manager. Next, open the Advanced settings and replace {LookupField} in $orderby and $filter clauses with regular fields as I did in the last sample.