Table of Contents

General Functions

Action Menu

Customize Templates

Introduction

In foreknown, documents such as offers, orders, invoices and activity reports of services are managed, which later also need to be exported for the customer, e.g. to be sent as a PDF document by e-mail. To ensure that these documents meet the requirements and the common design (CD) of your company, individual customization will be ensured.

Therefore, all documents created with foreknown are always based on a Freemarker (HTML, CSS) file for offers, orders and invoices and on Microsoft Excel files for activity reports.

Note: Freemarker is a template engine that allows you to dynamically fill HTML pages with data. In foreknown we use Freemarker templates (.fmt files) to insert the offer, order or invoice data into the HTML/CSS designed templates. More information and a comprehensive documentation on Freemarker can be found on the website https://freemarker.apache.org.

These templates can be downloaded via the template management in the system administration for an individual design. It is also possible to create several variants per document type (offer, order, invoice, activity report) e.g., to provide templates in multiple languages or for fixed-price and effort-based offers.

Manage Templates - List and Search


The administration of the templates is opened via the menu item "System Administration / Templates". Only users with the "Account Administrator" role can access this area.


To the Top ↑


General Functions

Search Template

In the upper area of the "Manage Templates" dialog is the search field and the "+ New" button (see also Create Template). Below this area all already created templates are displayed in form of a scrollable and sortable list.

If you now enter a search term in the input field, the list will only show the templates that contain the entered search term in the name. In this way, individual templates can be found and edited quickly.


To the Top ↑


Create Template

To create a new template, click on the "+ New" button located at the top right of the search box. This button opens the "Create Template" popup dialog.

Manage Templates - Create Template


The following properties must be specified for a new template:

  1. Base template: A base template must be selected from a drop-down list. This also determines the purpose of the template. A template can be created for offers, orders, invoices and activity reports.
  2. Name: Here the name of the template is entered, which should be as self-explanatory as possible, in order to be able to recognize the desired variant later when selecting a template.

With the action "Save" a new template is created. With the action "Reset" and "Cancel" the entries are discarded and in case of the action "Cancel" the dialog is closed.


To the Top ↑


Action Menu

In front of each entry in the list of templates is an action menu (button with 3 dots), which offers all actions available for a template.

Manage Templates - Action Menu


All actions are described in detail below.

Preview

The "Preview" action opens the selected template, fills it with the values currently stored in the variables and some sample data, and creates a PDF document from it. This is then made available for opening or saving locally.

In this way, you can quickly get an impression of whether the current version of the template meets your company's requirements.

Note: This function is not available for a template of the type "Service Entry Export".


To the Top ↑


Download current Template

This action downloads the current version of the template as Freemarker file (.fmt) or for activity reports as MS Excel (.xlsx) file, so that it can be adapted if necessary and uploaded again with the action "Upload edited template".


To the Top ↑


Download Base Template

This action downloads the basic version of the template as a Freemarker file (.fmt) or, for activity reports, as an MS Excel (.xlsx) file so that it can be customized if necessary and uploaded again. Basic version means that it is the original version of the template provided by foreknown and not yet customized.


To the Top ↑


Upload edited Template

This action uploads a customized version of the Freemarker file (.fmt) or, for activity records, the MS Excel (.xlsx) file. Thus, it overwrites the current version of the template.

NOTE: This action overwrites the current version of the template and is irrevocably!


To the Top ↑


Edit Master Data

This action opens the "Edit Master Data" dialog, which can only be used to change the name of the template. The base template can no longer be changed for an existing template.

A descriptive name should always be chosen so that when a template is assigned to an offer, an order, an invoice and an activity report, it is immediately apparent which template it is.


To the Top ↑


Edit Variables

This action opens the "Edit Variables" dialog, through which some "placeholders" for a template can be filled with concrete values. The variables are static values that are inserted when a template is used.

Manage Templates - Manage Variables


Important: Initially, all variables are filled with demo data and should therefore be adapted to your own company data.

The "Add Variable" action can be used to add additional variables of your own. However, in order to appear in the template, a new placeholder referencing this variable must be added to the template. The customization of the templates is described below.

The "Save" action saves the changes. With the action "Reset" and "Cancel" the entries are discarded and in case of the action "Cancel" the dialog is closed.

Placeholder of the MS Excel Basic Templates

Exactly one variable is currently already created for the "Activity Reports" template, as it is used in the base template:

  1. companyName: Contains the name of your company.

However, as already mentioned, any other variables can be added.

Placeholders of the Freemarker Basic Templates

