Interactive Grid makes it effortless to create master-detail relationships and go any number of levels deep and across. You can create all types of master-detail-detail screens with ease. In this post, I’ll demonstrate this new feature.
1. From the SQL
Workshop menu, select SQL Scripts
and click the Upload button. In the Upload Script screen, click the Choose File button. In the Open dialog box, select master_detail_detail.sql file from the Chapter 5 folder in the book’s source code and click Open. Enter Master Detail
Detail in the Script Name box and
click the Upload button. In the SQL Scripts interface click the Run button appearing in the last
column. On the Run Script screen,
click the Run Now button. The script
will execute to create four tables (MD_continent, MD_country, MD_city, and
MD_population) along with relevant data to demonstrate the new master detail
detail feature. You can view these tables from the SQL Workshop > Object
Browser interface.
2. Create a new page by clicking the Create Page button in the App Builder
interface. This time, select the first Blank
Page option and click Next. Set Page Number to 112, Name to Master Detail Detail, Page Mode to Normal, and click Next.
On the Navigation Menu screen, select
the first Navigation Preference to
not associate this page with any sales app navigation menu entry. On the final
wizard screen, click Finish.
3. In the Page Designer, right-click the Regions node on the Rendering tab and select Create
Region. Set the following properties for the new region. This region will
display data from the MD_continent
table.
Property
|
Value
|
Title
|
Continents
|
Type
|
Interactive Grid
|
SQL
Query
|
select * from MD_continent
|
After
entering the SQL query, click anywhere outside the query box. Expand the Columns folder under this region. Click
the CONTINENT_ID column. Set its Type to Hidden and set Primary Key
(under Source) to Yes. You must define a primary key
column for an interactive grid region, which is required to establish a master-detail relationship.
4.
Create another region under the Continents region by right-clicking the
main Regions node. This region will
act as the detail for the Continents
region. At run-time when you select a continent, this region will display a
list of countries in the selected continent. Set the following properties for
this new region.
Property
|
Value
|
Title
|
Countries
|
Type
|
Interactive Grid
|
SQL
Query
|
select * from MD_country
|
Expand
the Columns folder under the Countries region. Click the COUNTRY_ID column. Set its Type to Hidden and set Primary Key
(under Source) to Yes. You set the Primary Key property to Yes because this region will act as a master for the Cities region created in the next step. Now, associate this detail
region with its master (Continents). Click
the Countries region and
set the Master Region property (under
Master Detail) to Continents. This should be set when
this region is the detail region in a master-detail relationship with another
region on the page. For the master-detail relationship to work correctly, you
must also select the column(s) in the detail region, which are foreign keys to
the master region, by setting the Master
Column property. Click the CONTINENT_ID
column (a foreign key) in the Countries
region. Set its Type property to Hidden and Master Column (under Master
Detail) to CONTINENT_ID, which
references the same column in the master region.
5.
Create another region and place it under the Countries region. This region will show
a list of cities when you select a country from its master region. Set the
following properties for this region:
Property
|
Value
|
Title
|
Cities
|
Type
|
Interactive Grid
|
SQL
Query
|
select * from MD_city
|
Master
Region
|
Countries
|
Expand
the Columns folder under the Cities region. Click the CITY_ID column. Set its Type to Hidden and set Primary Key
(under Source) to Yes. Click the COUNTRY_ID column in this region. Set the Type of this column to Hidden
and Master Column to COUNTRY_ID to point to the same column
in the Countries region.
6.
Create the last region to display the population of
a city.
Property
|
Value
|
Title
|
Population
|
Type
|
Interactive Grid
|
SQL
Query
|
select * from MD_population
|
Master
Region
|
Cities
|
Expand
the Columns folder under the Population region. Click the POPULATION_ID column. Set its Type to Hidden. Since this is the last region, you do not need to specify
this column as a primary key. However, you have to set a couple of properties
for the CITY_ID column in this region to associate it with its master. Click
the CITY_ID column, set its Type property to Hidden, and Master Column to
CITY_ID. That’s it!
Run
the page and click the row representing Europe (A) in the first region. As you
click this row, the second region will display countries in the Europe
continent. Click Germany (B) in the second region. This will refresh the third
region with a list of cities in Germany. Click Berlin city (C) to see its
population (D) in the fourth region.
