EJS TreeGrid Features tutorial

TreeGrid features tutorial

Previous <<< Advanced tutorial Next >>> Calculations tutorial Back to Index

These tutorials show using advanced features of TreeGrid.

They show sorting, filters, grouping, searching and advanced filters.

Every example can be used on single page with this structure:

<html>
    <head>
        <script src="../Grid/GridE.js"> </script>
    </head>
    <body>
        <div style="width:100%;height:100%;"> 
           <treegrid Data_Url="AdvancedX ... .xml"> </treegrid>
        </div>
    </body>
</html>

If you modify in input data some attributes saved to cookies, increase <Cfg> attribute Version to suppress old configuration.

Sorting

Show example

Only variable rows are sorted. In tree are children rows sorted independently. Sorting can be done according up to 3 columns. The maximal column count can be set by MaxSortColumns attribute.
Values are always sorted according column type (string or number sort - see column 'E' does number sort).
Predefined sorting you can control by <Cfg> attributes Sorted, SortCols and SortTypes. These values are used only for first time, next are loaded from cookies (you can change this behavior by ...Lap, Version or id attributes).
The way of string sorting you control by column attributes SortType and WhiteChars. You can ignore case (column 'B'), ignore spaces (column 'C'), ignore custom white spaces (column 'D').
To not sort row's children set parent row's CanSort ='0', to not sort in root set it in <Root> tag.
To set row's fixed position in sorting or specify ranges that are sorted independently, set row's SortPos attribute.
To set order when no sort is done or the actual sort is not so specific, set DefaultSortCol attribute.
You can also provide predefined values for sorting by cell attributes SortValue and SortDescValue to ensure the sort order independently on cell value or for special cell types like Html, Link or Img.
You can control sorting dynamically by API event OnGetSortValue or you can do your own sorting in event OnSort or run your custom action after sort finish in OnSortFinish.
By <Cfg> attribute SortIcons you can set if columns sorts after click to whole caption or icon only or to hide icons fully (you can modify CSS style GHeader to change cursor).
See all available settings

Show data source

<Grid>
  <Cfg id='Features1' Sorted='1' 
       SortCols='D,E,A' SortTypes='0,1,0' 
       SortIcons='1'/>
  <Cols>
    <C Name='A' Type='Text' Width='50'/>
    <C Name='B' Type='Text' Width='50' 
       SortType='4'/>
    <C Name='C' Type='Text' Width='50' 
       SortType='8'/>
    <C Name='D' Type='Text' Width='50' 
       SortType='12' WhiteChars=' _'/>
    <C Name='E' Type='Int' Width='50'/>
  </Cols>
  <Body>
    <B>
      <I SortPos='1' 
         A='Top' B='ZZZ' C='AAA' 
         D='AAA' E='ZZZ' EType='Text' />   
      <I SortPos='2'
         A='Second' B='AAA' C='ZZZ' 
         D='AAA' E='ZZZ' EType='Text'/>   
      <I A='AAA1' B='AAA1' C='AAA1' 
         D='AAA1' E='AAA1' EType='Text'/>
      <I A='BBB2' B='BBB2' C='BBB2' 
         D='BBB2' E='BBB2' EType='Text'/>
      <I A='aaa3' B='aaa3' C='aaa3' 
         D='aaa3' E='aaa3' EType='Text'/>
      <I A='bbb5' B='bbb4' C='bbb3' 
         D='bbb2' E='bbb1' EType='Text'/>
      <I A='bbb1' B='bbb2' C='bbb3' 
         D='bbb4' E='bbb5' EType='Text'/>
      <I A='  aaa5' B='  aaa5' 
         C='  aaa5' D='  aaa5' 
         E='  aaa5' EType='Text'/>
      <I A=' bbb6' B=' bbb6' C=' bbb6' 
         D=' bbb6' E=' bbb6' 
         EType='Text'/>
      <I A='_a_a_a7' B='_a_a_a7' 
         C='_a_a_a7' D='_a_a_a7' 
         E='_a_a_a7' EType='Text'/>
      <I A='_aaa8' B='_aaa8' C='_aaa8' 
         D='_aaa8' E='_aaa8' 
         EType='Text'/>
      <I A='10' AType='Int' B='10' 
         BType='Int' C='10' CType='Int' 
         D='10' DType='Int' E='10'/>
      <I A='20' AType='Int' B='20' 
         BType='Int' C='20' CType='Int' 
         D='20' DType='Int' E='20'/>
      <I A='100' AType='Int' B='100' 
         BType='Int' C='100' CType='Int' 
         D='100' DType='Int' E='100'/>
      <I A='200' AType='Int' B='200' 
         BType='Int' C='200' CType='Int' 
         D='200' DType='Int' E='200'/>
      <I SortPos='-1' 
         A='Bottom' B='ZZZ' C='AAA' 
         D='AAA' E='ZZZ' EType='Text' />
    </B>
  </Body>
