After you have introduced yourself in the basic configuration of a Template you are prepared to learn how the so-called "data1.io Template Framework" is configured in order to be able to use your own (existing) Exce files for data1.io collection workflows.
Let`s start in the Template Wizard with a Full reset and Download the Template as an Excel File:
Open the file in MS Excel and open the Name Manager:
As you can see the Template Wizard has created 8 named ranges in order to activate this Excel file for READ/WRITE communication with your data1.io application.
Named Range "data1_CELLS01" for the data entry cell range
The core of all is the named range "data1_CELLS01". These are the cells where your Collector Users will enter their data:
More excactly, it is the cell area from the first data entry cell "top left" until the last data entry cell "bottom right" (so it must always be a rectangle of connected cells). As you will learn a little later, there can also be R/W deactivated rows and/or columns within this area.
Named Range for the R/W control panel
We call the two named ranges "data1_CELLS01_ColumnDefinition" and "data1_CELLS01_RowDefinition" the R/W Control Panel. It directly controls how the cells within the "data1_CELLS01" range are treated in the READ and WRITE process:
"Double 2" on the crossing of the two panels means that the cell is activated for the READ and WRITE process. Learn more about the R/W control panel here ...
Named Ranges for Dimension Mapping
Every value in a cell of the "data1_CELLS01" range is defined by dimension keys which are used as filter criteria in the READ process and are used as values for the dimension columns in the Fact Table during the WRITE process:
The named ranges for the dimension mapping can have 3 different formats:
- 1 cell = field value
- more cells horicontal = column value
- more cells vertical = row value
Please be aware that the suffix for the dimension in the named ranges comes from the definition in the Template Wizard:
Named Range for Measure Mapping
The measure mapping is done by the named range "data1_CELLS01_ByColumnMapping". The entry in the cells - which are treated as column definitions for the corresponding cells in the "data1_CELLS01" - consist of two parts:
- Measure field or Text field in the Fact Table (here: "Measure01" and "Text01")
- Relative Record ID (here "0" since at this time this feature is not active in the "By Column" mode)
Please be aware that the "By Column" mode and the measure mapping comes from the definitions in the Template Wizard:
Important Rules for all named Ranges
If want to enrich your own Excel files with this "data1.io Template Framework" - and we strongly recommend to do so - it is important to know the following rules:
- Every named range must be defined on sheet level and not on workbook level:
So take care to define the scope for named ranges on this sheet in your workbook that is used for data entry (named ranges on workbook level are not recognized by the data1.io Cloud Service):
- All named ranges must start with "data1_CELLS01"
"data1" is the prefix to make it easy to seperate the data1.io Template Framework from other named ranges in an Excel file.
"CELLS01" is the prefix for the so called schema. At this time the Web Client supports only single-schema Templates, so you have to use always the "CELLS01" (the Excel Client supports also multi-schema Templates).
- The named range "data1_CELLS01" must always be a rectangle of connected cells
Therefore the defined cell area must range from the first data entry cell "top left" to the last data entry cell "bottom right". A range of disconnected cells would not work:
You will learn later how to handle R/W inactive rows and columns within the rectangle.
- All other named ranges (except the field values) must have the exactly either the same number of rows or the same number of columns as the "data1_CELLS01" rectangle
So it is not a good idea to create a Template like this ...
... or like that:
Now you are prepared to enrich your own Excel File and upload it into your application!
Of course we recommend to start with the smallest possible piece of your existing Excel solution to keep things simple and clearly arranged.