PDA

View Full Version : Lianja 4.2 Query Picker



barrymavin
2018-06-05, 01:31
I've been looking at ways to provide a consistent way to query and select an item from related tables as this has been asked on numerous occasions.

In Lianja 4.2 we already have the "Custom Query Builder".

I have now combined "Miller Columns" with a browsable and searchable data grid.

The cascading lists of the "Miller Columns" and the data grid have a small "Search" icon which when clicked slides in the query builder allowing you to filter the individual columns and grid.

I'm still refining this prior to general release. It operates across desktop, web and mobile. I will document its usage in due course.

A picture says a thousand words. See below.

1685

The dialog button delegate.

1686

The "Query Picker" in operation.

1687

Using the Query Builder to filter records fo selection.

1688

phabio
2018-06-05, 01:51
I'm touched ... :o

another step to the best RAD!

thanks Barry!

you are welcome in Italy!

Fabio

barrymavin
2018-06-07, 00:39
The "Query Picker" is now fully working across desktop, web and mobile. Doc to come. This is included in 4.2Beta14.

1690

1691

phabio
2018-06-11, 03:01
Hi Barry
it's fantastic.

in the demo, what is the fourth parameter?
Lianja.showQueryPickerPanel("Browse Orders", "myCallback()", "southwind", "order_details");

I suppose it is "what needs to be analyzed", but I do not find how it is defined.

thanks
Fabio

barrymavin
2018-06-11, 03:40
Hi Fabio,

I'm currently documenting it.

https://www.lianja.com/doc/index.php/QueryPicker

So read through that doc if if you have any questions let me know. I'll add some additional explanations regarding params.

phabio
2018-06-11, 07:53
Ok, I try to play with parameters..

phabio
2018-06-11, 08:35
Hi Barry, I'm tryng.. without a good results..
but I've a question:
if I need to browse only one table.. it's possible?

phabio
2018-06-11, 08:56
Now, I'me try with this parameters:
database=southwind&
table=products&
tabletitle=Item Details&
selectcolumn=productid&
columncount=1&
columntitles=Categories&
columns=categories,categoryid|products,productid,p roductname,categoryid={}&
columnsorderby=||&
picklist=products|productid,productname|categoryid ={}

but don't work.

yvonne.milne
2018-06-11, 09:01
Hi Fabio,

See the latest updates to the parameters table. The {} should be encoded: %7B%7D

Regards,

Yvonne

phabio
2018-06-11, 09:13
YES!

now is better..

this work


m_par = [database=southwind&table=products&tabletitle=Item Details&selectcolumn=productid&columncount=1&columntitles=Categories&columns=categories,categoryid|products,productid,c ategoryid=%7B%7D&columnsorderby=||&picklist=products|productid,productname|categoryid =%7B%7D]
Lianja.showQueryPickerPanel("Browse Items", "", "southwind", "products", m_par, 900)


2 questions:
is possible to add a column to the "Coategories?

I've try with


m_par = [database=southwind&table=products&tabletitle=Item Details&selectcolumn=productid&columncount=1&columntitles=Categories&columns=categories,categoryid<b>,categoryname</b>|products,productid,categoryid=%7B%7D&columnsorderby=||&picklist=products|productid,productname|categoryid =%7B%7D]
Lianja.showQueryPickerPanel("Browse Items", "", "southwind", "products", m_par, 900)

but not work

If I don't need to have 2 table?
It's possible to only have the products table?

barrymavin
2018-06-11, 11:33
No it uses miller columns above a data grid. If there are no Miller columns it’s not a Query Picker it would just be a data grid and that’s not what it’s for.

You need to adjust the columncount. You have it set to 1 but you have two columns. I will look into using one column as I can see the use of that but it’s not what Miller columns are for.

... to answer your other question, no a column is a column from a table not a grid. So you cannot have multiple columns as one column.

phabio
2018-06-11, 11:39
Ok...
So, in the main example, I can't have "Customer ID" and "Customer name"...
so, it's not easy to get the right customer..

barrymavin
2018-06-11, 11:48
Ok...
So, in the main example, I can't have "Customer ID" and "Customer name"...
so, it's not easy to get the right customer..

