Archive for the ‘Filters’ Category

Filters

Posted: 2017-01-17 in Filters

Q:
I would like to filter the data based on one field What delegate is best to do this? (grid section)
A:
you want to filter the data displayed in the grid when it is loaded
put it in the ready delegate.


Q:
can I use more than one “like” clause in filter?
this, not work..

Code:
set filter to des_banca like "*UNI*" and PROVFINAN like 'MO'

sab giu 18 19:06:27 2016
**** Lianja error ****
set filter to des_banca like "*UNI*" and PROVFINAN like 'MO'
^
Data type mismatch

in select, work

Code:
select * from banche where des_banca like "*UNI*" and PROVFINAN like '*MO*'

A:
You can use == as an alternative to LIKE.
the == or Like condition, is not the same of FoxPro.
The == operator in Lianja works the same as exactly equal to when it does not contain patter matching characters.

If it does contain pattern matching characters then a pattern match is performed.
The patterns can be %*?[a-z][~a-z]


Q:
Is possible to change the section filter on the fly?
A:
The Section Attribute ‘SQL statement‘ allows you specify a SQL statement to populate a virtualtable – so you can set the WHERE clause there. You can access the attribute read/write with Lianja.get(“sectionname”).sql
Q2:
Is possible to combine this with the custom search?
A2:
I would think the simplest way to do this would be to create a virtual table with a variable.
Change the variable, and update the cursoradaptor.
pseudo code

Code:
create virtualtable myVT..... as select * from mytable where type ='{m.mytype}'
m.mytype = "X"
ca = cursorAdapter("myVT")ca.cursorRefresh()

Q:
I have a page section with a grid and I wish to change to change the grid records filter programmatically through some footer menu actions
A:
Once the footer has been set up, you should be able to use code similar to:

Code:
oGrid = Lianja.Get("page.section").grid
oGrid.filter = "CUSTNUM=&ncustnum AND NAME='SMITH'"

A2:
Call this to change the filter.

Code:
Lianja.get("page1.section1").grid.filter =" ="

Call this to remove the filter

Code:
Lianja.get("page1.section1").grid.filter =""


Filters

Posted: 2015-11-07 in Filters

Q: 
how do I set the filter on the table dynamically from code? Just to try, I’ve write into the load of the section: 

Code:
Lianja.get("AbiCab").sql = [select * from abicab where companyId = "SOF02"]

A:
– Create a Form Section based on your virtualtable with a ‘where 1=0’ clause, e.g. I’ve created one on southwind!vorders.
– In the Form Section ‘ready’, set the sql attribute, e.g. I’ve got:

Code:
////////////////////////////////////////////////////////////////
// Event delegate for 'ready' event
proc page1_section1_ready()
        lianja.get("section1").sql = "select * from orders where customerid='A'" 
endproc


Still not sure what you’ve tried, but I have noticed that the sql attribute on a Form Section isn’t being picked up when the Section is first loaded. If that is the issue, try setting the sql to itself in the Section.activate or Section.ready, e.g.

Code:
lianja.get("section1").sql = lianja.get("section1").sql


The Section Attribute ‘SQL statement’ allows you specify a SQL statement to populate a virtualtable – so you can set the WHERE clause there. You can access the attribute read/write with Lianja.get(“sectionname”).sql



Q: 
Is there a way to filter the records to be displayed or edited?
A:
With the table open in the Data Workspace, switch to the Console Workspace, check that the table you want is selected, then issue a SET FILTER command, e.g.

Code:
select orders
set filter to customerid = "ALFKI"

Then switch back to the Data Workspace and click the Refresh button on the Data Grid.



Q:
In a javasscript application I have a grid populated by dragging a local table onto a grid section.
I want to filter the grid.
I would expect this to work

Code:
Lianja.get("page1.section1").where = "status >0";
Lianja.get("page1.section1").refresh();

A:
Just use use the filter attribute if this is a native table. “Where” only works with Virtual Tables as Hank points out.
In Web Apps all data access is done using OData calls to the Cloud Server which in turn generates and executes SQL statements that return JSON encoded data so setting the filter results in a requery being performed.



Q:
How will .filter work with VT’s in web apps? The VT will in many cases have a complex WHERE clause already, so simple substitution of a filter clause would be infelicitous
A: 
It will be appended on the end of the WHERE clause that is in the VT definition.
Apply it in a delegate.

Code:
Lianja.get("page1.section1").filter = "ordervalue gt 1000";
Lianja.get("page1.section1").refresh()


Q: 
I have a form which is created from a table. 
How do I write a delegate to set the active record that is being displayed on the form by specifying the primary key?
A:
Set the primary key as the search key field in the form section. Then in a delegate:

Code:
Lianja.showDocument("page: page1.section1?action=search&text=keytosearchfor")


Currently, I am trying to get the form section to display only certain records based on the value of a column. Following the GridFilter Sample App, I used the Filter attribute with startsWith(column_name, string) function. However, the functionality doesn’t seem to work as expected. 
Screenshot 2014-08-11 15.37.18The parent record and the related child record changes, but the child record is not filtered to records that only starts with ‘C’ for Caddtypadrs
The parent table is a virtual table, not sure does it matters
In my case, the column which I am trying to filter ‘Caddtypadrs’ is not the parent-key relation column. Cempnoee from the parent section, and caddnoee from the child section are the parent-key columns.
A:
You can’t filter on the search field as that is handled by the parent->child relationship.
Look at the “Virtual tables” example and see how the relationship is handled using {…} macros in the select statement.
Use a SQL SELECT with {…} macros then and it will only retrieve based on the query.



Q:
May I know how we filter picklist based on value in another field on the record in a form section or grid section?
Example, user keys in Division and the Department picklist only shows Departments related to that Division
A:
select workdesc from v_workitem where iif(maintdetinput.grpcodeid = 0,.t.,grpcodeid = maintdetinput.grpcodeid) order by upper(workdesc)
Although looking at the SQL query that cannot be optimized as it is using an iif() function. May be better to use {} macros so that the query is properly optimized resulting in better performance.
The UI is dynamic.

Code:
Lianja.get("page1.section1.field1").choicelist = "East,West,North,South"

or

Code:
Lianja.get("page1.section1.field1").choicelist = "select customerid from customers"

You can change the selections in the “dataChanged” delegate if you need to.