Send vLookup data in a workflow email

Forums vLooup for SharePoint Send vLookup data in a workflow email

This topic contains 10 replies, has 3 voices, and was last updated by  Alexander Bautz 3 months, 2 weeks ago.

  • Author
    Posts
  • #20731

    Alexander Bautz
    Keymaster

    I got a question from Michel Hayek regarding sending the vLookup table data in a workflow email.

    This is not possible without pulling the data from the vLookup data object and writing it to a multiline text field in your form. This is because the vLookup data only “lives” in the browser, and is not stored in the list item when you save the form.

    To write the data to a multiline field when the form is saved you can use this snippet in your Custom JS. Replace vLookupTasks with the vLookup field name in your form, and Multiline with the multiline field you want to store the data in.

    function dffs_PreSaveAction(){
        write_vLookup_to_multiline("vLookupTasks","Multiline");
        return true;
    }
    
    function write_vLookup_to_multiline(vLookuField,targetField){
        var b = [], val;
        b.push("<table cellpadding='4' cellspacing='0'>");
        // Header row
        b.push("<tr>");
        jQuery.each(spjs.vLookup.dataObj[vLookuField][spjs.dffs.data.thisItemID].fields,function(fin,disp){
            b.push("<th valign='top'>"+disp+"</th>");
        });
        b.push("</tr>");
        // Body
        jQuery.each(spjs.vLookup.dataObj[vLookuField][spjs.dffs.data.thisItemID].items,function(i,item){
            b.push("<tr>");
            jQuery.each(spjs.vLookup.dataObj[vLookuField][spjs.dffs.data.thisItemID].fields,function(fin,disp){
                val = item[fin] !== null ? item[fin] : "";
                val = val.split("<").join("<").split(">").join(">");
                b.push("<td valign='top' style='"+(i%2===1?"background-color:#eaeaea;":"")+"'>"+val+"</td>");
            });
            b.push("</tr>");
        });
        b.push("</table>");
        setFieldValue(targetField,b.join(""));
    }

    Please note that the styling of the table must be done by adding inline style tags to the <tr> or <td> (like I have done to add color to alternating rows). The SharePoint stylesheets will not affect the layout when it is rendered in the email client of the user so you will have to experiment until you find your preferred style.

    Please note that the vLookup table must be made visible in a tab before saving the item for the vLookup dataObj to be available for this script.

    Hope someone finds this useful.

    Alexander

  • #20749

    MYH
    Participant

    Okay, thank you Alexander it worked for me.

  • #26285

    Notrega
    Participant

    I can’t seem to get this to work. I followed the directions and changed vLookupTask with my field name vLookupItem and Multiline with my field name Email_Text, but now it won’t let me save the record at all…

  • #26287

    Notrega
    Participant

    Changed field names and still no go, but here is what I got out of Console View –

  • #26293

    Alexander Bautz
    Keymaster

    The reason is that the vLookup data object is different in NewForm because the list item does not have any ID when it is not yet saved.

    Change the snippet from this:

    jQuery.each(spjs.vLookup.dataObj[vLookuField][spjs.dffs.data.thisItemID].fields,func...

    to this:

    jQuery.each(spjs.vLookup.dataObj[vLookuField][getFieldValue("_vLookupID")].fields,func...

    Please note that this must be done two places. It replaces spjs.dffs.data.thisItemID with getFieldValue(“_vLookupID”).

    Alexander

  • #26295

    Notrega
    Participant

    You sir are a fricking genius – THANK YOU!!
    Beers on me on payday

  • #26297

    Notrega
    Participant

    Last question – is there way to sort or group the view?

  • #26388

    Alexander Bautz
    Keymaster

    I see – change the snippet like this:

    function dffs_PreSaveAction() {
        write_vLookup_to_multiline({
            "fin": "vLookupTasks", // Name of vLookup column
            "writeTo": "Multiline", // Name of field to write to
            "groupBy": "Status" // Name of field to group by
        });
        return true;
    }
    
    function write_vLookup_to_multiline(arg) {
        var b = [], val, arr = [];
        b.push("<table cellpadding='4' cellspacing='0'>");
        // Header row
        b.push("<tr>");
        jQuery.each(spjs.vLookup.dataObj[arg.fin][spjs.dffs.data.thisItemID].fields, function (fin, disp) {
            b.push("<th valign='top'>" + disp + "</th>");
        });
        b.push("</tr>");
        // Body - make array of items
        jQuery.each(spjs.vLookup.dataObj[arg.fin][spjs.dffs.data.thisItemID].items, function (i, item) {
            arr.push(item);
        });
        // Sort by groupBy field
        arr.sort(function (a, b) {
            if (a[arg.groupBy] < b[arg.groupBy]) {
                return -1;
            }
            if (a[arg.groupBy] > b[arg.groupBy]) {
                return 1;
            }
            return 0;
        });
        var groupHeaderTracker = {};
        jQuery.each(arr, function (i, item) {
            if (groupHeaderTracker[item[arg.groupBy]] === undefined) {
                groupHeaderTracker[item[arg.groupBy]] = true;
                b.push("<tr>");
                b.push("<td colspan='99' valign='top' style='padding:5px;background-color:#c5c5c5;'>" + item[arg.groupBy] + "</td>");
                b.push("</tr>");
            }
            b.push("<tr>");
            jQuery.each(spjs.vLookup.dataObj[arg.fin][spjs.dffs.data.thisItemID].fields, function (fin, disp) {
                val = item[fin] !== null ? item[fin] : "";
                val = val.split("<").join("<").split(">").join(">");
                b.push("<td valign='top' style='" + (i % 2 === 1 ? "background-color:#eaeaea;" : "") + "'>" + val + "</td>");
            });
            b.push("</tr>");
        });
        b.push("</table>");
        setFieldValue(arg.writeTo, b.join(""));
    }

    Alexander

You must be logged in to reply to this topic.