The list of variables that are initially displayed is based on placeholders that are already used in the Freemarker basic templates. It is therefore recommended not to delete these default variables. In all Freemarker basic templates the following variables are already used:

  • Contact and Address Data of your Company

    1. companyName: Name of the company; will be displayed in the header and in the sender of the template; Initial: Muster GmbH.
    2. companyStreetAddress: Street and house number of the company; will be displayed in the header and in the sender of the template; Initial: Musterstrasse 1
    3. companyPostalCode: Postal code of the company; will be displayed in the header and in the sender of the template; Initial: 12345
    4. companyCity: City of the company; will be displayed in the header area and in the sender of the template; Initial: Sample City
    5. companyCountry: Country of the company; not used in the standard templates; Initial: Germany
    6. companyPhone: Phone number of the company; will be displayed in the header of the template; Initial: 0170 123456789
    7. companyEmail: Email address of the company; will be displayed in the header of the template; Initial: info@muster.de
    8. companyWebsite: Website of the company; will be displayed in the header of the template; Initial: www.muster.de
  • Data of the Standard Contact Person

    1. responsibleName: General contact person of the company; is displayed in the header area of the template; Initial: Max Mustermann; If necessary, is overwritten with the name of the contact person stored at the customer.
    2. responsiblePhone: Telephone no. of the general contact person of the company; is displayed in the header area of the template; Initial: 0170 123456789; If necessary, is overwritten with the telephone no. of the contact person stored at the customer.
    3. responsibleEmail: Telephone no. of the general contact person of the company; is displayed in the header area of the template; Initial: m.mustermann@muster.de; If necessary, is overwritten with the telephone no. of the contact person stored at the customer.
  • Bank Details

    1. bankName: Name of the company's bank; displayed in the header of the template; Initial: Demo Bank.
    2. bankAccountNumber: IBAN of the company's account; will be displayed in the header of the template; Initial: IBAN: DE00 0000 0000 0000
    3. bankId: BIC of the company's account; will be displayed in the header of the template; Initial: BIC: DEMO00BANK
  • Legal Information

    1. salesTaxNumber: VAT ID number of the company; displayed in the header of the template; Initial: VAT ID: DE12345678
    2. management: Names of the managing directors of the company; will be displayed in the header of the template; Initial: GF: Maxi Mustermann
    3. jurisdiction: Jurisdiction of the company; will be displayed in the header of the template; Initial: Local Court Münchshausen
    4. commercialRegister: Commercial register number of the company; will be displayed in the header of the template; Initial: HRB 1234
  • Settings

    1. showVatPerPosition: Flag, which determines whether the sales tax rate should be displayed for each invoice item; Initial: false
    2. showPaymentPlan: Only for offer and order templates; flag which determines whether a possibly stored payment plan should also be output; Initial: false
    3. showHeaderPerPosition: Only for invoice templates; flag that determines whether each invoice item should have its own column header; Initial: false
    4. showTaxAndGross: Only for offer templates; flag that determines whether the VAT and gross value should be shown in the printed offer.
    5. showServiceEntries: Only for offer templates; flag that determines whether positions of type "Time and Materials" should also print the service positions.

NOTE: The contact person data will be overwritten by the contact data of the " Responsible Person" of an offer, order or invoice, if this has been maintained.


To the Top ↑


Delete Template

An existing template can be deleted using this action. A confirmation dialog opens where the template is finally deleted by clicking on the delete button.


To the Top ↑


Customized Templates

The goal of the templates in foreknown is to create the option to realize a design customized to your company for all documents that can be created with foreknown. Below is a description of how you can customize the different template types. A distinction is made between Freemarker and Excel templates, since they are different technologies used for the templates.

Customize Offer, Order and Invoice Templates

A Freemarker template is used to create offers, orders and invoices. For each of these types, a basic template is provided to serve as a basis for creating a custom template.

The different customization options are described in more detail below.

Customize Design

The first step for creating a custom template is to download a basic template of the desired template type e.g. of the offer. This Freemarker .fmt file can then be edited in an editor. It is helpful to use an editor that supports the .fmt (Freemarker) format and thus allows text highlighting.

Note: The freemarker file uploaded to a foreknown template must have the extension ".fmt" and not the ".ftl" extension, which is also common.

The basic template is an HTML file with embedded CSS, which also has freemarker instructions. If only certain changes are needed to the basic template, in the simplest case only the CSS and the logo need to be adjusted.

Logo

The company logo is included in the base template as an <img> element with a Base64 string of the image as source. To convert your own logo into a Base64 string, you can use existing tools on the internet like https://www.base64-image.de/

