Follow

Merging data into Word Tables

Article last updated on the 26th of June 2020.

Contents

1. Introduction

2. Requirements

3. Configuration

   3.1. Merging of list data into Word tables

       3.1.1. First row

           3.1.1.1. [wpListName:Documents]

           3.1.1.2. [wpViewUrlName:TestView]

       3.1.2. Rows from row 2 on to field row

       3.2.3. Field row

       3.2.4. Rows after the field row

       3.1.5. Example

   3.2. Merging of Relations data into Word tables

       3.2.1. [wpRelationsTypeNAmes:Hund,Kat]

       3.2.2. [wpShowOnlyActiveRelations:0]

       3.2.3. Example

   3.3. Links to lists

   3.4. Set Subject property

1. Introduction

It is possible to merge data from a list on an entity site into a Microsoft Word table. This is done by setting up one or more tables in the Word templates where this feature is wanted (one table for each list)

These tables must be supplied with “tags” which lets the system know which data to merge into the tables.

It is important that the different tags are typed in exactly as presented in the following. It is therefore also important to give special notice to capitalization, to avoid unnecessary spaces and make sure all tags are encircled by square brackets.

2. Requirements

This feature requires a WorkPoint Server or a WorkPoint 365 solution. If you do not have this you can contact WorkPoint Sales at sales@workpoint.dk for more information.

3. Configuration

The following sections surrounds the configuration of this feature.

   3.1. Merging of list data into Word tables

The following table describes the structure of a table for use with this feature:

       3.1.1. First row

In the first row and cell of the table the name and the view of the list to be used as filter and sorting is stated. Note that these data MUST be stated in the first cell of the table. This row is removed by the system during execution of the data merging.

It is important to note that if the states list and view cannot be found during execution of the data merge, the row will not be removed, and a red error message is shown.

           3.1.1.1. [wpListName:Documents]

This tag states the name of the list from which data should be fetched. In this example the list is called “Documents”. This field is mandatory.

           3.1.1.2. [wpViewUrlName:TestView]

This tag states the URL name of the view that is to be used as filter and sorting. In this example the view is the “TestView”.

       3.1.2. Rows from row 2 on to the field row

Here the user can input custom static content. Typically, there will be a row with headers for the fields from the list which is merged into the table.

       3.1.3. Field row

In this row, the field-tags which indicates where fields from the list should be placed, is inserted. In each cell in a row, one or multiple tags can be inserted. Static content is also allowed.

There may only be one field row, and field tags may only be placed in this row.

During execution of the data merge, the field tags are replaced with the actual data from the list. Any formatting of the field tags is preserved.

It is important to note that the field names which are to be stated in the field tags are the internal SharePoint names. Also be aware of differences in upper- and lower-case letters.  If a field cannot be found in the list, the field tag is replaced with an error message in red color text.

For document lists, the fields “LinkfileName” and “LinkfilenameMenu” will be replaced with links to the documents.

For regular lists, the fields “LinkTitle” and “LinkTitleNoMenu” will be replaced with links to the property view of the elements.

       3.1.4. Rows after the field row

In the rows after the field row the user can input custom static content.

       3.1.5. Example

In this example, we will make a Project Task Report document template. The template will pull meta data from a project entity as well as all tasks on the project into a table.

A table with the following elements are created for use on the "Projects" business module of a Project Management solution:

  1. A set of meta data fields from the project have been selected to be merged into the document.
  2. A table has been added to contain all tasks of the project. In the first row, we have stated which list and view to pull data from ([wpListName:Tasks] and [wpViewUrlName:AllItems]).
  3. The task title is pulled by using the "[wpFieldName:Title]" value.
  4. The task status is pulled by using the "[wpFieldName:Status]" value.
  5. The person the task is assigned to is pulled by using the "[wpFieldName:AssignedTo]" value.
  6. The Due date for the task is pulled by using the "[wpFieldName:DueDate]" value.

If we generate a document based on this template, the result may look something like this:

Project Task Report.docx - Google Chrome

   3.2. Merging of Relations data into Word tables

It is also possible to merge relations data between entities into a word table. The way this is done is much like when merging list data into a word table as described in the previous sections. The structure of the table is as follows:

       3.2.1. [wpRelationsTypeNames:Hund,Kat]

This tag states the names of the types of relations to be fetched. Multiple names are separated by commas. If one wishes to fetch all relations regardless of their types, the value can be set to *. This field is mandatory. The tag MUST be stated in the first cell.

       3.2.2. [wpShowOnlyActiveRelations:0]

This tag states if only active relations should be fetched. A value of 0 means that all relations will be fetched while a value of 1 excludes inactive relations. This tag MUST be stated in the first cell.

The eligible fields that can be used in the field row are:

       3.2.3. Example

In this example, we will pull relations data for a project into a word table.

On a project in a Project Management solution, we have set up the following relations:

Design of Bike0057 - Home - Google Chrome

For reporting, we have created the following template for use on the Projects business module:

Project Task Report.dotx - Word
  1. A set of meta data fields from the project have been selected to be merged into the document.
  2. A table has been added to contain all relations of the project. In the first row, we have stated the relation types we want to pull from the system. These are stated in the "[wpRelationsTypes:Company,Contact,Project]" value. We also define that we want all relations, regardless of whether they are active or not in the value "[wpShowOnlyActiveRelations:0]".
  3. The relation titles are pulled by using the "[wpFieldName:RelationBTitle]" value.  
  4. The relation start dates are pulled by using the "[wpFieldName>Start]" value.
  5. The relation end dates are pulled by using the "[wpFieldName>End]" value.
  6. The relation descriptions are pulled by using the "[wpFieldName:Description]" value.

A document generated based on this template may look something like the following:

Project Relations Report.docx - Google Chrome

   3.4. Set Subject property

If one wishes for the subject property in Word to be filled with values from the entity (or the entity’s parent) that is being merged from, the following syntax can be inserted into the Word template’s subject property:

[wpListName:Companies,wpFieldName:wpCity]

wpListName:Companies states the entity list, while wpFieldName:wpCity is the internal name  for a field in the list. Note that the two pieces of information are separated by a comma without spaces around it.

Example:

The city [wpListName:Companies,wpFieldName:City] is a big city. Address is [wpListName:Companies,wpFieldName:wpPostalCode] [wpListName:Companies,wpFieldName:wpAddress1]

Note that if the business module from which data is to be pulled, i.e. the wpListName value, has translations, you must use the site collection language version of the title.

   3.5. Merging data from Business module entities

It is possible to merge fields from business module entities into Word tables. The syntax is very much like the previous merging methods. The only difference is the tags in the tables first row. Instead of "wpListName", "wpBusinessModuleListName" is used, and "wpBusinessModuleLookupFieldName" must be assigned.

MergeIntoWord.docx  -  Read-Only - Word

Note that if the business module from which data is to be pulled, i.e. the wpListName value, has translations, you must use the site collection language version of the title.

Have more questions? Submit a request