vLookup and a document template

[Paul Woodcock]

Hi Alex,

I have a SharePoint list connected to a SharePoint Document library via a vlookup. The document library has a word document template connected to it. When you are in the parent list is there a way of opening a new document generated from the template when you click + add new, instead of browsing for a document?

Usually when I create a new document from the template in a webpage I use the following script:

<a onclick="CoreInvoke('createNewDocumentWithProgIDEx',event,'https:\u002f\u002fcollaboration.contoso.com\u002fteams\u002fRegistrars\u002fTrainingExcessDeaths\u002fForms\u002fRegisterDeath\u002fPre-Registration-form.docx', 'https:\u002f\u002fcollaboration.contoso.com\u002fteams\u002fRegistrars\u002fTrainingExcessDeaths', 'SharePoint.OpenDocuments', false, 'ms-word')">

I tried this script in the from field of prefill values in child and {url} in the To field. But this did not work.

Is it possible to do this?

Thanks

Paul

• This topic was modified 4 years, 6 months ago by Alexander Bautz. Reason: Obfuscated the site URL

Attachments:

1. 

   image1.jpg
   

2. 

   image2.jpg
   

[Alexander Bautz]

Hi,
This is not possible without some custom js. I created this function to let you create a new document from a template, set the metadata used to connect it to your item / vLookup and then open it in Word for the user.

Please not that I could not access the actual template from the /Forms folder of the document library so you must either create a separate document library (named Templates in my example) or add the template document to your current document library as a docx and NOT a dotx (in case you use quickparts to show metadata in the document).

You must change the six variables in the top of the function.

function newDocFromTemplate() {
    var vLookupFieldname = "vLookupDocuments"; // Name of the vLookup field in this list
    var templateLibRelUrl = _spPageContextInfo.webServerRelativeUrl + "/Templates"; // Relative URL to the document library where the template is found
    var templateName = "Template_1.docx"; // Name of the tempate. It must be docx and not dotx
    var targetLibGUID = "{ab6a0e84-43e0-4acd-a76a-3d96e9a70933}"; // GUID of the save target document library. Used when getting and setting metadata to connect to this vLookup
    var targetLibPath = _spPageContextInfo.webServerRelativeUrl + "/Shared Documents"; // Relative URL to the document library you want to save to
    var newDocName = "New_doc_" + jQuery.now() + ".docx"; // Name of new document
    jQuery.ajax({
        "url": _spPageContextInfo.webServerRelativeUrl + "/_api/web/folders/GetByUrl('" + templateLibRelUrl + "')/Files/getbyurl('" + templateName + "')/copyTo(strNewUrl = '" + targetLibPath + "/" + newDocName + "',bOverWrite = true)",
        "method": "POST",
        "headers": {
            "Accept": "application/json; odata=verbose",
            "X-RequestDigest": jQuery("#__REQUESTDIGEST").val()
        },
        "success": function() {
            // Successfully created file - get id and set metadata
            var res = spjs.utility.queryItems({
                "listName": targetLibGUID,
                "query": "<Where><Eq><FieldRef Name='FileLeafRef' /><Value Type='Text'>" + newDocName + "</Value></Eq></Where>",
                "viewFields": ["ID", "EncodedAbsUrl"]
            });
            if (res.count > 0) {
                var item = res.items[0];
                var uRes = spjs.utility.updateItem({
                    "listName": targetLibGUID,
                    "id": item.ID,
                    "data": {
                        "_vLookupParentID": getFieldValue("_vLookupID") // Change this to match the query you use in the vLookup config to retrieve the documents
                    }
                });
                if (uRes.success) {
                    // Refresh vLookup table
                    jQuery("#vLookupManualRefresh_" + vLookupFieldname).trigger("click");
                    var a = document.createElement("a");
                    a.setAttribute("href", "ms-word:ofe|u|" + item.EncodedAbsUrl);
                    a.setAttribute("target", "_blank");
                    document.getElementsByTagName("body")[0].appendChild(a);
                    a.click();
                }
            }
        },
        "error": function(err) {
            // Failed to copy document
            alert("Failed to copy:\n\n" + JSON.stringify(err));
        }
    });
}

Add this to your Custom JS and call the function from a button created in a HTML section like this:

<input style="margin-left:0;" type="button" onclick="newDocFromTemplate();return false;" value="New from template">