Ill take a look and see. I can see the use case.

barrymavin
2018-06-11, 23:44
Hi Fabio,

I have extended this in 4.2Beta18 so that the cascading lists can contain multiple columns which can include keyLookup() or other calculations. See screen shot below.

1699

HankFay
2018-06-12, 01:14
Wonderful!

phabio
2018-06-12, 02:11
Fantastic

phabio
2018-06-19, 09:03
m_database = "database=southwind"
m_table = "table=products"
m_tabletitle = "tabletitle=Item Details"
m_selectcolumn = "selectcolumn=productid"
m_columncount = "columncount=1"
m_columntitles = "columntitles=Categories"
m_columns = "columns=categories,categoryid:categoryname|product s,productid,categoryid=%7B%7D"
m_columnsorderby = "columnsorderby=||"
m_picklist = "picklist=products|productid,productname|categoryid =%7B%7D"

m_par = m_database + "&" + m_table + "&" + m_tabletitle + "&" + m_selectcolumn + "&" + m_columncount + "&" + m_columntitles + "&" + m_columns + "&" + m_columnsorderby + "&" + m_picklist
? m_par


Lianja.showQueryPickerPanel("Browse Items", "", "southwind", "products", m_par, 900)


this, work fine.

the only left question is:
I've try with this:
m_picklist = "picklist=products|productid,productname|1=1"
on the bottom, I see all the "Products".. great
and the search work fine
1709

have you see if is possible to hide the top column and show only the picklist?

one suggestion..
if I set "columncount=-1"
you show only the picklist.. and the query are performed without to click on the header grid.. :-)

barrymavin
2018-06-19, 22:44
Thats not what its designed to do. You filter down through related data. Its not just a searchable grid.

phabio
2018-06-20, 02:12
Ok..
it's planned to create a searchable grid?
thanks
Fabio

barrymavin
2018-06-20, 02:59
Ok..
it's planned to create a searchable grid?
thanks
Fabio

If you submit an ER it will be considered but not in 4.2 which will ship this week. Itís already a massive release.

barrymavin
2018-06-21, 23:49
I have enhanced the QueryPicker so now it can display a searchable grid.

Just specify showcolumns=false in your parameters.

1710

And the result is...

1711

Bear in mind that this loads the whole grid in one go so that it is free text searchable.

phabio
2018-06-22, 02:23
Another Brick In The Wall...

great Barry!

do you have modified only the rsp or also the ShowQueryPickerPanel?

Fabio

barrymavin
2018-06-22, 07:06
Hi Fabio,

Itís a WebViewWidget. All I needed to do was edit the rsp file.

phabio
2018-06-22, 07:15
Good, I wait the next beta to test it.

Thanks
Fabio

phabio
2018-10-31, 13:28
Hi,
I'm triyng to use the QueryPicker with only the picklist



open database southwind

m_database = "database=southwind"
m_table = "table=products"
m_tabletitle = "tabletitle=product"
m_selectcolumn = "selectcolumn=productid"
m_columncount = "columncount=1"
m_columntitles = "columntitles=products"
m_columns = "columns=" + "products,productid,1=1"
m_columnsorderby = "columnsorderby="
m_picklist = "picklist=products|productid|1=1"
m_showcolumns = "showcolumns=false"

m_par = m_database + "&" + m_table + ;
"&" + m_tabletitle + ;
"&" + m_selectcolumn + ;
"&" + m_columncount + ;
"&" + m_columntitles + ;
"&" + m_columnsorderby + ;
"&" + m_picklist + ;
"&" + m_showcolumns


? m_par

Lianja.showQueryPicker("Ricerca progetti", "a=1", "southwind", "Products", m_par, 800)


but I got an error and the grid is empty.

where I wrong?

thanks
Fabio

barrymavin
2018-10-31, 20:45
Hi Fabio,

Remove the empty parameters and then remove each parameter one by one until you identify the issue. In my example above in this thread it is working as per the screenshot.

phabio
2018-11-01, 05:35
Ok, I try, but in the sample you have all the tables.

