DFFS Reverse Lookup

August 30, 2019: Updated to fix a typo in 2.2.1.3 Function call.

I got this request:

The OOTB Reverse Lookup in SharePoint is very useful for several use cases. Using DFFS cascading dropdowns means that capability is lost. Is there any way to emulate reverse lookup when using DFFS cascading dropdowns with large lists (over 5K), perhaps with some custom JavaScript? Something that works with a single choice or multiple choice?

https://spjsblog.com/forums/topic/can-custom-js-emulate-sp-reverse-lookup/#post-26634

This article includes two different approaches – one for connections with a standard lookup column, and one with connection based on text values (like when you use cascading dropdowns or vLookupID / vLookupParentID to match the parent and child).

1. Add this to the DFFS Tab where you want the items to show (in the Parent list)

<div style="font-size:1.5em;font-weight:300;">Related items</div>
<div id="relatedItemsPlaceholder"></div>

2.1 Code for Lookup column connection

Use this approach if your parent and child is connected with a normal SharePoint lookup column.

2.1.1 Add this to your Custom JS (in the Parent list)

2.1.1.1 Base function – do not edit

 function getRelatedItems(arg) {
    var isGuid = arg.listName.charAt(0) === "{", b = [];
    jQspjs.ajax({
        "url": arg.listBaseUrl + "/_api/Web/Lists/" + (isGuid ? "getById" : "getByTitle") + "('" + arg.listName + "')/items?$filter=" + arg.filter + "&$select=" + (arg.select.length > 0 ? arg.select.join(",") : "*") + ",Id,FileDirRef" + (arg.expand.length > 0 ? "&$expand=" + arg.expand.join(",") : "") + "&$top=" + arg.itemLimit,
        "type": "GET",
        "headers": {
            "accept": "application/json; odata=verbose",
            "content-type": "application/jsom;odata=verbose",
            "X-RequestDigest": document.getElementById("__REQUESTDIGEST").value
        },
        "success": function (data) {
            if (data.d.results.length > 0) {
                // Header row
                b.push("<tr class='dffs_relatedItems_headerRow'>");
                if (arg.linkToItem.view.on) {
                    b.push("<td class='dffs_relatedItems_headerCell'></td>");
                }
                if (arg.linkToItem.edit.on) {
                    b.push("<td class='dffs_relatedItems_headerCell'></td>");
                }
                jQuery.each(arg.viewFields, function (i, o) {
                    b.push("<td class='dffs_relatedItems_headerCell'>" + o.label + "</td>");
                });
                b.push("</tr>");
                jQuery.each(data.d.results, function (i, item) {
                    var viewLink = item.FileDirRef + "/DispForm.aspx?ID=" + item.Id, editLink = item.FileDirRef + "/EditForm.aspx?ID=" + item.Id;
                    b.push("<tr class='dffs_relatedItems_row'>");
                    if (arg.linkToItem.view.on) {
                        b.push("<td class='dffs_relatedItems_cell'><img style='cursor:pointer;vertical-align:middel;' onclick='openInDlg(\"" + viewLink + "\")' src='" + arg.linkToItem.view.icon + "'></a></td>");
                    }
                    if (arg.linkToItem.edit.on) {
                        b.push("<td class='dffs_relatedItems_cell'><img style='cursor:pointer;vertical-align:middel;' onclick='openInDlg(\"" + editLink + "\")' src='" + arg.linkToItem.edit.icon + "'></a></td>");
                    }
                    jQuery.each(arg.viewFields, function (j, o) {
                        var a = o.fin.split("/"), val;
                        if (a.length === 1) {
                            val = item[a[0]];
                        } else {
                            val = item[a[0]][a[1]];
                        }
                        if (o.parseFunction !== null) {
                            val = window[o.parseFunction](val, item);
                        }
                        b.push("<td class='dffs_relatedItems_cell'>" + val + "</td>");
                    });
                    b.push("</tr>");
                });
            }
            if (b.length === 0) {
                b.push("<div style='padding:10px;'>" + arg.labels.noItemsFound + "</div>");
            } else {
                b.unshift("<table class='dffs_relatedItems_table'>");
                b.push("</table>");
            }
            jQuery("#" + arg.placeholderID).html(b.join(""));
        },
        "error": function (err) {
            console.log(err);
        }
    });
}

