What you really seem to need is 2 tabular forms on one page, which unfortunately isn't possible (yet?). How to solve this then? Unless you really absolutely need to have this editing functionality on one page: i wouldn't. If you do (and i mean absolutely must), then there is going to be fiddling involved with javascript.
My advice: make 2 reports, provide an edit link. This is standard functionality too. Sure, no multiple edits at once, but this'll be easiest to maintain afterwards. Anything custom will have you ponder again afterwards, so it's worth considering.
If you do really need it, i think this is the most comfortable way to go about it: NO fiddling in the sql, except maybe a column more. No screwing around with unions with duals and ordering by some "pseudo" columns (because you need some way to make sure the dual column(s) are between certain values...).
Example with the EMP table
Region source:
select
"ROWID",
"EMPNO",
"ENAME",
"JOB",
"DEPTNO"
from "EMP"
where "DEPTNO" IN (10, 20)
order by "DEPTNO"
DEPTNO is a hidden item. The order by is very important, since further processing depends on a certain ordering of the rows. Turn off column header sorting (checkbox for sort for columns on the report attributes).
Javascript:
function bind_for_changes(){
//since emp is sorted by deptno, i can loop over the elements in this way
//i also have deptno as a hidden item in the report, so that i can check its value
//since this hidden item is the 4th editable item, its id will be f04_####
//be sure to check the source output if you want to be sure.
//for each input item with name f04 (deptno), do:
//check if its value differs from our stored value.
// if it does, but our var is still -1, then it is only the first record. Do nothing.
// if it does and the var is not -1, then there is a difference between deptnos.
// for example, 10 -> 20.
// this change will be detected on the first row of deptno=20. So the splitter row
// needs to be before this one.
// The input item sits in a TD, the TD in a TR. We need to add a TR element BEFORE
// the current one, so we get the TR and add a new one before it, with a TD in it.
// note the colspan=3, which is the amount of displayed columns. If not sure, check the DOM.
// Or, provide 3 TDs
var deptno = -1;
$("input[name='f04']").each(function(){
if(deptno != $(this).val()){
if(deptno == -1){
deptno = $(this).val(); //first time, first record
//dont change anything
} else {
//a change between deptno's. Add a row after the current one
deptno = $(this).val();
var tr = $(this).parent().parent();
var newtr = $("<tr class='highlight-row'><td colspan=3 class='data'>In between rows!</td></tr>");
tr.before(newtr);
};
};
});
};
Do note, like i state in the js comment: you need to be aware of what the page will generate/be able to read the source/know DOM. More or less editable columns can and will screw your code up!
For example, on my page when you view source, what matters is the structure of the row in the report:
<tr class="highlight-row">
<td headers="EMPNO" class="data">7782</td>
<td headers="ENAME" class="data">
<label for="f02_0003" class="hideMeButHearMe">Ename</label>
<input type="text" name="f02" size="12" maxlength="2000" value="CLARK" id="f02_0003" />
</td>
<td headers="JOB" class="data">
<label for="f03_0003" class="hideMeButHearMe">Job</label>
<input type="text" name="f03" size="9" maxlength="2000" value="MANAGER" id="f03_0003" />
<input type="hidden" name="f01" value="" id="f01_0003" />
<input type="hidden" name="f04" value="10" id="f04_0003" />
<input type="hidden" id="fcs_0003" name="fcs" value="3C09CABCBA62BE1A064146D162012CEF">
<input type="hidden" id="frowid_0003" name="frowid" value="AAuDjIABFAAAACTAAC" />
<input type="hidden" id="fcud_0003" name="fcud" value="U" />
</td>
</tr>
Take note of the IDs and names of the inputs, and also where they're placed.
Also, since apex 4.1 (i believe), there is a mapping after the table element
<input type="hidden" name="fmap" value="CHECK$01" id="fmap_001" />
<input type="hidden" name="fhdr" value="Select Row" id="fhdr_001" />
<input type="hidden" name="fmap" value="ENAME" id="fmap_002" />
<input type="hidden" name="fhdr" value="Ename" id="fhdr_002" />
<input type="hidden" name="fmap" value="JOB" id="fmap_003" />
<input type="hidden" name="fhdr" value="Job" id="fhdr_003" />
<input type="hidden" name="fmap" value="DEPTNO" id="fmap_004" />
<input type="hidden" name="fhdr" value="Deptno" id="fhdr_004" />
This can be translated from the column attributes. But always be carefull and doublecheck. It never hurts and you still need to know the output code.
However, if all of this sounds too advanced/difficult, then don't go through with it! You'll only come to regret it afterwards. Rather use standard functionality for something like this: 2 normal reports with an edit link to a single record form page!
But if you must, in my opinion, this is the cleanest path. You just need to know what you're doing.
