2-day Developer Guide Version 4.0.1
@FOEXplugins
FOEX 2-day Developer Guide, version 4.0.1 Copyright © 2018, FOEX GmbH. All rights reserved. Authors: Peter Raganitsch, Matt Nolan, Dietmar Gabauer This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you November not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse engineering, disassembly, or de-compilation of this software, unless required by law for interoperability, is prohibited. The information contained herein is subject to change without notice and is not warranted to be error-free. If you find any errors, please report them to us in writing. tryfoexnow.com
2 of 4
FOEX 2-day Developer Guide
Version 4.0.1
Table of Contents Introduction ........................................................................................................... 1 Requirements/Prerequisites ................................................................................1 Required Database Objects .................................................................................2 Defining FOEX attributes ......................................................................................3 About the application you will build in this tutorial ..........................................4 Creating a FOEX Application ................................................................................ 5 Creating an updatable Grid (page 2) ................................................................... 6 Creating a FOEX Grid in Row Editing Mode ........................................................ 6 Modifying FOEX Grid columns ...........................................................................13 Adding a LOV and Column Formats ..................................................................15 Changing the design of the Pane to fit .............................................................17 Adding customer validation to the grid ............................................................18 Change row editing to cell editing mode..........................................................21 Creating a Master-Grid & Detail-Form (page 3) ............................................... 25 Creating a read-only FOEX Grid ......................................................................... 25 Freezing columns in a grid .................................................................................29 Adding sort and sort sequence ......................................................................... 30 Saving grid state .................................................................................................. 31 Creating a FOEX Form .........................................................................................32 Changing the layout of the Pane .......................................................................34 Creating a Grid-Form Master Detail Relation ..................................................35 Adding a display image to the form ..................................................................38 Styling items within a FOEX form ......................................................................41 Creating a Tree Grid (page 4) ............................................................................. 44 Explaining the SQL query for Tree Grids ..........................................................47 tryfoexnow.com
3 of 4
FOEX 2-day Developer Guide
Version 4.0.1
Export data from the tree grid...........................................................................52 Add custom icons to tree entries ......................................................................53 Creating a FOEX Form (page 5) .......................................................................... 55 Creating a Master-Detail-Detail relationship: tree Grids & a modal FOEX Form (page 6) .................................................................................................................61 Creating two FOEX grids within a layout ..........................................................61 Adding a third read-only grid to the Center Pane ...........................................69 Creating a FOEX Form that opens as a modal window ..................................75 Creating a FOEX grid combo .............................................................................. 79 Connect the form to the grid ............................................................................. 81 Making the modal window non-modal ............................................................83 Creating a Master-Detail-Detail relationship: a master Form and two Grids as detail (page 7) ......................................................................................................87 Creating a FOEX form with a button for Insert and Update .......................... 87 Creating a Grid dependent on the Form ..........................................................90 Change settings for inserting records to the grid ...........................................92 Creating a second grid as a detail to the first grid .......................................... 93 Adding new data to three tables in two steps ................................................95 Creating a Tree linking to all pages built previously (page 1) ........................ 96 Adding a HTML region to the Center Pane ......................................................96 Creating a FOEX Tree linking to the other pages .............................................98 Adding a toolbar to the North Pane ............................................................... 106 Changing the FOEX Application Settings ........................................................113
tryfoexnow.com
4 of 4
FOEX 2-day Developer Guide
Version 4.0.1
Introduction Requirements/Prerequisites This document is based on the assumption the FOEX Plugin Framework v4.0.x is already installed in the Oracle APEX 5.0.x, 5.1.x, 18.1.x Instance you are going to test with and has a workspace with the FOEX Demo Applications installed. If this is not the case please refer to FOEX Plugins Installation Guide, which is contained within the FOEX Shipment. It contains the installation notes and Demo Application Installation into your workspace. Alternatively you can sign up for a test workspace on www.tryfoexnow.com to complete this 2 day developer guide which will have everything setup for you to start this tutorial immediately. For the development of FOEX applications a current version of Firefox or Google Chrome browser is required and the installation of the FOEX Developer Add-on (browser add-on) has been performed prior to starting. If you have not yet installed it please see the FOEX First Steps Guide.
tryfoexnow.com
1 of 114
FOEX 2-day Developer Guide
Version 4.0.1
Required Database Objects The required database objects for all pages you will create using this guide are objects that are installed when creating the APEX-Sample Database Application (DEMO_CUSTOMERS, DEMO_STATES, DEMO_ORDERS, DEMO_ORDER_ITEMS, DEMO_PRODUCT_INFO). Please install the sample database application via packaged applications if it is not already installed!
Sample Database Application
tryfoexnow.com
2 of 114
FOEX 2-day Developer Guide
Version 4.0.1
Defining FOEX attributes With APEX 5 the definition of FOEX attributes has changed. Wherever FOEX attributes can be set, you find a blue FOEX Config button at the attribute pane of the APEX 5 Page Designer.
Hidden Field vs. Hidden Column
A Hidden Field column in a Grid will be shown as a hidden object, Hidden Columns are excluded from the page.
Defining button images
Images can be set by using the proper CSS class which can be entered manually (1) or selected by opening the icon list (2) e.g.:
tryfoexnow.com
3 of 114
FOEX 2-day Developer Guide
Version 4.0.1
About the application you will build in this tutorial In this tutorial we will create an application to show the different possibilities with FOEX Plugins on the basis of an imaginary Mail Order Business. We’ll create a simple FOEX Form to insert new customers, a FOEX updatable grid with the possibility to insert/update/delete customers. We’ll bind a read-only FOEX Grid to a FOEX Form as Master/Detail relationship to insert/update/delete products. We’ll also create three reliant FOEX Grids with Master/Detail/Detail relationship on the basis of Order Items, that belong to an Order, which belong themselves to a customer. We create a FOEX Tree Grid to summarize Orders, which can be exported as CSV. On the Start Page we’ll combine all these pages with a FOEX Tree that gives the option to open all pages in the Center Pane of the same page.
tryfoexnow.com
4 of 114
FOEX 2-day Developer Guide
Version 4.0.1
Creating a FOEX Application Each time you create a new FOEX Application start by copying the FOEX Template Application contained within your workspace (see FOEX First Steps for further detail).
tryfoexnow.com
5 of 114
FOEX 2-day Developer Guide
Version 4.0.1
Creating an updatable Grid (page 2) In this tutorial we create a Grid where you can insert/update/delete one record after the other in the row editing mode. Then, we’ll modify the Grid to cell editing mode where you can execute changes on several records at the same time. We integrate some customer-specific validations and change the format of our items. To perform this task you'll create a page with FOEX Viewport and one updatable FOEX Grid in the Center pane.
Creating a FOEX Grid in Row Editing Mode 1. Press the Create FOEX Page button 2. Enter 2 as Page Id, Customers – updatable Grid as Page Title. In the modal window Center is ticked and can't be ticked off. Keep the setting Show Region as Tabs (tabpanel). Press Create
tryfoexnow.com
6 of 114
FOEX 2-day Developer Guide
Version 4.0.1
3. You see now the Viewport and the Center Pane Region, that were just created
4. Right click on the Center Pane Region and select Create FOEX Sub Region to open the wizard for creating a Grid
5. Enter Region Title Parent Region
tryfoexnow.com
Customers Center Pane
7 of 114
FOEX 2-day Developer Guide
Version 4.0.1
6. Click on the Grid icon 7. Change Allowed Operations to Create, Update, Delete 8. Enter Table/View Primary Key Item Include Buttons
SQL Query
DEMO_CUSTOMERS CUSTOMER_ID (is set automatically) uncheck Apply Changes leave Create and Delete checked Change the generated SQL to SELECT , , , , , , , , , , , FROM
tryfoexnow.com
CUSTOMER_ID CUST_FIRST_NAME CUST_LAST_NAME CUST_STREET_ADDRESS1 CUST_STREET_ADDRESS2 CUST_CITY CUST_STATE CUST_POSTAL_CODE PHONE_NUMBER1 PHONE_NUMBER2 CREDIT_LIMIT CUST_EMAIL DEMO_CUSTOMERS 8 of 114
FOEX 2-day Developer Guide
Version 4.0.1
9. Click Create
10. The Wizard has created two regions: Customers, which is a FOEX Grid region and Customers – Query Definition, which is a standard APEX Report region whose Condition-Type is set to Never. Also two buttons were created.
tryfoexnow.com
9 of 114
FOEX 2-day Developer Guide
tryfoexnow.com
Version 4.0.1
10 of 114
FOEX 2-day Developer Guide
Version 4.0.1
11. Select Attributes of FOEX Grid region Customers and open the FOEX Settings page by clicking on the blue FOEX Config button
12. In the FOEX Settings set Panel Collapsible to No and leave the other attributes with their default value
tryfoexnow.com
11 of 114
FOEX 2-day Developer Guide
Version 4.0.1
13. Now you can run this page by pressing the Run button
14. Your first self-made FOEX Grid is displayed
15. Click on one row in the FOEX Grid and you can see that all columns are defaulted to input fields
tryfoexnow.com
12 of 114
FOEX 2-day Developer Guide
Version 4.0.1
Modifying FOEX Grid columns 1. Change back to the Page Designer Tab/Window and expand the Columns node at Customers – Query Definition report region. Select CUST_FIRST_NAME and make the following changes Heading Cell Width
First Name 150
!
2. Run the page again. You can see the new heading, the expanded field and when now clicking on a row you can change the value of the First Name and update your changes.
tryfoexnow.com
13 of 114
FOEX 2-day Developer Guide
Version 4.0.1
3. Go back to the Page Designer and change all Column Attributes
e.g. as following definitions: Change all Columns to Type Text Field
Make following changes Column Name / Alias
Col.
Heading
Cell
Align.
Width
Comp.
Sum
Sort
CUST_FIRST_NAME
First Name
left
CUST_LAST_NAME
Last Name
left
CUST_STREET_ADDRESS1
Street
left
CUST_STREET_ADDRESS2
left
CUST_CITY
City
left
CUST_STATE
State
left
Yes
CUST_POSTAL_CODE
Postal Code
left
Yes
PHONE_NUMBER1
Phone No1
left
PHONE_NUMBER2
Phone No2
left
CREDIT_LIMIT
Credit Limit
right
CUST_EMAIL
Email
left
150
Sort
Seq.
Yes
1
Yes
2
200
Yes
150
Yes 200
Yes Yes
Note: When you don’t define a column’s width, the default width is 100
4. Run the page and check the changes
tryfoexnow.com
14 of 114
FOEX 2-day Developer Guide
Version 4.0.1
Adding a LOV and Column Formats 1. For column CUST_STATE change the Attribute Type to Select List, Select the List of Value Type SQL Query and enter the following SQL - Query SELECT , FROM ORDER
INITCAP(STATE_NAME) AS D
ST AS R
DEMO_STATES
BY 1
2. For column CREDIT_LIMIT add the Appearance – Format Mask you want to use to display the number (e.g. FML999G999G999G999G990D00)
tryfoexnow.com
15 of 114
FOEX 2-day Developer Guide
Version 4.0.1
3. Run the page and when you click on a State you get a LOV you can select from. The Credit Limit is displayed as currency amount.
4. When you press on Create an empty row is shown. Insert the new record by pressing Update.
5. Click on a record that you want to delete, then press Delete.
tryfoexnow.com
16 of 114
FOEX 2-day Developer Guide
Version 4.0.1
Changing the design of the Pane to fit Until now the Pane is shown as a Tab. As there will be no other Panes on this page we can change this.
1. Select Attributes of the Center Pane and change the Layout to Only 1 Child Region (fit) and save changes
2. When you run the page the Pane uses the whole Viewport
tryfoexnow.com
17 of 114
FOEX 2-day Developer Guide
Version 4.0.1
Adding customer validation to the grid We'll now add some customer validation - you can add some for each grid item. We check that the Credit Limit is between 0 and 5000 and the name of the City has at least 2 Char. Therefore we write our own procedure FOEX2DAYS_GRID_VALIDATION
1. To add the Procedure, go to SQL Workshop ---> SQL Commands CREATE OR REPLACE PROCEDURE foex2days_grid_validation ( p_crud_request IN FX_CRUD_REQUEST_T ) AS BEGIN FOR i IN 1.. p_crud_request.new_record.count LOOP -IF p_crud_request.new_record(i).name = 'CREDIT_LIMIT' THEN -IF to_number(
p_crud_request.new_record(i).value_varchar2
) < 0 OR
to_number(
p_crud_request.new_record(i).value_varchar2
) > 5000 THEN fx_p_grid.add_grid_error ( p_column_name =>
p_crud_request.new_record(i).name , p_message =>
'The credit limit has to be '||
'between 0 and 5000' ); END IF; -END IF; -IF p_crud_request.new_record(i).name = 'CUST_CITY' THEN -tryfoexnow.com
18 of 114
FOEX 2-day Developer Guide
Version 4.0.1
IF length(
p_crud_request.new_record(i).value_varchar2
) < 2 THEN fx_p_grid.add_grid_error ( p_column_name =>
p_crud_request.new_record(i).name , p_message => 'The city must be at least '
||
'2 characters in length' ); END IF; -END IF; -END LOOP; END;
2. Open Region Attributes of Customers (FOEX Grid region) and insert FOEX2DAYS_GRID_VALIDATION to Computation and Validation Procedure
tryfoexnow.com
19 of 114
FOEX 2-day Developer Guide
Version 4.0.1
3. Run the page and modify a city to a single character and the Credit Limit to 9999. Now press Update and you'll get the message
4. Press OK, then move the mouse over one of the red triangles and you'll get the message you've defined in the procedure
tryfoexnow.com
20 of 114
FOEX 2-day Developer Guide
Version 4.0.1
Change row editing to cell editing mode We’ll change the Editing Mode from Row to Cell Editing to be able to change different records within one step. We’ll also modify the Address to be a Textarea. 1. Select Attributes of Region Customers (FOEX Grid) and change the Edit Mode to Cell Editing. Save the changes.
tryfoexnow.com
21 of 114
FOEX 2-day Developer Guide
Version 4.0.1
2. Create a new region button SAVE similar to the automatically created buttons with Button Icon Action When Button Clicked Database Action
fa fa-floppy-o Defined by Dynamic Action SQL UPDATE action
!
tryfoexnow.com
22 of 114
FOEX 2-day Developer Guide
Version 4.0.1
3. For all three buttons change Button alignment to Left
! 4. When you now run the page all buttons are on the left side and you can add new rows and update cells from different rows within one step. All changes are marked with a red triangle in the upper left corner in the cell. By pressing Save you commit all changes to the database.
tryfoexnow.com
23 of 114
FOEX 2-day Developer Guide
Version 4.0.1
5. In Cell Editing mode you can display textfields with longer content as Textareas. Go to Customers – Query Definition, select the column CUST_STREET_ADDRESS1 and change Type Attribute to Text Area.
! 6. When you click on a street the whole text is shown as a text area where you can add/change some text. The full profit you'll get when you change the length of the field in the database, as DEMO_CUSTOMERS.CUST_STREET_ADDRESS1 is VARCHAR2(60).
tryfoexnow.com
24 of 114
FOEX 2-day Developer Guide
Version 4.0.1
Creating a Master-Grid & Detail-Form (page 3) In this tutorial we’ll create a “List of Products” as a read-only FOEX Grid that works as master and a FOEX Form as detail, where we can update/insert/delete products. To have a better overview we’ll freeze the Product Name in the FOEX Grid, so we can scroll to the right and have the Product Name always displayed. To perform this task, we create a page with Viewport and a Center Pane, where both regions are placed. To synchronize the records between the master and the detail we’ll use dynamic actions.
Creating a read-only FOEX Grid 1. Create a new FOEX page in your application with Page Id 3 and Name Products - ReadOnlyGrid (Master) Form (Detail). Just create the Center Pane for this page
tryfoexnow.com
25 of 114
FOEX 2-day Developer Guide
Version 4.0.1
2. Right Click on the Center Pane Region and Create FOEX Sub Region 3. Enter the following values: Region Title Parent Region Region Type Allowed Operations SQL Query
Products ..Center Pane Grid Read Only SELECT PRODUCT_ID , PRODUCT_NAME , PRODUCT_DESCRIPTION , CATEGORY , LIST_PRICE , CASE PRODUCT_AVAIL WHEN 'Y' THEN 'Yes' WHEN 'N' THEN 'No' END AS PRODUCT_AVAILABLE FROM DEMO_PRODUCT_INFO
!
tryfoexnow.com
26 of 114
FOEX 2-day Developer Guide
Version 4.0.1
When you run this page all columns are displayed.
4. As we don't want to see the Primary key column, go to Page Designer for page 3, expand Column node under Products – Query Definition and Column PRODUCT_ID. In the column settings set the Type to Hidden Field.
tryfoexnow.com
27 of 114
FOEX 2-day Developer Guide
Version 4.0.1
5. Change the Column Attributes (Heading, Column width, Alignment, Number Format..) of the report columns as described for page 2.
Enter Maximum Row Count to 500 and set an appropriate pagination size e.g. 50 for the Number of Rows setting. Column Name / Alias
Column Heading
Cell
Width Comp.
Align. Sum
PRODUCT_NAME
Product Name
left
PRODUCT_DESCRIPTION
Description
left
CATEGORY
Category
left
LIST_PRICE
List Price
right
PRODUCT_AVAILABLE
Product Available left
tryfoexnow.com
Sort
Sort
Seq.
700
28 of 114
FOEX 2-day Developer Guide
Version 4.0.1
Freezing columns in a grid 1. Open the FOEX Settings of column PRODUCT_NAME and set Freeze Column Yes
! 2. When you now run page 3 you see that the Product Name always is on the first position also when scrolling to the right.
tryfoexnow.com
29 of 114
FOEX 2-day Developer Guide
Version 4.0.1
Adding sort and sort sequence By default all columns are marked as sortable. For column PRODUCT_NAME set the sort sequence to 1 ascending.
Sorting can be done by 1 to n columns. This can be set at design time but also at runtime. The sort order can be modified by adding/removing columns from the sort order.
tryfoexnow.com
30 of 114
FOEX 2-day Developer Guide
Version 4.0.1
Saving grid state You can display the Grid with saved Grid state (remember resized column widths, hidden columns, re-ordered columns via drag & drop) every time you reload the page.
Note: State saving can be disabled/enabled application wide in the Component Settings of the FOEX Application Settings. (Shared Components -> Component Settings)
Select Attributes of the Products grid, open FOEX Settings using the blue FOEX Config button, then scroll down and change the Save State to Yes.
Now every time the page is opened it will be displayed in the same way as it was left.
tryfoexnow.com
31 of 114
FOEX 2-day Developer Guide
Version 4.0.1
Creating a FOEX Form 1. Right click on the Centre Pane and select Create FOEX Sub Region Region Title Parent Region Choose Table/View Primary Key Item Include Buttons Include Columns
Product ..Center Pane Form DEMO_PRODUCT_INFO P3_PRODUCT_ID Apply Changes, Create, Delete PRODUCT_ID
PRODUCT_NAME
PRODUCT_DESCRIPTION
CATEGORY
PRODUCT_AVAIL
LIST_PRICE
PRODUCT_IMAGE
! tryfoexnow.com
32 of 114
FOEX 2-day Developer Guide
Version 4.0.1
2. Now you see the recently created grid and the newly created form
3. The buttons are displayed in order of their sequence. You can change the order as you prefer. (e.g. DELETE (10) , SAVE (20), CREATE (30) or by using drag & drop. 4. When running the page you see a tab for the grid and another for the form. Next, we’ll change the layout
tryfoexnow.com
33 of 114
FOEX 2-day Developer Guide
Version 4.0.1
Changing the layout of the Pane 1. Go to Page Designer, select Attributes of the Center Pane and set Layout to Vertically Stack Regions (vbox) on the Settings Pane.
2. When you run page 3, you can see both regions but without any relation between the two.
tryfoexnow.com
34 of 114
FOEX 2-day Developer Guide
Version 4.0.1
Creating a Grid-Form Master Detail Relation 1. Create a Dynamic Action for the FOEX Grid Products
2. Choose FOEX Master Detail [Plug-in] as True Action (if not done automatically) • Insert PRODUCT_ID of the Products Grid SQL in Master Connection Column • Insert PRODUCT_ID of the Products Form items source in the Detail Connection Column
tryfoexnow.com
35 of 114
FOEX 2-day Developer Guide
Version 4.0.1
3. Set Select Master Record after Load to Select First Record
4. In Affected Elements choose Selection Type Region and Region …Product
!
5. Run page 3. After page load, the first row of the grid will be selected and the form is loaded. Click on another row of the grid and it will sync with the form.
tryfoexnow.com
36 of 114
FOEX 2-day Developer Guide
Version 4.0.1
6. To have the right filename and mime type set File Name Column to FILENAME and Mime Type Column to MIMETYPE for column P3_PRODUCT_IMAGE
tryfoexnow.com
37 of 114
FOEX 2-day Developer Guide
Version 4.0.1
7. After making the above changes to the P3_PRODUCT_IMAGE item, run the page and you should see the following when clicking on the download button
Adding a display image to the form 1. In addition to downloading a previously uploaded file we November want to view it, if it’s an image. Our next step is to add a new display image to the form 2. Right click on the Product’s Items node and select Create Page Item
tryfoexnow.com
38 of 114
FOEX 2-day Developer Guide
Version 4.0.1
3. Apply the following attributes to the newly created page item Name Type Labe Filename Column Mime Type Column Default Mime Type Image Width Image Height Show full Image View Source Type Database Column Name Source Used
P3_PRODUCT_IMAGE_DISPLAY
FOEX Display Image [Plug-In]
Image
FILENAME
MIMETYPE
image/jpeg
210
140
No
Database Column
PRODUCT_IMAGE
Always, replacing any existing value…
4. Now run the page and you should see the following display tryfoexnow.com
39 of 114
FOEX 2-day Developer Guide
Version 4.0.1
5. Click different rows within the grid to see the displayed image changing accordingly (or use the Up/Down arrows after selecting the first row)
tryfoexnow.com
40 of 114
FOEX 2-day Developer Guide
Version 4.0.1
Styling items within a FOEX form 1. Select Attributes of FOEX Field Container Product – Field Container and change the Default Label Width to 200
! 2. Select P3_PRODUCT_NAME and under Appearance set Template to Required and Value Required to Yes.
tryfoexnow.com
41 of 114
FOEX 2-day Developer Guide
Version 4.0.1
3. Select Page Item P3_PRODUCT_AVAIL and change the following attributes Type Label Show Radio Items in 1 Row? Template Value Required List of Values - Type Static Values
!
tryfoexnow.com
FOEX Radio Group [Plug-In]
Product Available
Yes
Required
Yes
Static Values STATIC2:Yes;Y,No;N
!
42 of 114
FOEX 2-day Developer Guide
Version 4.0.1
4. Run the page and you’ll see a beautifully designed Master-Detail relationship where you can insert, update and delete products.
tryfoexnow.com
43 of 114
FOEX 2-day Developer Guide
Version 4.0.1
Creating a Tree Grid (page 4) In this tutorial we’ll create a page with Viewport and a FOEX Tree Grid in the Center pane. 1. Create a new FOEX page in your application with Page Id 4 and Name Orders per Customer – Tree Grid. Again, just create the Center Pane for this page but using Only 1 Child Region (fit).
2. Right click on the Centre Page region and select Create FOEX Sub Region Region Title Parent Region Choose Allowed Operations
tryfoexnow.com
Orders per Customer
..Center
TreeGrid
Read Only
44 of 114
FOEX 2-day Developer Guide
Version 4.0.1
SQL-Query
SELECT 'C'||TO_CHAR(C.CUSTOMER_ID) AS ID , TO_CHAR(NULL) AS PID , C.CUST_FIRST_NAME ||' '|| C.CUST_LAST_NAME AS TEXT , NULL AS URL , NULL AS DISPLAY_SEQ , NULL AS CSS_CLASS , TO_CHAR('Customer ID: '||C.CUSTOMER_ID) AS INFO , NULL AS CUSTOM , COUNT(OI.ORDER_ITEM_ID) AS ORDERS_TOTAL FROM DEMO_CUSTOMERS C , DEMO_ORDERS O , DEMO_ORDER_ITEMS OI WHERE C.CUSTOMER_ID = O.CUSTOMER_ID AND O.ORDER_ID = OI.ORDER_ID GROUP BY C.CUSTOMER_ID , C.CUST_FIRST_NAME , C.CUST_LAST_NAME UNION /* all Categories per Customers */ SELECT 'O'||C.CUSTOMER_ID||SUBSTR(PI.CATEGORY,1,1) AS ID , 'C'||TO_CHAR(C.CUSTOMER_ID) AS PID , PI.CATEGORY AS TEXT , NULL AS URL , NULL AS DISPLAY_SEQ , NULL AS CSS_CLASS , 'Customer Name: '||C.CUST_LAST_NAME AS INFO , NULL AS CUSTOM , COUNT(OI.ORDER_ITEM_ID) AS ORDERS_TOTAL FROM DEMO_CUSTOMERS C , DEMO_ORDERS O , DEMO_ORDER_ITEMS OI , DEMO_PRODUCT_INFO PI WHERE C.CUSTOMER_ID = O.CUSTOMER_ID AND O.ORDER_ID = OI.ORDER_ID AND OI.PRODUCT_ID = PI.PRODUCT_ID GROUP BY C.CUSTOMER_ID , C.CUST_LAST_NAME , PI.CATEGORY UNION /* all Products per Customer and Category */ SELECT 'P'||C.CUSTOMER_ID||PI.PRODUCT_ID AS ID , 'O'||C.CUSTOMER_ID||SUBSTR(PI.CATEGORY,1,1) tryfoexnow.com
45 of 114
FOEX 2-day Developer Guide
, PI.PRODUCT_NAME , NULL , NULL , NULL , 'Category: '||PI.CATEGORY , NULL , COUNT(OI.ORDER_ITEM_ID) FROM DEMO_CUSTOMERS C , DEMO_ORDERS O , DEMO_ORDER_ITEMS OI , DEMO_PRODUCT_INFO PI WHERE C.CUSTOMER_ID = O.CUSTOMER_ID AND O.ORDER_ID = OI.ORDER_ID AND OI.PRODUCT_ID = PI.PRODUCT_ID GROUP BY C.CUSTOMER_ID , PI.PRODUCT_NAME , PI.PRODUCT_ID , PI.CATEGORY
Version 4.0.1
AS AS AS AS AS AS AS AS
PID TEXT URL DISPLAY_SEQ CSS_CLASS INFO CUSTOM ORDERS_TOTAL
!
tryfoexnow.com
46 of 114
FOEX 2-day Developer Guide
Version 4.0.1
3. Change the settings in the Regions Attributes of Orders per Customer (Tree Grid): Get Child Nodes Via A JAX? No
!
4. Run page 4. You can now expand/collapse nodes.
Explaining the SQL query for Tree Grids
tryfoexnow.com
47 of 114
FOEX 2-day Developer Guide
Version 4.0.1
1. The first UNION selects all parent nodes (all different Customers) with PID (Parent Id) NULL the TEXT is the displayed information in INFO we write the tooltip SELECT 'C'||TO_CHAR(C.CUSTOMER_ID) AS ID
, TO_CHAR(NULL) AS PID
, C.CUST_FIRST_NAME ||' '||
C.CUST_LAST_NAME AS TEXT
, NULL AS URL
, NULL AS DISPLAY_SEQ
, NULL AS CSS_CLASS
, TO_CHAR('Customer ID: '||C.CUSTOMER_ID)
AS INFO
, NULL AS CUSTOM
, COUNT(OI.ORDER_ITEM_ID) AS ORDERS_TOTAL
FROM DEMO_CUSTOMERS C
, DEMO_ORDERS O
, DEMO_ORDER_ITEMS OI
WHERE C.CUSTOMER_ID = O.CUSTOMER_ID
AND O.ORDER_ID = OI.ORDER_ID
GROUP BY C.CUSTOMER_ID
, C.CUST_FIRST_NAME
, C.CUST_LAST_NAME
2. The second UNION selects the number of products per customer and category. As Parent ID (PID) we have the corresponding IDs from the first select, these are the first child-nodes SELECT 'O'||C.CUSTOMER_ID||SUBSTR(PI.CATEGORY,1,1)
AS ID
, 'C'||TO_CHAR(C.CUSTOMER_ID) AS PID
, PI.CATEGORY AS TEXT
, NULL AS URL
, NULL AS DISPLAY_SEQ
, NULL AS CSS_CLASS
, C.CUST_FIRST_NAME ||' '||
C.CUST_LAST_NAME AS INFO
, NULL AS CUSTOM
, COUNT(OI.ORDER_ITEM_ID) AS ORDERS_TOTAL
FROM DEMO_CUSTOMERS C
, DEMO_ORDERS O
, DEMO_ORDER_ITEMS OI
, DEMO_PRODUCT_INFO PI
WHERE C.CUSTOMER_ID = O.CUSTOMER_ID
tryfoexnow.com
48 of 114
FOEX 2-day Developer Guide
Version 4.0.1
AND O.ORDER_ID = OI.ORDER_ID
AND OI.PRODUCT_ID = PI.PRODUCT_ID
GROUP BY C.CUSTOMER_ID
, C.CUST_FIRST_NAME ||' '||
C.CUST_LAST_NAME
, PI.CATEGORY
3. The third UNION selects all Products per Customer, Category and Productname. As Parent ID (PID) we have the IDs of the second Query (the parentnodes for these children). So this is the third level of our Tree Grid SELECT 'P'||C.CUSTOMER_ID||PI.PRODUCT_ID
AS ID
, 'O'||C.CUSTOMER_ID||SUBSTR(PI.CATEGORY,1,1)
AS PID
, PI.PRODUCT_NAME AS TEXT
, NULL AS URL
, NULL AS DISPLAY_SEQ
, NULL AS CSS_CLASS
, PI.CATEGORY AS INFO
, NULL AS CUSTOM
, COUNT(OI.ORDER_ITEM_ID) AS ORDERS_TOTAL
FROM DEMO_CUSTOMERS C
, DEMO_ORDERS O
, DEMO_ORDER_ITEMS OI
, DEMO_PRODUCT_INFO PI
WHERE C.CUSTOMER_ID = O.CUSTOMER_ID
AND O.ORDER_ID = OI.ORDER_ID
AND OI.PRODUCT_ID = PI.PRODUCT_ID
GROUP BY C.CUSTOMER_ID
, PI.PRODUCT_NAME
, PI.PRODUCT_ID
, PI.CATEGORY
4. Important is that all IDs are allover different (over all 3 UNIONs)
tryfoexnow.com
49 of 114
FOEX 2-day Developer Guide
Version 4.0.1
5. As you can have more than one aggregating function, add SUM(OI.UNIT_PRICE)
AS UNIT_PRICE_TOTAL
to all three queries in the Report-Definition. 6. Now you have a tree grid.
7. Change the properties of Report-Columns in the Orders per Customer Query region TEXT
Heading Cell Width ORDERS_TOTAL Column Alignment UNIT_PRICE_TOTAL Column Alignment Format Mask
tryfoexnow.com
Customer
200
right
right
FML999G999G999G999G990D00
50 of 114
FOEX 2-day Developer Guide
Version 4.0.1
8. The new design of your tree grid should now look like this
tryfoexnow.com
51 of 114
FOEX 2-day Developer Guide
Version 4.0.1
Export data from the tree grid 1. Edit the report attributes and set “CSV Export Enabled” to “Yes”
2. At the bottom, on the right ride, you’ll see a CSV Export button
tryfoexnow.com
52 of 114
FOEX 2-day Developer Guide
Version 4.0.1
3. Press CSV Export and you’ll get a list of all levels of the tree grid 4. To export just the information that is shown on the screen, go back to the FOEX Settings of the tree grid and set “Export Visible Rows Only” to “Yes
Add custom icons to tree entries 1. With FOEX 4.0.1 Font Awesome icons are used as the default icon set. This icon set is also included in APEX 5.
You can add any icon within the pack to any component which accepts icon CSS classes using the following format: fa [icon-name]. The formerly default FOEX icon pack FamFamFam is still available and can be used as before. In this example we will add the same CSS class to all products 'fa fa-shopping-cart'
tryfoexnow.com
53 of 114
FOEX 2-day Developer Guide
Version 4.0.1
2. Now run the page and you should see the new icon in use
tryfoexnow.com
54 of 114
FOEX 2-day Developer Guide
Version 4.0.1
Creating a FOEX Form (page 5) On this page we’ll create a simple FOEX Form, which will be included as a modal window in our application. The Items within the FOEX Container are formatted as useful for an Input Mask. The development of the FOEX Form follows the same steps as the ones on page 3. 1. Create a new FOEX page in your application with Page Id 5 and Name New Customer. Just create the Center Pane for this page with Only 1 Child Region (fit). 2. On the Centre Pane create a FOEX Sub Region and enter Region Title Parent Region Choose Table/View Primary Key Item Include Buttons
Customer
..Center Pane Form DEMO_CUSTOMERS P5_CUSTOMER_ID Create (uncheck Apply Changes and Delete)
Choose all columns to be included on the form
tryfoexnow.com
55 of 114
FOEX 2-day Developer Guide
Version 4.0.1
3. Select the FOEX-Form region Customers and change the following on the tab Region Attributes Allowed Operations Prevent Header Panel Collapsible
Create, Reset Yes No
! tryfoexnow.com
56 of 114
FOEX 2-day Developer Guide
Version 4.0.1
4. Give P5_CUST_POSTAL_CODE a lower Sequence than P5_CUST_CITY
5. Change P5_CUST_STATE type to FOEX Popup LOV [Plug-In] Select SQL Query as Type of the List of Values and insert the following SQL query SELECT INITCAP(STATE_NAME) AS D , ST AS R FROM DEMO_STATES ORDER BY 1
6. Change P5_CREDIT_LIMIT
Min Value to 0 and Max Value to 5000
Spinner Step Size to 10
Format Mask 999G999G999G999G990D00 and pick a Default Value of 1000
tryfoexnow.com
57 of 114
FOEX 2-day Developer Guide
Version 4.0.1
7. Change the layout
8. Change the properties for every page item residing in the second column Appearance - Template Custom Config
Hidden
"margin":"0 0 0 5" for space between the fields
9. For P5_CUST_EMAIL add additionally "vtype":"email" to Custom which will add a client side email validation
tryfoexnow.com
58 of 114
FOEX 2-day Developer Guide
Version 4.0.1
10. Change the field labels to something suitable for you 11. Change the Default Label Width to e.g. 150
12. Move the CREATE Button Alignment to Left
!
tryfoexnow.com
59 of 114
FOEX 2-day Developer Guide
Version 4.0.1
13. Your page should now look like this
tryfoexnow.com
60 of 114
FOEX 2-day Developer Guide
Version 4.0.1
Creating a Master-Detail-Detail relationship: tree Grids & a modal FOEX Form (page 6) In this tutorial we combine a layout with two grids placed beside each other with a third grid underneath them. For inserting new records, we’ll include a PL/SQL process. On the other hand we’ll add a button that opens a modal window defined on the same page.
Creating two FOEX grids within a layout 1. Create a new FOEX page in your application with Page Id 6 and Name Existing and New Order - MasterDetailDetail Grid + new Window. Just create the Center Pane for this page and change the layout to Default Show Region as Tabs (tabpanel). We will change the layout setting in a few steps time. 2. To create a FOEX layout, click on Create FOEX Region. In the Region Title enter Customer and Order, in the Parent Region choose ..Center Pane, then click the Layout icon.
tryfoexnow.com
61 of 114
FOEX 2-day Developer Guide
Version 4.0.1
3. Choose Horizontal as the layout type, since we want to have two regions beside each other. Click Create.
4. Select Attributes of the newly created FOEX Layout region Customer and Order. On the Property Pane, open FOEX Config and change Prevent Header to Yes and set Flex to 1.
5. Also at Property Pane, open FOEX Child Default Config and set Flex to 1.
tryfoexnow.com
62 of 114
FOEX 2-day Developer Guide
Version 4.0.1
6. Create an updatable grid named Customers using the default Row Editing Mode on Table DEMO_CUSTOMERS as on page 2, with two buttons Create and Delete. This Grid has as parent region the newly created Layout Region (Customer and Order)
SQL Query: SELECT , , , , , , , , , , , FROM tryfoexnow.com
CUSTOMER_ID
CUST_FIRST_NAME
CUST_LAST_NAME
CUST_STREET_ADDRESS1
CUST_STREET_ADDRESS2
CUST_CITY
CUST_STATE
CUST_POSTAL_CODE
PHONE_NUMBER1
PHONE_NUMBER2
CREDIT_LIMIT
CUST_EMAIL
DEMO_CUSTOMERS 63 of 114
FOEX 2-day Developer Guide
Version 4.0.1
We will make a slight adjustment to this grid so that the Editor is only invoked on double click. To do this select Attributes for the Customers grid, open FOEX Config and change Clicks to Edit to 2.
7. Create a second grid (this time Create, Update) with the parent region Customer and Order
Name the Grid Table/View Name Primary Key Item Include Buttons
tryfoexnow.com
Orders DEMO_ORDERS
ORDER_ID
Create (Tick off Apply Changes)
64 of 114
FOEX 2-day Developer Guide
Version 4.0.1
SQL – Query SELECT , , , , FROM ORDER
ORDER_ID CUSTOMER_ID
NVL(O.ORDER_TOTAL, 0) AS ORDER_TOTAL
ORDER_TIMESTAMP
USER_NAME
DEMO_ORDERS O
BY ORDER_ID DESC
8. In FOEX Config change Clicks to Edit to 2.
9. Format the columns ORDER_ID CUSTOMER_ID ORDER_TOTAL ORDER_TIMESTAMP
USER_NAME
tryfoexnow.com
Default Value DEMO_ORD_SEQ.NEXTVAL Type Hidden Field Column Alignment right Format Mask FML999G999G999G999G990D00 Type Date Picker Format Mask DD. Mon YYYY HH24:MI Default Value TO_CHAR(SYSDATE,'DD. Mon YYYY HH24:MI') Type Display Text (saves state) Default Value :APP_USER
65 of 114
FOEX 2-day Developer Guide
tryfoexnow.com
Version 4.0.1
66 of 114
FOEX 2-day Developer Guide
Version 4.0.1
10. Edit Button Settings of Button CREATE_1 in the Orders Region Label Horizontal Alignment
Add Order
Left
11. To bind the two grids we’ll create a dynamic action on the Customer region Name Event Selection Type Region
Master Detail (Customer -> Order)
FOEX Grid - Before Render [FOEX Grid]
Region
Customers
! Action Master Connection Column(s) Detail Connection Column(s) Selection Type Region tryfoexnow.com
FOEX Master Detail [Plug-In]
CUSTOMER_ID
CUSTOMER_ID
Region
……Orders 67 of 114
FOEX 2-day Developer Guide
!
Version 4.0.1
!
12. Run the page and when clicking on a customer you'll see the list of orders in the second grid.
13. We want the first row to be selected after refresh, therefore we’ll edit the Master Detail dynamic action and set Select Master Record after Load to Select First Record
! 14. Run the page and you will see data in the Orders grid that corresponds to the selected Customer row.
tryfoexnow.com
68 of 114
FOEX 2-day Developer Guide
Version 4.0.1
Adding a third read-only grid to the Center Pane 1. Add a new Grid to the Center Pane named Order Items using the SQL Query Allowed Operations Read Only Select Statement
SELECT OI.ORDER_ITEM_ID , OI.ORDER_ID , OI.PRODUCT_ID , OI.UNIT_PRICE , OI.QUANTITY FROM DEMO_ORDER_ITEMS OI
!
2. After creating the region edit the report attributes and set the Type setting for Columns ORDER_ITEM_ID and ORDER_ID to Hidden Field as we did for columns in the Orders Grid 3. Next, change the PRODUCT_ID column Type setting to Plain Text (based on List of Values) and use the following SQL - Query SELECT PRODUCT_NAME AS D , PRODUCT_ID AS R FROM DEMO_PRODUCT_INFO
tryfoexnow.com
69 of 114
FOEX 2-day Developer Guide
Version 4.0.1
4. Run the page and you'll see the Layout-Region on one tab and the grid on a second one.
5. We want to see all three regions on the same page, so select Attributes of the Center Pane region and change the Layout to Vertically Stack Regions (vbox). This could also have been done, when creating the region.
Our FOEX-Layout-Region Customer and Order has the Layout Horizontally Stack Regions (hbox), therefore the first two grids are beside each other.
tryfoexnow.com
70 of 114
FOEX 2-day Developer Guide
Version 4.0.1
6. We want to see all ordered items for a customer, when we select a customer. Therefore, we add some lines to the Order Items SQL statement.
SQL Query
SELECT , , , , , FROM INNER
OI.ORDER_ITEM_ID OI.ORDER_ID OI.PRODUCT_ID OI.UNIT_PRICE OI.QUANTITY O.CUSTOMER_ID DEMO_ORDER_ITEMS OI JOIN DEMO_ORDERS O ON O.ORDER_ID = OI.ORDER_ID
7. Change the Properties of the new Column CUSTOMER_ID Type
Hidden Field
8. To connect Orders and Order Items Region to the Customers Region with only one Dynamic Action, we must set a Static ID for Orders and Order Items.
Best practice of naming Static ID’s for regions is to use a Prefix like PR<pagenumber>_
to make them unique across the application Region Static ID
Orders PR6_ORDERS
! tryfoexnow.com
71 of 114
FOEX 2-day Developer Guide
Region Static ID
Version 4.0.1
Order_Items PR6_ORDER_ITEMS
!
9. Change the Master - Detail (Customer - Order) dynamic action Selection Type jQuery Selector
jQuery Selector #PR6_ORDERS,#PR6_ORDER_ITEMS
!
10. To bind the Orders and Order Items grid together add a Master-Detail dynamic action on the Orders grid. Name Event
tryfoexnow.com
Master Detail (Order -> Order Items) FOEX Grid - Before Render [FOEX Grid]
72 of 114
FOEX 2-day Developer Guide
Action Master Connection Column(s) Detail Connection Column(s)
Version 4.0.1
FOEX Master Detail [Plug-In] ORDER_ID ORDER_ID
!
!
!
tryfoexnow.com
73 of 114
FOEX 2-day Developer Guide
Version 4.0.1
11. When you run the page the first customer on the grid is selected
12. When you click on a customer the other two grids are refreshed and show the details that belong for the chosen customer. Selecting an order filters the Order Items; refresh it and display only Order Items for this order.
tryfoexnow.com
74 of 114
FOEX 2-day Developer Guide
Version 4.0.1
Creating a FOEX Form that opens as a modal window To add new Order Items, we create a FOEX Form on the same page and to open this Form we create a button in the third Grid (Order Items). 1. Create a new FOEX region (with no Parent region). Choose FOEX Window from the list of plugins and name it Add Order Item Window
! 2. Select Attributes from the Add Order Item Window region, then choose Add Order Items as Window Title and leave all other items with their predefined settings.
tryfoexnow.com
75 of 114
FOEX 2-day Developer Guide
Version 4.0.1
3. Create a FOEX form by using the Create FOEX Region button Region Title Parent Region Region Type Table Primary Key Item Include Buttons Include Columns
Add Order Item Add Order Item Window FOEX Form DEMO_ORDER_ITEMS P6_ORDER_ITEM_ID Apply Changes, Create, Delete ORDER_ITEM_ID ORDER_ID PRODUCT_ID UNIT_PRICE QUANTITY
After creating it, edit the FOEX settings of Add Order Item Form region attributes and set the Prevent Header attribute to Yes
tryfoexnow.com
76 of 114
FOEX 2-day Developer Guide
Version 4.0.1
4. Add a region button to the Order Items grid region. Name it ADD_ORDER_ITEM Action Horizontal Alignment Button Icon
Defined by Dynamic Action left fa fa-plus
5. Create a dynamic action for this button Name Event Action
Open Window on Add Order Item Button Click FOEX Button - Normal Button Click APEX - Show
!
! Selection Type Region Fire On Page Load
Region Add Order Item Window No
6. Add a second True Action to reset the window every time it is opened/closed Action Exclude Items from Reset Selection Type Region
tryfoexnow.com
FOEX Form Reset [Plug-In] P6_ORDER_ID Region ..Add Order Item
77 of 114
FOEX 2-day Developer Guide
Version 4.0.1
7. Run the page and click on the [+ Add Order Item] button.
A modal window will open with all the displayed fields empty.
8. Change the P6_ORDER_ID and P6_UNIT_PRICE page items to hidden
! tryfoexnow.com
78 of 114
FOEX 2-day Developer Guide
Version 4.0.1
Creating a FOEX grid combo 1. Add three new Page Items to this FOEX Field Container (all FOEX Display Fields) between Item P6_PRODUCT_ID and P6_UNIT_PRICE P6_CATEGORY P6_PRODUCT_DESCRIPTION P6_LIST_PRICE
Label: Category Label: Description Label: List Price
2. Change the Default Label Width to 150 at the region attributes of the Add Order Item - Field Container 3. Change P6_PRODUCT_ID Display As FOEX Grid Combo [Plug-In] Label Product Sort Columns CATEGORY,PRODUCT_NAME Hide Columns PRODUCT_ID Grid Custom Config (component level) Item Display Value - Which Grid Column? PRODUCT_NAME Item Return Value - Which Grid Column? PRODUCT_ID Exclude Columns from Grid Filter PRODUCT_ID Set Other Items P6_PRODUCT_DESCRIPTION:PRODUCT_DESCRIPTION,P6_CATEG ORY:CATEGORY,P6_LIST_PRICE:LIST_PRICE Match Field Width Yes List of Values - SQL Query SELECT , , , , FROM WHERE
PRODUCT_ID
PRODUCT_NAME AS "Product Name"
PRODUCT_DESCRIPTION AS "Product Description"
CATEGORY AS "Category"
LIST_PRICE AS "List Price"
DEMO_PRODUCT_INFO
( PRODUCT_AVAIL = 'Y'
AND :P6_ORDER_ITEM_ID IS NULL
)
OR :P6_ORDER_ITEM_ID IS NOT NULL
tryfoexnow.com
79 of 114
FOEX 2-day Developer Guide
Version 4.0.1
4. Run page 6 and open the window
When you choose a record the display items are filled with the corresponding values (as defined within Set Other Items within the grid combo plugin attributes in the previous steps).
If you try to create an Order Item, you will get an error because of P6_ORDER_ID has a value of NULL.
To avoid this, we must bind the form to the grid as described in the next step.
tryfoexnow.com
80 of 114
FOEX 2-day Developer Guide
Version 4.0.1
Connect the form to the grid 1. To bind the form to the grid we’ll add a Master Detail Dynamic Action similar to the one on page 3, but this time we want to show the FOEX Window when a record is selected. Create a Dynamic Action for the region Order Items Name Event Action Master Connection Column(s) Detail Connection Column(s) Options Refresh Master after Detail Save Region
!
Master Detail (Order Items Grid -> Order Items Form) FOEX Grid - Before Render [FOEX Grid] FOEX Master Detail [Plug-In] ORDER_ITEM_ID ORDER_ITEM_ID Auto Focus Detail on Master Selection Refresh Master Row ..Add Order Item
!
tryfoexnow.com
81 of 114
FOEX 2-day Developer Guide
Version 4.0.1
2. Run page 6 and click on an order item. The FOEX Window should appear and all fields show their corresponding values. 3. Set the ORDER_ID for new Order Items. Change properties for page item P6_Order_ID
Value Protected
NO
4. Change properties for the master detail relation Master Detail (Order –> Order Items)
Set Page Items
P6_ORDER_ID
5. To get the value of P6_ORDER_ID in session state, add it to Page Items to Submit of Order Items – Query Definition
! 6. When choosing an order, you can insert/update/delete an order item 7. Finally, we have to define the Primary Key on the Order Items grid. This is needed for the Refresh Master on Detail Save property.
Change Order Items Attributes
Primary Key Column
ORDER_ITEM_ID
tryfoexnow.com
82 of 114
FOEX 2-day Developer Guide
Version 4.0.1
Making the modal window non-modal You can also have a non Modal Window, which reacts to clicks in the background. This will allow you to easily navigate through records in the form by selecting other rows in the grid 1. Select the region attributes for the Add Order Item Window and change Modal to NO
! 2. Run the page and choose an order with more than one order items. Open the modal window by clicking on one of the order items. You see that the background isn't disabled and when you click on another order item the values in the modal window are synchronized. Click Reset and the fields will be emptied, so you can insert a new order item. We need to avoid the possibility of the ORDER_ID not being set i.e. the Orders grid does not have a row selection (therefore P6_ORDER_ID_DRILL is not set and our PLSQL Expression default on P6_ORDER_ID evaluates to NULL).
tryfoexnow.com
83 of 114
FOEX 2-day Developer Guide
Version 4.0.1
You can trigger this issue by editing an order item after page load, click the Reset button and try adding a record.
To avoid this, we’ll create a validation on the form.
Name Type Item Error Message Associated Item Type Value
tryfoexnow.com
P6_ORDER_ID is not null Item is NOT NULL P6_ORDER_ID Please select an order before adding or
saving an order item P6_ORDER_ID Request is contained in Value CREATE_2 SAVE (CREATE_2 or Name of Forms Create
Button!)
84 of 114
FOEX 2-day Developer Guide
Version 4.0.1
tryfoexnow.com
85 of 114
FOEX 2-day Developer Guide
Version 4.0.1
We can run the page now and try to trigger the error: 1) Select a row from the order items grid after the page loads 2) Click the Reset button in the form 3) Add a new record 4) Click the Create button
The validation error message should appear Last step in validating the selected Order-ID is, to reset the P6_ORDER_ID when a different customer is selected.
To do this, add a Dynamic Action on the Customers Grid: Name Event Selection Type Region Selection Type
On Customers Section Change reset P6_ORDER_ID FOEX Grid - After Row Select Region Region …Customers (FOEX Grid [Plug-In])
True Action: Action Affected Elements: Selection Type Region
FOEX Form - Reset Region ..Add Order Item (FOEX Form [Plug-In])
Be aware that database constraints can prevent you from inserting a new order item (you will get the error message displayed below). To solve this issue, you could create an SQL NOT EXISTS validation on the P6_PRODUCT_ID page item. Feel free to do it now!
tryfoexnow.com
86 of 114
FOEX 2-day Developer Guide
Version 4.0.1
Creating a Master-Detail-Detail relationship: a master Form and two Grids as detail (page 7) In this tutorial we’ll create a FOEX form for managing customers and two depending FOEX grids, one for managing orders of the selected customer and a second for managing order items for the selected order. We’ll also create one button that saves changes on all three tables.
Creating a FOEX form with a button for Insert and Update 1. Create a new FOEX page in your application with Page Id 7 and Name Master Detail Detail (Form Grid Grid). Just create the Center Pane for this page and change the layout to Vertically Stack Regions (vbox)
! tryfoexnow.com
87 of 114
FOEX 2-day Developer Guide
Version 4.0.1
2. Create a FOEX Layout with Center Pane as the parent region. Do this by using the context menu of the Center Pane Name Region Type
Customer and Order Horizontal
!
3. Edit the attributes of the Customer and Order region Prevent Header Flex
Yes 1
4. Create a FOEX region Region Title Parent Region Choose Region Type Table/View Primary Key Item Include Buttons Include Columns
tryfoexnow.com
Customers Form …Customer and Order Form DEMO_CUSTOMERS P7_CUSTOMER_ID Create, Apply Changes and Delete CUSTOMER_ID CUST_FIRST_NAME CUST_LAST_NAME CUST_STREET_ADDRESS1 CUST_STREET_ADDRESS2 CUST_CITY CUST_STATE CUST_POSTAL_CODE PHONE_NUMBER1 PHONE_NUMBER2 CREDIT_LIMIT CUST_EMAIL 88 of 114
FOEX 2-day Developer Guide
Version 4.0.1
PHONE_NUMBER1 PHONE_NUMBER2 URL CREDIT_LIMIT TAGS
!
5. Edit the region attributes for the Customers Form and add CUST_LAST_NAME to the Order by attribute (in FOEX Settings)
Note: By adding the order by clause we will include form pagination controls e.g. Next/ Previous and First/Last
6. Change the Reset, Create, Save and Delete button alignment to left
tryfoexnow.com
89 of 114
FOEX 2-day Developer Guide
Version 4.0.1
Creating a Grid dependent on the Form 1. Create a FOEX Grid Region Title Parent Region Choose Region Type Allowed Operations Table/View Buttons Primary Key ITEM SQL Query
List of Orders …Customer and Order FOEX Grid Create, Update, Delete DEMO_ORDERS Apply Changes, Create ORDER_ID SELECT TO_CHAR(ORDER_ID) AS ORDER_ID
, TO_CHAR(CUSTOMER_ID) AS CUSTOMER_ID
, NVL(ORDER_TOTAL, 0) AS ORDER_TOTAL
, ORDER_TIMESTAMP
, USER_NAME
FROM DEMO_ORDERS
! Note: The TO_CHAR conversion in the SQL statement is necessary when using long numbers, because of javascript limitations:
Oracle NUMBER 38 digits (max) Javascript Number 15 digits (max)
tryfoexnow.com
90 of 114
FOEX 2-day Developer Guide
Version 4.0.1
2. Change the region attributes of List of Orders Static ID Edit Mode
PR7_ORDERS Cell Editing
3. Change column preferences of List of Orders - Query Definition ORDER_TOTAL
ORDER_TIMESTAMP
Type Format Mask Type Format Mask
Plain Text FML999G999G999G999G990D00 Date Picker DD. Mon YYYY HH24:MI
4. Modify the CREATE_1 button Label Horizontal Alignment
Add Order Left
5. Modify the SAVE_1 button Horizontal Alignment
Left
6. Add a dynamic action to connect Customers Form and List of Orders Name Event Selection Type True Action Master Connection Column(s) Detail Connection Column(s) Selection Type Region
tryfoexnow.com
Master Detail (Customers Form -> List of Orders) FOEX Form - Before Render Region FOEX Master Detail [Plug-In] CUSTOMER_ID CUSTOMER_ID Region ……List of Orders
91 of 114
FOEX 2-day Developer Guide
Version 4.0.1
Change settings for inserting records to the grid 1. Go to List of Orders - Query Definition and change preferences for CUSTOMER_ID Type
Hidden Field
2. Select column definition of ORDER_ID inside of List of Orders – Query Definition and change the FOEX settings Default (PL/SQL Expression)
TO_NUMBER(SYS_GUID(), 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx')
!
3. Select column definition of USER_NAME inside of List of Orders – Query Definition and change the FOEX settings Type Default (PL/SQL Expression)
Display as Text (saves state) :APP_USER
4. Run the page. As you click through the customers list, the order grid gets refreshed
tryfoexnow.com
92 of 114
FOEX 2-day Developer Guide
Version 4.0.1
Creating a second grid as a detail to the first grid 1. Create a FOEX Grid using the Create FOEX Region button Region Title Parent Region Choose Region Type Allowed Operations Table/View Nam Include Buttons SQL Query
Order Items ..Center Pane FOEX Grid Create, Update, Delete DEMO_ORDER_ITEMS Apply Changes, Create, Delete SELECT TO_CHAR(ORDER_ITEM_ID) AS ORDER_ITEM_ID
, TO_CHAR(ORDER_ID) AS ORDER_ID
, TO_CHAR(PRODUCT_ID) AS PRODUCT_ID
, UNIT_PRICE
, QUANTITY
FROM DEMO_ORDER_ITEMS
!
2. Change preferences of the FOEX grid Order Items Static ID Edit Mode
tryfoexnow.com
PR7_ORDER_ITEMS Cell Editing
93 of 114
FOEX 2-day Developer Guide
Version 4.0.1
3. Change preferences of ORDER_ID from the region Order Items – Query Definition Display As
Hidden Field
4. Change properties of PRODUCT_ID Type Heading LOV Type SQL Query
Select List Product SQL Query SELECT PRODUCT_NAME AS D
, PRODUCT_ID AS R
FROM DEMO_PRODUCT_INFO
ORDER BY 1
5. Change the UNIT_PRICE column to Plain Text 6. Change button preferences SAVE_2
Horizontal Alignment
Left
CREATE_2
Label Horizontal Alignment
Add Order Item
Left
DELETE_1
Horizontal Alignment
Left
7. Create a second master detail dynamic action to connect Order Items Grid to List of Orders Grid Name
Master Detail (List of Orders ->
Order Items) Event FOEX Grid - Before Render [FOEX Grid] Selection Type Region Region ….List of Orders Master Connection Column(s) ORDER_ID Detail Connection Column(s) ORDER_ID Option check Detail Automatic Saving Refresh Master after Detail Save Refresh Master Row Selection Type Region Region ….Order Items
tryfoexnow.com
94 of 114
FOEX 2-day Developer Guide
Version 4.0.1
Adding new data to three tables in two steps Adding data into the three tables used by page 7 can be done with two saves only. The Master-Detail connection between Orders and Order Items manages the saving of both Grids. Make sure that you run page 7 - Customer and Order. Next, create a new customer and save it Note: If a customer is shown, due to earlier tests, press Reset first and then insert the new customer.
Step 1: Add a record to the List of Orders, select the newly created record and add some order items. Step 2: Click the Save button on the List of Orders grid and you’ll see that the order and all order item records are inserted.
tryfoexnow.com
95 of 114
FOEX 2-day Developer Guide
Version 4.0.1
Creating a Tree linking to all pages built previously (page 1) In this tutorial we will create a new page with 3 panes. In the center we will create a Welcome region, in the top/north pane a Toolbar and in the Left/West pane a Navigation-Tree. The leaves of the Tree open the previously created pages from this application in the Center Pane using a new Tab for each of them. In the end, we’ll include a button in the toolbar to open a modal window with a previously created page.
Adding a HTML region to the Center Pane 1. Create a new FOEX page in your application with Page Id 1, Name Start Page and 3 Panes (North, West, Center). Choose Only 1 Child Region (fit)
! tryfoexnow.com
96 of 114
FOEX 2-day Developer Guide
Version 4.0.1
2. Select Page Definition and for further styling add the following to the CSS Inline section div.welcomeUser { padding:20px; font-size:18px; font-weight:bold; color:#0082C0; } div.toolbarUser { font-size: 16px; font-weight:bold; color:#0082C0; }
!
3. Add a HTML region as a sub-region to the Center Pane and name it Welcome. Change the region’s template to FOEX Region and enter as HTML Text Region Source Welcome &APP_USER.
! tryfoexnow.com
97 of 114
FOEX 2-day Developer Guide
Version 4.0.1
4. Change the attributes for the Welcome region Output As
HTML
!
Creating a FOEX Tree linking to the other pages 1. Change West Pane’s title to Navigation 2. Change region width to 350, since our tree links will have long names
tryfoexnow.com
98 of 114
FOEX 2-day Developer Guide
Version 4.0.1
3. Open Shared Components -> Navigation -> Lists 4. Create a new list from scratch
5. Add Name Type
FOEX2Days Page Index Static
!
tryfoexnow.com
99 of 114
FOEX 2-day Developer Guide
Version 4.0.1
6. Add List Entry Label Target Page ID or custom URL 2Day FOEX Developer’s Pages Customers - Updatable Grid 2 Products - Master/Detail 3 Products per Customer - Tree Grid 4 Existing and New Order - Master/Detail/Detail Grid 6
7. Select Do not create list region(s) and click Create List
tryfoexnow.com
100 of 114
FOEX 2-day Developer Guide
Version 4.0.1
8. Perform the following changes on the 2 Days FOEX Developer's Pages list entry, as this is our tree root, which has no link List used with FOEX Plugin Tooltip Icon CSS Class Target
FOEX Tree, Button or Wizard Tooltip for Main Node Not in List and fa fa-desktop - No Target -
!
9. For all other list entries do the following: Parent List Entry List used with FOEX Plugin Tooltip Clear Cache
2 Days FOEX Developer’s Pages FOEX Tree, Button or Wizard Tooltip for page X the target page (e.g. 2)
! tryfoexnow.com
101 of 114
FOEX 2-day Developer Guide
Version 4.0.1
10. Go back to page 1 and add a FOEX Tree using the Create FOEX Region button Region Title Parent Region Choose Region Type Allowed Operations SQL Query SELECT , , , , , , , FROM WHERE AND AND
tryfoexnow.com
Tree Plugin Navigation Tree Read Only
TO_CHAR(LIST_ENTRY_ID) ID
TO_CHAR(LIST_ENTRY_PARENT_ID) PID
ENTRY_TEXT TEXT
ENTRY_TARGET URL
DISPLAY_SEQUENCE DISPLAY_SEQ
ENTRY_ATTRIBUTE_03 CSS_CLASS
ENTRY_ATTRIBUTE_02 INFO
ENTRY_ATTRIBUTE_04 CUSTOM
APEX_APPLICATION_LIST_ENTRIES
APPLICATION_ID = :APP_ID
LIST_NAME = 'FOEX2Days Page Index'
0 = CASE
WHEN CONDITION_TYPE_CODE IS NOT NULL
OR AUTHORIZATION_SCHEME_ID IS NOT NULL
THEN
FX_P_UTILS.AUTH_CONDITION_CHECK
( CONDITION_TYPE_CODE
, CONDITION_EXPRESSION1
, CONDITION_EXPRESSION2
, AUTHORIZATION_SCHEME_ID
)
ELSE
0
END 102 of 114
FOEX 2-day Developer Guide
Version 4.0.1
!
11. Edit the Tree Plugin Region Attributes and change the following Get Child Nodes Via A JAX? Stop Default Node Click Event? Prevent Header
to No to Yes to Yes
! tryfoexnow.com
103 of 114
FOEX 2-day Developer Guide
Version 4.0.1
12. Add a dynamic action to the FOEX Tree region Tree Plugin Name Event Action
Tree FOEX FOEX FOEX FOEX Yes
Yes
Last
No
No
Use URL from Triggering Element? Reload if Already Open? Insert at Position Share/Reuse Tab/Panel/Slice? Open a new Tab/Panel/Slice every time? Affected Elements Selection Type Region
!
tryfoexnow.com
Leaf Click
Tree
Tree – Leaf Click/ Select
Content Loader
Content Loader [Plug-in]
Region
..Center Pane
!
104 of 114
FOEX 2-day Developer Guide
Version 4.0.1
13. Change the layout of the Center Pane to Show Regions as Tabs (tabpanel)
! 14. After expanding the tree, the page looks like this
15. If you just see the loading sign and no page opens when you click on the leaves of the tree, edit Application Properties and make the following change in the menu Security - Section Browser Security Embed in Frames
tryfoexnow.com
Allow from same origin
105 of 114
FOEX 2-day Developer Guide
Version 4.0.1
16. Click on the leaves of the tree and the previously created pages will be opened in the Center Pane. For each page a new tab is opened.
Adding a toolbar to the North Pane 1. In the North Pane create a FOEX Toolbar as a sub-region, with the Title Toolbar
tryfoexnow.com
106 of 114
FOEX 2-day Developer Guide
Version 4.0.1
2. Add a FOEX Display Field to the toolbar region for our logo on the start page Name P1_LOGO
Type FOEX Display Field [Plug-In]
Appearance - Template Hidden
Width Static Value
12 ![](#IMAGE_PREFIX#foex/images/
FOEX.png)
!
Note: A field width of 12 = 120 pixels wide. By default, if no width is defined in the additional config attribute within the item settings then we, FOEX, will use this setting multiplied by 10. The same applies for height on text areas.
tryfoexnow.com
107 of 114
FOEX 2-day Developer Guide
Version 4.0.1
3. Add a Logout button to the toolbar Name Label Sequence Action URL Target Button Icon
tryfoexnow.com
LOGOUT
Logout
30 (items and buttons in the toolbar are ordered
by display sequence) Redirect to URL &LOGOUT_URL. fa fa-unlock
108 of 114
FOEX 2-day Developer Guide
Version 4.0.1
Finally, we’ll add a button for page 5 - to open the page as a modal window. 4. Create another region button within the toolbar Name Label Sequence Action Button Icon
tryfoexnow.com
NEW_CUSTOMER New Customer
20 Defined by Dynamic Action fa fa-plus
109 of 114
FOEX 2-day Developer Guide
Version 4.0.1
5. Create a dynamic action for this button Name Event
Open new Window for New Customer FOEX Button - Normal Click
!
Action Use URL from Triggering
Element? Open URL New Content Title Modal Maximizable Maximized in Render Closeable Close Mode Window ID Width Height
tryfoexnow.com
FOEX Window Open [Plug-in]
No
f?p=&APP_ID.:5:&APP_SESSION.::&DEBUG.:5
New Customer
Yes
Yes
No
Yes
Hide
WINDOW_FOR_CUSTOMER
600
400
110 of 114
FOEX 2-day Developer Guide
Version 4.0.1
6. We want to close the modal window after a new customer was inserted, therefore we add a dynamic action to page 5 Name Event Selection Type Region
On CRUD Success Close Window FOEX Form - CRUD Success Region ….Customers
! tryfoexnow.com
111 of 114
FOEX 2-day Developer Guide
Action The Window in on this page? Selection Type jQuery Selector
Version 4.0.1
FOEX Window Close [Plug-In] No jQuery Selector #WINDOW_FOR_CUSTOMER
!
7. Run page 1 and after successfully adding a new customer via the newly created button, the modal window closes.
tryfoexnow.com
112 of 114
FOEX 2-day Developer Guide
Version 4.0.1
Changing the FOEX Application Settings If you would like to change the default Ext JS theme, the icon theme, enable state saving etc. you can do this within the FOEX Application Settings plugin. Navigate to: Shared Components -> Component Settings -> FOEX Application Settings
Ok, that’s it! You’ve just covered the basic concepts of creating an application using only FOEX plugins. There’s more capability for you to discover within our development tool. We hope that this tutorial was a good incentive for you to check our documentation and discover the great functionality of the FOEX Plugin Framework. Don’t forget to use our support forum for any technical questions.
tryfoexnow.com
113 of 114
FOEX 2-day Developer Guide
tryfoexnow.com
Version 4.0.1
114 of 114