Trouble with dependant dropdown feature phpgrid 1.5

QuestionsTrouble with dependant dropdown feature phpgrid 1.5
Andreas asked 5 years ago

Hello all,

I am currently struggling to get a working dependant dropdown.

This is the relavant part of my code:

$col = array();
$col["title"] = "Customer";
$col["name"] = "id_customer";
$col["dbname"] = "customer.customerName"; // this is required as we need to search in name field, not id
$col["width"] = "45";
$col["align"] = "left";
$col["search"] = true;
$col["editable"] = true;
$col["editrules"] = array("edithidden"=>true);
$col["hidden"] = false;
$col["edittype"] = "select"; // render as select
# fetch data from database, with alias k for key, v for value
$str = $grid->get_dropdown_values("select distinct id_customer as k, customerName as v from customer");

$col["editoptions"] = array(
"value"=>$str,
"onchange" => array("sql"=>"SELECT id_customerAddress as k, CONCAT(addressStreet,' / ',addressZIP,' / ',addressCity) as v FROM customerAddress WHERE id_customer = '{id_customer}'",
"update_field" => "id_facilityAddress")
);
//$col["editoptions"] = array("value"=>$str);
$col["formatter"] = "select"; // display label, not value
$col["stype"] = "select"; // enable dropdown search
$col["searchoptions"] = array("value" => ":;".$str);
$cols[] = $col;

$col = array();
$col["title"] = "Address";
$col["name"] = "id_facilityAddress";
//$col["dbname"] = "customerAddress.id_customerAddress"; // this is required as we need to search in name field, not id
$col["width"] = "45";
$col["align"] = "left";
$col["search"] = false;
$col["editable"] = true;
$col["editrules"] = array("edithidden"=>true);
$col["hidden"] = true;
$col["edittype"] = "select"; // render as select
# fetch data from database, with alias k for key, v for value
$str = $grid->get_dropdown_values("SELECT id_customerAddress as k, CONCAT(addressStreet,' / ',addressZIP,' / ',addressCity) as v FROM customerAddress;");
$col["editoptions"] = array("value"=>$str);
$col["stype"] = "select"; // enable dropdown search
$col["searchoptions"] = array("value" => ":;".$str);
$col["formatter"] = "select"; // display label, not value
$cols[] = $col;

When I open the edit dialog the addressfield is populated with all available addresses but when I select a customer the address dropdown gets emptied and
there are no addresses in the dropdown list anymore.

When I manually replace: '{id_customer}' with the ID of an existing customer ID, for example 1, I do get the addresses of this customer when I do a selection in the customer dropdown.

It seems to me that '{id_customer}' is not properly handled.

Does anybody know a solution to this problem ?

Thank you for your kind help

best regards

Andreas

10 Answers
Andreas answered 5 years ago

Dear Abu,

can you confirm, that the dependent dropdown feature is actually working ?

I have tried hard to get a working solution but failed.

Thank you for your kind support.

best regards

Andreas

Abu Ghufran answered 5 years ago

Hello Andreas,

Your code seems fine and it should work as expected.
Can you come online on chat, so that i can have quick review of complete code and result.

Please email me at [email protected] when available.

Domingo Bermejo answered 5 years ago

Dear Abu,

I have the same problem:

$col =array();
$col["title"]="Nombre";
$col["name"]="nombre";
$col["search"] = true;
$col["editable"]=true;
$col["width"]="50px";
$col["formatter"]="select";
$col["edittype"]="select";
$str = $g->get_dropdown_values("SELECT DISTINCT id AS k, nombre AS v FROM alimentos");
$col["editoptions"] = array("value"=>$str,
"onchange"=> array("sql"=>"SELECT DISTINCT idfraccion AS k, concat( nombre, ' (', cantidad, 'g)' ) AS v FROM alimentosfracciones af, relalimentosfracciones raf WHERE af.id=raf.idfraccion AND raf.idalimento='{id}'",
"update_field"=> "idfraccion"));
$col["stype"] = "select"; // enable dropdown search
$col["searchoptions"] = array("value" => ":;".$str);
$cols[]=$col;