// Open form in dialog
function openInDlg(url) {
    var options = {
        "url": url
    };
    SP.UI.ModalDialog.showModalDialog(options);
}

2.1.1.2 Parse function for date values

 function dateObjToFriendlyDate(v, item) {
    var r = "";
    try {
        var d = new Date(v);
        r = d.toLocaleDateString(_spPageContextInfo.currentUICultureName);
    } catch (ignore) {
        // Nothing
    }
    return r;
}

2.1.1.3 Function call

getRelatedItems({
    "placeholderID": "relatedItemsPlaceholder",
    "listName": "{8c75ce59-f1a0-4823-b5a1-81afa9884b27}", // Use DisplayName or GUID (GUID must start and end with curly brace)
    "listBaseUrl": _spPageContextInfo.webServerRelativeUrl,
    "filter": "ParentItem eq '" + spjs.dffs.data.thisItemID + "'", // Please note that the field you want to filter by must be indexed if your liste has more than 5000 items
    "select": ["Title", "Author/Id", "Author/Title", "Created"],
    "expand": ["Author"],
    "linkToItem": {
        "view": {
            "on": true,
            "icon": "data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAABAAAAAQCAYAAAAf8/9hAAABMUlEQVQ4T6WSvUsDQRDF522bYKmgtaS2kKvD3P4PoiD4WQmSKpDCUtBOOxVNJQjWVnuTA7urrIOtCNopB3YzEkkgJpeQj2kWhsePt28eqDve+2VVrQPYIKJFIvo0swfn3FkI4b2nG3zRWTCzB3BlZhd5njezLPuOomihVCptAqgR0ZGIhCIIqtVqxTl3D+AgSZKXQVEcx2tmdqOqW2matoccMPM5EbVF5HaUTWbeI6KKiNSJyPp1YOZH51wjhPA6CuC9X1XVUwD7SZJ8/QPEcfxkZrsi8jHGwRKAuyLd/A66GbyJyOVMGcx9hXE9KJfLOwAaZvZjZtutVuu5sEid5bgmquoKgGtVPR6E/DVxkmHmdSJqElGtv5UTA7pfHYJMBSiCTA3oQTqZmNnhTIA+Jye/ts29i6m1dSsAAAAASUVORK5CYII="
        },
        "edit": {
            "on": true,
            "icon": "data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAABAAAAAQCAYAAAAf8/9hAAAA6klEQVQ4T+WTPQoCMRBG5yNrs9VWijewzwWEqa30BN5ABGsbUbAQBFvP4AUWFvcSgqWVIFYWCkNGIruw+LOrtSmSEPLeTMIMKBvM3ACwVtW+P6rax3F8fNzzUwYnRNTKhRXrTlXbXlIUPKLn5k+CYqb/KrDW1sIwjNI0PRGR/vyJ1towiqI5EfUBTH4SZPACwE1ElkEQrL4WFGFjzFhEpkR0+UrwDs6yGFUKyuAkSa6lgir4uRdeSpmZewA6xpiBf3Oeto+cl3ppBsw8A1BX1SaAvYiMinBZNx5UtUtEQwBn59zGObd9hr3gDipMDAs8Vm/dAAAAAElFTkSuQmCC"
        }
    },
    "viewFields": [
        {
            "label": "Title",
            "fin": "Title",
            "parseFunction": null
        },
        {
            "label": "Author",
            "fin": "Author/Title",
            "parseFunction": null
        },
        {
            "label": "Created",
            "fin": "Created",
            "parseFunction": "dateObjToFriendlyDate"
        }
    ],
    "labels": {
        "noItemsFound": "There are no related items"
    },
    "itemLimit": 3
});

2.1.1.4. This example uses the following lists

The parent list has only the Title field.
The child list ha a lookup to the parent list.
Please note that only 3 items are retrieved because of the itemLimit setting in the function call.
This item does not have any child items.

2.2 Code for text field connection

