Right of the bat: this will only work with wizard generated tabular forms and not with manual tabular forms.
My goal was to lessen dependance on the f## arrays in the tabular forms, which especially in javascript can be outright frustrating. Until i used this code, i usually declared some global params and commented a lot about how each array maps to which column. Only to then afterward having to switch the column around with another one, or add one in, and find the code just doesn't work anymore because of the f-arrays.
var gEmpno = 'f01', gEname = 'f05', gSal = 'f06';
$('input[name="f04"][type="text"]:visible')Oh no. A column had to be added between 3 and 4! Renumber all javascript code.:-( Switched column order in the report attributes? Renumber all code :-(
My code works with the mapping which apex generates when it renders a tabular form. As an example, i have tabular form based on EMP:
select "EMPNO", "EMPNO" EMPNO_DISPLAY, "ENAME", "JOB", "MGR", "HIREDATE", "SAL", "COMM", "DEPTNO" from "#OWNER#"."EMP"This the query generated by apex when selecting the EMP table as source and indicating EMPNO is the primary key (no rowid used).
I only altered column DEPTNO to be a select list instead of a textfield, so it would show the actual department names and so i would have select list in my tabular form.
When the page is runned, then below the tabular form HTML an additional mapping will be rendered:
<input type="hidden" id="fmap_001" value="EMPNO" name="fmap"> <input type="hidden" id="fhdr_001" value="Empno" name="fhdr"> <input type="hidden" id="fmap_002" value="ENAME" name="fmap"> <input type="hidden" id="fhdr_002" value="Ename" name="fhdr"> <input type="hidden" id="fmap_003" value="JOB" name="fmap"> <input type="hidden" id="fhdr_003" value="Job" name="fhdr"> <input type="hidden" id="fmap_004" value="MGR" name="fmap"> <input type="hidden" id="fhdr_004" value="Mgr" name="fhdr"> <input type="hidden" id="fmap_005" value="HIREDATE" name="fmap"> <input type="hidden" id="fhdr_005" value="Hiredate" name="fhdr"> <input type="hidden" id="fmap_006" value="SAL" name="fmap"> <input type="hidden" id="fhdr_006" value="Sal" name="fhdr"> <input type="hidden" id="fmap_007" value="COMM" name="fmap"> <input type="hidden" id="fhdr_007" value="Comm" name="fhdr"> <input type="hidden" id="fmap_008" value="DEPTNO" name="fmap"> <input type="hidden" id="fhdr_008" value="Deptno" name="fhdr">This is what i based my mapping on. I only look at the fmap array. And all items are always in the same order as they are in the report attributes. This order also determines which f##-array has to be assigned. A hidden column for example could be the last element in the mapping and thus be f09, while the inputs for array f09 are rendered in the same column as the inputs for array f05.
I've tested through playing with elements in the tabular form, hide them, reorder them, remove and add. The mapping would always reflect the actual correct values.
Place this code in the javascript section of the page:
var gaInputMapping = new Array(); $().ready(function(){ apex.debug('Initializing input mapping array...'); $("input[name='fmap']").each(function(index){ var lsHeader = $(this).val(), lsName = 'f'+('0'+(index+1)).slice(-2), lsNName = $("[name='"+lsName+"']")[0].nodeName, lMap = {"header":lsHeader,"name":lsName,"nodeName":lsNName}; apex.debug('Header: '+lsHeader+' - Array name: '+lsName+' - Node type: '+lsNName); gaInputMapping.push(lMap); }); }); function getNameWithHeader(pHeader){ var lsName; $.each(gaInputMapping, function(index){ if(gaInputMapping[index].header==pHeader.toUpperCase()){ lsName= gaInputMapping[index].name; }; }); return lsName; }; function getHeaderWithName(pName){ var lsHeader; $.each(gaInputMapping, function(index){ if(gaInputMapping[index].name.toUpperCase()==pName.toUpperCase()){ lsHeader= gaInputMapping[index].header; }; }); return lsHeader; }; function getMapEntry(pHeader){ var lRet; $.each(gaInputMapping, function(index){ if(gaInputMapping[index].header==pHeader.toUpperCase()){ lRet= gaInputMapping[index]; }; }); return lRet; }; function getSelector(pHeader){ var lsSel; $.each(gaInputMapping, function(index){ if(gaInputMapping[index].header==pHeader.toUpperCase()){ lsSel= gaInputMapping[index].nodeName + "[name='" + gaInputMapping[index].name + "']"; }; }); return lsSel; }; function getObjectInSameRow(pHeaderFind, pCurrentItem){ return $(getSelector(pHeaderFind), $(pCurrentItem).closest("tr")); };With these, i've executed some lines in the Firebug console:
>>> gaInputMapping [Object { header="EMPNO", name="f01", nodeName="INPUT"}, Object { header="ENAME", name="f02", nodeName="INPUT"}, Object { header="JOB", name="f03", nodeName="INPUT"}, Object { header="MGR", name="f04", nodeName="INPUT"}, Object { header="HIREDATE", name="f05", nodeName="INPUT"}, Object { header="SAL", name="f06", nodeName="INPUT"}, Object { header="COMM", name="f07", nodeName="INPUT"}, Object { header="DEPTNO", name="f08", nodeName="SELECT"}] >>> getNameWithHeader("EMPNO") "f01" >>> getNameWithHeader("MGR") "f04" >>> getHeaderWithName("f03") "JOB" >>> getMapEntry("SAL") Object { header="SAL", name="f06", nodeName="INPUT"} >>> getMapEntry("DEPTNO") Object { header="DEPTNO", name="f08", nodeName="SELECT"} >>> getSelector("HIREDATE") "INPUT[name='f05']" >>> getSelector("DEPTNO") "SELECT[name='f08']" $(getSelector("SAL"),"#rEmp").change(function(){ var lRow = $(this).closest("tr"), lSal = $(this).val(), lComm = $(getSelector("COMM"), lRow).val(), lEmpno = $(getSelector("EMPNO"), lRow).val(), lDeptno = $(getSelector("DEPTNO"), lRow).val(); console.log(lEmpno+' - '+lDeptno+' - '+lSal+' - '+lComm); }); 7369 - 20 - 1804 - 5 (after changing the first row sal value from 1803 to 1804)Another example: get the EMPNO item on the same row as the current item. The getObjectInSameRow function is just a wrap-up of a frequently used technique to target an item on the same row as the current item, and allows you to do so via headers. There is no need to play with rowids and substrings.
$(getSelector("SAL"),"#rEmp").each(function(){ console.log(getObjectInSameRow('EMPNO', $(this))); });This system is a lot more robust to me. I can now safely use the headers and don't have to use a coded f##-array anywhere anymore. I can switch columns and adjust their properties.
There are limits of course: removing columns while functionality relies on them would give you errors, obviously. Changing an item so no input/select is generated anymore, but display only will also break your functionality, as a non-submitting item will not generate an array. In short though, using headers is more intuitive and makes the code more maintainable, and that's all there really is to it.
Tom, I agree with you, much easier to work with. This code is a thing of beauty.
ReplyDeleteI gotta tell you, I learned something new. I never noticed that map after the tabular form. Thanks!
-Jorge
Hi Jorge, great you like it! You may be interested to look at this github repo https://github.com/tompetrus/oracle-apex-tabform-ext . I haven't really done a write-up on it (I really should get back to making a blogpost again!) but there is an extensive demo and documentation - much of this is seated on code found in this post!
DeleteAh nice. Good call on making it an extension of apex.widget.tabular Glad you made it available on Github. (yes, yes, blog some more) :)
DeleteThis comment has been removed by the author.
ReplyDeleteHi Tom,
ReplyDeleteThis blog was really helpful. I did use this in my code and it works beautifully, Although I have issues when I use apex built-in tabular from validations. If validation fails then it gives me error as
report error:
ORA-01403: no data found
ORA-06510: PL/SQL:
This probably happens as newly added row does not appear anymore on browser and hence gives no data found error. But if I take out this code and add validation on apex tabular form without any custom Javascript code then it works fine.
Any ideas?
Hi Brinal,
DeleteI don't see how the js code would be responsible for this - It's just some sugar and doesn't interfere with the default workings. It may depend on implementation though - where are you using some code of it, and by chance, have you had a chance to verify it's working in this situation?
Do you have custom plsql to interface with the data arrays (apex_application.g_f##)?
When the validation fails and the page reloads, are you getting javascript errors in the console?
Hi Tom,
ReplyDeleteI played lot with it and figured out that it is not your javascript code issue, It happened as I added some javascript code to dynamically take away disabled property on pop-up -key lov's. Due to this when we add new row to an apex and if there is any validation on apex that fails it will give no data found error. Although it works perfectly fine when validation does not fail and data is successfully submitted.
So I created dynamic action on event before page submit and added disabled property back to lov's which took care of this issue. So apparently built-in apex does not like any custom modifications to the html.
Right now I am struggling with validation issue. If you add validation on pop-up lov without any custom code and if validation fails this will take away value inside pop-up lov. This happens only when we add new row to tabular form. I have replicated this issue on oracle apex.com which simply uses tabular form with validation. Looks like it is an apex bug.
Thanks Tom for taking time and replying to my comment. This is really beautiful contribution to target tabular rows.