[Answers] Filter

[josipradnik]

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:

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:

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

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.

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

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.

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:

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.

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

or

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