Manage Customers Profile using Interactive Grid and Input Form - Part 5
Summary
TLDRThis Tech mining video tutorial guides viewers on creating a customer management setup using Oracle Apex. It covers creating interactive grid and form pages for browsing, searching, adding, and removing customer profiles from a database. The tutorial demonstrates using wizards, configuring properties, and implementing features like breadcrumbs, modal dialogs, and validations to ensure data integrity. Viewers learn to enhance user experience with dynamic actions and input masks, culminating in a functional customer profile management system.
Takeaways
- 😀 The video demonstrates how to create a customer profile management setup using Oracle Apex.
- 🔍 It covers the creation of two pages, one for viewing and editing customer information and another for adding or removing customer records.
- 📊 The first page is an interactive grid displaying all customers from a database, with each customer's name as a clickable link.
- 📝 The second page is a form for inputting details of a new customer, modifying an existing customer's profile, or deleting a customer from the database.
- 🛠️ Oracle Apex's built-in wizards are used to facilitate the creation and configuration of these pages.
- 🔑 The primary key for customer records is set to 'Customer ID Number', and a sequence is used for auto-generating primary key values.
- 🎯 The interactive grid allows for data manipulation, including sorting, filtering, and direct cell editing, providing a more dynamic user experience than traditional reports.
- 📝 The form page supports functionalities like computations, validations, processes, and branches, enhancing data integrity and user interaction.
- 🌐 Breadcrumbs are used for hierarchical navigation, allowing users to understand their position within the application and navigate back easily.
- 🚫 Validations are created to prevent actions such as deleting customers with existing orders or setting a credit limit above the predefined cap.
- 🔄 A dynamic action is used to refresh the interactive grid immediately when the form page closes, ensuring the data displayed is always current.
Q & A
What is the main purpose of the video?
-The video demonstrates how to create a setup for managing customer profiles, including browsing, searching, modifying, adding, and removing customer records using Oracle Apex.
What are the two pages created in the video?
-The two pages created are an interactive grid page for viewing and searching customer records (page 2) and a form page for adding, modifying, and deleting customer details (page 7).
What is the difference between an interactive grid and an interactive report in Oracle Apex?
-An interactive grid is similar to an interactive report but offers additional features such as cell editing, fixed headers, frozen columns, scroll pagination, multiple filters, sorting, aggregates, and computations.
What is the purpose of a modal dialog page in Oracle Apex?
-A modal dialog page is a standalone page that appears on top of its calling page, preventing users from interacting with the underlying page until it is closed.
How does the breadcrumb navigation feature work in the application?
-Breadcrumbs provide a hierarchical list of links indicating the user's location within the application, allowing users to quickly navigate back to any level of the hierarchy.
What is the significance of setting the 'Data Source' attribute to 'Local Database'?
-Setting the 'Data Source' attribute to 'Local Database' specifies that the interactive grid will populate its data from a database table, enabling the display of customer records.
Why is the 'Customer ID' column set to 'Hidden' in the interactive grid?
-The 'Customer ID' column is set to 'Hidden' because it is a primary key column used for data integrity in the database and does not need to be displayed in the application interface.
How does the video guide users to modify the SQL query for the interactive grid?
-The video instructs users to replace the auto-generated SQL statement with a custom one that concatenates the first and last names of customers and combines the two address columns into a single address column.
What is the role of the 'Dynamic Action' created on page 2?
-The Dynamic Action on page 2 is responsible for refreshing the interactive grid immediately when the form page closes, ensuring that the grid displays the most recent data without requiring a browser reload.
How does the video address the issue of primary key violation when creating the first customer record?
-The video suggests that beginners may encounter a primary key violation due to the initial value of the sequence object. It recommends clicking the create button multiple times or adjusting the sequence object's start value to resolve the issue.
What validations are created in the video to ensure data integrity?
-Two validations are created: one to check that the customer's credit limit does not exceed a set cap of five thousand dollars, and another to prevent the deletion of customer records with existing orders.
Outlines
😀 Welcome and Overview of Customer Profile Management
This paragraph introduces the video, welcoming viewers and outlining the setup for managing customer profiles. It details the features of browsing, searching, modifying, adding, and removing customer records using Oracle Apex. It also describes the interactive grid and form pages for viewing and editing customer information, created using the Oracle Apex wizard.
😀 Creating Pages with Oracle Apex Wizard
This paragraph explains the creation of two application pages using the Oracle Apex wizard. The first page displays all customer data in an interactive grid, while the second page is a modal dialog form for adding, modifying, and deleting customer records. The wizard screens and their functionalities are described in detail.
😀 Setting Up Interactive Grid and Columns
This paragraph describes the setup of the interactive grid page. It includes instructions for setting properties, hiding columns, and saving and running the page to view the interactive grid displaying customer data. It also covers transforming customer name columns into links and setting up link properties.
😀 Configuring Create Customer Button and Form Page
This paragraph details the customization of the create customer button and form page. It explains modifying button properties, configuring form page items for better visual appearance, and ensuring mandatory fields are marked in red. The paragraph also describes saving progress and testing the form page.
😀 Adjusting Form Item Properties and Validations
This paragraph focuses on modifying form item properties, setting placeholders, and creating validations. It explains setting properties for optional items, adding placeholders for phone numbers, and creating validations for customer credit limits and preventing deletion of customers with existing orders.
😀 Changing Item Types and Attaching LOVs
This paragraph covers altering item types and attaching List of Values (LOVs). It describes changing the state item from text field to select list and attaching the state LOV created in a previous video. Additional properties like display null value and extra values are also explained.
😀 Creating and Configuring Validations
This paragraph elaborates on creating and configuring validations for form items. It includes steps for creating a validation for customer credit limits and preventing deletion of customers with orders. The paragraph also covers modifying wizard-generated dynamic actions for page refreshes.
😀 Using Search Bar and Actions Menu
This paragraph explains the usage of the search bar and actions menu in the interactive grid. It details searching for customer records, removing filters, resetting the grid, and exploring additional options in the actions menu. The paragraph also describes creating and modifying customer records using the form page.
😀 Summary of Learned Concepts
This paragraph summarizes the key concepts covered in the video. It includes creating report and form pages, modifying form input items, applying input masks, creating validations, and using dynamic actions. The video concludes with a thank you note to the viewers.
Mindmap
Keywords
💡Tech mining
💡Customer Profiles
💡Oracle Apex
💡Interactive Grid
💡SQL Select Query
💡Form Page
💡Modal Dialog Page
💡Breadcrumb Navigation
💡Primary Key
💡Dynamic Action
💡Validation
Highlights
Introduction of a setup to manage customer profiles with capabilities to browse, search, modify, and delete customer records.
Utilization of Oracle Apex wizard to create two pages for viewing and editing customer information.
Demonstration of an interactive grid on page two displaying a list of all customers using a SQL select query.
Explanation of how customer names in the interactive grid act as links to a detailed form page.
Introduction of page seven as an input form for adding new customers, modifying existing ones, and deleting records.
Overview of declarative development features offered by Oracle Apex for creating pages manually.
Description of the interactive grid's advanced functionalities compared to the interactive report.
Guidance on setting properties for the interactive grid page, including unique identification and page mode.
Explanation of modal dialog pages and their standalone nature in Oracle Apex applications.
Process of selecting a data source for the interactive grid from a local database table.
Use of breadcrumbs for hierarchical navigation within the application.
Instructions on setting up a primary key for database operations and the use of sequences for primary key values.
Customization of the interactive grid's appearance and behavior through column properties.
Transformation of the customer name column into a link to facilitate easy access to detailed profiles.
Modification of form page properties for better visual representation and user interaction.
Introduction of list of values (LOV) for selecting predefined options, such as states.
Application of input masks to standardize user input for phone numbers.
Implementation of validations to ensure data integrity, such as credit limit checks and preventing deletion of records with existing orders.
Use of dynamic actions to automatically refresh pages, enhancing user experience and data consistency.
Summary of the video's educational content, including the creation of report and form pages, modification of form items, and application of validations and dynamic actions.
Transcripts
a very warm welcome to Tech mining
in this video you will create a setup to
manage customers profiles that will
allow you to browse and search customer
records modify customers profiles add
record of a new customer to the database
and remove a customer from the database
this setup is based on the demo
customers table which was created in
part 2 video
in this video you will create two pages
with the help of Oracle Apex wizard to
view and edit customers information
this first figure is of page two of our
sales web application which is an
interactive grid and displays a list of
all customers from the database table
using a SQL select query
each customer's name appears as a link
in the interactive grid
when you click the name of a customer
this form page appears with complete
profile of the selected customer
this second figure is of page 7 which is
an input form to receive details of a
new customer modify the record of an
existing customer and delete customer
from the database
let's get our hands dirty with some
practical work to learn more exciting
declarative development features offered
by Oracle Apex
the home page of our application was
created by the app builder wizard at the
time when the application was created
the rest of the pages in this
application will be created manually
with the help of wizards and copy
utility
these following steps shows you through
to create the two pages via a built-in
wizard
you will use this create button
throughout this video series to create
new application pages
on this first wizard screen click
interactive grid
this wizard screen allows you to select
a single option from a collection of
multiple choices
this screen presents subcategories of
reports and requires a single selection
the report will base on the option you
selected here means an interactive grid
will act as a report to display all
customers from the database
to version 5.0 Apex used the interactive
report feature to present data in a
tabular form
since version 5.1 you are provided with
a new feature called an interactive grid
which is similar to the interactive
report but it allows you to manipulate
data simply by clicking on a cell and
editing its value
it also introduces fixed headers Frozen
columns scroll pagination multiple
filters sorting Aggregates and
computations
it is designed to support all item types
and item type plugins
these all functionalities is the major
difference between the interactive grid
and interactive report
[Music]
set the properties for the interactive
grid page
an application Express each page is
identified with a unique number
interactive grid page will be recognized
by number two
just like numbers a page is provided
with a unique name for visual
recognition
you can recognize a page by its name in
the app builder interface
the page mode property specifies how you
want to see a page it has two options
normal and modal dialog
new pages created in Oracle Apex default
to normal
in our application page 2 is a normal
page
7 on the other hand is a modal dialog
page a modal dialog page is a standalone
page which appears on top of its calling
page and doesn't allow users to do
anything else unless it is closed
a modal dialog page can be displayed
only on top of another page
[Music]
include form page
this page will carry a form to add
modify and delete customers and will be
called from page 2 customers
this page is a modal dialog page which
supports all the functionality of a
normal page including computations
validations processes and branches
[Music]
the form page will have number seven
[Music]
in the data source attribute select
local database in Source type we
specified to use a database table data
to populate this interactive grid
next you selected the default value
appearing in table view owner attribute
this is usually the database schema to
which you are connected once you select
a schema all tables within that schema
are populated in the table view name
drop-down list from where you select a
table demo customers in the current
scenario whose data will be displayed in
the interactive grid
[Music]
expand the navigation wizard screen
[Music]
a breadcrumb is a hierarchical list of
links
it indicates where the user is within
the application from a hierarchical
perspective
users can click a specific breadcrumb
link to instantly switch back to any
level
we use breadcrumbs as a second level of
navigation at the top of each page a
breadcrumb shared component was created
by the app builder when you created this
application earlier
for the breadcrumb parent entry for this
page select the home menu entry to
create a hierarchy in this application
foreign
foreign
[Music]
navigation preference map to an existing
navigation menu entry for this page set
existing navigation menu entry to setup
this step will make the setup entry
active in the main navigation menu when
this page is accessed
click next
[Music]
on this primary key page
set the first primary key column
attribute to customer ID number
in this step you specified the primary
key column
a primary key is a column or set of
columns that uniquely identify a record
in a table
forms perform insert update and delete
operations on table rows in the database
a sequence is a database object that
automatically generates primary key
values for every new customer record
leave the second primary key column to
default
click the create button to finish the
pages creation process
both pages are created and their
structures are presented in the page
designer
[Music]
during these Pages creation when you
choose demo customers table from tables
view name select list all the columns
from that table were selected
to hide the rest of the columns from
that table at runtime
expand the columns node under customers
region
set the type property to Hidden these
columns will not display at runtime
foreign
[Music]
foreign hit the save and run page button
to take a look at Pages we've just
created
[Music]
this is page 2 of our application which
contains an interactive Grid in which
you can view all customers data
these are displayed columns for which we
have not set the hidden property in
previous step you can also access the
page too from this main menu under this
setup entry
[Music]
this is the form page page 7 of our
sales web application which connects to
the local database and fetches data from
demo customers table into relevant page
items listed under the page items node
in page designer the same page items are
used to receive user input when a new
customer record is created
clicking the create button will also
open form page 7 but this time it
appears without any existing data
[Music]
under the source section select SQL
query to see the default query generated
for the interactive grid
foreign
[Music]
is provided in video description
download it open part 5 folder to get
all files used in video
[Music]
this SQL statement in SQL query text
area by replacing the existing query
here the auto-generated select SQL
statement is replaced with a custom
statement that uses the concatenation
operator to join columns
the new statement joins last and first
names of customers into a single column
the new concatenated column is
recognized by customer name
similarly the two address columns are
combined to form a single address
[Music]
expand the columns node under customers
region
click customer name column
[Music]
change its heading foreign
[Music]
the headings of other columns for better
visual look
[Music]
thank you
click the customer ID column make sure
that its type property is set to Hidden
column
this action will hide the column at
runtime
primary key columns are added to
database tables to enforce data
integrity and are not displayed in
applications
this is why such columns type property
is set to Hidden to make them invisible
at runtime
hit the save and run page button
note these columns headings are changed
click the actions menu
from the action menus list select
columns
in the columns window make sure all the
columns are selected that is they all
have a check mark if you remove a check
mark from a column it disappears from
the interactive grid report
[Music]
using this Arrow icon arrange these
displayed columns in this order
click the save button in the columns
window to apply the changes
[Music]
click the actions menu again select
report option click save option
after you modify an interactive grid
save it using this option otherwise you
will lose the applied settings when you
access it later
thank you
click page 2 in the developers toolbar
at the bottom of your screen to access
the page designer
click the customer name column to set
the properties
[Music]
in these properties you are transforming
the customer name column into a link
that will lead to page 7.
scroll down to the link section
click no link defined under Target to
bring up the link Builder dialog box in
the link Builder dialog box set the link
properties
foreign
[Music]
use this lovs in the set items section
to select the item name and the value
[Music]
when you click a customer's name in the
interactive grid report at runtime the
ID of that customer is stored in this
substitution string
then it is forwarded to this
corresponding page item p7 customer ID
on page 7 which displays the profile of
the selected customer using this ID
[Music]
clear cache property blanks all the
items on page 7.
[Music]
properties close the link Builder dialog
box using the OK button
[Music]
save and run the page
the customer name column will now appear
as a link
click any customer name to see the
details on page 7 which pops up on top
of page 2.
[Music]
after transforming the customer name
column into a link hide this pencil edit
icon at runtime
[Music]
mark this column as hidden
foreign
[Music]
click the customer's interactive grid
region
[Music]
make sure its template property is set
to standard
the selected template will place a title
and a border for the interactive grid
region
[Music]
in previous versions of Apex developers
used to create these buttons manually
but in this version Apex built-in
Wizards facilitated Developers
modify the properties of this wizard
generated create button
[Music]
the label of this button is set to
create customer
[Music]
the button is placed in the copy
position the position property provides
you with over a dozen values
the best way to understand the other
options is to try each one to see its
effect
[Music]
a hot attribute renders the button in a
dark color
the remaining properties create a link
to call Page 7.
the clear cache property blanks all the
items on the target page page 7.
don't forget to save your progress time
to time
[Music]
this is another way to call the desired
page from this search box
call form page page seven
[Music]
click each item under the customer's
detail region and modify the properties
these properties will provide form page
a better visual look at runtime
[Music]
this property provides a respective
sequence for the page items using
numbers
start new row is set to on through which
page items will be placed in a new row
at runtime
column and column span properties are
set to automatic which specifies how
many columns a page item should span
template property is set to required
which specifies that the page item must
be filled out before submitting the form
[Music]
the width property sets page items width
on the page
some values for the value required
property are set to on
if value required is set to on and the
page item is visible Oracle Apex
automatically performs a not null
validation when the page is submitted
then you are asked to input a value for
the field
this property Works in conjunction with
template required property to signify
mandatory column items displaying in red
color at runtime
save your work
[Music]
modify the properties for the remaining
page items
[Music]
by turning off this start new row
property this current page item will be
placed besides its previous page item
and will not use a new row
if start new row property is off then
this new column property is set to on
for an item to use a new column
[Music]
foreign
[Music]
template property is set to optional
which specifies that this page item is
not mandatory to be filled out
it will also not be marked in red color
at runtime
[Music]
if value-required property is set to off
then no validation is performed and a
null value is accepted
[Music]
foreign
[Music]
foreign
by setting this six value this page item
will be spent in this given value
[Music]
foreign
[Music]
this time empty the width property
further this item will be transformed
into a select list item
[Music]
thank you
[Music]
foreign
foreign
[Music]
thank you
[Music]
foreign
[Music]
foreign
[Music]
foreign
[Music]
foreign
[Music]
set the type property from text area to
text field
[Music]
thank you
[Music]
foreign
[Music]
refresh the Run page
[Music]
here's your modified form page page
seven these page items displaying in red
color are the items for which we have
set template property to required to
make these items mandatory
[Music]
foreign
[Music]
expand the pre-rendering node rename the
wizard generated process
if you see a different process name then
there is nothing to worry about as it
sometimes happens due to change in Apex
version
this process is responsible to
initialize form region items
the process fetches and displays data in
page items when you select a customer by
clicking the corresponding edit icon on
the reports page and it initializes the
page items when you create a new
customer record
this is the processing tab this process
is also generated by built-in wizards of
Oracle Apex
change the name of this process to
process customer data
this process is of automatic row
processing DML type and performs insert
update or delete action on a form region
customer details region in the current
scenario it's a good practice to save
your work after performing each and
every step
in this step you'll work on the state
column
first you will alter its type from text
field to a select list then you will
attach a list of value Lov to this
column
Oracle Apex allows you to change an
item's type from its default state to
another desirable type
for example this state item was
generated as a text type by the wizard
now we want to change this item to a
select list to hold a predefined States
list
[Music]
to display this list you'll attach the
state's Lov to this item
the state Lov was created in part 3
video of this series
[Music]
turn off display extra values property
an item may have a session State value
which does not occur in its list of
values definition if you choose not to
display this extra session State value
and there is no matching value in the
list of values definition the first
value will be the selected value
for instance while creating a new
customer record you will see choose a
state as the first value in the list
turn on display null value property
which is the default
the display null value property makes it
possible for a user to choose a null
value instead of one of the list items
it also provides additional properties
appear on the screen for you to specify
the display value for this new entry
which is choose state in the current
scenario
enter choose state in null display value
this step along with the previous one
generates a placeholder that appears on
top of the Lov asking for a selection
whenever you call this page to create a
new customer record
save your work
modify the two phone number items
[Music]
set their value placeholder property
under appearance section
[Music]
when a new customer record is added this
placeholder is shown in the two phone
number items to receive input in the
specified format
as you type in values the placeholders
will be replaced by the number you
entered
[Music]
validations enable you to create logic
controls to verify whether user input is
valid or not
in this step you will create a
validation to check customers credit
limit
the customer form contains a field named
credit limit which is used to assign a
credit cap to each customer with a
figure of five thousand dollar in the
current scenario
if you enter a value more than the
assigned cap you will be prevented by
presenting an appropriate message
in the left pane of page 7 click the
processing tab right-click the
validating node select create validation
from the context menu
this action will add a new validation
set the following properties for this
new validation
[Music]
thank you
type the same code as shown on the
screen for PL SQL expression property
[Music]
type this error message
note that the validation error message
displays only when the validation fails
the equality test or evaluates to false
this is the second validation to prevent
the deletion of those customers who have
placed orders
[Music]
foreign
ation is performed using a custom PL SQL
function which returns either a true or
false value
[Music]
the return value is based on a select
query which returns false if records
exist for the selected customer
if the returned value is false the error
message is displayed and the record
deletion process is aborted
[Music]
this property specifies if you want a
validation to execute only when a
specific button is clicked
[Music]
before the final run of this customer's
setup modify the wizard generated
Dynamic action on page 2 which refreshes
the interactive grid page immediately
when the form page closes
foreign
[Music]
make sure you're all the properties
should match the properties shown in
video
refresh is an action which executes when
the condition evaluates to true in other
words after the form page closes
all is set save the page and run it
the grid has a search bar comprising a
magnifying glass a text area and a Go
Button
the magnifying glass is a drop down list
you can use this list to limit your
search to a specific column
the bar allows you to search a string in
the report appearing underneath
type Albert in the text area and click
the Go Button
you will see a row displaying record of
Albert Lambert
[Music]
click the remove filter icon to
reinstate the grid to its previous state
alternatively you can click the reset
button appearing on the top right of the
grid
[Music]
the actions menu carries some more
options which will be explored in
subsequent videos
foreign
customer button calls the second page of
this setup page 7 where you enter
profile of a new customer
create a new customer by only filling up
the required Fields displaying in red
color
type first and last names for new
customer
this was the select list item State Lov
which we created in previous steps
select any state you like
[Music]
enter any zip code
credit limit value must be less than
five thousand dollars
this was the input mask which we entered
for two phone number items
[Music]
enter any email address
[Music]
click create button
you might encounter a primary key
violation message while creating first
customer record
this is because the sequence object for
this table is created with an initial
value of 1.
when you try to save the first customer
record one is assigned as its primary
key which already exists in the table
to cope with this situation developers
drops and recreate auto-generated
sequence objects with a higher start
with value
to keep things simple I'd suggest
beginners to click the create button on
the form page several times
after a few clicks the record will be
saved
here's the record you've just created
as you can see the customer name column
appears as a link
if you want to modify a specific record
click the corresponding link
again the same form page comes up where
all the fields are populated with
provided information
you are free to test your work
check the credit limit validation by
entering a value more than 5000 in the
credit limit box
click the apply changes button at the
bottom you'll see that error message
which you entered while creating this
validation
[Music]
click the name of any other customer
try to delete this record
you won't be able to do that because
there are some orders placed by this
customer and the validation you created
in previous steps will prevent the
deletion process
foreign
try renaming any record
[Music]
this is the immediate change without
reloading your browser's window
this happened because of the
wizard-generated dynamic action on page
2 which refreshes the interactive grid
page immediately when the form page
closes
here's the summary of what we learned
declaratively created report and form
pages
modified form input items using 12
columns grid layout
change the default type of an item and
used list of values learned how to apply
input mask to page items
created validations to prevent customer
record deletion with existing orders and
to check customers credit limits
used a dynamic action to automatically
refresh a page
thanks for watching
[Music]
Ver Más Videos Relacionados
Taking User Input Via Wizard in Oracle APEX - Part 17(B)
Display Images in Oracle APEX Interactive Reports and Forms - Part 8
Page Copy Utility Oracle APEX - Part 16
Interactive Report & Form Page Oracle APEX - Part 7
Learn How To Create Master Detail Pages in Oracle APEX - Part 10
Managing Events Using Calendar Page - Part 36
5.0 / 5 (0 votes)