View Source

***************************************************** ***************************************************** ***************************************************** ***************************************************** ***************************************************** ********************** WARNING ********************** ********************** WARNING ********************** ********************** WARNING ********************** ********************** WARNING ********************** ********************** WARNING ********************** ********************** WARNING ********************** ********************** WARNING ********************** ********************** WARNING ********************** ********************** WARNING ********************** ********************** WARNING ********************** This Confluence article was automatically generated from Asciidoc. Any changes you make to this document will be overridden! If you want to change the content, consider leaving a comment. You can edit the content directly here: https://gitlab.pricefx.eu/training/pricefx-knowledge-base/-/tree/dev/public/content/docs/concepts/persisted-data/publishing-templates ***************************************************** ***************************************************** ***************************************************** ***************************************************** ***************************************************** ********************** WARNING ********************** ********************** WARNING ********************** ********************** WARNING ********************** ********************** WARNING ********************** ********************** WARNING ********************** ********************** WARNING ********************** ********************** WARNING ********************** ********************** WARNING ********************** ********************** WARNING ********************** ********************** WARNING **********************

Within Pricefx you can export your documents to XLS, DOCX and PDF formats. For this publishing procedure Excel or Word templates are used and you can customize them (add a logo, use brand colors, etc.). This section explains how to manage these Excel and Word templates and set up the publishing.

The following documents/objects can be exported:

Quotes
Contracts
Rebate Agreements
Rebate Records
Price Records
Price Lists/Matrix Price Lists
Live Price Grids/Matrix Live Price Grids

Requirements for Publishing Templates

In order to publish templates from Pricefx environment there are several requirements that must be met:

The uploaded Excel files must be in the XLSX or XLSM format, Word files in the DOCX format.
The version required to open and create XLSX or XLSM file type is Excel 2007 (or later).
The maximum file size is 5 MB.
Excel templates can have as many tabs as needed.
Excel templates can (and likely will) contain some macros. Templates that should be convertible to PDF cannot contain macros.
It is advisable to store a backup of the default template versions.
The publishing template name must not have more than 1000 characters.

Publishing to XLS

In order to publish Excel format or XLS files, we need to perform these three operations:

Create an Excel template.
Upload the template.
Test the template.

Using Default Excel Template

There is no need to create an Excel template from scratch, there is an ability via Pricefx built-in capability to export XLS files and create a default template. This process revolves around the following sequence:

Navigate to an exportable list (Quotes, Price Lists, Rebates, etc).
Select a specific entry (Quote, Price List, Rebate Agreement, etc).
Click the Export button and select the Export XLSX option.

Examine the Excel file, it will generally have two sheets (Data and Header) created automatically. The actual layout will vary depending upon the Pricefx type downloaded (Quote, PL, etc).

Excel File Sample

Here is an example of a downloaded Excel file:

It will contain a Data sheet:

Header sheet:

When an Excel template is applied, the Pricefx server will add three additional tabs (Data, Header and Meta) into the template and populate them with raw data from the selected object.

Extracted content (all lines from the selected object) will be contained in the Data tab and the Header tab will contain all inputs and results from the object’s header.

If there is a time field, in the exported Excel file it is always converted to the UTC time (= server time), whereas the UI shows the time according to the browser’s timezone. XLS download is different in this respect than Excel Client which shows the same time as the UI.

