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.

Please note, the structure of this article is to introduce the key concepts, with increasing details around implementation options

Entry level: 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”

Detail Level: Expression Lookup Mode vs. Lookup Dataset

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

The text labels on Pivot, Dashboard, Chart and etc. appear in the alphabetical order by default i..e the bars displayed will default to alphabetical order, 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 the ordering will be enforced on any views that use that property:

Addendum: using look-ups 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 lookup table by tagging it as ‘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 teh lookup dataset  based on:

  • Property being type text
  • Property having the same name as property in 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:

 

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

 

Have more questions? Submit a request

Comments