All posts by Alexander Bautz

DFFS Reverse Lookup

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

I’ll make this short – just follow the below steps.

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>

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

 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);
        }
    });
}

// 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;
}

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

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
});

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;
} 

This example uses the following lists

The parent list has only the Title field.
The child list ha a lookup to the parent list.

When you click the First parent item you see the children

Please note that only 3 items are retrieved because of the itemLimit setting in the function call.

When you click the Second parent item you see this message

This item does not have any child items.

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

Post any questions in the comments below.

Alexander

CommentBox for SharePoint v3.1.0.8

I have released a new version of CommentBox for SharePoint with some bugfixes and some new features.

Look here for the complete change log: https://spjsblog.com/commentbox-for-sharepoint/commentbox-v3-x-change-log/

Post any comments below, but please post bugs or questions in the forum: https://spjsblog.com/forums/forum/comment-box-for-sharepoint/

Alexander

Error in SPJS-vLookup_frontend.js in v4.4.4.2 (July 31, 2019) fixed

There were an error in the file SPJS-vLookup_frontend.js in the latest version 4.4.4.2 dated July 31, 2019 that made text fields prefilled when adding a child item show two times like “Text value, Text value”.

Download the package again from here and update the file found in /DFFS/plugins/SPJS-vLookup_frontend.js and ensure it shows as v2.2.135.

Sorry for the inconvenience,
Alexander

DFFS Package updated to v4.4.4.1

I have fixed a few bugs and small issues, and added some more error handling regarding custom functions. You find the compete change log here, and the updated files in the download section of the user manual.

Post comments below, but use the form if you have questions or you think you have found a bug.

Best regards,
Alexander