For security reasons (to prevent formula injections), all data cells that contain data starting with = , + , - or @ are prefixed with apostrophe (') in the exported XLS file.

Publishing to .docx

Overview

Within Pricefx we have the ability to publish to both the DOC and PDF formats. However, doing this will require the configuration of a conversion service and definition of template export logic.

Step by Step

For publishing to the DOC or PDF format, we will need to perform the following steps:

Definition of an alternative conversion service.

Documents can be exported to DOCX/PDF using a DOCX template. The template is processed via a Pricefx conversion service. If the Pricefx service is not working, the template is processed via the CloudConvert service. As a fallback solution, you may want to define a connection to CloudConvert.

Learn more about CloudConvert here.

Both the Pricefx solution and CloudConvert use the open source library Docxtemplater. It supports a few simple variable types in the template.

Creation of template export logic.
Creation of a DOCX template.
Uploading the template and link the export logic.
Test the publishing process.

Create DOCX Template

To create and build a DOCX template that can be used for exporting a Pricefx object (Quote, Pricelist, etc.) to a DOC or PDF format, we will need to perform two actions:

Create the corresponding calculation logic.
Create the necessary DOCX file.

Create the Calculation Logic

This calculation logic will prepare the data for the Word template format. The underlying principle is One Element = One Output Variable in the DOCX template.

Complex elements can return a map or an array of maps that can contain a set of multiple variables.

Calculation Logic Example

Let’s assume that we have designed the following template in MS Word and the expressions in curly brackets are variables that you intend to use:

To replace the variables with data, you create elements in the calculation logic which will provide the values. The elements must be named as the variables in the Word file. Each element retrieves/calculates the variable value, which replaces the placeholders in the template. An element’s result can be a primitive (Boolean, numerical or string) value or a tree structure combining any nesting of primitives, lists and maps.

Step 1: Get Object Details

To fill in dynamic values, you need the context of that Quote. Use the API call api.currentItem which gives you a quoteView.

For example, to get the name of the Quote, use the following code:

api.currentItem("uniqueName")

Step 2: Populate the Rows

To populate the table rows in the sample template above, we need to get the Quote’s line items:

api.currentItem("lineItems").collect { li ->
  [sku: li.sku, qty: 1, price: li.outputs.find { it.resultName == "Listprice"}.result ]
}

The api.currentItem() call without arguments can have significant performance impact. Make sure that you use the string path param to access only the data you need for the template, or make sure you cache the value of api.currentItem() without arguments as the very first call in your logic.

For CFS, Price Lists, LPG, Rebate Records and Simulations, you always have to wrap the variable with the 'items' tag: {#items} indicates the start of a row; {/items} indicates the end of a row in the template. For example: {#items} Hello {name}! {/items}

Step 3: Set Output Filename

We can also have a variable name of the output PDF file. Add an element to the logic named targetFileName which will define the file name pattern. For example:

api.currentItem('uniqueName') + "-" + api.currentItem('label') + "-" + api.currentItem('customerName')

Here is a sample of calculation logic with PDF output:

controller.addHTML("<a href='/pricefx/${api.currentPartitionName()}/formulamanager.executeformula/Market-Pricing?output=pdf&templateName=market_pricing_doc' target='_new'>Export Market-Pricing PDF from DOC template</a>")

The output=pdf parameter setting will ensure that the result will be a PDF-formatted output.

Create DOCX File

The second component of our DOCX template will be the creation of the DOCX file layout. We will need to create a new MS Word document and use the previously defined variables (elements in the logic) with this syntax {variableName}.

You can find the full syntax here

When creating the document in Open Office XML, the generated text needs to be a valid XML. For example, if you have "&" in the text, it needs to be escaped.

Example of Variables in DOCX Template

Now, we can present sample logic and templates showing how variables can be used. For a general description of how to create a DOCX template, see About Publishing to DOC/PDF.

The following variable types can be used:

Simple variable

{simpleVariable}

Repeated variables

{#products}
   {name}, {price} €
{/products}

Repeated table row
Variables used for condition statement (e.g., block)

{#conditionValueTrue} conditional statement {/conditionValueTrue}
{#conditionValueFalse} conditional statement not visible in output document {/conditionValueFalse}

Else if statement

{#repo}
  {name}
{/repo}
{^repo}
  No repos
{/repo}

You can also insert a complex XML with formatting, as shown in this demo.

Define Publishing Templates

Overview

Publishing templates can be defined in the Configuration section of the partition. To publish a template there is a precise sequence of events.

Step by Step

Publishing templates can be created by following these sequence of steps:

Select one of the supported Pricefx objects (Quotes, Contracts, etc.).

For each object, you can define multiple templates. When you click the 'Download Excel', 'Download Word' or 'Download PDF' icon below a data table, and if there is more than one template for the object, you will be prompted to select the template that you want to use.

Click on the New button to create a new template of object type selected.
Complete the New Template panel by adding these pieces:
- Adding a template name.
- Define the configurable options:
  - Default indicates the template, which will be used if no template is specified for export (this can happen if you use the executeformula command). Even though you can select multiple default templates, selecting only one for each format makes sense.
  - Convertible to PDF marks the template as suitable for PDF export and makes it available in the PDF export template dropdown menu. See Publish to PDF.
  - To Sign is available for Quotes, Rebate Agreements and Agreements/Promotions. Marks the template as intended for export to an e-signature system.
  - Preprocessing Logic is a calculation logic that prepares data for the Word template. Intended for export to PDF.
- Click the Upload Template button and provide your Excel/Word file.

Via e-signature systems, users will be able to export documents (Quotes, Contracts, Rebate Agreements) from Pricefx and send them via the e-signature system to other people. Currently, Pricefx only supports DocuSign as its electronic signature system.

Publishing Example

Step 1 - Select the System Configuration option:

Step 2 - In the System Configuration panel, scroll down to locate the Templates section:

Step 3 - Choose the Publishing Templates option and the following panel will appear:

Step 4 - From this panel, we will need to select the specific Pricefx object type around which we will build a template:

Stept 5 - Once a specific object type has been selected, then a listing of its created templates will appear. Additionally, we will have the ability to create a new template:

Step 6 - When the New button is selected, then the following New Template panel will appear to allow us to add a new template for the selected object type:

Publish to PDF

If the publishing template is marked as Convertible to PDF, the end users will be allowed to export documents to PDF. When that happens, Pricefx first creates a .docx or .xlsx document which is then converted to a PDF. By default, Pricefx is able to convert .docx documents to PDF but for .xlsx documents, it needs to use a web service called CloudConvert.

CloudConvert is an online service that does conversions from XLSX to PDF through MS Excel — and so the final layout is exactly the same as when you print the document locally in your MS Office.

To be able to download PDF files from Pricefx, you need to enable the service under Administration › Configuration › External Systems › Cloud Convert.

By clicking Enable you express that you have read the CloudConvert Privacy Policy and Terms of Use and agree with them.

Verify with the project team that this procedure is acceptable before enabling the PDF publishing. If not, an alternative is to produce the PDF files yourself via MS Excel.

For more details, see Cloud Convert in Documentation.

References

Unity

Classic

Publishing Templates

Knowledge Base

Export of PL with ResultMatrix object

External - DOCX Templates