niota

In case you have a table where there are interconnected columns, you can configure cascasding lookup in niota. This means the selection of the first column reduces the options of the second. If the second is also connected to the third, then selection of the second column reduces the elements of the third lookup.

Typical example is a table with addresses or personal information and the typical geographical hierarchy like Country → State → Region

One country has several states One state has more than one region

To implement this, you need basically four tables and five lookup configurations.

Tables/Views
T_Person – This is the table you want to edit for example
T_Country – Simple country lookup data table/view
T_State – Relationship table/view where all states per country are listed
T_Region – Releationship table/view where all regions per state are listed

Upon this tables you have to define 5 lookup objects (please feel free to use you own names)
#1 – MyCountry – simple lookup on T_Country that displays country ID + description
#2 – MyCountryState – relationship lookup to find all states per country
#3 – MyState – simple lookup on T_State that displays state ID + description
#4 – MyStateRegion – relationship lookup to find all regions per state
#5 – MyRegion – simple lookup on T_Region that displays region ID + description

Please mind that lookup type = 1 (cascaded lookup) requires three columns in the definition of the instance. Column #1 – is the filtered column Column #2 – is the key to be displayed Column #3 – is the text to be displayed

For technical reasons in case of cascading lookup a table column needs following settings:
#1 – a standard lookup on the column (to indicate the system that there is a lookup at all)
#2 – an extended lookup definition for the column
#3 – a reference to the filtering column in this table. Note that the filtering column itself MUST also have a simple lookup defined.

Below you see the settings for the columns Country_ID, State_ID and Region_ID

Country_ID has simple lookup. This is needed because the filtering in the dependent columns is done on event “select element”.

State_ID has a simple lookup to indicate the system there is a lookup rule at all. There is also the extended lookup MyCountryState linked and a reference to the filtering column Country_ID. The use/ignore extended lookup is just to toggle usage between on/off

Region_ID is configured very similar to State_ID. It has a standard lookup, an extended lookup and a reference to the filtering column.

LookUp instances It’s recommended to define the lookup objects on specific lookup instances. In this example we define the “simple” lookup for state as a subset of all states per countries and the lookup for region as a subset of the relationship for regions per state.

Relationship lookup for regions

Simple lookup for regions, derived from relationship object (table/view) for regions per state.

Lookup instances for states and states per country.

Relationship lookup data for states per country.

Simple lookup for state, derived from relationship object (table/view) for states per country.

Result If you have configured all correctly, you should see now in you editing table T_Person tree lookups.

When you select a country, the next dropdown for states should be filtered on all states of this country.

(in real live there are nine federal states in Austria!)

When you filter a state, the next drop-down should display only the regions of this state.

(in real live there are more regions!)

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[T_Country](
	[Country_ID]          [varchar](2) NOT NULL,
	[Description_Country] [varchar](20) NOT NULL,

 CONSTRAINT [PK_T_Country] PRIMARY KEY CLUSTERED 
(   
     [Country_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[T_State](
	[Country_ID]          [varchar](2) NOT NULL,
	[State_ID]            [varchar](3) NOT NULL,
	[Description_State]   [varchar](20) NOT NULL

 CONSTRAINT [PK_T_State] PRIMARY KEY CLUSTERED 
(   
     [Country_ID] ASC,
	 [State_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[T_Region](
	[State_ID]            [varchar](3) NOT NULL,
	[Region_ID]           [varchar](5) NOT NULL,
	[Description_Region]   [varchar](20) NOT NULL

 CONSTRAINT [PK_T_Region] PRIMARY KEY CLUSTERED 
(   
     [State_ID] ASC,
	 [Region_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO

drop table if exists [dbo].[T_Person];

CREATE TABLE [dbo].[T_Person](
	[Person_ID]           [varchar](6) NOT NULL,
	[Country_ID]           [varchar](3) NOT NULL,
	[State_ID]            [varchar](3) NOT NULL,
	[Region_ID]           [varchar](5) NOT NULL,
	[Description_Person]  [varchar](20) NOT NULL,
	[ZipCode]			  [varchar](6)

 CONSTRAINT [PK_T_Person] PRIMARY KEY CLUSTERED 
(   
     [Person_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO