This topic is locked

Dependent dropdown lookup in child table based on master record

4/17/2015 12:20:54 PM
PHPRunner Tips and Tricks
F
FunkDaddy author

Want to change a field in the master record form and have it's value control another lookup field inside a inline add/edit child form? Simply pass a newly created window variable from master record down to the child tables
Step 1:

Read Sergey's how-to-article tips & tricks forum entry titled "Custom dependent dropdown boxes" here http://www.asprunner.com/forums/topic/22701-custom-dependent-dropdown-boxes/
Step 2:

By using the same principles explained in his tutorial you can also pass a field value from master record form by simply instantiating a global window variable in javascript, which can then be used as a reference in your $.post script as is explained in the article.
Example:
I have a master record called "Epics" that allows me to enter notes about a feature needed in software development (agile anyone?). In that form I have a dropdown that let's me associate the epic entry with a particular software product name so I know what project/product the epic belongs to. Based on the value of this product name, I want my child record forms that are embedded in the master form to have a field that is dependent on the one in the master. So in the master I simply keep track of that product name when the dropdown is changed.



//Lets make the LinkProductID field in master record trigger a dependent lookup in the child based on the selected product

var LinkProductID = Runner.getControl(pageid, 'LinkProductID');
//The window var is used by the details table to adjust the dependent dropdowns accordingly in JS OnLoad events

LinkProductID.on('change',function(){

window.productID = this.getValue();

//console.log(window.productID); //debug

});


Step 3:

Using Sergey's code for the javascript portion I simply modify one line as follows:



//modify this..

'category' : this.getValue() },
//...to this

'category' : window.productID },
//This now tells your function to pick-up the global window variable that was set by your master record :-)


Note that you will be placing Sergey's javascript sample code in your child record javascript events since you want the dropdown values to change there.
Step 4:

There is one potential gotcha as well... if you are working on a child dropdown that is already setup as dependent lookup on another child field you will need to add a setTimeout function inside the $.post request in order to pause the callback in order to make it wait for the native PHPRunner dependent lookup to complete it's dependency dropdown update.
For example, if looking at sergey's code:



//Original sample code

var ctrlMake = Runner.getControl(pageid, 'Make');

var ctrlModel = Runner.getControl(pageid, 'Model');

var func = function(e){

$.post("mylookup.php", {

'category' : this.getValue() },

function(data) {

var sel = $("#value_Model_1");

sel.empty();

for (var i=0; i<data.length; i++) {

sel.append('<option value="' + data[i].id + '">' + data[i].desc + '</option>');

}

}, "json");

};
ctrlMake.on('keyup', func);

ctrlMake.on('change', func);
//You would update it to this:

var ctrlMake = Runner.getControl(pageid, 'Make');

var ctrlModel = Runner.getControl(pageid, 'Model');

var func = function(e){

$.post("mylookup.php", {

'category' : this.getValue() },

function(data) {

setTimeout(function(){

var sel = $("#value_Model_1");

sel.empty();

for (var i=0; i<data.length; i++) {

sel.append('<option value="' + data[i].id + '">' + data[i].desc + '</option>');

}

},1000);//end setTimeout (set for 1 sec)

}, "json");

};
ctrlMake.on('keyup', func);

ctrlMake.on('change', func);


Step 5:

Done! Keep in mind of course that you need to edit the code examples provided by Sergey to match your own needs (the SQL query, the sent post values, and the returning callback script values etc).

romaldus 4/19/2015

Thanks for this

H
Hertz2P 3/26/2017

Hi Marcelo,
I'm a bit of a novice, but this is exactly what I'm trying to do, and can't seem to make it work.. I wish you would have posted your working scripts because I didn't know what to do with the extra variable.. Here's what I've done, can you see my error(s)?
I have a Master table 'Foreman's Daily Report' with a field 'Project'. I have a Details Table called 'Onsite Equipment' with a field 'Model' and I have another table 'Equipment' with fields 'Model' and 'CurrentLocation'
I want the Lookup Wizard on 'Onsite Equipment' to only show 'Model' WHERE 'Project' (Master Table) = 'CurrentLocation'.
So I put this in Master Table Add page, JavaScript OnLoad event:



//Lets make the LinkProductID field in master record trigger a dependent lookup in the child based on the selected product

var LinkProductID = Runner.getControl(pageid, 'Project');
//The window var is used by the details table to adjust the dependent dropdowns accordingly in JS onload events

LinkProductID.on('change',function(){

window.productID = this.getValue();

//console.log(window.productID); //debug

});


I put this in 'Onsite Equipment' Add page, JavaScript OnLoad event:



var ctrlModel = Runner.getControl(pageid, 'Model');

var func = function(e){

$.post("mylookup.php", {

'category' : window.productID },

function(data) {

setTimeout(function(){

var sel = $("#value_Model_1");

sel.empty();

for (var i=0; i<data.length; i++) {

sel.append('<option value="' + data[i].id + '">' + data[i].desc + '</option>');

}

},1000);//end setTimeout (set for 1 sec)

}, "json");

};
ctrlModel.on('keyup', func);

ctrlModel.on('change', func);


And I put this in mylookup.php file in output directory:



<?php
include("include/dbcommon.php");
$result = array();

$make = db_addslashes(trim(postvalue('category')));

$sql = "SELECT * FROM Equipment WHERE currentLocation = '$make'";

$rs = CustomQuery($sql);
while ($data = db_fetch_array($rs)) {

$result[] = array(

'id' => $data['Model'],

'desc' => $data['Model'],

);

}

echo json_encode($result);

?>


Incidentally, I couldn't find mylookup.php on the server, even after a full build/upload all files, so I manually uploaded it to the root directory on the server. Not really sure where the file is supposed to be..
Here is the console error that I'm getting: SCRIPT438: Object doesn't support property or method 'on'
Here is the Debugger code:



Runner.pages.PageSettings.addPageEvent('Onsite Equipment',Runner.pages.constants.PAGE_ADD,"afterPageReady",function(pageObj,proxy,pageid,inlineRow,inlineObject){var ctrlModel=Runner.getControl(pageid,'model');var func=function(e){$.post("mylookup.php",{'category':window.productID},function(data){var sel=$("#value_Model_1");sel.empty();for(var i=0;i<data.length;i++){sel.append('<option value="'+data[i].id+'">'+data[i].desc+'</option>');}},"json");};ctrlModel.on('keyup',func);ctrlModel.on('change',func);});


Any help appreciated,
Thanks!

J
jackwood 7/4/2017

Hi Marcelo,
Im'Still Confuse How to Linked MyLookup Object Control to Master Dropdown.

For Example:

I have master-Details payment Transaction.

If in the header I select dropdown to Master Customer. So at InLine add detail I have a dropdown to select to Outstanding Payment based on Customer that I Select from Master.
Is there any sample can I download and Implement in my Case ?
Thanks and appreciate for help.