Configuring forms for automatic completion

Configuring Fields

Most field types are eligible for automatic completion by Excel upload. When editing these fields in the form builder, you can find a section in the configuration called “Spreadsheet reference”. This entry field accepts Excel formulas. Most Excel formulas for looking up data are supported. These include, among others, V and H -lookup, index/match, and direct cell references. It is advised to construct more complex formulas in the Excel file first, before entering the formula in the field configuration.

When working with Excel files with multiple sheets, it is also advised to use the sheet reference in the formula. A cell reference formula with a sheet reference is built up in the following structure:

=<sheetname>!<cellreference>

For example:

=Sheet2!B23

Not using a sheet reference in the formula will automatically point to the active sheet, saved in the Excel file.

Date

Only cells with the format “Date” are supported.

Select/Radio

The value from the cell is matched against the labels of the options in the configured database or database relation. The match between the value in the cell and the available values produced from the database or database relation needs to be an exact (case-sensitive) match.

Select(multi)/Checkbox

As with Select/Radio fields, the match between the cell and the values of the database and database relations need to be exact (case-sensitive).

Additionally, you can configure a delimiter here if you multiple values in 1 cell in Excel. All values found this way need to match with the configured database or relation, if one or more values are not matched, no values will be filled in.

In the example above, cell A1 produces the following values:

france;germany;belgium

These equates to:

• france

• germany

• belgium

All values here can be matched with the configured database, which produces a valid result.

Text

The value found in the cell is converted to string without formatting and input in the DOQMIND form field.

Textarea

The value found in the cell is converted to string without formatting and input in the DOQMIND form field. Line breaks in the cell are not transferred to the DOQMIND form field.

Testing reference mapping

Once a form contains all of necessary mappings, it can be validated by matching with an example Excel. This can be done choosing “Test reference mapping” in the quick actions menu.

An Excel can now be uploaded. Once uploaded, a section at the top of the form appears confirming the selected Excel file. If correct, “Validate” can be clicked to perform the test. As long as the current session in the form builder is active, the reference file will remain. This allows you to make quick and easy adjustments to the spreadsheet references and re-test, without having to upload new files every time.

Once validated, each configured field in the form will show the value found in the uploaded Excel. There are three possible outcomes:

• The references correctly found are shown in green.

• The references that didn’t return any value are shown in yellow. This is mainly due to the found cell in the Excel file being empty

• The references that return an error are shown in red. Here, the Excel error will be shown. For further help with understanding these Excel errors, please check https://support.microsoft.com/en-us/office/detect-errors-in-formulas-3a8acca5-1d61-4702-80e0-99a36a2822c1

For a comprehensive look at automatic form completion by Excel, please see How-to: Automatically Fill in Form

Task Execution

During the task execution, fields with an invalid mapping are left empty. If this is not acceptable, a field can be configured to be “required”. This way we are ensured that a value is filled in.