Best Practice with Lookups: 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.  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.
Dataset

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.

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