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:
- Lookups tab: OrgVue provides one tab to store common Lookups
- 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:
- 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"