Validation Rule: External Drop-down List

#EN #Excel #Validation #Dropbox

Updated over a week ago

Important information

You will need to use the keyword «list» to use the following validation rules and to create an external drop-down list.

When creating an external drop-down list, make sure you do it in the correct format.

Validation rules available

Create an external list from a file

Validation rule to use: Updated by [Excel_File_Name]

Allows to list items from a column in the external list that has the exact same name as the title of the field or column.

  • The items will be listed as they are in the external list

  • Don't leave blanks in the external list

  • If you want something to be set to "N/A", you need to use:
    N/A: for text and drop-down list fields/columns
    NA: for option buttons, date and/or time fields/columns
    NaN: for numeric fields/columns

  • The list field will contain a Search field that will allow you to filter long lists faster

Fill a grid with items using a matrix

Validation rule to use: Fills grid with [Excel_File_Name]_

Allows to fill a grid with items from an external list formatted as a matrix into the columns with the exact same titles as the matrix.

  • The underscore (_) after the Excel file name is important and allows you to target the right column in the matrix

  • The items will be listed as they are in the external list

  • It is not possible to create rowgroups with this validation rule

Use an external list to trigger a matrix to fill a grid

Validation rule to use: Fills grid with [Excel_File_Name]_ Updated by [Excel_File_Name]

Allows to fill a grid with items from an external list formatted as a matrix into the columns with the exact same titles as the matrix, but the external list you select from is the trigger.

  • The underscore (_) after the Excel file name is important and allows you to target the right column in the matrix

  • The items will be listed as they are in the external list

  • The list field will contain a Search field that will allow you to filter long lists faster

  • It is not possible to create rowgroups with this validation rule

In order for everything to work properly, you need to:

  • Have an external list which dresses a list of all the columns used in the matrix. This list needs to be in the Lists folder of your Dropbox;

  • Have a matrix that contains all items you need to have in your grid according to your selection, with the columns in the matrix that are the same as the list (this creates the link between the two). Everything that will be checked will be taken in account when selecting this column. The matrix needs to be in the Lists folder of your Dropbox;

  • Have a field in your form that has exactly the same name as the column in your external list (in this example, it is "Department:");

  • Add in column B of this field the keyword list;

  • Add in column C of this field the validation rules Fills grid with [Excel_File_Name]_ and Updated by [Excel_File_Name]
    The order you use those rules is really important;

  • Add in your grid the title(s) of the column(s) you are targeting in your matrix so that the items from it appear;

  • Once in the form, make your choice in the drop-down list;

  • Once an item is selected, the grid will be filled automatically by the item you've selected, which is in fact the column of the same name in the matrix.

Invalid characters to use

The following list of characters cannot be used in the title of your columns (Row 1), but only for the ones that are in your matrix, on the left of the separator ("!") column:

  • \

  • /

  • :

  • *

  • ?

  • "

  • <

  • >

  • |

  • #

  • ç

  • %

Keywords available with this validation rule

Did this answer your question?