This topic is locked

Custom dependent dropdown boxes

10/22/2014 4:19:48 PM
PHPRunner Tips and Tricks
admin

There are situations when built-in dependent dropdown boxes won't cut. For instance you need to use a different SQL query based on master dropdown selection or make dropdown box dependent on two fields instead of just one. Here is how this can be done in PHPRunner.
Let consider a simple Cars database.


[size="4"]1. Simple dependent dropdown box[/size]

In Cars table setup both Make and Model as Lookup wizards pointing them respectively to Makes and Models tables.
Proceed to Events screen in PHPRunner and add the following code to Cars table Add page Javascript OnLoad event



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);


A quick explanation of what this code does. When the value of master control (Make) changes we send a POST request via AJAX to custom PHP file named mylookup.php. This PHP file executes SQL query retrieving data for the second lookup and returns it in JSON format. Once data is received we delete all existing items from the second dropdown and populate it with new items.
Here is the code for mylookup.php file that needs to be created in the output directory.

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

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

$sql = "SELECT * FROM Models WHERE Make = '$make'";

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

$result[] = array(

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

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

);

}

echo json_encode($result);

?>


Note that in this code we use Model as both Link Field and Display Field. If you want to display a list of models but save their IDs in the database here is how this code needs to be modified (only ID field name changes).

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

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

$sql = "SELECT * FROM Models WHERE Make = '$make'";

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

$result[] = array(

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

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

);

}

echo json_encode($result);

?>


Note: exactly the same code will work if Make is setup as a simple text field. As long as you type correct Make value Model dropdown box will be populated with the list of matching models.
[size="4"]2. Using a different SQL query[/size]
Lets says that data in your Models table is not very clean and Make field contains values like 'Tesla' and 'Tesla Motors'. When someone types Tesla we want to display all models that have 'Tesla' as a part of their Make.
Javascript code won't change, just the SQL part of PHP code will change.

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

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

$sql = "SELECT * FROM Models WHERE Make LIKE '%$make%'";

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

$result[] = array(

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

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

);

}

echo json_encode($result);

?>


Another slightly artificial example where we select the year of make first and then list all models available for that year or for later years.

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

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

$sql = "SELECT * FROM Models WHERE YearOfMake >= $year";

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

$result[] = array(

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

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

);

}

echo json_encode($result);

?>


[size="4"]3. Making dropdown box dependent on two other fields[/size]
This is the most interesting and challenging one. Lets assume we have two dropdown boxes (Make and Model) and need to filter YearOfMake dropdown box depending on Make and Model selection.
Javascript OnLoad event



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

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

var func = function(e){

$.post("mylookup.php", {

'make' : ctrlMake.getValue(),

'model' : ctrlModel.getValue() },

function(data) {

var sel = $("#value_YearOfMake_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);


Note that now we need to pass both Make and Model to our mylookup.php file. Here is the updated mylookup.php file.

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

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

$model = db_addslashes(trim(postvalue('model')));

$sql = "SELECT * FROM Models WHERE make='$make' and model='$model'";

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

$result[] = array(

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

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

);

}

echo json_encode($result);

?>


This is it.

A
Anapolis 12/11/2014

Sergey, I just got around to reading this thoroughly.
GREAT tutorial.
Thank you for taking the time to help us "break out of the box" with useful extra addons like this lesson.
I can see these examples can be extended to cover other situations, as well, that are not handled natively inside PHPRunner.
I'm copying this and working it through so that I can familiarize myself with a real break-out functionality.

J
jackolantern 1/8/2015

Awesome, ur rock.

DealerModulesDevClub member 4/8/2015

I have to start reading the Tips and Tricks more often. This one saved me a ton of work! Thanks Sergey!

E
etreasure 5/27/2015

Scenario 3 of this tutorial is exactly what I've needed. I am able to successfully implement the code sample with modification on add/edit/search pages. However, I would also like to use this approach to control dropdown choices on the search panel of the list page. So far, I've been unsuccessful. My suspicion is that it has something to do with the availability of methods from the Javascript API. Specifically, the getControl and getValue methods used on the add/edit/search pages and not available on the list page. Is it possible to adapt this method so that it will function on the list page?
Thanks.

--Emrys

admin 5/28/2015

Emrys,
you are correct, Javascript API is only available on Add/Edit pages.
To do something similar on Search panel you need to use jQuery instead of Javascript API. It's not extremely complicated but some coding skills will be required and we do not have such code example available.

A
Abul 10/8/2015

I tried to apply this tricks. It did not work for me. In my situation let say I have Master table "Sale" with child table "Car". I put "Make" as dropdown field on Master table "Sale" and "Model" as dropdown field on "Car" table. Now I want Model list on child table "Car" based on Make field selected on Master Table "Sale". I cannot workout this trick in this my case. Have you any advice? Thanks.

Z
zupermanzito 11/5/2016

what about if i have more dropdowns that depend on a master...
in your example you have:

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

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

var func = function(e){

$.post("mylookup.php", {

'category' : this.getValue() },

function(data) {



[size="5"]var sel = $("#value_Model_1");[/size]

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);


but what if i need:
var sel = $("#value_Model_1");

var sel = $("#value_Model_2");

var sel = $("#value_Model_3");

var sel = $("#valueModel+ii");

J
Jkelleyus 2/27/2019

This is a great description of what I need to do. However, I need to do it in ASPRunner.
Any chance we can get a similar tutorial for ASP?
Thanks!
John

J
Jkelleyus 2/27/2019

This is a great description of what I need to do. However, I need to do it in ASPRunner.
Any chance we can get a similar tutorial for ASP?
Thanks!
John