The result, a very long string, should then be inserted into the <img src="..."/>. Example:

<img src="data:image/jpeg;base64,/9j/4AAQSkZJRgABAQEAYABgAAD...">
Fonts

The following Google Fonts are pre-installed on the foreknown servers: Avro, Cormorant Garamond, Crimson Text, EB Garamond, IBM Plex Sans, IBM Plex Serif, Lato, Libre Baskerville, Lora, Manrope, Merriweather, Muli, Neuton, Noto Sans, Noto Serif, Open Sans, PT Sans, PT Serif, Roboto, Roboto Condensed, Roboto Slab, Source Serif Pro, Titillium Web, Ubuntu

These can be used in the stylesheet of the template, for example, as follows:

body {
    font-family: 'Roboto', sans-serif;
}

If the desired style is not available, the desired Google Fonts can be imported via a link in the header:

<html>
  <head>
    ...
    <link href="https://fonts.googleapis.com/css2?family=Poppins:ital,wght@0,400;0,700;1,400;1,700&amp;display=swap" rel="stylesheet" />
    <style>
      body {
        font-family: 'Poppins', sans-serif;
      }
      ...
    </style>
  </head>
  <body>
  ...
  </body>
</html>

Note: Only fonts of the Google Font API (https://fonts.googleapis.com) can be loaded. Other urls are not supported.

CSS Adjustments

Some changes regarding font sizes, spacing and colors can also be made only through CSS adjustments, if no fundamental structural changes are necessary.

All CSS instructions are located in the head section of the .fmt (html) file and can be adjusted there.

Customize HTML Structure

To make structural changes to a basic template, there are a few aspects to consider:

  1. The current templates are based on the sizes of a DIN A4 page as a portrait. Many width and height specifications are given in millimeters (mm) to ensure that the dimensions correspond to the DIN A4 format. Therefore, when table structures used, make sure that the sum of column widths are within the range of a DIN A4 page. Furthermore, some CSS instructions are used, which are used dedicated to a print output, in order to e.g. output a footer with page number.
  2. With the offer and order templates it is to be noted that there are different position types and settings in foreknown. Each type, whether "Fixed price", "Time & Material" or "Other Position" has other data that are displayed differently. Furthermore, there are variants that result from the properties of a position, e.g. if no quantity specification is required or if a price range has been activated. For this reason each variation has its own HTML area in the base template. These should all be considered individually when making adjustments to the presentation of positions.
  3. Besides the positions there are also text positions. In the basic template you can see how to differentiate between a offer, order, invoice position and a text position and how to identify and print lines above and below a position.
  4. There are some variables that control the display of certain information e.g. the variable showHeaderPerPosition makes sure that the column headers per position are repeated in an invoice if the value is set to "true".
  5. The data of an offer, order or invoice and the related customer data are also integrated using the notation ${offer | order | invoice.}. The basic templates of each document type provides all necessary variables. Ensure that all data, which could have null or empty values, are surrounded by an if-statement, which checks this e.g., with the ...?has_content notation.

Generally spoken, the basic templates can be fully customized in terms of HTML and CSS which gives you full freedom designing your individual templates.

Recommendation: If major changes are to be made to a basic template, developers should be available with HTML and CSS knowledge and a good understanding of a template engine like Freemarker. To test the structure (without Freemarker statements) during development, the following service can be used: https://sandbox.openhtmltopdf.com/

To test templates with Freemarker expressions, a new version of a .fmt file must be uploaded to foreknown via the template management with the action "Upload edited template" and can be tested by printing e.g. a draft offer.

If you want support in designing your individual templates, please contact us via support@foreknown.io.

Use of Template Variables

To include the value of a variable in a freemarker template the following expression is used for example:

<# if var.companyName?has_content>
  <div>${var.<name-der-variable>}</div>
</#if>

The if-statement ensures that the company name is only output if the variable has a value. If a variable was accessed that did not exist, the Freemarker process would get an error and no printed offer, order or invoice would appear.


To the Top ↑


Customize Excel Template

An Excel template is used to export filtered data for service entries in the form of a table. The concept of Excel templates is based on different types of placeholders that you can place within an Excel spreadsheet in the header, footer and table. However, there are a few rules to follow.

The following screenshot shows how an Excel template can look like.

Template - Activty Report


We see the header line with the static heading "Activity Report" and the placeholder [#FILTER#monthYear#]. Below that is the actual table with various placeholders and at the end the footer, where a placeholder [#VARIABLE#companyName#] and standard fields of Excel are used.


To the Top ↑


Special Features & Restrictions

In Excel tables, a distinction is made between the header, the table and the footer. Not all placeholders can be used in all areas. There are the following restrictions to consider.

  1. The header and footer of an Excel table are divided into three fields. Only placeholders of type "FILTER" and "VARIABLE" can be used in these fields. However, several placeholders can be inserted per field.
  2. All placeholder types can be used in the table. However, only one placeholder can be inserted in a cell. Placeholder may not be combined with other text in a cell. The "LIST" placeholder may only be used once and all "LIST" placeholders must be in exactly one row of the table. No other placeholders can be used in this row. However, columns with formulas or static values can be inserted, which are then transferred to each generated service line.
  3. The Excel sheet is responsible for correct display of values in the cells of the table that contain placeholders. Although a "text" has been inserted by inserting a placeholder, the cell must be assigned the correct type and format in Excel in order to correctly display the inserted value. For example, the cell with the date of a service must also be set as "date" with the desired format in Excel.
  4. If you want to be able to provide Excel tables in different country-specific versions (DE, US), you must create separate templates for each country in which the cells are formatted accordingly.

To the Top ↑


foreknown Placeholder for Services

In order to be able to insert the data from foreknown into an Excel table, a variety of placeholders of different types are provided, which are described below. A placeholder is always structured as follows:

[#<TYPE>#<name>#]

There are 4 different placeholder types: FILTER, LIST, VARIABLE, SUM

FILTER Placeholder

These placeholders essentially provide the information resulting from the currently selected filter settings. These placeholders can be used in the header, table and footer.

  • [#FILTER#accountName#]: Returns the name of your foreknown account.
  • [#FILTER#createdAt#]: Returns the current date. The date format is set in the cell of the excel sheet. The cell must be assigned a date format in Excel.
  • [#FILTER#monthYear#]: Returns the month and year of the period selected in the filter. The following rules apply:
    • If no time period has been set in the filter, "month/year" will be determined from the current date.
    • If a period was selected that is within a month, "month/year" of the period is returned.
    • If a period was selected that covers several months, "Month/Year - Month/Year" will be determined from the first and last month.
    • The cell must be assigned a text format in Excel.
  • [#FILTER#amount#]: Returns the number of found entries resulting from the filter settings. The cell must be assigned a numeric format in Excel.
  • [#FILTER#startDate#]: Returns the start date of the period selected in the filter. If no period is set, the text "No start date selected" is displayed. The cell must be assigned a date format in Excel.
  • [#FILTER#endDate#]: Returns the end date of the period selected in the filter. If no period is set, the text "No end date selected" is displayed. The cell must be assigned a date format in Excel.
  • [#FILTER#period#]: Returns the start and end date as text e.g. "01-01-2021 - 31-01-2021". The cell in the Excel table must therefore have the format "Text". The date format is generated in foreknown depending on the set language. If no period is set, the text "No period selected" will be displayed.
  • [#FILTER#status#]: Returns a text with all statuses that were selected in the filter settings. If the filter was not set, the text "No status selected" will appear. The cell must be assigned a text format in Excel.
  • [#FILTER#orders#]: Returns a text with all the order numbers that were selected in the filter settings. If the filter was not set, the text "No order selected" will appear. The cell must be assigned a text format in Excel.
  • [#FILTER#employees#]: Returns a text with all employees selected in the filter settings. If the filter was not set, the text "No employee selected" appears. The cell must be assigned a text format in Excel.
  • [#FILTER#customers#]: Returns a text with all customers numbers selected in the filter settings. If the filter has not been set, the text "No customer selected" will appear. The cell must be assigned a text format in Excel.
  • [#FILTER#serviceTypes#]: Returns a text with all service types selected in the filter settings. If the filter was not set, the text "No service type selected" will appear. The cell must be assigned a text format in Excel.
  • [#FILTER#invoices#]: Returns a text with all invoices selected in the filter settings. If the filter was not set, the text "No invoice selected" will appear. The cell must be assigned a text format in Excel.
  • [#FILTER#summary#]: Returns a text dynamically composed of the filter settings for status, orders, employees, customers, invoices, and service types, and outputs only the selected filters in a multiline text. If no filters are set, the text "No filters selected" is displayed. The cell must be assigned a text format in Excel.

To the Top ↑


LIST Placeholder

These placeholders provide the data of the services resulting from the currently selected filter settings. These placeholders can only be used in the table and must be on one row.

  • [#LIST#employee#]: Provides the name of the employee that the service is based on. The cell must be assigned a text format in Excel.
  • [#LIST#employeeWithCostCenter#]: Returns the name of the employee with the cost center behind it.
  • [#LIST#date#]: Returns the date of the service. The cell must be assigned a date format in Excel.
  • [#LIST#duration#]: Returns the billable duration in hours or days depending on the unit. The cell must be assigned a numeric format in Excel.
  • [#LIST#durationBooked#]: Returns the booked duration in hours or days depending on the unit. The cell must be assigned a numeric format in Excel.
  • [#LIST#orderName#]: Returns the name of the order assigned to the service. The cell must be assigned a text format in Excel.
  • [#LIST#orderNumber#]: Returns the order number of the order assigned to the service. The cell must be assigned a text format in Excel.
  • [#LIST#orderDetails#]: Returns in a text the order number, order description, item number and service type assigned to the service. The cell must be assigned a text format in Excel. "Text break" should also be set.
  • [#LIST#serviceType#]: Returns the name of the service type assigned to the service. The cell must be assigned a text format in Excel.
  • [#LIST#task#]: Returns the key and name of the task for which a service was created. Since a task can also consist of several working time entries (daily rates), several key/name pairs maybe output comma-separated.
  • [#LIST#taskKey#]: Returns the key of the task for which a service was created. Since a task can also consist of several working time entries (daily rates), several keys may be output comma-separated.
  • [#LIST#taskName#]: Returns the name of the task for which a service was created. Since a task can also consist of several working time entries (daily rates), several names may be output comma-separated.
  • [#LIST#customerId#]: Returns the customer number of the customer assigned to the service. The cell must be assigned a text format in Excel.
  • [#LIST#customerName#]: Returns the name of the customer assigned to the service. The cell must be assigned a text format in Excel.
  • [#LIST#description#]: Returns the description of the service. The cell must be assigned a text format in Excel.
  • [#LIST#descriptionWithTaskKey#]: Provides the description of the working time and the key of the task, for which a service was created. Since a service can also consist of several working time entries (daily rates), several description/key pairs may be output comma-separated.
  • [#LIST#rate#]: Provides the hourly or daily rate of the service. The cell must be assigned an accounting format in Excel.
  • [#LIST#unit#]: Returns the unit of the service e.g. hours, days. The cell must be assigned a text format in Excel.
  • [#LIST#priceTotal#]: Returns the amount of the service (duration * rate). The cell must be assigned an accounting format in Excel.
  • [#LIST#accountLabel#]: Returns the value of the label with the prefix "Account:". If the service entry has multiple working time entries, the values are provided as a comma-separated list. The cell must be assigned a text format in Excel.
  • [#LIST#customerLabel#]: Returns the value of the label with the prefix "Customer:". If the service entry has multiple working time entries, the values are provided as a comma-separated list. The cell must be assigned a text format in Excel.
  • [#LIST#projectLabel#]: Returns the value of the label with the prefix "Project:". If the service entry has multiple working time entries, the values are provided as a comma-separated list. The cell must be assigned a text format in Excel.
  • [#LIST#epicLabel#]: Returns the value of the label with the prefix "Epic:". If the service entry has multiple working time entries, the values are provided as a comma-separated list. The cell must be assigned a text format in Excel.

To the Top ↑


VARIABLE Placeholder

This placeholder can insert the values of the variables that can be maintained at the template. The placeholder can be used in the header, footer and table. It is not possible to use it in the line that contains the LIST placeholder. An example is shown below.

  • [#VARIABLE#companyName#]: Returns the value of the variable "companyName" maintained at the template.

All cells containing a VARIABLE placeholder must be given the format "Text".

Documentation-Link: For detailed information on how to maintain variables on templates, see "Edit Variables" on this page.


To the Top ↑


SUM Placeholder

This placeholder is used to be able to insert sums into the Excel sheet for certain performance properties.

  • [#SUM#duration#]: Returns a formula that sums all rows in the "Billable Duration" column as a partial result. The cell must be given a numeric format in Excel.
  • [#SUM#durationBooked#]: Returns a formula that sums all rows in the "Booked Duration" column as a partial result. The cell must be given a numeric format in Excel.
  • [#SUM#priceTotal#]: Returns a formula that sums all rows of the "Amount" column as a partial result. The cell must be given an accounting format in Excel.

Note: For each SUM placeholder used, there must always be a column with the corresponding LIST placeholder. Example: If you use the placeholder [#SUM#duration#], you must also use the placeholder [#LIST#duration#] in the row for the LIST placeholders.

To the Top ↑