Template data fields

Link copied to clipboard

Data fields describe a single element of data, such as “invoice number”, “price” or “company name”. For each data field in the template you specify the following:

Template data fields

Target columns (for download templates)

Link copied to clipboard

Target columns define where to position the result value. If you want to skip the downloading of specific data, choose None for this data field. To change the target column, click Edit Edit and select the new column value in the Column list. Multiple data fields can be populated from the same target column.

This option is available for rate download templates. To specify where you would like the value to appear in download templates with printable form media such as invoices, use Layout Designer.

Format

Link copied to clipboard

A format describes data fields so that they may be converted from the database (default) format to a specific format during download.

For example, if you choose format “011E.164” for the “Destination” data field, then the template engine will know that you require the downloaded numbers to be in a US dialing format, so the international number must be prefixed by “011.” Therefore, “011” will be prefixed to the number “4201234567” during the download, so the number in the output file will be “0114201234567.

In order to provide better quality control of data, PortaBilling supports several data types. These define what kind of data appears in the data field, as well as possible ways to process it. Currently supported data types include:

  • Destination (phone number).
  • Date/time
  • Number
  • Time interval (number of minutes/seconds)
  • Text
  • Boolean (yes/no)

A data type is predefined for each data field, so you do not need to specify one. For each data type in PortaBilling there is a set of default formats so that you will usually not have to invent anything from scratch. If you encounter a data format for which there is no suitable data type in PortaBilling, you may choose “Other”. You will then be able to specify a custom format rule.

“Other” format rule

If none of the PortaBilling default formats suit your needs, you can choose “Other” and enter your own formatting rule. See below for a description of how a format rule works for data fields of a particular type.

Number

For numeric data fields (for example, “First price” or “Connect fee”), the value you enter in the Other Format field is considered to be the format specification ###,###.##, where:

  • A # sign or the digits 0-9 represent one digit of the input or output number.
  • The first character from the right which is not a digit or # (e.g., in #,###.00) specifies a decimal separator.
  • The first character from the left which is not a digit or # (e.g., in #,###.00) specifies a group separator.
  • The number of characters after the decimal separator specifies the precision of the number. However, # indicates no zero padding, and 0 indicates zero padding.
  • If the number has too many digits for the format specification, it will be rounded to the number of decimal digits specified in the format.
  • A group separator (if present) specifies which character should be used to split groups of 3 digits in the number. If none is specified, then no group separation will be done.
  • It is sufficient to specify a group separator only once, even if your real numbers will be large and contain more than one digit group. All digits to the left of the decimal point are always shown, regardless of how many of them are actually shown in the format.

This is important mainly for download templates, where the format or precision you use to present numbers in documents differs from the format in which data is stored in the database. For upload templates, number format specification is mainly used to handle non-standard group or decimal separators. See the table below for examples of formats for converting numbers.

Format

Number

Result

Comment

#,###.##

1.6

1.6

No zero padding

#,###.00

1.6

1.60

Zero padding

#,###.00

34567.6

34,567.60

Digit groups

####.00

34567.6

34567.60

No digit groups

#.#

2.382

2.4

Rounding

#.#

2569.31

2569.3

Significant part is never truncated

#.###,##

2569.31

2.569,31

German format

# ###,##

7892569.31

7 892 569,31

Yet another custom format

Table 1-1 – Examples of number formats.

Date and Time

Link copied to clipboard

