27 Sep, 2016

Creating An External List From Excel And Importing It

Create your external lists more quickly and easily by creating them in Excel.

When you create your form, you may be required on more than one occasion to insert defined lists for the same elements, whether for the same form or different forms. With Kizeo Forms, you will be able to  create a standard list that avoids having to input the same list each time! We call this an  External list. In this tutorial, we teach you how to create this list from Excel.

You must use Microsoft Excel 2007 at least in order to  register your list in .xlsx format. If you do not have the Microsoft Office suite, you can download the trial version of obtain the full pack on the  partner's site.

The different kinds of lists.

There are various forms of lists on Kizeo Forms:

  • The Simple list: this is a list of towns and cities, for example.

  • The Hierarchical list: this is a list with several levels (up to 9). For example, you can create a list that offers you a certain number of regions, where each region offers you the corresponding departments, and finally each department offers you the corresponding cities.

  • The referential list: this allows several fields to be linked to a list (up to 49). During input, when an item is selected from this list, the data linked to that item will be automatically inserted into the field provided for that purpose. You can, for example, create a list of persons involved. When one of these is input, their address and telephone number will appear automatically.

  • The referential and Hierarchical list: this, following a list of several levels (9), allows automatic input of the fields linked to that list (up to 49). By following the two examples above, we will choose our person involved from a list of seniority of towns and cities. The information particular to that person involved will be included automatically in the Reference fields provided for that purpose.

  • The List with codes: this allows the export, for a selected item, to display a name different from that read during the input. This may be an abbreviation, code or reference, for example.

  • The filtered list: this will display or hide the elements of an external list according to the information shown in the user profiles.

Step 1. Creation of lists in Excel.

For these lists, there are two very important points that will prevent proper operation if they are not respected:

  • Your list, whatever form it takes, must be created in an Excel file, saved in .xlsx format.
Your list must be created in an Excel file, saved in .xlsx format  
  • Kizeo Forms recognises the list type according to the column names. It is therefore essential to name the columns exactly as explained below and always start with A1.

The Simple List.

  • Cell A1 must be named label.
  • Start inputting your list at A2.

The Hierarchical list.

Hierarchical list may contain up to 9 levels, that is, 9 columns on the Excel file.

  • On this file, on the first line, cells A1 and B1 must be named level_label, and the last column must be named label.
  • Input your list.

Explanations: take the example of California. By selecting the State of California, you will then have to choose between several cities in California: Los Angeles, San Francisco, San Diego and Santa Ana. Then, when choosing Los Angeles, you will be able to select a county within Los Angeles (Pasadena, Santa Clarita or Long Beach).

The Referential List.

The  referential list may contain up to 49 columns.

  • Cell A1 must be renamed labeland the following cells B1, C1, D1 etc. must be renamed ref.
  • Input your list.

Explanations: take the example of Leroy. When you select Leroy, his first name, address and member number will appear in the fields provided for the purpose (the fields Reference or  Calculation). Reading will be by column: column 1 is defined by the last name; column 2 is defined by the forename, and so on.

The Hierarchical & Referential List.

The Hierarchical & Referential list of seniority may contain 9 columns for the seniority section and 49 columns for the referential section. Given that the last column of seniority and the first column of referential are common to both lists.

  • In your Excel file, name cells A1, B1, C1, etc. in your section named Hierarchical list level_label. The last column corresponding to the Hierarchical List, which is also the first column of the Referential List, must be named label. The following columns, which correspond to the Referential List, must be named ref.
  • Input your list.

Explanations: take the example of Languedoc-Roussillon. When you select Languedoc-Roussillon, the following list will offer you Aude, Gard and Hérault. When you choose Hérault: Béziers and Montpellier will be offered to you. If you choose Montpellier: Girard, Dupont, Dufour and Martin will appear. If you select Martin: Marie, Montpellier, 25 Avenue Saint Lazare, 34000 and 7620 will appear in the fields provided for the purpose (Reference fields).

The List with Codes.

This function is available only for elements originating from a label column. The code appears in the History and in the Excel exports (lists and personalised).

  • The column preceding label must be named code.

Explanations: Take the example of Jules Petit. On export, instead of seeing Petit appear, his member code 89512 will appear in the History and the Excel imports.

The Filtered List.

The  Filtered list will  display or hide the  elements of an e xternal list according to the information shown in the  user profiles.

  • In your Excel file, rename cells A1, B1 and C1 with user_ref1user_ref2 and user_ref3 if you have three types of filters to insert therein.
  • The following column, D1, must be named Label. This is the column that corresponds to the elements in your list.
  • Input your filters and the elements in your list.

Explanations: Users with the filter  Germany configured in their user profile will see on their mobile or tablet the elements Ginger  Breads, MandelkuchenRussischer ZupfuchenChocolate cakeApple Pie and  Speculaas. In contrast, they will not see the other elements on the list appear.

Step 2. Importing the lists.

  • Go to the online platform and click on Create an external list. Click on the Excel option and click or drop your excel list file here.
Importe Excel file
  • Once your list is imported click on Overwrite everything then Confirm the changes and go back to edit.
Confirm changes list
  • The list will be displayed automatically.
  • Do not forget to save! 

Go a little bit further…


Go digital today!

Create an account to test the Kizeo Forms App Free of charge for 15 days !

We are available to answer your questions, suggestions, remarks, etc
Would you like to subscribe to our Newsletter?