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