===== How to configure cascading lookup =====

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.

**Currently two cascading levels are supported!**

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\\

{{:niota-docs:pasted:20251111-180449.png}}


**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''


{{:niota-docs:pasted:20251111-180504.png}}
 
''Country_ID'' has simple lookup. This is needed because the filtering in the dependent columns is done on event “select element”.

{{:niota-docs:pasted:20251111-180523.png}}

''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 checkbox is just to toggle usage between on/off

{{:niota-docs:pasted:20251111-180533.png}} 

''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.

{{:niota-docs:pasted:20251111-180542.png}}  

{{:niota-docs:pasted:20251111-180552.png}}

Relationship lookup for regions

{{:niota-docs:pasted:20251111-180606.png}} 

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

{{:niota-docs:pasted:20251111-180615.png}} 

Lookup instances for states and states per country.

{{:niota-docs:pasted:20251111-180633.png}} 

Relationship lookup data for states per country.

{{:niota-docs:pasted:20251111-180642.png}} 

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.

{{:niota-docs:pasted:20251111-180652.png}} 

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

{{:niota-docs:pasted:20251111-180710.png}}

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

{{:niota-docs:pasted:20251111-180721.png}}
 
{{:niota-docs:pasted:20251111-180736.png}}

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

 
{{:niota-docs:pasted:20251111-180745.png}}

(in real live there are more regions!)

==== sample DDL ====
<code>
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
</code>