Use this approach if your parent and child list are connected with a common text string – for example when using cascading dropdown or vLookupID / vLookupParentID.

2.2.1 Add this to your Custom JS (in the Parent list)

2.2.1.1 Base function – do not edit

 function getRelatedItemsText(arg) {
    var isGuid = arg.listName.charAt(0) === "{", b = [], vf = ["ID", "FileDirRef"], res;
    jQuery.each(arg.select, function (i, fin) {
        vf.push(fin);
    });
    res = spjs.utility.queryItems({
        "listName": arg.listName,
        "query": arg.filter,
        "viewFields": vf,
        "rowLimit": arg.itemLimit
    });
    if (res.count > 0) {
        // Header row
        b.push("<tr class='dffs_relatedItems_headerRow'>");
        if (arg.linkToItem.view.on) {
            b.push("<td class='dffs_relatedItems_headerCell'></td>");
        }
        if (arg.linkToItem.edit.on) {
            b.push("<td class='dffs_relatedItems_headerCell'></td>");
        }
        jQuery.each(arg.viewFields, function (i, o) {
            b.push("<td class='dffs_relatedItems_headerCell'>" + o.label + "</td>");
        });
        b.push("</tr>");
        jQuery.each(res.items, function (i, item) {
            var viewLink = "/" + item.FileDirRef.split(";#")[1] + "/DispForm.aspx?ID=" + item.ID, editLink = "/" + item.FileDirRef.split(";#")[1] + "/EditForm.aspx?ID=" + item.ID;
            b.push("<tr class='dffs_relatedItems_row'>");
            if (arg.linkToItem.view.on) {
                b.push("<td class='dffs_relatedItems_cell'><img style='cursor:pointer;vertical-align:middel;' onclick='openInDlg(\"" + viewLink + "\")' src='" + arg.linkToItem.view.icon + "'></a></td>");
            }
            if (arg.linkToItem.edit.on) {
                b.push("<td class='dffs_relatedItems_cell'><img style='cursor:pointer;vertical-align:middel;' onclick='openInDlg(\"" + editLink + "\")' src='" + arg.linkToItem.edit.icon + "'></a></td>");
            }
            jQuery.each(arg.viewFields, function (j, o) {
                var a = o.fin.split("/"), val;
                if (a.length === 1) {
                    val = item[a[0]];
                } else {
                    val = item[a[0]][a[1]];
                }
                if (o.parseFunction !== null) {
                    val = window[o.parseFunction](val, item);
                }
                b.push("<td class='dffs_relatedItems_cell'>" + val + "</td>");
            });
            b.push("</tr>");
        });
    }
    if (b.length === 0) {
        b.push("<div style='padding:10px;'>" + arg.labels.noItemsFound + "</div>");
    } else {
        b.unshift("<table class='dffs_relatedItems_table'>");
        b.push("</table>");
    }
    jQuery("#" + arg.placeholderID).html(b.join(""));
}

// Open form in dialog
 function openInDlg(url){
     var options = {
         "url":url
     };
     SP.UI.ModalDialog.showModalDialog(options);
 }

2.2.1.2 Parse function for date values and people picker

 // Parse function for people picker
function getPeoplePickerName(v, item) {
    var r = "";
    if (v !== null) {
        r = v.split(";#")[1];
    }
    return r;
}

// Parse function for date fields
function dateObjToFriendlyDate(v, item) {
    var r = "";
    try {
        var d = new Date(v);
        r = d.toLocaleDateString(_spPageContextInfo.currentUICultureName);
    } catch (ignore) {
        // Nothing
    }
    return r;
}

2.2.1.3 Function call

