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

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

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