This type is used for data fields that contain either both the date and time (e.g., Effective from) or just the date (e.g., Invoice date). Each data field has a default format. For instance, for Period From Date on the Field tab of the invoice template it is YYYY-MM-DD. This means that end users see dates in this format: 2015-08-23. All of the date and time format specifiers are listed below:

  • YYYY – year (four digits, e.g., 2009).
  • YY – year (short format, e.g., 05). Note that this format can lead to potential data interpretation problems, since “10” could be either 2010 or 1910. The PortaBilling template engine considers the year values 00-30 to be in the 21st century, and 31-99 to be in the 20th century.
  • MM – number of the month (01…12).
  • MON – abbreviated name of the month (e.g., Jan). Only English names of the months are currently supported.
  • MONTH – full name of the month (e.g., January). Only English names of the months are currently supported.
  • DD – day of the month with a leading zero for 1 through 9; for example, 01, 06, 14, 25.
  • D – day of the month without a leading zero for 1 through 9; for example, 1, 6, 14, 25.
  • HH or HH24 – hour (24-hour format).
  • HH12 – hour (12-hour format).
  • AM/PM – a.m. or p.m. indicator.
  • MI – minutes.
    Very often people make the mistake of using MM as a format for minutes. This is incorrect, as MM is a format for month. Thus, HH:MM:SS will clearly not work correctly.
  • SS – seconds.
  • TZ – time zone name (e.g., CET).
  • XXX – any combination of non-space characters; used in formats for parsing dates to skip a variable part of the date, for example, the name of a weekday (see examples below in Table 1-2).
  • All other symbols in the format (spaces, delimiters like /, -, : or ; and other characters) represent themselves. So they are placed as-is in the output date string, and must be found in the input string exactly as they were entered.

PortaBilling administrators can customize date and time formats according to their needs and we show you how to do it for different template types.

Rate upload and download templates
Link copied to clipboard

Specify your customized date and time format for the required data field within the Other Format Rule field. Click Save to apply the changes:

Template data fields

Let’s take a look at some examples for importing dates into:

Date/time string

Description

Correct format specification

07/04/2009

July 4th 2009, US date format

MM/DD/YYYY

04-07-2009

July 4th 2009, European date format

DD-MM-YYYY

07-04-09

July 4th 2009, US date format, short year format

MM-DD-YY

11:05 a.m.

Time, 12-hour format

HH12:MI AM/PM

11:05

Time, 24-hour format

HH:MI

18:58:00

Time with seconds

HH:MI:SS

2009-07-04 14:00:00

Date and time, ISO format

YYYY-MM-DD HH:MI:SS

07/04/09 2:00:00 p.m.

Date and time, US format

MM/DD/YY HH12:MI:SS AM/PM

1-May-2009 18:52

Date and time, abbreviated month name

D-MON-YYYY HH:MI

Sun, 16 Mar 09

Date and time, skip weekday name

XXX D MON YY

12th of March, 2009

Date with “as is” elements

Dth of MONTH, YYYY

Table 1-2 – Examples of date and time formats for upload.

The following table gives examples for using templates to convert values in the PortaBilling database to the desired format.

Date/time in

Format specification

Resulting value

01-May-2014 12:34:56

MM/DD/YYYY

05/01/2014

01-May-2014 12:34:56

DD-MM-YYYY

01-05-2014

01-May-2014 12:34:56

MM-DD-YY

05-01-14

01-May-2014 12:34:56

HH12:MI AM/PM

12:34 p.m.

01-May-2014 12:34:56

HH:MI

12:34

01-May-2014 12:34:56

HH:MI:SS

12:34:56

01-May-2014 12:34:56

YYYY-MM-DD HH:MI:SS

2014-05-01 12:34:56

01-May-2014 12:34:56

MM/DD/YY HH12:MI:SS AM/PM

05/01/14 12:34:56 p.m.

01-May-2014 12:34:56

D-MON-YY

1-May-14

01-May-2014 12:34:56

D of MONTH, YYYY

1 of May, 2014

01-May-2014 12:34:56

YYYYMMDD HHMISS

20140501 123456

Table 1-3 – Examples of date and time formats for download.

Invoice template
Link copied to clipboard

To apply your customized date and time format for the invoice template, insert this code into the Other Format Rule field of the required data field:

Porta::Date->new_iso($value)->asCustomFormat(‘DD-MM-YYYY’)

where ‘DD-MM-YYYY’ is an example of date format.

Invoice template

Parsing module
Link copied to clipboard

Please note that when importing data that has dates in a customized format (e.g., rate upload), you can use the Time::ParseDate module to recognize the date format. This module examines a data string and finds bits of data, regardless of how they are placed or separated there. In order to use the Time::ParseDate module for parsing dates, choose “Auto” as the format