Maintain logic across properties with Dependent Lookups

Dependent Lookups are used when there is a logical relationship across different properties.  For example, across geographic ‘Region’, ‘Country’, and ‘Location’ properties. 


Other common applications involve maintaining logic across functional properties (e.g. 'Function', 'Subfunction' and 'Department'), and aligning cost centres with organizational subgroups.

Dependent Lookups operate in two directions: 

• Top-down:  After a value is selected for a higher-level property, only values which maintain the defined logic are presented. In the example above, when NORAM is selected for the Region property, only ‘USA’ and ‘Canada’ can be selected as Country values.
• Bottom-up:  When a value is selected for a lower-level property, the values for higher-level properties automatically populate.  For example, when ‘Philadelphia’ is selected as the Location, the Country property value sets to ‘USA’, and Region sets to ‘NORAM’.

Top Tip:  Using a worksheet slide, Dependent Lookups provide a fast way to bulk edit multiple properties for multiple nodes:

  

How to add a Dependent Lookup

The are 3 steps:

1. Prepare the Lookup file
2. Load the file as a Lookup dataset in Settings
3. Apply the Lookup in the required dataset

Step 1:  Prepare the Lookup file

The lookup dataset specifies the properties to be used and the logical – i.e. ‘dependent’ – relationship between property values.  In this example, three geographic properties are used:  ‘Region’, ‘Country’, and ‘Location’.

File Requirements

1. An Id property with a unique identifier for each relationship must be included
2. Properties (columns) must be presented in descending order of dependence

• Blank cells must be excluded

Tip:  Export an Orgvue worksheet slide with the properties to be used in the Lookup added as columns.  This will ensure the headings of your Lookup dataset match the property keys in the dataset it will be applied to.  The exported file can also be used to sort cases and rapidly extract each of the required dependent relationships:

Step 2:  Load the Lookup dataset in Settings

• Follow Steps 1-5 for Adding a new dataset using Settings.

At the ‘Edit details’ page:

1. Enter a name for the Lookup dataset (‘Geographic Levels’ in this example).
2. Select ‘Lookup’ as the Dataset Type.
3. Select’ ‘Flat’ as the Node Relationship type.
4. Select the ID property used in the Lookup file.
5. Enable users to access the lookup by applying at least ‘Read-only’ permission.
6. Select ‘Next’.

 
At the ‘Configure properties’ page:

• Check the file requirements are met:

1. Lookup dataset property keys match the property keys used in the dataset the lookup will be used with; and
2. Properties are listed in descending order of dependence.

7. Check the boxes indicating ‘Dependent’.
8. Select ‘Upload’.

 
At the ‘Summary’ page:

9. Select ‘Back to Datasets’.

The Lookup dataset is now seen.

1. In Settings, and
2. On the Workspace Homepage when the ‘Datasets’ tab is selected.

Step 3:  Apply the Lookup in the required dataset.

In this example, the ‘Geographic Levels’ Lookup will be applied to a People dataset, ‘Org Data Mar’.  (The same approach applies when a Lookup is applied to a Positions dataset).

Add an empty text property to the required dataset:

• The property being is added to support the operation of the lookup.  It is ‘read only’ and is not used in visualizations.
• The example below shows ‘Manage Properties’ being accessed in Workspace. This can be also accessed in Settings (select the required dataset; select ‘…’ select ‘Manage Properties’).

1. Select ‘Data’.
2. Select ‘Manage Properties’.
3. Select the ‘Add’ Icon.
4. Enter a Display name.  Tip:  Using a name which identifies this property as being used for the dependent lookup will be helpful for future reference.
5. Enter a Key for the property.
   1. A description for the property is optional.
6. The ‘Type’ must be set to ‘Text’.

7. Select the ‘Values’ Menu.

8. Select ‘Provide users with a set of values’.
9. Select ‘Provide values from a Lookup Dataset (Manual)’.
10. Select your Lookup Dataset (Geographic Levels in this example).
11. Select ‘Save.
12. Close the ‘Manage Properties’ window.
13. Reload Orgvue.

With the Dependent Lookup set:

1. Values for each dependent property can be set when one of the properties is edited.
2. When a value is selected for a higher-level property, only values which maintain the logic can be selected for lower-level properties.  For example,
   • When NORAM is selected as the ‘Region’, only ‘USA’ and ‘Canada’ can be selected for the ‘Country’ property.
3. When a value is selected for a lower-level property, the higher-level property values automatically populate.  For example,
   • When ‘Philadelphia’ is selected as the Location, the Country property value sets to ‘USA’, and the Region property value sets to ‘NORAM’.

Related articles:

• Control property values using Expression Lookups
• Standardize property values using Lookup tables
• How do I order my values?
• Editing property values
• Data Cleaning