</Grid>

Filters

Show example

Filter you can add as fixed row <Filter> (into <Head> or <Foot> section).
Predefined filter values you can set as cell values in filter and filter operator as cell attribute Filter. Predefined values are used only if configuration is not loaded from cookies.
To let a user to filter according to more values in one column or by number or date ranges set filter cell attribute Range="1". It affects also calendar and Defaults dialogs to select more items.
If you want to not filter some row, set its attribute CanFilter="0".
If you want to not filter in whole column, set the column attribute CanFilter="0".
If you want to not filter some row by specified column, set the cell attribute CanFilter="0". In this case you have to set also column's attribute CanFilter="2".
If you want to hide rows in tree if they has hidden all children set row's attribute CanFilter="2". In this case you have to set also <Cfg> attribute FilterEmpty="1".
If you want to show all rows in tree that fulfill the condtion (to show parent rows that contain some children that fulfill the condition) set StandardFilter="2".
You can let a user to choose from given list of values or some or all values in this column in other rows by Button="Defaults".
You can let a user to only input or select the filter value(s) by Defaults dialog or calendar and hide the filter menu by filter cell attribute ShowMenu="0". You can also predefine the filter operator by filter cell attribute DefaultFilter. For Enum type you can define item to remove filter operator by filter cell attribute FilterOff.
You can specify which operators will be shown in filter menu by filter cell attribute MenuItems.
To get count of all or filtered rows you can use function countrows.
You can also provide predefined values for filtering by cell attribute FilterValue to run filter independently on cell value.
You can control filtering dynamically by API event OnGetFilterValue and / or OnRowFilter or you can do your own sorting in event OnCanFilter.
See all available settings

Show data source

<Grid>
  <Cfg id='Features2' MainCol='A' 
    FilterEmpty='1'/>
  <Cols>
    <C Name='A' Width='110'/>
    <C Name='B' Type='Int' Width='40'/>
    <C Name='C' Type='Float' Width='60'/>
    <C Name='D' Type='Date' Width='100'/>
    <C Name='E' Type='Enum' Width='60'
       Enum='|alfa|beta|gama|delta'/>
  </Cols>
  <Head>
    <I Kind='Filter' A='F' AFilter='11'
       AButton='Defaults' ARange='1' 
       ADefaults='|*RowsVariable'
       DRange='1'/>
  </Head>
  <Body>
    <B>
      <I A='One' B='1' C='1.0' 
         D='1/1/2000' E='0'/>
      <I A='Two' B='2' C='-1.0' 
         D='1/11/2000' E='1'/>
      <I A='Three' B='3' C='121.3' 
         D='6/3/1999' E='3'/>
      <I A='Four' B='4' C='-331' 
         D='12/31/2006' E='2'/>
      <I A='Five' B='5' C='16.43' 
         D='5/15/2005' E='1' 
         CanFilter='1'>
        <I A='Five One' B='6' C='1000' 
           D='3/7/2002' E='1'/>
        <I A='Five Two' B='7' C='453.2' 
           D='8/21/2002' E='1'/>
        <I A='Five Three' B='8' C='-3000' 
           D='7/12/2003' E='3'/>
        <I A='Five Four' B='9' C='567' 
           D='5/25/2006' E='0'/>
        <I A='Five Five' B='10' C='-0.03' 
           D='3/17/2100' E='2'/>
      </I>
    </B>
  </Body>
  <Foot>
    <I Calculated='1' CanEdit='0' 
       AFormula = '"Displayed: "+countrows(4) + " / " + countrows(5)' 
       BType='Text' CType='Text' EType='Text'/>
  </Foot>
