New post
Question
· Apr 24, 2018

How can I programmatically select a row on a zen.TablePane when I only know the row's value?

#ZEN #Caché

Hi -

I'm trying to figure out an elegant way to select a row on a table pane when I don't have an index, but I DO have a value.

My page has a typical tablePane (with a tableNavigator) which is being populated from a table of records, and the value for each row is the object instance ID for the class that has the rest of the columns being displayed. What I'm trying to do is add in a URL parameter for a targeted object ID that will populate the specific "fields" elsewhere on my page, which are the same fields that are populated when I do an onSelectedRow() from my tablePane. Populating the fields is easy, but I would like to also highlight (select programmatically) the corresponding row in my tablePane, but I have no idea if the appropriate row is currently rendered, or if it's even in the table... but if I CAN, I would like to highlight/select the row.

Does anyone have an example of how to do this?

Discussion (7)1
Log in or sign up to continue
Robert Cemper · Apr 24, 2018

Hi Chip,

If I understand it correct you have a value and look for the related rowID in your tablePane.

tablePanes get typically feeded by a well know SQL SELECT and it numbers its rows sequentially.

For ease of explanation,  I use  SELECT name,home_city TOWN FROM Sample.Person  to fill your tablePane

Let's look for TOWN = 'Denver'

SELECT *, %vid rowID from ( 
      SELECT name,home_city TOWN FROM Sample.Person
) WHERE TOWN %STARTSWITH 'DENVER'

Of course, there is still arithmetic required if you have several sections to calculate.
This just gives you the 'absolute' rowID, not the relative.
HTH, Robert

0 0
Chip Gore · Apr 24, 2018

My problem is that my table (which *is* filled via an SQL) can have multiple pages of "displayed sub-sets" (i.e. the Page Size of the table, controlled by the Table Navigator), and when I launch the page directly with an instance ID passed by URL value, I can load the form, but I don't see anyway to figure out which page and which row to "jump to" and "select" programmatically. (my use case is relatively simple, as I don't have any client side filters or sorting that is done, just a "result set being displayed"

0 0
Robert Cemper · Apr 24, 2018

yeah, table navigators chop the original resultset.

but with the absolute rowID (%vid) and the pageSize (pS)  you should get pageNum= %vid\pS+1
and relative rowID= %vid#pS

Or directly in SQL  with pageSize = 12

SELECT *, %vid AbsRowID,
                    CEILING(%vid/12) PageNum,
                   {fn MOD(%vid,12)} RelRowId from (
      SELECT name,home_city TOWN FROM Sample.Person
) WHERE TOWN %STARTSWITH 'DENVER'

you will keep the * away as you don't need the content

It's all based on the fact that the 'base'query always generates the same sequence of rows 
Just dynamic filters may break this. 

For paging also tablePane has to use the same mechanics. 
 

0 0
Robert Cemper · Apr 24, 2018

BTW. Class %ZEN.Component.querySource  still does it as iit did in past.

For more tricky manipulation you have

/// (optional) Name of Server-side callback method to call to create
/// a <class>%ResultSet</class> object.<br>
/// This must be the name of a server-only method in the page
/// class that contains this component.
Property OnCreateResultSet As %ZEN.Datatype.delegator(FORMALSPEC = "*tSC:%Status,pInfo:%ZEN.Auxiliary.QueryInfo", RETURNTYPE = "%ResultSet");

/// (optional) Name of Server-side callback method to call to execute the
/// <class>%ResultSet</class> object.<br>
/// This must be the name of a server-only method in the page
/// class that contains this table pane.
Property OnExecuteResultSet As %ZEN.Datatype.delegator(FORMALSPEC = "pRS:%Library.ResultSet,*tSC:%Status,pInfo:%ZEN.Auxiliary.QueryInfo", RETURNTYPE = "%Boolean");
 

0 0
Chip Gore · Apr 24, 2018

Success!!!

I created a callable ZenMethod function that allows me to pass in the table component ID, the SQL Table name and the Row ID, that will jump the table to the correct page and row to match the ID passed in.


Method jumpTable(componentID as %String, tableName As %String, id As %String, pageSize As %Integer) [ ZenMethod ]
{
  set SQLtxt = "SELECT *, CEILING(%vid/"_pageSize_") PageNum, {fn MOD(%vid,"_pageSize_")} RelRowId FROM ( SELECT ID FROM "_tableName_") WHERE ID = "_id
  set sql1 = ##class(%ResultSet).%New()
  do sql1.Prepare(SQLtxt)
  do sql1.Execute()
  while sql1.Next() {
    set page = sql1.Get("PageNum")
    set row = sql1.Get("RelRowId")
  }
  set Table = %page.%GetComponentById(componentID)
  set Table.selectedIndex = (row-1)
  set Table.currPage = page
}

This then can be used for any table on any zen page.


Thanks

0 0
Limyandi Vicotrico · Feb 8, 2021

Great work Chip, Robert

One question, are you using InterSystems IRIS or Cache?

I am using Cache and am not able to use the %VID, is there any alternative to the %VID value, as I need the row number (the %ID field is not a normal incremental ID).

SOLVED:

I just realised that I will need to use:
SELECT 
%VID FROM ( SELECT ID FROM TableName )

Thanks,

Rico.

0 0