fepli
ManualReferenceSystem and data protectionAdmins

XLSX templates

An XLSX template puts together an Excel spreadsheet from the offers of a season: with fields of the offers, answers from questionnaires and your own formulas.

You create the template under Export templates with New template > XLSX, or start from a ready-made template.


Sheet and rows

FieldMeaningValuesDefault
SheetName of the sheet in the Excel fileup to 31 characters, without [ ] : * ? / \Offers
One row perWhat the spreadsheet has a row forOffer, Date, ChildOffer
  • Date: an offer with three dates gives three rows.
  • Child: each child with a place gives one row, that is with the status confirmed, participated or no-show. The offer’s fields appear in each of these rows.
  • For an instance with encryption, fepli takes the names that the unlocked browser decrypted. fepli also sorts the children by them (Exports and receipts with encryption).

Table

The middle shows the table as the file will have it, worked out with the first offers of the season that currently has an editing phase. If it has no offers, fepli uses sample offers.

ElementMeaning
Letter above the columnSelects the column. Its settings appear below (Column). fx marks a calculated column, a red dot a column with an error.
ColumnAdds a column: Offer field, Questionnaire answer, Calculation or Cost breakdown (Cost breakdown).

Questionnaire answer is only available once there is a question whose answer can be taken over. Cost breakdown only once a questionnaire has a cost breakdown.


Column

FieldMeaningValues
HeadingHead of the column in the file. Formulas refer to it.Text
ContentWhere the column’s values come fromOffer field, Questionnaire answer, Calculation
Offer fieldA field of the offer, such as Title, Date, Fee or Children. With Child, also the child fields.Field of the offer
QuestionA question from a questionnaire. If it no longer exists, it says “(question no longer exists)”.Question
Cost lineFor a question with a cost breakdown: one of its lines or Sum of all lines, plus Fixed costs or per participantLine
Sum of columns, FormulaKind of calculation. Add up columns selects the number columns of the sum.–
FormulaA calculation with columns in square brackets (Formulas)Expression
FormatDisplay in ExcelText, Number, Amount, Date and time, Date, Yes/no as 1/0
Result rowRow below the tablenone, Sum, Average

The arrows move the column, Duplicate creates a copy next to it, Remove column removes it.

A column with an Offer field first gets the format that suits the field, e.g. Amount for Fee and Date and time for Date. fepli writes text columns as text, so that a postcode such as 01067 keeps its leading zero.

Below the column, In Excel shows the formula the column has in the file and where its value comes from. An answer from a questionnaire stays empty if the questionnaire is not filled in. Next to it are sample values of the first offers.

Formulas

A formula calculates with other columns, e.g. [Fixed costs] + [Variable costs] * [max participants].

  • Allowed are numbers, +, −, *, /, brackets and the functions MIN, MAX and RUNDEN. A semicolon separates several values.
  • You name a column by its heading in square brackets. Moving a column breaks no formula. Renaming it updates the references.
  • fepli saves the formula as an Excel formula. If somebody changes values in the file, it keeps calculating.

Child fields

The child fields appear under Offer field only when One row per is set to Child. In a row per offer or date they stay empty.

FieldContent
Last name, First nameName of the child
Date of birth, AgeDate of birth and age of the child
Street, Postal code, CityAddress of the child, otherwise that of the account, otherwise that of the registration
Account holderHolder of the participant account
StatusRegistration status
RuleContent
Who sees the templateOnly accounts with the role View and create participants. The export does not offer it to anyone else, not even under Recently used (Export).
Masked dataIf the account that creates the export sees participant data partly hidden, the file is hidden in the same way.
Other templatesOnly templates with Child hold children. PDF, XML and templates with a row per offer or date hold none.
ChildrenCounts all children with a place, including erased and restricted ones. These never get a row of their own, though.

Cost breakdown

The entry Cost breakdown under Column creates two columns for each line of the cost breakdown: Fixed {line} and Var {line}. The columns Fixed costs and Variable costs with the sums are added too.


Messages

MessageMeaning
“No columns chosen.”The table has no column.
“The formula is missing.”The calculation has no formula.
“There is no column “{name}”.”The formula names a heading that no column has.
“Two columns are called “{name}” – the reference is ambiguous.”2 columns have the same heading. Rename one of them.
“Allowed are columns in [ ], numbers, + − * / ( ) and MIN, MAX, RUNDEN.”The formula contains another character.
“The formula does not add up – is a bracket, a number or an operator missing?”A bracket, a number or an operator is missing.
“Refers to itself in a circle.”A formula goes round in a circle.