Master Detail FAQs
Master Detail FAQs
Q) How to dynamically change detail grid's dropdown field content, based on selection of master grid row?
Step1: In master-detail.php, we added a dropdown in detail grid, with daraUrl param set to a php page.
$col = array();
$col["title"] = "Client";
$col["name"] = "client_id";
$col["width"] = "100";
$col["align"] = "left";
$col["search"] = true;
$col["editable"] = true;
$col["edittype"] = "select"; // render as select
$col["editoptions"] = array("dataUrl"=>"http://jqgrid/dev/build-select.php");
$cols[] = $col;
Put build-select.php in some web access path, and set correct http url. and, we also set the master grid selection id into session variable.
$id = intval($_GET["rowid"]);
if ($id > 0)
$_SESSION["rowid"] = $_GET["rowid"];
Step2: In build-select.php, we read the session variable and show the dropdown select based on that variable data.
<?
if (!isset($_SESSION) || !is_array($_SESSION))
session_start();
if ($_SESSION["rowid"] == "1") {
?>
<select>
<option value='1'>One</option>
<option value='2'>Two</option>
</select>
<?
}
else {
?>
<select>
<option value='3'>Three</option>
<option value='4'>Four</option>
</select>
<?
}
For cascaded dropdown, Following config will be required.
On change this column ($col), it will run sql and search it's data on particular field (search_on), then update another dropdown specified in update_field.
$col["editoptions"] = array(
"value"=>$str,
"onchange" => array( "sql"=>"select note as k, note as v from invheader",
"search_on"=>"client_id",
"update_field" => "note" )
);
Q) How to load grid with table that have fields with spaces?
You will need to alias the spaced fields with '-' and set query in select_command. e.g.
$g->select_command = "SELECT `i d` as `i-d`, `client id` AS `client-id`, `inv date` AS `inv-date` FROM invheader";
Rest functionality (add/update/del/search) will be handled by library.
Q) How to use table with composite keys index?
For composite keys - there are two possible approaches:
1) Creating a new AUTO_INCREMENT column directly in the database, so that each row has a unique id, then using this column for primary key. You can hide the column using hidden => true.
2) In your SELECT statement (select_command), you may try to select a first column as special concat value that is based on composite keys. This will handle the listings. For insert/update/delete, you will need to use custom events on_update/on_insert/on_delete to parse the new concat field and perform desired operation. Refer demos/editing/custom-events.php for help.
// example code 1
$g->select_command = "SELECT concat(pk1,'-',pk2) as pk, col2, col3 FROM table";
$e["on_insert"] = array("add_client", null, true);
$g->set_events($e);
function add_client($data)
{
$pk = $data["params"]["pk"];
list(pk1, pk2) = explode("-",$pk);
$data["params"]["pk1"] = $pk1; // setting $data will make sure it will be there in INSERT SQL
$data["params"]["pk2"] = $pk2;
}
// example code 2
// Step1: Select concatenated PK with combined composite key
$g->select_command = "SELECT concat(Year_No,'-',Order_No,'-',LocationID,'-',TranscationId) as pk, Year_No, Order_No, LocationID, TranscationId, Startdate, ExpiredDate FROM mylocations ";
// Step2: Connect on_update event hander
$e["on_update"] = array("update_data", null, false);
$g->set_events($e);
// Step3: In handler, split the PK with your separator, and execute custom UPDATE query
function update_data($data)
{
list($Year_No,$Order_No,$LocationID,$TranscationId) = explode("-",$data["pk"]);
$s = array();
foreach($data["params"] as $k=>$v)
{
$s[] = "$k = '$v'";
}
$s = implode(",",$s);
$w = "Year_No=$Year_No and Order_No=$Order_No and LocationID=$LocationID and TranscationId=$TranscationId";
mysql_query("UPDATE mylocations set $s WHERE $w");
}
Q) How to show footer row in subgrid?
In subgrid_detail, do following config.
$grid["footerrow"] = true;
$g->set_options($grid);
$e["js_on_load_complete"] = "subgrid_onload";
$g->set_events($e);
In parent grid, put function to fill subgrid footer
<script>
var subgrid_onload = function () {
var grid = $("td.subgrid-data > .tablediv > div").attr("id").replace("gbox_","");
grid = jQuery("#"+grid);
// sum of displayed result
sum = grid.jqGrid('getCol', 'total', false, 'sum');
c = grid.jqGrid('getCol', 'id', false, 'sum');
grid.jqGrid('footerData','set', {id: 'Sum:' + c, total: 'Total: '+sum});
};
</script>
Q) How to reload parent after new record added in subgrid ?
Following config in subgrid_detail will reload parent when new record is inserted in detail grid. Reloading parent will make subgrid hidden again.
In subgrid_detail php file, just add this line in grid options.
$grid["add_options"]["afterSubmit"] = "function(){jQuery('#list1').trigger('reloadGrid',[{page:1}]); return [true, ''];}";
where list1 is parent list id.
Q) How to keep subgrid expanded after parent grid's refresh ?
Following JS code snippet will keep subgrid opened after parent's refresh. You can place this script with parent grid's code.
PHP Part:
// reload previous expanded subgrids on load event
$e["js_on_load_complete"] = "grid_onload";
$grid->set_events($e);
JS Part:
<script>
var scrollPosition = 0
var ids = new Array();
function grid_onload()
{
jQuery.each(ids, function (id,data) {
$("#list1").expandSubGridRow(data);
jQuery("#list1").closest(".ui-jqgrid-bdiv").scrollTop(scrollPosition);
});
}
// custom refresh button
jQuery(document).ready(function(){
jQuery('#list1').jqGrid('navButtonAdd', '#list1_pager',
{
'caption' : 'Refresh',
'buttonicon' : 'ui-icon-extlink',
'onClickButton': function()
{
ids = new Array();
$('tr:has(.sgexpanded)').each(function(){ids.push($(this).attr('id'))});
scrollPosition = jQuery("#list1").closest(".ui-jqgrid-bdiv").scrollTop()
$("#list1").trigger("reloadGrid");
},
'position': 'last'
});
});
</script>
Q) How to disable subgrid expansion on specific row ?
Following snippet will remove subgrid from e.g. rowid 2
$opt["loadComplete"] = "function(){
var rowid=2;
jQuery('tr#'+rowid+' td[aria-describedby$=subgrid]').html('');
jQuery('tr#'+rowid+' td[aria-describedby$=subgrid]').unbind();
}";
// ...
$g->set_options($opt);
Q) How to select inserted row in master grid, and refresh the detail grid for linked operations?
Following add_options setting in master grid will SELECT the newly inserted row.
$opt["add_options"]["afterComplete"] = "function (response, postdata) { r = JSON.parse(response.responseText);
jQuery( document ).ajaxComplete(function() {
jQuery('#list1').setSelection(r.id);
jQuery( document ).unbind('ajaxComplete');
});
}";
$grid->set_options($opt);
If you wish to show highlight effect, you can include jquery ui script,
<script src="//code.jquery.com/ui/1.10.4/jquery-ui.js"></script>
And have highlight code in afterComplete:
$opt["add_options"]["afterComplete"] = "function (response, postdata) { r = JSON.parse(response.responseText); jQuery('#'+r.id,'#list1').effect('highlight', {color:'yellow'}, 2000); }";
$grid->set_options($opt);
A condition that new record should come in grid list, You can sort grid to decending id, to show newly added record on top of grid.
Q) How to refresh parent grid on subgrid insertion?
Following code steps will refresh subgrid's parent on child grid insertion. It will also expand the last child subgrid after refresh.
First, disable default refresh button of parent grid.
$grid->set_actions(array(
"refresh"=>false // will add a custom refresh button
)
);
Set on_load event handler for parent.
// reload previous expanded subgrids on load event
$e["js_on_load_complete"] = "grid_onload";
$grid->set_events($e);
Put following script tag in parent grid.
<script>
var scrollPosition = 0
var ids = new Array();
// expand last child grid on load
function grid_onload()
{
jQuery.each(ids, function (id,data) {
$("#list1").expandSubGridRow(data);
jQuery("#list1").closest(".ui-jqgrid-bdiv").scrollTop(scrollPosition);
});
}
function reload_parent()
{
ids = new Array();
$('tr:has(.sgexpanded)').each(function(){ids.push($(this).attr('id'))});
scrollPosition = jQuery("#list1").closest(".ui-jqgrid-bdiv").scrollTop()
$("#list1").trigger("reloadGrid");
}
// custom refresh button
jQuery(document).ready(function(){
jQuery('#list1').jqGrid('navButtonAdd', '#list1_pager',
{
'caption' : 'Refresh',
'buttonicon' : 'ui-icon-extlink',
'onClickButton': function()
{
reload_parent();
},
'position': 'last'
});
});
</script>
In child subgrid, set afterSubmit to call reload_parent() after insert.
$grid["add_options"]["afterSubmit"] = "function(){ reload_parent(); return [true, ''];}";
Q) How to enable cell edit on double click and functional on master-detail grid?
Adding following options will make celledit work on double click and functional on master detail grid.
// celledit double click (master-detail) - list1 is grid id
$opt["cellEdit"] = true;
$opt["beforeSelectRow"] = "function(rowid) { if (jQuery('#list1').jqGrid('getGridParam','selrow') != rowid) { jQuery('#list1').jqGrid('resetSelection'); jQuery('#list1').setSelection(rowid); } return false; }";
$opt["ondblClickRow"] = "function (rowid, iRow,iCol) { jQuery('#list1').editCell(iRow, iCol, true); }";
$grid->set_options($opt);
Refer demos/master-detail/excelview-detail.php
Q) How can I set the selected row (first row) on load page?
Following config will select the first row of grid on load event. You can customize as per your need.
PHP:
$e["js_on_load_complete"] = "grid_onload";
$grid->set_events($e);
JS:
function grid_onload(ids)
{
// get row ids from grid (with id: list1)
var ids = $("#list1").jqGrid('getDataIDs');
setTimeout( function(){ jQuery('#list1').jqGrid('setSelection', ids[0], true); },100);
}
Also check: https://www.gridphp.com/support/questions/select-row-on-load-page/
Q) How can I change the detail title when I select a row from master grid?
On master grid, set onselect event.
$e["js_on_select_row"] = "do_onselect";
$grid->set_events($e);
and in html part add this script.
<script>
function do_onselect(id)
{
// get selected row company column value
var ctype = jQuery('#list1').jqGrid('getCell', id, 'company'); // where invdate is column name
// set company name in detail grid caption
jQuery('#list2').jqGrid('setCaption','Company Details: ' + ctype);
}
</script>
Also check: https://www.gridphp.com/support/questions/select-row-on-load-page/
Q) How can hide/show detail grid on selecting a row from master grid?
On master grid, set onselectrow event. If gender is male, it will hide detail grid otherwise show it. (where list1 is master, list2 is detail)
$opt["onSelectRow"] = "function(id){
var gid = jQuery('#list1').jqGrid('getCell', id, 'gender');
if (gid == 'male')
$('#list2').jqGrid('setGridState', 'hidden');
else
$('#list2').jqGrid('setGridState', 'visible');
}";