How do I improve data validation using lookups?

There may be instances when you want to apply data validation rules to the properties in worksheets so that when content is shared with colleagues, they can only enter a set number of options for a particular property: lookups are a good solution to this requirement.

Lookup access rights (security) is not covered in this article, see here.

Background: Lookup Modes

OrgVue’s four lookup modes provides control and flexibility in using lookups.

Auto For datasets in the Lookup tab which share the same name as the key of the property, a lookup function will be performed. However, no other properties from the lookup dataset are imported.
None No lookup will be performed regardless of their being a dataset of the same name in the Lookup tab.

This mode allows you to select any dataset from either the Lookup tab or those tagged as lookup.  The dataset does not need to have the same name as the property key but the lookup will always be performed on the id field of the lookup dataset.  The properties of the lookup dataset will also be imported and are available for use in charts, dashboards, pivot view and elsewhere. The imported properties can also be accessed via expressions.

Expression This is where a lookup list is defined using a manual expression.


Using lookups for (secure) centralised data management

There are two ways to create a lookup table:

  1. Lookups tab: OrgVue provides one tab to store common Lookups
  2. Lookup tag: you can make any dataset in any tab into a lookup table by tagging it with ‘lookup’

When you need to add a new column to many datasets in reference to a single column, create it as a lookup table and do it once

When multiple datasets share the same property, make changes in a lookup dataset, then the changes will cascade to all

The datasets make the connection between properties in their worksheet and the lookup dataset based on:

  • Property being type text
  • Property having the same name as the property in the lookup dataset

Below is the configuration for a property in a destination dataset

In Auto mode, the source lookup dataset must have a property with the same name i.e. here, country to connect between the lookup dataset and source dataset

Back in the main dataset, the properties in the selected lookup dataset have been imported and available to use in the main dataset (which can be used in expressions or charts and pivot view)

It is also possible to use security to manage data distributions at a more granular level:

Supplementary topics

  • sample lookup tables e.g. colour, see here
  • using lookups to enforce standardisation e.g. hiding true salaries see here
  • working with expressions in lookups, see here



Expression Lookup Mode vs. Lookup Dataset

If you only have a few options:

  • Use Expression lookup mode and manually enter the accepted values in a list, e.g.: [“Country A”, “Country B”]

If you have a large number of options:

  • Create a lookup dataset containing the values for the list
  • Go back to your main dataset and open the property in question
  • Set up a lookup (Dataset mode) and select the dataset you just created
  • Enable “Restrict to Lookup”
  • The sequence in the Lookup dataset will determine the sequence in which this property is displayed in Dashboards, Charts, etc

Expression Lookup Mode enables enforcement of ordering besides providing a pick list

The text labels on Pivot, Dashboard, Chart, etc. appear in alphabetical order by default i.e. the bars displayed will default to alphabetical order. They can be sorted by size within the Chart Manager, or have a manual order defined for them using the “Expression” type lookup in the Edit Property dialogue e.g. ["firstValue",’"secondValue","thirdValue"] : or in context it should be possible to work with named colours ["red","green","blue"].

The Property should be configured as below, and is displayed as on the right:



Expression mode is useful for creating a lookup list on a manually entered set of options for the drop-down, with enforced ordering.

Make sure the syntax is correct. Enclose each item for the drop-down in quotation marks, separate each with a comma, and surround the whole expression in square brackets:

["Germany","United Kingdom","France","Poland", "Romania"]

Note that the ordering will be enforced on any views that use that property:


 For additional supporting information for this article, search on key terms "security" and "property"


Have more questions? Submit a request