function dffs_ready(){
     getRelatedItemsText({
         "placeholderID":"relatedItemsPlaceholder",
         "listName":"{f4b05663-4689-4e67-8928-855b3492dcd3}", // Use DisplayName or GUID (GUID must start and end with curly brace)
         "listBaseUrl":_spPageContextInfo.webServerRelativeUrl,
         "filter": "<Where><Contains><FieldRef Name='DFFS_PN_Lookup' /><Value Type='Text'>"+getFieldValue("Title")+";</Value></Contains></Where>",
         "select":["Title","Author","Author","Created"],
         "expand":["Author"],
         "linkToItem":{
             "view":{
                 "on":true,
                 "icon":"data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAABAAAAAQCAYAAAAf8/9hAAABMUlEQVQ4T6WSvUsDQRDF522bYKmgtaS2kKvD3P4PoiD4WQmSKpDCUtBOOxVNJQjWVnuTA7urrIOtCNopB3YzEkkgJpeQj2kWhsePt28eqDve+2VVrQPYIKJFIvo0swfn3FkI4b2nG3zRWTCzB3BlZhd5njezLPuOomihVCptAqgR0ZGIhCIIqtVqxTl3D+AgSZKXQVEcx2tmdqOqW2matoccMPM5EbVF5HaUTWbeI6KKiNSJyPp1YOZH51wjhPA6CuC9X1XVUwD7SZJ8/QPEcfxkZrsi8jHGwRKAuyLd/A66GbyJyOVMGcx9hXE9KJfLOwAaZvZjZtutVuu5sEid5bgmquoKgGtVPR6E/DVxkmHmdSJqElGtv5UTA7pfHYJMBSiCTA3oQTqZmNnhTIA+Jye/ts29i6m1dSsAAAAASUVORK5CYII="
             },
             "edit":{
                 "on":true,
                 "icon":"data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAABAAAAAQCAYAAAAf8/9hAAAA6klEQVQ4T+WTPQoCMRBG5yNrs9VWijewzwWEqa30BN5ABGsbUbAQBFvP4AUWFvcSgqWVIFYWCkNGIruw+LOrtSmSEPLeTMIMKBvM3ACwVtW+P6rax3F8fNzzUwYnRNTKhRXrTlXbXlIUPKLn5k+CYqb/KrDW1sIwjNI0PRGR/vyJ1towiqI5EfUBTH4SZPACwE1ElkEQrL4WFGFjzFhEpkR0+UrwDs6yGFUKyuAkSa6lgir4uRdeSpmZewA6xpiBf3Oeto+cl3ppBsw8A1BX1SaAvYiMinBZNx5UtUtEQwBn59zGObd9hr3gDipMDAs8Vm/dAAAAAElFTkSuQmCC"
             }
         },
         "viewFields":[
             {
                 "label":"Title",
                 "fin":"Title",
                 "parseFunction":null
             },
             {
                 "label":"Author",
                 "fin":"Author",
                 "parseFunction":"getPeoplePickerName"
             },
             {
                 "label":"Created",
                 "fin":"Created",
                 "parseFunction":"dateObjToFriendlyDate"
             }
         ],
         "labels":{
             "noItemsFound":"There are no related items"
         },
         "itemLimit":3
     });
 }

2.2.1.4 This example uses the following lists

DFFS_PN_Lookup is a plain text multiline field used with Cascading dropdown with multichoice looking up the Title field in the PartNumberList.
When you click on one part number it will list all the ticket where this part number is listed. Please note that only 3 is show because of the itemLimit setting in the function call.

3. Change the parameters in the function call like this

placeholderID

Type: string

This is the ID used in the DFFS TAB as placeholder for the table.

listName

Type: string

This is the GUID or the DisplayName of the Child list.

listBaseUrl

Type: string

This is the base url of the Child list. Use the variable:

 _spPageContextInfo.webServerRelativeUrl

if the list is in the current site, or type in the base url like this if it is in another site:

/Sites/YourSite

filter

Type: string

When you use example one for use with lookup column connection this is a REST filter to get the correct items from the Child list. Replace the field name “ParentItem” from the example to match the lookup field name in the Child list. The variable spjs.dffs.data.thisItemID represents the current items ID.

When using text field connection this is a CAML query where you set the correct query to get the child items. Look at the CAML query example in the code snippet above.

select

Type: array of strings

This is an array of the field names you want to return from the query. If you use the lookup column connection and you want to return a people picker or a lookup field, you must specify the name like this to get both the Id and the Title of a people picker:

["Author/Id","Author/Title"]

If you use the text field connection you only add the plain field internal name like this:

