Release

Search

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.

Unfortunately, in some situations even Time::ParseDate is unable to properly identify the date. For example, the string "02-01-2009" could either mean February 1st or January 2nd. In such situations, an exact format specification is required.

Other data types

Link copied to clipboard

For other data types (e.g., Destination, Text, or Interval) there are no specific format elements. The "Other" format rule is regarded as a Perl macro; thus it is evaluated, and the result of the evaluation is considered a new value. For a detailed description of Perl macros, see the Post-processing rules section below. The original string value is received as a parameter, and the converted value should be returned in the required format.

The following table provides a description of which type of output value is required for other format rules, depending on the data field type.

Data field type

Other format rule in

Output value

Destination

Perl code

string

Number

Number format

N/A

Boolean

Perl code

string (‘Y’ or ‘N’)

Interval

Perl code

number

DateTime

Date/time format

N/A

Text

Perl code

string

Table 1-4 – Output value type for other format rules.

Post-processing rules

Link copied to clipboard

Sometimes you will need to perform a very specific conversion which is not possible using the standard PortaBilling formats. In this case, you can use post-processing rules. A post-processing rule is a piece of Perl code that is executed at run-time, and the value it returns becomes the final value for a field. This is similar to formulas or macros in Excel, giving you a virtually unlimited ability to change your data as you need to. There are a few requirements for writing post-processing rules:

  • A processing rule is Perl code, i.e. one or more Perl operations separated by semicolons.
  • The input value (to be processed) is in the $value variable.
  • The final result is the value returned by the last expression in the list.

tip TIP: One of the most useful things post-processing rules can do is to create new data which is not based on the input data. This is required, for example, when certain data fields (e.g., Interval 1 and Interval N) are absent from the input file. If you know that Interval 1 is 30 seconds, and Interval N is 6 seconds, you can simply assign these values as constants in the post-processing rule.

Let’s look at a few examples:

Post-processing rule

Input data

Result

Remove all leading white space from the string

$value =~ s/^\s+//;$value

Aruba

Aruba

Remove all leading and trailing white space from the string

$value =~ s/^\s+//; $value =~ s/\s+$//;$value

Aruba

Aruba

Add 1 before the phone number, but only if it does not start with 0

$value =~ m/^0/ ? $value : '1'.$value;

6041234567

16041234567

Take only the part of the input string following the hyphen

my @a = split('-', $value, 2); $a[-1];

Norway-Oslo

Oslo

Assign a constant value to the field

$value = 30

None

30

30

None

30

Replace all hyphens with spaces and remove all quotes

$value =~ s/-/ /g; $value =~ s/\”//; $value

Germany-'Berlin'

Germany Berlin

Use an external (custom) module to translate English country names into Czech

use Czech::Translate; transl_data($value);

Austria

Rakousko

Table 1–5 – Examples of using post-processing rules.

This manual does not cover details of programming in Perl. There are excellent books on this subject, for example:
  • Programming Perl by Larry Wall, Tom Christiansen, Jon Orwant (ISBN 0-596-00027-8)
  • Learning Perl by Randal L. Schwartz, Tom Phoenix (ISBN 0-596-00132-0)
  • Perl Cookbook by Tom Christiansen, Nathan Torkington (ISBN 1-56592-243-3)

Values returned by a post-processing rule should have the same type as the input value it receives. The following table provides information about types of input values for post-processing rules, depending on the data field type.

Data field type

Input value ($_)

Destination

String

Number

number

Boolean

string (‘Y’ or ‘N’)

Interval

number

DateTime

unixtime

Text

string

Table 1–6 – Input value type for post-processing rules.

See Also

On this page

Release
What's new
Admin manuals
Handbooks
API
UI help
Back to main menu
Search