Thursday, 8 June 2017

Using Master Detail And Detail And Detail in Oracle APEX

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 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 to 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 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 the Berlin city (C) to see its population (D) in the forth region.




The instruction provided in this post are extracted from the book Oracle Application Express 5.1 Basics & Beyond






No comments:

Post a Comment