Grids are among the most powerful features of Orbeon Forms. With Orbeon Forms 2021.1, we are improving grids in two ways.
First, the Grid Settings dialog now shows the number of grid rows in the grid. This parallels the number of grid columns, with some differences.
Grid Columns
Grid Rows
12 or 24
arbitrary
set in dialog
set in editor area
view in dialog
view in dialog
Number of grid rows
Second, for repeated grids only, an option allows you to automatically show a row number at the beginning of each grid repetition. You enable this in the Grid Settings dialog as well.
Enabling showing the repetition number
The number automatically shows to the left of the grid and updates as the user add or remove repetitions.
Repetition number
We hope you will like these enhancements to grids!
Orbeon Forms 2021.1 includes a new feature to import and export Excel files. [1]
Previous Orbeon Forms versions already supported an Excel import feature. Here are the main differences between the two types of Excel imports:
Existing import feature
New import feature
multiple form documents at a time (batch)
one form document at a time
row and column-based
named range based
no repeat support
repeat support
fixed layout
customizable layout
The new document-based import feature does not replace the previous one but complements it. Each feature might be improved in the future.
The Excel export feature, on the other hand, is entirely new.
Scenarios
This feature is aimed at organizations whose users are familiar with Excel spreadsheets. Users can export a form to a spreadsheet and fill it out on their computers. They can pass the spreadsheet around for other users to complete it. When they are done, they can import the resulting spreadsheet back to Orbeon Forms, where the data is captured, validated, shown to the user for review, and then saved to the Orbeon Forms database.
In addition, you don’t have to export a form to Excel to import data: as long as you have a spreadsheet that includes the appropriate named ranges (see below), you can use it for importing. This means that you can reuse existing spreadsheets with a few additions, and keep a spreadsheet layout that users are familiar with.
The export feature
The Excel export feature allows you to export a form definition to an Excel file. The Excel document contains all the fields of the form. Say you have the following form in Form Builder:
Example of form in Form Builder
Form Runner attempts to mirror the layout of the original form and includes section titles and field labels as well.
Example of exported form
Named ranges
The Excel file format supports so-called named ranges. This allows you to give one or more cells a name. Orbeon Forms uses this feature to assign every cell, or, in the case of repeated grids, groups of cells, a name which matches [2] the control name assigned in Form Builder. This also allows reimporting data in those cells.
Named ranges in Excel
Repeated grids
Repeated grids are supported. When repeating over more than one row, they are flattened to show one Excel row for reach repetition. [3]
Repeated grid
Here is how the repeated grid appears in the Excel document:
Repeated grid in Excel
Selection controls
Selection controls are represented with a list of items above the field. The user can then enter the value in the field for import. [4]
The import feature
How it works
You use the same Excel Import page as before. The page is configured to support the document-based Excel import. You start by uploading the document.
Upload
The uploaded document then goes through validation, and a summary is provided. In this case, there is a missing required field.
Validation summary
Finally, you have the opportunity to review the entire data before saving it. The user can choose to fix the error directly in the form and then save, or can fix the error in the Excel file and start over with the import process.
Data review
Cells in the Excel document are matched with the form’s cells using the named ranges. If you use an Excel document that has been exported by Orbeon Forms, then the named ranges are already included. But you can also modify the document produced by Orbeon Forms, or create your own spreadsheet from scratch. You just need to include the proper cell names that you need. You can even have field values that are calculated in your Excel document, although this is not generated by Orbeon Forms, and the result will be imported. [5]
Limitations
As available in Orbeon Forms 2021.1, the new Excel import and export feature has the following limitations:
no 24-column mode
no dynamic list of choices (for example dynamic dropdowns)
no calculations or validations are exported
no nested repeats
repeated grids or sections are always flattened
no section templates support
some form controls are not supported, including
Formatted Text
Handwritten Signature
Attachments
Conclusion
We hope you will like this new and improved Excel import and export feature! It will be available with Orbeon Forms 2021.1 PE.
We refer to “Excel files” also as “Excel spreadsheets” or “Excel documents”. ↩
Excel names do not support all the character supported in Form Runner names. So the names are modified when needed. ↩
Say you have a form that contains 2 fields, a and b, and you want users to provide a value for a or for b; it is ok if they provide values for both a and b, but it isn't ok if they leave both a and b empty. A practical example would be a form where you have 2 fields: "Home phone number" and "Mobile phone number", and you need users to leave at least one of those two numbers.
In Form Builder, with the UI shown below, you can declare a field to be required (the "Yes" radio button), or not required, which is the default (the "No" radio button), but you can also write a formula which determines dynamically whether the field is required (the "Formula" radio button), and this is what allows you to handle a case as the one we're considering here.
Say your two fields are named home-phone and mobile-phone. You want to say that the home phone is required if no mobile phone was provided. You can do this with the formula xxf:is-blank($mobile-phone). Conversely, the formula for the mobile phone will be xxf:is-blank($home-phone).
At runtime, the asterisk to the left of the label also updates as you interact with the form, becoming:
A red asterisk if a value is required, and none is provided
A black asterisk if a value is required, and one is provided
No asterisk if a value is not required, because another phone number was provided
The Orbeon Forms date field allows users to type a date, but also to select a date using a date picker. While most people are used to interacting with date pickers using a mouse, touchpad, or a touch interface, the Orbeon Forms date picker also supports doing so with the keyboard:
Tab to the date field, if the focus is not already on that field.
Hit the ↓ Down arrow key to open the date picker.
Use arrows keys to navigate through the calendar; for instance hitting the ↓ Down arrow key takes you to following week.
Press ↵ Enter to select the highlighted date.
This makes the date picker more accessible, but also faster to use for power users, especially in cases where you want to enter a relative date, like "a week from now", or "a month from now, skipping to Monday if that date falls on a weekend". Using the keyboard to select a date in the date picker has been a feature of the product since Orbeon Forms 2019.1.
