Table of Contents
What is an external list?
An external list is an Excel file listing the elements that will allow you to create a list type control in Paperless Forms forms.
An external list is a file that is separate from the form template, which means that it can be connected to several forms while having only one source.
External lists are generally used to create a selection of important items, such as:
products
suppliers
clients
rooms
departments
employees
etc.
In addition, it is possible to add subsequent columns to give more information to the items, such as limits or specifications, for example.
Creating an external list
First, you need to generate a new Excel file in the Lists folder of your file manager (Dropbox/Azure).
In this file, start adding the titles of the columns you want to have in the external list, starting from cell A1. These titles must be exactly those of the titles used in the fields and columns of your forms.
Add them consecutively, always with the titles all on line 1 of the Excel file. Line 1 is specifically reserved for the titles of the various columns.
Add as many columns as needed.
Please note that added columns do not have to be existing columns in your forms, or in any specific order, but may still be relevant to your needs.
In this example, City or Country may not be a field or column in the form, but is useful in the external list to gather information in one place.
Triggering an external list
From a form of your choice, create a field/column on the list type form;
In column C, add the following validation rule, specific to connecting to an external list:
Updated by [...]
« [...] » must be replaced by the name of your Excel file which is an external list;When you tap on the list field/column on your form, the list will be available.
Adding information from the external list
When you have selected an item in an external list, it is possible to display more information related to it, such as limits or specifications, for example.
As long as all the information is part of the list, it will be possible to display it on the form and especially to take advantage of it to compare oneself with it.
On the form containing the field (or column) that uses the external list, add one or more fields (or columns, respectively) on the form, assigning them the correct type of control (text, num, yesno, etc. );
Be sure to add the read only validation rule in column C to prevent the information from being editable;
Select an item in the list to see information automatically added to the other fields (or columns).
Adding references pictures to an external list
Programming the reference pictures
The reference images are used to provide even more information about the chosen item.
The images should be in the same folder as the form template's Excel file, in a folder named "Reference Photos", not with the external list.
Just add a column where you will add the full name and extension of the images (if any), and when the item is chosen from the list, the image will be added.
Precisions
Here are some specifications for reference images to make sure you add them correctly:
There is no maximum number of images you can refer to
It is strongly suggested that the field (or column) you use in your form to display images be read only as well as optional, to prevent users from removing references, but also to avoid invalidities due to empty image cells
The name of the image file must match exactly
The image file extension must be appended to the name and also match
Programming conditional lists
Programming a conditional list
Conditional lists allow you to create an initial external list that will affect a future external list depending on the selected item.
For example, you might have an initial list that offers Fresh and Frozen items, and depending on the choice, the next list will list potentially completely different items.
Start by creating a basic external list, where you will have one or more columns containing items;
From a form of your choice, create a field/column on the list type form;
In column C, add the following validation rule, specific to connecting to an external list:
Updated by [...]
« [...] » must be replaced by the name of your Excel file which is an external list that you will use as the initial list, or known as the first level;Then create a new external list for each of the items in the initial list.
The names of the Excel files must be identical to the names of the items appearing in the initial list;
The title of the column that will be used to be listed in the second level lists must be identical to the title of the field in the form;
In the form, create a field/column on the list type form;
In column C, add the following validation rule, specific to connecting to an external list:
Updated by [...]
« [...] » must be replaced by the name of the field containing the initial or known as the first level list;In the imported form, select an item in the initial list, or known as the first level;
Then select an item in the conditional or known as second level list.
You can also try modifying the initial list item to notice a different conditional list.
Precisions
Here are some specifications for conditional lists to ensure you create and use them correctly:
A conditional list will never be generated as long as the initial, or previous, list that should trigger it is empty
There is no limit on the number of levels of conditional lists in a form
If an item of a conditional list is selected and the initial or previous list is modified, the subsequent levels will be erased
If there are additional columns in one of the conditional lists, you could use them to add additional information to the form
Limitations
An external list must absolutely be deposited in the Lists folder of the template manager used in order to be used in Paperless Forms
When initially created, an external list can take between 10 and 15 minutes to be available in the system
Changes made to a modified matrix will generally be available within 1 to 2 minutes after saving them
The maximum number of rows in a matrix is 10 000
The maximum number of columns in a matrix is 400
These characters can't be used in the titles of the headers on Row 1:
\
/
:
*
?
"
<
>
|
#
ç
%
It will make the external list unusable if that is ever the case
Row 1 of the file is used for the titles of the different columns
Everything on the same line (example: row 2) of the external list Excel file will be part of the same element
It is not possible to merge cells in an external list
External lists are retroactive and do not require the form templates to which they are connected to be updated; you just have to wait for the list to update and when it opens it will be at its last iteration
Items from an external list already selected in forms which are then removed from this external list will not be removed from the forms; only future forms will not be able to select removed items
An item selected in a list type field cannot display information in the columns of a form grid
An item selected in a list type column cannot display information in the fields of a form