["Author"]

expand

Type: array of strings

Only used with the lookup column connection. This array must contain all the people pickers and lookup columns you specify on the format shown above where you access one or more properties like FieldName/Property:

["Author"]

linkToItem

Type: object

This setting specifies whether or not to show the view and edit links to the items. Set “on” to true and specify the path to the icon you want to use – or keep the base64 encoded example icon.

viewFields

Type: array of objects

This is the specification of the fields you will be showing in the table. Set the “label”, the “fin” and alternatively the “parseFunction”. There is an example of a parseFunction for the Created field called dateObjToFriendlyDate – you find the function in the above code snippet.

labels

Type: object

This is used to specify the labels used in the solution – currently only one label is used.

itemLimit

Type: integer

Set the maximum number of items you want to return. If you set it to for example 10 and only have 5 items only 5 items will be show but if there are 11 items the last one will be skipped.

4. Add this to your Custom CSS (in the Parent list) 

 #relatedItemsPlaceholder{
    padding:5px;
    border:1px #e1dfdd solid;
}
.dffs_relatedItems_table{
    border-collapse:collapse;
}
.dffs_relatedItems_headerCell{
    padding:2px 3px;
    font-size:1.3em;
    font-weight:300;
}
.dffs_relatedItems_cell{
    padding:2px 3px;
    font-size:1.1em;
    font-weight:300;
}
.dffs_relatedItems_row:hover td{
    background-color:#f3f2f1;
}

You must read trough the code and modify the argument to the function call to getRelatedItems.

Post any questions in the comments below.

Alexander

8 Comments on “DFFS Reverse Lookup”

  1. I have carefully implemented this but I get this error when I set Click function name to “getRelatedItems” in the Parent list tab where I want the Related items to appear and then I click the Tab in Display form:

    [DFFS: click function name “getRelatedItems”]
    TypeError: arg.listName is undefined

    I initially placed the child list GUID in Line 85
    “listName”: “{1ACC9539-803D-41AF-8870-C59454BB0DA1}”, // Child list GUID
    and then added to Line 2 as well (still get same error)
    var isGuid = arg.listName.charAt(0) === “{1ACC9539-803D-41AF-8870-C59454BB0DA1}”, b = [];

    I think I’m just missing a parameter setting somewhere. Any help much appreciated.

    Mike

  2. Unfortunately still getting same message.

    I have confirmed that your troubleshooting tips return “function” twice when Parent Display form loads, i.e.,
    // Check that jQuery is loaded:
    alert(typeof($));
    // Check that a specific function is loaded:
    alert(typeof(getRelatedItems));

    Function call arguments are as follows:

    getRelatedItems({
    “placeholderID”: “ReverseLookup”,
    “listName”:”{1ACC9539-803D-41AF-8870-C59454BB0DA1}”,
    “listBaseUrl”: “/sites/abcdef”,
    “filter”: “DFFS_Parent_Lookup eq ‘” + spjs.dffs.data.thisItemID + “‘”,
    “select”: [“Title”],
    “expand”: [“Author”],

    Error message when clicking the DFFS Parent list tab:

    DFFS: click function name “getRelatedItems”]
    TypeError: arg.listName is undefined

    Mike

    1. Are you calling getRelatedItems directly from your rule? – if so, you must change it to for example init_getRelatedItems and then wrap your function call like this in Custom JS:

      function init_getRelatedItems (){
  // Call getRelatedItems inside
  getRelatedItems({
    "placeholderID": "ReverseLookup",
    ...
    ...
  });
}

  3. No longer getting the undefined error, so progress. Thanks for the help on that.

    However, script is returning the following in my Parent DFFS Reverse Lookup tab regardless of my parameter entries in the function call:

    Related items
    There are no related items

    Any ideas?

  4. For some reason I cannot load an attachment in these post comments. Also, there is no formatting bar for code, bold, etc. Tried both IE and Firefox.

  5. Hi,
    I’m actually a bit confused myself – I have mostly used the forum and wonder if attachments never was possible in the normal page comments?

    Just create a new forum topic and post your code and images there.

    Alexander

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.