Another thought I had was to get the list of json keys from a query and create a column for each one. I do not know the list ahead of time so it’ll have to be done dynamically. I’ll hide these columns, but have them searchable so they’ll show up in the search dialog dropdown. My question is can we dynamically create grid columns by looping through a query result set? Thanks again.

Both of your cases are doable.

For main question, you can set:

$cols[$i][“searchoptions”][“value”] = “key1:label1; key2:label2”;

and it will be shown in search options of that column. What you can do is to run query by:

$rs = $g->get_all(“SELECT …………. FROM TABLE”); // debug $rs to know format

And feed $rs in searchoptions value manually. It is exactly what is done inside when you provide sql for dropdown.

Thanks. After thinking about it the better solution would be to dynamically generate columns and hide the ones we don’t want to show. How would I go about doing that? I haven’t seen it in the doc or examples. Thanks again!

If i understand correctly, To dynamically generate column, you might need to:

1. Fetch your data using query: $rs = $g->get_all(“select …… tab”);
2. Iterate $rs in a loop
3. Make custom columns inside body like:

$col = array();
$col[“title”] = “col_name”;
$col[“name”] = “col_name”;
$col[“width”] = “20”;
$col[“editable”] = false;
$col[“hidden”] = true;
$cols[] = $col;

The col_name could be the field used in WHERE clause of search. You can use some there expression as well e.g.

$col[“dbname”] = “concat(table.field,table.field2)”;