$col =array();
$col["title"]="Racion";
$col["name"]="idfraccion";
$col["search"] = true;
$col["editable"]=true;
$col["width"]="50px";
$col["edittype"]="select";
$str = $g->get_dropdown_values("SELECT DISTINCT af.id AS k, concat( nombre, ' (', cantidad, 'g)' ) AS v FROM alimentosfracciones af, relalimentosfracciones raf WHERE af.id=raf.idfraccion ");
$col["editoptions"] = array("value"=>$str);
$col["formatter"] = "select"; // display label, not value
$col["stype"] = "select"; // enable dropdown search
$col["searchoptions"] = array("value" => ":;".$str);

$cols[]=$col;

How did you solve it?

Regards

Domingo Bermejo answered 5 years ago

Dear Abu,

I fixed it.

Thanks

Abu Ghufran answered 5 years ago

Hello Arne,

Try setting select formatter.
$col["formatter"] = "select";

Screenshot of the issue would help in resolution.
I'll email you the update.

Arne answered 5 years ago

hi Abu,
if you have a fix for this, I am also interested in this.

If I concat my two column, the value is cleared when I edit the record.

My code:
$col = array();
$col["title"] = "Virksomhetsmodell"; // caption of column
$col["name"] = "forretningId"; // grid column name, same as db field or alias from sql
// $col["dbname"] = "F.struktur+F.forretningNavn"; // this is required as we need to search in name field, not id
$col["width"] = "10"; // width on grid
$col["editable"] = true;
$col["edittype"] = "select"; // render as select
# fetch data from database, with alias k for key, v for value
$str = $g->get_dropdown_values("select distinct id as k, (struktur+forretningNavn) as v from [INDIT].[dbo].[forretning] union select top 1 '','' from [INDIT].[dbo].[forretning] order by 2");
$col["editoptions"] = array("value"=>$str);
$cols[] = $col;

I'm using SQL Server, not MySQL.

Arne answered 5 years ago

Thanks Abu,
with this new jqgrid_dist.php the inline edit is fixed.
And the concat-problem is fixed!
Good and thanks!

Here is my code that is functional now!

$col = array();
$col["title"] = "Virksomhetsmodell"; // caption of column
$col["name"] = "forretningId"; // grid column name, same as db field or alias from sql
//$col["dbname"] = "F.struktur+F.forretningNavn"; // this is required as we need to search in name field, not id
$col["width"] = "10"; // width on grid
$col["editable"] = true;
$col["edittype"] = "select"; // render as select
# fetch data from database, with alias k for key, v for value
$str = $g->get_dropdown_values("select distinct id as k, (struktur+forretningNavn) as v from [INDIT].[dbo].[forretning] union select top 1 '','' from [INDIT].[dbo].[forretning] order by 2");
$col["isnull"] = true; // THIS DOES NOT WORK, SO I HAVE TO USE SELECT..UNION!
//$str = $g->get_dropdown_values("select distinct id as k, (struktur+forretningNavn) as v from [INDIT].[dbo].[forretning] order by 2");
$col["editoptions"] = array("value"=>$str);
//$col["formatter"] = "select";
$col["stype"] = "select"; // enable dropdown search
$col["searchoptions"] = array("value" => ":;".$str);
$cols[] = $col;

PS: the icons on the Action column is gone and the textlabel for Edit,Clone,Delete is back. Strange! But that doesn't matter..

PS2: the isnull option does not work for me. Also strange!

But many thanks for all your support!
🙂

Abu Ghufran answered 5 years ago

For action icons, edit jqgrid_dist.php and set
$this->internal["actionicon"] = false;
to
$this->internal["actionicon"] = true;

isnull, works at insertion, when you need to set NULL in insert sql.
If you want blank entry in dropdown, use this:

$col["editoptions"] = array("value"=>":;".$str);

actual notation is key:val;key2:val2.

aravindan answered 5 years ago

Hi Abu,

I want dependcy droplist in editable grid.ex(country,state,city)
and next three column is if a=10 &b=10(onclick event)
then automatically retrieve c=20..
Is this possible..

Abu Ghufran answered 5 years ago

For dropdown dependent, refer demos/appearance/dropdown-dependent.php sample code.
For your second case, you can put onblur event on first and second column, and set value to third column.

Refer faqs: Q) How to populate other column, based on previous columns? http://phpgrid.org/faqs

Your Answer

15 + 9 =

OR, enter

Attach code here and paste link in question.
Attach screenshot here and paste link in question.



How useful was this discussion?

Click on a star to rate it!

Average rating / 5. Vote count:

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!