[Answers] Filter

**josipradnik** · 2015-02-26, 02:17

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.

The 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.

All topics in [Answers] alphabetically: http://www.lianja.com/community/show...ll=1#post12352

**josipradnik** · 2016-11-04, 02:27

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 ="<field> =<value>"

Call this to remove the filter

Code:

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

All topics in [Answers] alphabetically: http://www.lianja.com/community/show...p?2717-Answers

WJ · 2019-10-22, 02:13

Hi Josipradnik,

I am trying to create a filter picklist based on the value in another field. For example, if the user selects the owner as of the contact title on the first picklist, the second picklist will only display a list of customer names whose contact title is the owner. May I know what mistake I made in my code?

Name: error 01.PNG
Views: 250
Size: 16.2 KB

Name: error 01.PNG
Views: 250
Size: 16.2 KB

Name: error 02.PNG
Views: 221
Size: 5.8 KB

**barrymavin** · 2019-10-22, 02:56

"txtboxtwo" should be a reference to the UI element not the literal value of the choicelist.

Code:

var cb = Lianja.getElementByID("page1.section1.field2");
cb.choicelist = "select .... etc";

**barrymavin** · 2019-10-22, 03:01

datachanged is the wrong delegate also.

use change of interactivechange.

See doc for delegates:
https://www.lianja.com/doc/index.php/Custom_Delegates

WJ · 2019-10-22, 03:10

Hi Barry,

Thanks for correcting my mistakes

WJ · 2019-10-22, 06:26

Hi Barry,

I just corrected some of the mistakes I made in my code but it still doesn't work after testing it at the desktop view. So, can you please help me to check my code again. Your help will be greatly appreciated.

Name: error 03.PNG
Views: 159
Size: 17.1 KB

Name: error 03.PNG
Views: 159
Size: 17.1 KB

Thanks & Best Regards

**barrymavin** · 2019-10-22, 11:13

What does not work. Can you elaborate.

You can put some debugging in to help yourself using console.log()

WJ · 2019-10-22, 18:11

When I execute the code in desktop view, the contact name field still display as a textbox instead of a filter picklist. May I know which part of the code do I need to make some changes?