The report editor allows you to add, remove or edit the report templates.
Once Track is installed with several example report templates that you can edit or copy to create your own reports. The repots can be opened in your spreadsheet editor (e.g. Microsoft Excel) or saved as a csv file for further processing. You can also copy the report to the clipboard for pasting into another application.
You open the Template editor by clicking Edit report templates... on the Reports panel available from top right menu.
From the Report Editor you can either edit one of the existing reports or make a copy of an existing report.
Report Template
- Click the + button to create a new template as a copy of the displayed report template.
- To create a report from your Calendar appointments. the Report template name must begin with the text "Cal".
If it does not start with "Cal", then the report will be based on your orders. - To create a template where there is a row for every item in an order (blind, shutter, curtain etc), the template name must start with the text "blind" or "Item" e.g. "Blind Values". To include columns for item data e.g. width, drop, range etc, use the "Order or Item value" as the input data (see below). Any other report name will result in a row for each order or quote.
- Do not create a template starting with "Product" as this is used for a special type of report where each column is a product name, so is not applicable to the report editor.
Each row in the Report Editor describes a column in the output report table.
Column Name
The header name of the column in the report table.
Width
The width in screen pixels of the column used by the table display.
Input Data
The internal data for the column. Contact support if you require input data that is not included in the available list.
Format
Additional information needed for some types of input data.
For a Date, the formats include: (see below for all date formats)
| Format | Example output |
|---|---|
| dd/MM/yy | 21/05/23 |
| dd.MM.yyyy | 21.05.2023 |
| ddd MMM d yyyy | Tue May 1 2023 |
For Product types in the order you can enter a comma separated list of product types in the format e.g.
| Products | Product types in the order | Roller, Vertical, Venetian, Roman, Curtain, Shutter |
The report column will then show the number of each product type in the order e.g.
| Products |
| 2 x Roller 1 x Vertical |
If product name of an item in an order does not contain any of the words in the list, then the full product name is displayed.
Input data: The List1 or List2 Value
If you have configured List1 or List2 lists in Once Setup, then in Once Mobile the user must select one of the values from these lists before sending a quote or order. You can extract the selected value into a report column. Typical uses for the LIst1 or List 2 values could be the salesperson, the business branch or any other data you want to select from a list.
Input data: Payment amount/date/method/notes (Once Track V0.9 and later)
If you select Applies to "Payment Date", then you will get a line in the report for each payment made in the selected date range.
For other Applies to date types, then if you have multiple payments in an order, these will be grouped into the same cells for each payment field. e.g.
| Payment date | Payment amount | Payment Method | Payment Notes |
| 21/07/23 10/08/23 | 200 220 | Cash Card Reader | In showroom ref12345 |
You can also create separate columns for up to 4 payments in an order e.g. Payment 1 date, Payment 1 amount etc.. Contact support for instructions on handling more than 4 payments in an order.
Input data: Extract a value...
This allows you to extract text from the various notes fields. If the text starts with the specified prefix then all text from the prefix to the end of the line is extracted into a column. This is how you can record and extract any other data you want to be able to record and extract in your reports.
Examples:
| Notes field | Example prefix | Text typed in the field | Purpose |
| Appointment notes | sr: | sr: yes | Used to record if the enquiry was taken in the showroom |
| Customer Notes | Order Num: | Order Num: 0123456789 | Your internal Order Number if different to the Once Order Ref. |
| Worksheet Notes | Fit: | Fit: 2hrs | Fitting time estimate |
| Appointment Info | Cm: | Cm: L ( or E) | The appointment is a result of marketing campaign such as L for leaflet or E for email etc. |
Input data: Order or Item Value
The word you type in the column name is used as a keyword for a value in the Once Order.
If your report name starts with "blind" or "Item", there will be a report row for each item (blind, shutter etc), then the keyword can also apply to items values e.g. width, drop, product, range etc. The keyword is not case sensitive.
You need to select "Order or Item value" as the input data.e.g.
| Keyword | Notes |
|---|---|
| orderID | The order reference. Same as Input Data "Order Ref" |
| custName | Customer Name. |
| NofN | The item (e.g. blind) number and total number in the order |
| room | |
| product | |
| ranges | The ranges name. you can also use the keyword supplier, to match past versions of Once Setup and Mobile which used the term supplier for the first selection under each product. |
| range | |
| colour | You can also use the keyword color |
| fabricCode | Only if you have Fabric codes in your ranges tables. Normally only applies if you are using EDI ordering or sending order directly to Blindata systems. |
| width | always in mm |
| drop | always in mm |
| fixType | Fixing Type |
| fixheight | Fixing Height in mm |
| extras | The item extras, displays one per line e.g. Bottom Bar: Aluminum Chain: Nickel chain |
| slats | The calculated number of slats for a vertical blind. |
| item_price | The price of the item |
| extras_price | The total extras prices for the item |
| step1 | Item status1 (True/False). Same for step2, step3, step4 and step5. See also Reports: Showing Item Status e.g. "Ordered" or "Delivered" |
| custAdd | Customer address (excludes customer name, and postcode). Line breaks will be replaced by ", " (comma space). |
| custAddr | Customer name, business name, address and postcode. Line breaks will be replaced by ", " (comma space). |
| custEmail | Customer email |
| custPcode | Customer postcode |
If the value you require is not in the above list, contact Once Support as there may be special keywords that can be use. e.g. calc/tradePrice.
Date Formats
| Expression | Output |
|---|---|
| d | the day as number without a leading zero (1 to 31) |
| dd | the day as number with a leading zero (01 to 31) |
| ddd | the abbreviated localized day name (e.g. 'Mon' to 'Sun'). |
| dddd | the long localized day name (e.g. 'Monday' to 'Sunday'). |
| M | the month as number without a leading zero (1-12) |
| MM | the month as number with a leading zero (01-12) |
| MMM | the abbreviated localized month name (e.g. 'Jan' to 'Dec'). |
| MMMM | the long localized month name (e.g. 'January' to 'December'). |
| yy | the year as two digit number (00-99) |
| yyyy | the year as four digit number |
Some Dates can also include the time using the format hh and mm for hours and minutes.
These expressions may be used for the time part of the format string:
| Expression | Output |
|---|---|
| h | the hour without a leading zero (0 to 23 or 1 to 12 if AM/PM display) |
| hh | the hour with a leading zero (00 to 23 or 01 to 12 if AM/PM display) |
| H | the hour without a leading zero (0 to 23, even with AM/PM display) |
| HH | the hour with a leading zero (00 to 23, even with AM/PM display) |
| m | the minute without a leading zero (0 to 59) |
| mm | the minute with a leading zero (00 to 59) |
| s | the second without a leading zero (0 to 59) |
| ss | the second with a leading zero (00 to 59) |
| z | the milliseconds without leading zeroes (0 to 999) |
| zzz | the milliseconds with leading zeroes (000 to 999) |
| AP or A | interpret as an AM/PM time. AP must be either "AM" or "PM". |
| ap or a | Interpret as an AM/PM time. ap must be either "am" or "pm". |
All other input characters will be treated as text. Any sequence of characters that are enclosed in single quotes will also be treated as text and not be used as an expression.