</Grid>

Grouping

Show example

You can group rows in tables and even in trees. In tree you can group only one level from existing tree, usually root level, the first level of rows with CanGroup='1'. In tree you cannot ungroup rows already positioned in tree in input XML data.
User interface for grouping you can provide by grouping row, a solid space row <Group>. Where the row is placed is not set by its section, but by its attribute Space.
User can group rows in two ways - by selecting predefined grouping from combo on grouping row (see attributes List, ListWidth, ListCustom, Cols, Types) or by dragging column's caption to grouping row (see attribute Custom). You can permit one or both methods by setting appropriate attributes of the grouping row.
The rows are grouped in tree as children of new added rows. These rows have Def='Group'. Therefore by defining the default row named "Group" you can preset attributes of group rows, usually hide other cell values by Visible attribute or use some aggregate function.
You can pre-select grouping by <Cfg> attributes GroupCols, GroupTypes, GroupMain.
If you use server paging, you have to provide your own grouping on server according to sent attributes GroupCols and GroupTypes, similarly to sorting.
You can suppress grouping according to some column by CanGroup attribute and also you can more control grouping by column attribute GroupType.
To control grouping from client script you can use OnGroup and OnGroupFinish events and DoGrouping API method.

Show data source

<Grid>
  <Cfg id='Features3' 
    GroupMain='A' GroupCols='C'/>
  <Cols>
    <C Name='A' Width='110'/>
    <C Name='B' Type='Int' Width='40'/>
    <C Name='C' Type='Float' Width='60'/>
    <C Name='D' Type='Date' 
       Width='100'/>
    <C Name='E' Type='Enum' Width='60'
      Enum='|alfa|beta|gama|delta'/>
  </Cols>
  <Def>
    <D Name='Group'
       Calculated='1' CalcOrder='B'
       ACanEdit='1' BFormula='sum()' 
       CVisible='0' DVisible='0' 
       EVisible='0'
       />
  </Def>
  <Head>
    <I Kind='Group' 
       List='|None|By A|By A&amp;B|By C' 
       ListCustom='Custom'
       ListWidth='70' 
       Cols='||A|A,B|C'
       Custom='1'
       />
  </Head>
  <Body>
    <B>
      <I A='One' B='1' C='1.0' 
         D='1/1/2000' E='0'/>
      <I A='One' B='1' C='1.0' 
         D='1/11/2000' E='1'/>
      <I A='One' B='2' C='2.0' 
         D='6/3/1999' E='3'/>
      <I A='Two' B='1' C='7' 
         D='12/31/2006' E='2'/>
      <I A='Two' B='1' C='7' 
         D='5/15/2005' E='1'/>
      <I A='Three' B='1' C='12' 
         D='3/7/2002' E='1'/>
      <I A='Four' B='1' C='12' 
         D='8/21/2002' E='1'/>
      <I A='Four' B='2' C='1.0' 
         D='7/12/2003' E='3'/>
      <I A='Four' B='3' C='1.0' 
         D='5/25/2006' E='0'/>
      <I A='Five' B='1' C='1.0' 
         D='3/17/2100' E='2'/>
    </B>
  </Body>
</Grid>

Searching and advanced filters

Show example

