Forms Record Groups:-

It is a collection of data.
It is source for LOV.
It will hold data physically.

Record Groups can be created in three types.
1) Based on select statement.
2) Based on static values.
3) Created through code dynamically.

Advantage
Used to display more than one column of the table on to screen at once.

LOV ( List of values )
Used to display the content of the record group at runtime.
It will not hold any data.
It is a GUI component used to represent data in the record group.

Example
Create table EMPEIGHT same as standard EMP table.
Create form based on EMPEIGHT table by using data block and layout wizard.
Save the form with the name EMP_RG.fmb

In Object Navigator, select Record Groups Click on create button  Data in the Record Group Select based on query radio button.
Enter the following query:
select * from dept; Ok
Name the Radio Groups as RG1

Select LOV , Click on create button Use LOV wizard Existing Record Group ( RG1 ) Modify existing Record Group Next Next Move all the columns from Radio Group columns to LOV columns   select return value of deptno column  , click on lookup return item Select EMPEIGHT.DEPTNO Next Title - Dept Information Next Next Move EMPEIGHT.DEPTNO to Assigned Items Finish.

In the Object Navigator, we can see that LOV is created.

As we have created LOV using wizard, LOV is assigned to DEPTNO item.

In the property palette of DEPTNO item , Under List of Values
We can see LOV41 ( our LOV ) is assigned.

Save the form.
Compile the form.
Run the form.

Keep the cursor on item DEPTNO, we can see that List of values ( hint message ) is displayed.

Keep the cursor on Item Deptno and press Ctrl+L to display LOV.

Select value from LOV, deptno is returned.

Displaying LOV through code
We can display LOV automatically using code , instead of pressing Ctrl + L
Create trigger  WHEN-NEW-ITEM-INSTANCE  on DEPTNO item, provide the following code and compile.

declare
                a boolean;
begin
                a := show_lov ( 'LOV41');
end;

Save the form.
Compile the form.
Run the form.

Now, the moment you keep the cursor in DEPTNO item, LOV is displayed.

Enter the following values in the form.

Empno - 11
Deptno - 50 
Save.

We get a message " Transaction complete: 1 records applied and saved."

Note: 50 is not value present in the LOV.
So, by default you can enter any value.

We can restrict the end user to enter a value present in the LOV.
By using the property validate from list.

In the property palette of the item DEPTNO
List of values
Validate from List -- Yes

Save the form.
Compile the from.
Run the from.

 

Enter the following values in the form.

Empno - 2222
Deptno - 50 
Save.

We get a message  "Invalid value for the field DEPTNO"

Records Groups based on static values

Open an existing form EMP_RG.fmb
Create new Record Group Select static values radio button
Column Name
JOB

Column Values
ANALYST
CLERK
MANAGER
PRESIDENT
SALESMAN

Click on OK Button.

Name the Record Group as RG2

Creating LOV Manually
In the Object Navigator Create new LOV Select Build a new LOV manually OK
Name the LOV as LOV2

In the property palette of LOV2, provide the following details
Functional
Title -- JOB
Record Group  -- RG2
Column mapping properties
Column Names
JOB
Return Item
EMPEIGHT.JOB
Display width
120
Column Title
JOB

Now, we need to attach LOV to the text item.
Under Data Block EMPEIGHT Select JOB In the property palette
List of Values
List of Values --  LOV2

Save the form.
Compile the form.
Run the form.

Keep the cursor on JOB and press Ctrl+L, we can see the LOV  is displayed.

Creating Record Group at runtime using code
Record Group can be created through code and can be assigned to LOV at runtime.
Advantage
Memory will be saved.
Note
LOV cannot be created at runtime.

Built-in used to create Record Groups at run time.

Built-in	Description
create_group_from_query	Used to create Record Group based on a select statement dynamically. It returns ID of the Record Group
populate_group	Executes the query associated with the given record group and returns a number indicating success or failure of the query. Upon a successful query, POPULATE_GROUP returns a 0 (zero). An unsuccessful query generates an ORACLE error number that corresponds to the particular SELECT statement failure.
find_group	Searches the record group and returns a record group ID when it find. You must define an appropriately typed variable to accept the return value. Define the variable with a type of RecordGroup.
delete_group	Used to remove the existing Record Group

 

Built-in for LOV

Built-in	Description
Set_lov_property	Used to change the properties of LOV at runtime.
	Syntax: set_lov_property ( lov_name, property, value )
Id_null	It is a operator. Checks the ID number is null value or not.
	It if is null, it  returns TRUE

Example
Open existing form EMP_RG.fmb
In the layout editor, create new button. In the property palette
Name -- B1
Label - Click Here

Create WHEN-BUTTON-PRESSED trigger and provide the following code and compile.

declare
                rg_name varchar2(20) := 'sal_range';
                rg_id RecordGroup;
                errcode                number;
                a boolean;
begin
                rg_id := find_group ( rg_name);
                if NOT id_null ( rg_id) THEN
                                delete_group ( rg_id);
                end if;
               
                rg_id := create_group_from_query ( rg_name , 'Select job, min(sal) lowpay , max(sal) highpay
                             from emp group by job' );

                errcode := populate_group ( rg_id);
               
                set_lov_property ( 'rlov' , group_name, rg_name);
                a := show_lov( 'rlov');
                end;

Now, In the above code , look at first parameter of set_lov_property  ie  rlov.
rlov is the name of the lov.

So, now we need to create LOV with the name RLOV with null values at the design time.
It holds the run runtime Record Group content.
LOV must have same structure of runtime Record Group.

 

In the Object Navigator,  Create new LOV  using LOV wizard New Record group based  on the query SQL Query statement -- select job, min(sal) lowpay, max(sal) highpay from emp where job is null group by job  Move all columns from Record Group columns to LOV Columns ForJOB column provide return value -- Click on Lookup return item Select EMPEIGHT.JOB Title - JOB Next Next Move Return Item ( EMPEIGHT.JOB ) to Assigned Item Next Finish.

Name the LOV as RLOV.

Save the form.
Compile the form.
Run the form.

Click on the button, the code in the trigger WHEN-BUTTON-PRESSED  is executed, at runtime Record Group is created and the LOV is displayed.