phabio
2018-11-01, 06:04
I get everytime this error
gio nov 1 11:03:18 2018
**** Lianja error ****
odata_read([/southwind/products&$select eq productid], [_00002ef000d7.tmp])
^
Unrecognized phrase/keyword near column 52

phabio
2018-11-02, 12:18
Strange..
this, work: 1 column and 1 detail


m_database = "database=SFM"
m_table = "table=documenti" // la table col massimo dettaglio
m_tabletitle = "tabletitle=Documenti" // descrizione table col massimo dettaglio
m_selectcolumn = "selectcolumn=ID" // il campo della tabella selezionata
m_columncount = "columncount=1" // numero finestrelle sopra al dettaglio
m_columntitles = "columntitles=Progetti" // titoli
m_columns = "columns=" + "progetti,clienteid:id:progetto" + "|documenti,descrizione,progettoid=%7B%7D"
m_columnsorderby = "columnsorderby=progetto|stato"
m_picklist = "picklist=documenti|progettoid,id,descrizione,caso, stato|progettoid=%7B%7D"
m_showcolumns = "showcolumns=false"

m_par = m_database + "&" + m_table + ;
"&" + m_tabletitle + ;
"&" + m_selectcolumn + ;
"&" + m_columncount + ;
"&" + m_columntitles + ;
"&" + m_columns + ;
"&" + m_columnsorderby + ;
"&" + m_picklist
/*
+ ;
"&" + m_showcolumns
*/

? m_par

Lianja.showQueryPicker("Ricerca progetti", "a=1", "SFM", "Clienti", m_par, 800)


this:


m_database = "database=SFM"
m_table = "table=documenti" // la table col massimo dettaglio
m_tabletitle = "tabletitle=Documenti" // descrizione table col massimo dettaglio
m_selectcolumn = "selectcolumn=ID" // il campo della tabella selezionata
m_columncount = "columncount=1" // numero finestrelle sopra al dettaglio
m_columntitles = "columntitles=Progetti" // titoli
m_columns = "columns=" + "progetti,clienteid:id:progetto" + "|documenti,descrizione,progettoid=%7B%7D"
m_columnsorderby = "columnsorderby=progetto|stato"
m_picklist = "picklist=documenti|progettoid,id,descrizione,caso, stato|progettoid=%7B%7D"
m_showcolumns = "showcolumns=false"

m_par = m_database + "&" + m_table + ;
"&" + m_tabletitle + ;
"&" + m_selectcolumn + ;
"&" + m_columncount + ;
"&" + m_columntitles + ;
"&" + m_columns + ;
"&" + m_columnsorderby + ;
"&" + m_picklist + ;
"&" + m_showcolumns

? m_par

Lianja.showQueryPicker("Ricerca progetti", "a=1", "SFM", "Clienti", m_par, 800)


return the error
ven nov 2 17:17:59 2018
**** Lianja error ****
odata_read([/SFM/documenti&$select eq progettoid,id,descrizione,caso,stato], [_0000375803a7.tmp])
^
Unrecognized phrase/keyword near column 55

I've added only the clause:
showcolumns=false

phabio
2018-11-02, 12:28
also with SouthWind data:


open database southwind

m_database = "database=southwind"
m_table = "table=products"
m_tabletitle = "tabletitle=product"
m_selectcolumn = "selectcolumn=productid"
m_columncount = "columncount=1"
m_columntitles = "columntitles=products"
m_columns = "columns=" + "products,productid,productname,1=1"
m_columnsorderby = "columnsorderby="
m_picklist = "picklist=products|productid,productname|1=1"
m_showcolumns = "showcolumns=false"

m_par = m_database + "&" + m_table + ;
"&" + m_tabletitle + ;
"&" + m_columntitles + ;
"&" + m_picklist + ;
"&" + m_columncount + ;
"&" + m_showcolumns

Lianja.showQueryPicker("Ricerca progetti", "a=1", "southwind", "Products", m_par, 800)


if I remove the "m_showcolumns", work

phabio
2019-05-07, 06:13
Hi,
can someone post a sample of a single column querypicker on a DB different from SouthWind?
thanks
fabio