What is a matrix?
A matrix is an Excel spreadsheet built as a list for Paperless Forms, but will fill a table instead of providing a list of the items, like a list control would do.
Some may refer to it as master schedule, but it can also be used for other purposes:
sanitation master schedule
preventive maintenance
production
recipes
pre-operational inspections
etc.
A matrix will contain everything that might apply to the current need, but according to what is set up, will allow the user to automatically fill a table with different items depending on their needs.
Here is an example of a master schedule for sanitation, that is being used on a monthly form:
In this example, we have monthly tasks and according to the X's present on the matrix, not all tasks apply to every month. When you see the X under a month, the item will be in the filled table when you select that month.
As an end user, you only have to manage that one file.
In the system itself, every column after the seperator (yellow column in the image) is an individual list.
Creating a matrix
Adding the content for the table in the form
First, you need to generate a new Excel spreadsheet in the Lists folder of your file manager (Dropbox/Azure).
In that spreadsheet, start adding the titles of the columns you wish to have in the matrix, from cell A1. These titles need to be exactly the ones from headers in tables in your forms.
Add them consecutively, always with the titles all being on row 1 of the spreadsheet.
Row 1 is specifically reserved for the titles and the matrix triggers.
Add as many columns as you require.
Please note that the columns that are added don't have to necessarily be existing columns in your forms, but could still be relevant to your needs.
In this example, Frequency might not be a column in the table in the form, but is useful in the matrix to help schedule the different tasks properly.
Adding the matrix separator
Once you have added all the columns you need, add another one for the matrix seperator.
It consists in an exclamation mark (« ! »).
Everything on the left of that column is what will fill the table in the form.
Everything on the right of that column is the matrix, which will define when/how the items fill the table in the form.
We suggest you add a vivid color to this column, as you must not add anything else to it. You may also hide the column, which will still have it work.
Adding when it will be triggered in the matrix
Would that be a month, recipe number, department or something else, everything that goes on the right of the matrix separator will serve as your trigger.
It will define what will help you pick one column or another when you call on that matrix in your form.
Once you have added your items on the left of the matrix separator, mark with X's when each item should be triggered on the right side of it.
In this example, it would be depending on which month it should be done in.
Triggering a matrix
Multiple types of controls can trigger a matrix, such as text or numeric.
However, since the trigger needs to be inputted by the user, some controls are more dynamic than the ones suggested above:
List
Combo
Option Button
What is part of that control, to trigger the matrix, has to be exactly the same as in the matrix, on the right of the matrix separator.
For example, if your matrix contains all the months of the year, your list, combo or other control needs to have the exact same items.
From a form of your choice, create a field on the form that will be used as the trigger to fill out the table in your form;
In Column C, add the following validation rule, specific to a table filling:
Fills grid with [...]
« [...] » should be replaced with the name of your matrix Excel file;
Since a matrix is a single Excel spreadsheet you will be managing, it had to be separated in the system in order to know what is part of what.
For each column on the right of the matrix separator, it generates a list in the Paperless Forms system. It still is only one file for the user to manage, but in the system it is recognized as multiple lists.
Each column on the right of the matrix separator is an individual list and is named after the spreadsheet's name.
For example, matrix_sanitation_January, matrix_sanitation_February, etc.
Because of that, it will be important to add an extra underscore after the name of the matrix that you are targeting;
This will make it so that when you select the item with the trigger, it will select the right list and populate the table in the form.
The table has to have the exact same titles in it from the matrix, case for case, space for space, word for word.
If there is any difference, it will not work and the table will not fill properly.
Adding groups of rows to a matrix
Programming the groups of rows
Groups of rows will allow you to add more depth to your forms and make them more separated and convenient for the users.
Start by adding rows between the items where you want to separate them;
Merge all the cells on that row, for the number of columns you have;
Give that row of merged cells a name;
Before the name, add a pound sign (« # ») in order to make this a group of rows in Paperless Forms;
Once you fill the table in your form, you will be seeing group of rows in it.
Precisions
Here are some specifications regarding the groups of rows to ensure you add them properly:
If you want to use groups of rows, you need to start with one right at Row 2 (under the headers)
If a group of rows does not have items for the selected trigger, it will not feature in the table
You can have as many groups of rows as you would like
If you add more pound signs (« # ») to a group of rows, you will create a hierarchy, such as this:
And will result in this:
There is no number of levels you can add for groups of rows
If you don't add a pound sign (« # ») to a row of merged cells, it will be ignored as a group of rows
This becomes useful if you want to create rows of merged cells to help you navigate into what can become an enormous file without having to use the group of rows feature
Adding reference pictures to a matrix
Programming the reference pictures
Just like you would refer to pictures in your forms through lists, you can do it with a matrix too.
The pictures need to be in the same folder as the Excel spreadsheet of the template of the form, in a folder named « Photos Reference », and not with the matrix.
Simply add a column where you will add the full name and extension of the pictures to (if applicable), and when the table gets filled, it will add the pictures if they exist and are named accordingly.
It will look like this:
Precisions
Here are some specifications regarding reference pictures to ensure you add them properly:
There is no maximum number of pictures you can refer to
It is highly suggested that the column you use in your form to display the pictures is read only as well as optional, to avoid users from removing the references, but also avoiding invalidities due to empty picture cells
The name of the picture file needs to be a perfect match
The extension of the picture file needs to be added to the name and match too
Limitations
A matrix must absolutely be deposited in the Lists folder of the template manager used in order to be used in Paperless Forms
Upon its initial creation, a matrix 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, including the left and right side of the matrix seperator, and the separator as well
On the right of the matrix separator, these characters can't be used in the titles of the headers on Row 1:
\
/
:
*
?
"
<
>
|
#
ç
%
It will make the matrix unusable if that is ever the case
There are no restrictions on the character used to mark the items on the right of the matrix separator, however X's are highly suggested
There are no restrictions on the character used as the matrix separator, however the « ! » is highly suggested and some exceptions might not work
If you are using a list control to fill in the table, you will need to use the Fills grid with [...] validation rule first, then followed by the Updated by [...] that triggers the drop-down list in the control
Error proofing
Any type of user can trigger a matrix and fill a table.
As long as there is nothing in the table, the matrix can be triggered.
However, if anything is added to a filled table and a user tries to trigger the matrix again, they will be prompted with a username and password requirement, preventing them from clearing the table.
Only administrators and management users can enter their credentials to trigger the matrix again and overwrite the content in the table.
Matrices ideas
Precisions
It is important to note that these are suggestions.
They might not suit you, nor they are completed templates.
You might need to add more columns here and there, and do feel free to do so!
Daily sanitation/maintenance/other
A daily matrix would be used for tasks to be done on a 7 days schedule, which includes dailies, tasks that occur once a week or multiple times a week.
Best controls to trigger this matrix:
Combo
List
Option button "suntosat"
Weekly sanitation/maintenance/other
A weekly matrix would be used for tasks to be done weekly on a 52 weeks schedule, which includes weekly, monthly, annual and other tasks that are being done throughout the year (biannual, quarterly, etc.).
Best controls to trigger this matrix:
Numeric
Text
Combo
List
Monthly sanitation/maintenance/other
A monthly matrix would be used for tasks to be done on a 12 months schedule, which includes monthly, annual and other tasks that are being done throughout the year (biannual, quarterly, etc.).
Best controls to trigger this matrix:
Combo
List
Pre-operational inspection
A matrix for this type of need would be used for items to be completed depending on a specific department, plant, room, etc.
Best controls to trigger this matrix:
Combo
List
Option button (see list)
