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
| Field | Meaning | Values | Default |
|---|---|---|---|
| Sheet | Name of the sheet in the Excel file | up to 31 characters, without [ ] : * ? / \ | Offers |
| One row per | What the spreadsheet has a row for | Offer, Date, Child | Offer |
- 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.
| Element | Meaning |
|---|---|
| Letter above the column | Selects the column. Its settings appear below (Column). fx marks a calculated column, a red dot a column with an error. |
| Column | Adds 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
| Field | Meaning | Values |
|---|---|---|
| Heading | Head of the column in the file. Formulas refer to it. | Text |
| Content | Where the column’s values come from | Offer field, Questionnaire answer, Calculation |
| Offer field | A field of the offer, such as Title, Date, Fee or Children. With Child, also the child fields. | Field of the offer |
| Question | A question from a questionnaire. If it no longer exists, it says “(question no longer exists)”. | Question |
| Cost line | For a question with a cost breakdown: one of its lines or Sum of all lines, plus Fixed costs or per participant | Line |
| Sum of columns, Formula | Kind of calculation. Add up columns selects the number columns of the sum. | – |
| Formula | A calculation with columns in square brackets (Formulas) | Expression |
| Format | Display in Excel | Text, Number, Amount, Date and time, Date, Yes/no as 1/0 |
| Result row | Row below the table | none, 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 functionsMIN,MAXandRUNDEN. 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.
| Field | Content |
|---|---|
| Last name, First name | Name of the child |
| Date of birth, Age | Date of birth and age of the child |
| Street, Postal code, City | Address of the child, otherwise that of the account, otherwise that of the registration |
| Account holder | Holder of the participant account |
| Status | Registration status |
| Rule | Content |
|---|---|
| Who sees the template | Only accounts with the role View and create participants. The export does not offer it to anyone else, not even under Recently used (Export). |
| Masked data | If the account that creates the export sees participant data partly hidden, the file is hidden in the same way. |
| Other templates | Only templates with Child hold children. PDF, XML and templates with a row per offer or date hold none. |
| Children | Counts 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
| Message | Meaning |
|---|---|
| “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. |