Let me know how this works out.

Alexander

[Paul Woodcock]

Hi Alex,

I’m getting the following error message:

Failed to copy:
{“readyState”:4,”responseText”:”The length of the URL for this request exceeds the configured maxUrlLength
value.”,”Status”:400,”statusText”:”Bad Request”}

I’m wondering if the document is not encoded correctly, and this is being pushed into the url?

Paul

[Alexander Bautz]

Which version of SP are you running?

I’m not sure what could cause this, but, ensure all variables in the top of the function are correct.

Alexander

[Paul Woodcock]

Hi,

Its an on premise SharePoint 2013 site. I also occasionally get the attached error. I’ve checked the variables again. I’m fairly certain they are correct but I’ll go back over them.

Paul

Attachments:

1. 

   Error.jpg
   

[Alexander Bautz]

The error message looks like it is the SharePoint “error message page” HTML code you get in return. You will see more error details if you open the dev tools (hit F12) and look at the Console and Network tabs. I recommend using Google Chrome (or the new Edge with Chromium engine) because they have better debugging capabilities.

It may very well be that the code only works in SP Online – I’ll see if I can test it in an on-prem site later to see if I find out more.

Alexander

[Alexander Bautz]

I tested it in an on-prem 2016 site without problems. Can you show me the six variables you use in the top of the function?

Alexander

[Paul Woodcock]

Hi Alex

My variables are:

var vLookupFieldname = "vLookup_Children"; // Name of the vLookup field in this list
    var templateLibRelUrl = _spPageContextInfo.webServerRelativeUrl + "/teams/Registrars/TrainingExcessDeaths/Forms/RegisterDeath"; // Relative URL to the document library where the template is found
    var templateName = "Pre-Registration-form.docx"; // Name of the tempate. It must be docx and not dotx
    var targetLibGUID = "{BD751507-B0AF-4ED3-9315-800384BFD011}"; // GUID of the save target document library. Used when getting and setting metadata to connect to this vLookup
    var targetLibPath = _spPageContextInfo.webServerRelativeUrl + "/teams/Registrars/TrainingExcessDeaths/Forms"; // Relative URL to the document library you want to save to
    var newDocName = "New_death_" + jQuery.now() + ".docx"; // Name of new document

[Alexander Bautz]

Is the “Forms” here a folder in a document library or is it a subsite with a document library named RegisterDeath in it? – please explain the URL path so I know what is subsites, document libraries and folders.

Alexander

[Paul Woodcock]

Hi Alex,

– Registrars is the site
– TrainingExcessDeaths is the document library
– Forms is part of the file path but I’m not sure what it is
– RegisterDeath is the content type where the pre-registration-form.docx document template is located.

I don’t have any folders in the document library.

I’ve tried just using teams/Registrars/TrainingExcessDeaths as the file path for templateLibRelUrl and targetLibPath. But I still get the same error message.

From looking at the Microsoft REST documentation I think there is a slight difference between SharePoint 2016/online and Sharepoint 2013. I’m going to try altering the REST api code tomorrow when I’m back in work.

Paul

[Alexander Bautz]

OK, you must move the template out from the /Forms folder like I specified in the original comment above:

Please not that I could not access the actual template from the /Forms folder of the document library so you must either create a separate document library (named Templates in my example) or add the template document to your current document library as a docx and NOT a dotx (in case you use quickparts to show metadata in the document).

This means that if you add the template to the same document library as you plan to save the files in your templateLibRelUrl must be

var templateLibRelUrl = _spPageContextInfo.webServerRelativeUrl + "/teams/Registrars/TrainingExcessDeaths";

and your targetLibPath must be:

var targetLibPath = _spPageContextInfo.webServerRelativeUrl + "/teams/Registrars/TrainingExcessDeaths";

Ensure you have a file named Pre-Registration-form.docx in the TrainingExcessDeaths library.

Alexander

[Paul Woodcock]

Hi Alex,

Thank you for taking the time to help me. I got it working. I had not appreciated that the ‘template’ must be an item in a document library rather than stored as a template within the content type settings.

Paul

• This reply was modified 4 years, 6 months ago by Paul Woodcock.

[Alexander Bautz]

Thanks for the feedback – I’m glad it worked out.

Alexander

[Author]

Posts