In this article, we explain how you can upload an Excel file yourself and use this data in your forms.
For this functionality, you need at least a Professional license. Additionally, you require certain user rights. If you do not have the appropriate rights, ask the administrator in your organization for access.
With Incontrol, you can import your Excel data into an Incontrol form in two ways. For instance, you can synchronize an online file from OneDrive. You can read more about this in this article.
Formatting an Excel File
To import data from an Excel file, the Excel must be structured in the right way. With Incontrol, you can import rules within an Excel sheet/table. Furthermore, you can link different sheets together, allowing you to import data into dynamic content. In this article, we use an example Excel with 3 sheets:
- Municipalities
- Employees
- Equipment
We aim to import a municipality with all its employees and the equipment they have at their disposal.
In the sheets, we have created tables with the correct data. The tables are as follows:
Municipalities
Employees
Equipment
The table always starts in cell A1. If you want to be able to import data within a dynamic content, it is important that you can link tables together via a common field. In this example, we want to be able to fetch the corresponding employees based on the selected municipality. And from these employees, we subsequently want to import the correct equipment. Therefore, in the table of Employees, you also see the municipality listed, and in the table of Equipment, the name of the employee.
Note: These tables are thus divided over 3 tabs in Excel.
Add a connector
To be able to use your Excel file, you first need a connector within your organization. Fortunately, you can easily create one yourself within our web portal.
- Go to 'Organization' on the left side of the menu
- Go to 'Connectors'
- Click on the plus button at the bottom right of the screen
- A pop-up will open
- Search for the 'Microsoft Excel' Connector and select it
- Next, select 'Use an Excel file as datasource in a form'
- Name your new connector, so you can easily recognize your new Excel file
- Click on 'Add'
- Upload a new Excel file by clicking on 'No file chosen' and select your file
- You will now see the following overview:
-
- You can choose to save the original Excel file, so you can always download it again later for editing.
- If you want the Excel data to be available even if the app user does not have an Internet connection, you can check the "Make available offline" checkbox. The app (currently only iOS) will then save the complete content locally, so it can be searched regardless of an active connection. Watch the video and article "Being able to use an Excel file offline".
- If you share drafts with third parties, you can specify whether they also get access to import data from this connector. Third parties are all individuals who do not have an account in your organization in Incontrol.
- Select from which column you want to import the data.
- In our example, we choose the 'Municipalities' tab.
- Under 'Column', you see the headers of your Excel table.
- Within Incontrol, you actually import a row of data from your table.
- Under 'List title', you can indicate based on which values you can search as a user.
- These are also displayed in your search results up to a maximum of 256 characters.
- Under 'List description', you can check extra columns that are sent as a description to the user.
- The user sees the titles in bold when importing the data.
- Descriptions are placed in a separate row per column in the search results. The apps support up to 4 lines to keep the overview.
- Under 'Import alias', you can change the alias if necessary.
- The system defaults to your column names. Note: A space is displayed with an '_'
- If you do not want to include the data from your column in your import, you can display an '_' in the 'Import alias'.
- When you are done, you can click on the floppy disk icon at the top right to save.
- For a Windows computer, you can also use the combination CTRL + S.
- For a Mac, you can also use the combination CMND + S.
Importing data into Dynamic Content
It is now also possible to import data from your Excel into dynamic content. This is only possible by uploading your Excel to Incontrol and not with the online version of Excel. Below we explain how to set up the connector.
- Add a linked sheet by pressing the '+ Linked sheet' button
- You will then see the following overview
-
- You are now going to link sheets together. In this way, you can fill dynamic content with all corresponding rows that you have selected from the primary sheet using the linked sheet.
- If done correctly, you have created tables with matching values, so the tables can be linked together.
- In our example, we want to import a municipality and based on that import all employees associated with that municipality.
- Under Primary sheet column, select the column from the primary table on which you want to load multiple rows into dynamic content.
- In this example, we select the Municipality column from the Municipality tab.
- Under Linked sheet column, select the column that matches in the tab you want to import into the dynamic content.
- In this example, we select the Municipality column from the Employees tab.
- As the illustration indicates, a row in the Municipality table can have multiple rows in the Employees table where the Municipality column is equal to Municipality.
- Specify an alias that you will link to the dynamic content in your form.
- In this example, we call this alias employees, so Incontrol knows which dynamic content it will later need to create items in.
- Optionally, specify additional aliases for the columns of the linked sheet.
- In our example, we add another linked sheet, and it looks as follows.
- Once you are done, you can save the connector and use it in your form. How to set this up is explained in the article Importing Data from an Excel File.