You can search in TreeGrid cells strings like Google or by expression like TreeGrid formula. Click to Help button to see the syntax.
The found rows or cells can be filtered, selected, colored or focused.
Search interface can be provided by space row <Search>. You can have more <Search> rows to provide more functions and settings for search.
The search setting is in <Cfg> tag, attributes SearchExpression, SearchAction, SearchType, SearchMethod, SearchDefs, SearchCols. You can set these attributes also by you custom user interface or script to better control searching see also Special rows tutorial.
If you use server paging, you have to provide your own searching on server according to sent attributes listed above, similarly to sorting.
You can suppress search according to some column by CanSearch attribute.
To control searching from client script you can use OnSearch and OnSearchFinish state events, OnRowSearch control event and DoSearch API method.

Show data source

<Grid>
   <Cfg id="Features4" ConstHeight="1" 
      SaveAttrs="S3,List,S5,List,S6,T1,S6,T2,S6,E1,S6,E2"/>
   <Cols>
      <C Name="id" Type="Text"/>
      <C Name="A" Type="Text"/>
      <C Name="B" Type="Text"/>
      <C Name="C" Type="Float"/>
      <C Name="D" Type="Enum" 
         Enum="|Zero|One|Two|Three|Four|Five|Six|Seven|Eight|Nine|Ten"/>
      <C Name="E" Type="Date"/>
   </Cols>
   <Header id="id" A="Text1" B="Text2"
      C="Float" D="Enum" E="Date"/>
   <Body>
      <B>
         <I id="r01" A="James Smith" B="Paris" C="10.3" D="1" E="1/1/2000"/>
         <I id="r02" A="Mark Jones" B="London" C="12" D="2" E="6/25/2007"/>
         <I id="r03" A="Eric Walker" B="Berlin" C="-10.3" D="1" E="1/1/2000"/>
         <I id="r04" A="James King" B="London" C="1200" D="6" E="12/31/1999"/>
         <I id="r05" A="Henry Smith" B="Rome" C="0.03" D="8" E="6/24/2007"/>
         <I id="r06" A="Carl Hill" B="Paris" C="12.45" D="0" E="4/8/2004"/>
         <I id="r07" A="Mark Brown" B="Lisboa" C="0.345" D="10" E="10/20/2001"/>
         <I id="r08" A="Joe Walker" B="Berlin" C="-12" D="3" E="10/20/2001"/>
         <I id="r09" A="Eric Jones" B="Prague" C="-0.345" D="4" E="4/16/1994"/>
         <I id="r10" A="James Smith" B="Wroclaw" C="4560" D="10" E="6/25/2007"/>
         <I id="r11" A="Jack Reed" B="Madrid" C="1200" D="2" E="4/13/2010"/>
         <I id="r12" A="Mark Brown" B="Budapest" C="10.3" D="7" E="5/6/2002"/>
         <I id="r13" A="Jack Rogers" B="Moscow" C="12" D="1" E="2/18/1999"/>
         <I id="r14" A="James Davis" B="Prague" C="-0.345" D="9" E="4/12/2006"/>
         <I id="r15" A="Fred James" B="Moscow" C="12.46" D="9" E="3/25/2005"/>
         <I id="r16" A="Billy Smith" B="Rome" C="-1200" D="2" E="1/24/2000"/>
      </B>
   </Body>
   <Solid>
      <I Space="4" Html="&lt;DIV style='font-size:11px;padding-left:5px;color:blue;'>Simple searching, filter rows after input expression&lt;DIV>"/>
      <Search id="S1" Space="4" 
         Cells="Expression" 
         ExpressionAction="Filter"
         />
      <I Space="4" Html="&lt;DIV style='font-size:11px;padding-left:5px;color:blue;'>Simple searching with buttons for actions&lt;DIV>"/>
      <Search id="S2" Space="4" 
         Cells="Expression,Filter,Select,Mark,Find,Clear,Help"       
         />
      <I Space="4" Html="&lt;DIV style='font-size:11px;padding-left:5px;color:blue;'>Simple searching with button and combo for actions&lt;DIV>"/>
      <Search id="S3" Space="4" 
         Cells="Expression,Actions,Search,Clear"
         />
      <I Space="4" Html="&lt;DIV style='font-size:11px;padding-left:5px;color:blue;'>Set specific attributes for searching&lt;DIV>"/>
      <Search id="S4" Space="4" 
         Cells="Cols,Case,Type"
         ColsLabel="Columns"
         ColsDefaults="|All|Text1|Text2|Text1 &amp; Text2|Float|Enum|Date"
         ColsCols="||A|B|A,B|C|D|E"
         ColsToolTip="Searches only in selected columns"
         CaseLabel="Case&amp;nbsp;sensitive"
         TypeLabel="Search&amp;nbsp;in&amp;nbsp;cells"
         NoColor="1"
         />
      <I Space="4" Html="&lt;DIV style='font-size:11px;padding-left:5px;color:blue;'>Predefined filters&lt;DIV>"/>
      <Search id="S5" Space="4" 
         Cells="List,Actions"
         ListWidth="150" 
         ListDefaults="|None|This year|Last year|April|Positive float|Rome|London or Paris|James from London|Eight or less then four"
         ListExpressions="@@Date>=1/1/2007 AND Date&lt;1/1/2008@Date>=1/1/2006 AND Date&lt;1/1/2007@(new window.Date(Date)).getMonth()==3@Float>=0@Text2 = Rome@Text2==London or Text2==Paris@Text2==London and Text1 starts James@Enum==8 || Enum&lt;4"
         ActionsAction="Refresh"
         />
      <I Space="4" Html="&lt;DIV style='font-size:11px;padding-left:5px;color:blue;'>Selects rows according to more filters&lt;DIV>"/>
      <I Space="4" Html="&lt;DIV style='font-size:11px;padding-left:5px;font-style:italic;'>Cannot be done by Search row; must be done by standard Space row&lt;DIV>"/>
      <I id="S6" Space="4" 
         Cells="T1,L1,T2,L2,E1,L3,E2,Select"
         T1Type="Select" T1="Any" T1Width="65" 
         T1Defaults="|Any|*RowsAllColB*"
         T2Type="Select" T2="Any" T2Width="65"
         T2Defaults="|Any|*RowsAllColB*"
         E1Type="Select" E1="Any" E1Width="55"
         E1Defaults="|Any|Zero|One|Two|Three|Four|Five|Six|Seven|Eight|Nine|Ten"
         E2Type="Select" E2="Any" E2Width="55"
         E2Defaults="|Any|Zero|One|Two|Three|Four|Five|Six|Seven|Eight|Nine|Ten"
         L1="or" L1Width="17"
         L1CanFocus="0" L1NoColor="1"
         L2="and" L2CanFocus="0"
         L2Width="24" L2NoColor="1"
         L3="or" L3CanFocus="0"
         L3Width="17" L3NoColor="1"
         SelectType="Button"
         SelectCaption="Select"
         SelectAction="
            var t='',e='';
            if(T1!='Any') t+='Text2='+T1;
            if(T2!='Any') t+=(t?' or ':'')+'Text2='+T2;
            if(E1!='Any') e+='Enum='+choose(E1,split(Row['E1Defaults']),[null,0,1,2,3,4,5,6,7,8,9,10]);
            if(E2!='Any') e+=(e?' or ':'')+'Enum='+choose(E2,split(Row['E2Defaults']),[null,0,1,2,3,4,5,6,7,8,9,10]);
            if(t&amp;&amp;e) t='('+t+')and('+e+')';
            else if(e) t=e;
            Grid.SearchExpression=t;
            Grid.DoSearch('Select');
            "
         NoColorState="1"
         />
      

   </Solid>
</Grid>

Previous <<< Advanced tutorial Next >>> Calculations tutorial Back to Index