# grouping layout **URL:** \ **Category:** Classic Servoy\ **Created:** [September 16, 2005, 11:32pm UTC](https://forum.servoy.com/t/grouping-layout/4762 "2005-09-16T23:32:31Z")\ **Posts on this page:** 11\ **Page:** 1
**Author:** ![sammyzheng](https://avatars.discourse-cdn.com/v4/letter/s/53a042/32.png) [@sammyzheng](https://forum.servoy.com/u/sammyzheng)\ **Post date:** [September 16, 2005, 11:32pm UTC](https://forum.servoy.com/t/grouping-layout/4762/1 "2005-09-16T23:32:31Z")
I need to have a layout that shows grouped information. Serial ID Data 1 PASS 1 PASS 1 PASS 1 PASS 1 PASS 2 FAIL 2 FAIL 2 FAIL 2 FAIL 2 FAIL Is it possible to just show 2 rows? Serial ID DATA 1 PASS 2 FAIL ---
**Author:** ![ROCLASI](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/roclasi/32/4371_2.png) [@ROCLASI](https://forum.servoy.com/u/ROCLASI)\ **Post date:** [September 16, 2005, 11:43pm UTC](https://forum.servoy.com/t/grouping-layout/4762/2 "2005-09-16T23:43:22Z")
You could load the form with a custom query. From the top of my head…: ```auto var query = 'select id from table where condition=? group by column1, column2 order by column1;'; var ds = databaseManager.getDataSetByQuery(controller.getServerName(), query, [yourCondition], 1000); controller.loadRecords(ds); ``` Hope this helps. ---
**Author:** ![sammyzheng](https://avatars.discourse-cdn.com/v4/letter/s/53a042/32.png) [@sammyzheng](https://forum.servoy.com/u/sammyzheng)\ **Post date:** [September 16, 2005, 11:51pm UTC](https://forum.servoy.com/t/grouping-layout/4762/3 "2005-09-16T23:51:13Z")
> ROCLASI: > You could load the form with a custom query. > > From the top of my head…: > > ```auto > var query = 'select id from table where condition=? group by column1, column2 order by column1;'; > > ``` var ds = databaseManager.getDataSetByQuery(controller.getServerName(), query, [yourCondition], 1000); controller.loadRecords(ds); > ```auto > > Hope this helps. > > ``` Thanks for alwasy getting back to my questions so quickly. When you say load a form with custom query, do i have to create a new form, leave it blank? then somehow attach the query to the form? I’m not clear how this work… ---
**Author:** ![ROCLASI](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/roclasi/32/4371_2.png) [@ROCLASI](https://forum.servoy.com/u/ROCLASI)\ **Post date:** [September 17, 2005, 2:36am UTC](https://forum.servoy.com/t/grouping-layout/4762/4 "2005-09-17T02:36:57Z")
You can use the form you are using now. By using this code (ofcourse it’s pseudo code so adjust it to your needs) you load the data in that form. Kinda like controller.loadAllRecords() will load all data from that table. Hope this helps. ---
**Author:** ![sammyzheng](https://avatars.discourse-cdn.com/v4/letter/s/53a042/32.png) [@sammyzheng](https://forum.servoy.com/u/sammyzheng)\ **Post date:** [September 22, 2005, 10:25pm UTC](https://forum.servoy.com/t/grouping-layout/4762/5 "2005-09-22T22:25:10Z")
> ROCLASI: > You can use the form you are using now. > By using this code (ofcourse it’s pseudo code so adjust it to your needs) you load the data in that form. > Kinda like controller.loadAllRecords() will load all data from that table. > > Hope this helps. This is the first time i used the getDataSetByQuery and it’s acting so wierd. I do a simple query such as “select serial\_no from table1 where lotnum=10001” yet it pulls up multiple lot #s, other than 10001. It’s as if the SQL commands doesn’t even work. i followed the same syntax as below. But, if i do just "select \* from table1 where lot\_num=10001, the queries come out correct. Does it have something to do with what fields i place on the existing form? or does the SQL query execute regardless of what fields I put in the form? Please help. thanks, var query = ‘select id from table where condition=? group by column1, column2 order by column1;’; var ds = databaseManager.getDataSetByQuery(controller.getServerName(), query, [yourCondition], 1000); controller.loadRecords(ds); ---
**Author:** ![ROCLASI](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/roclasi/32/4371_2.png) [@ROCLASI](https://forum.servoy.com/u/ROCLASI)\ **Post date:** [September 22, 2005, 10:37pm UTC](https://forum.servoy.com/t/grouping-layout/4762/6 "2005-09-22T22:37:08Z")
> sammyzheng: > This is the first time i used the getDataSetByQuery and it’s acting so wierd. > > I do a simple query such as “select serial\_no from table1 where lotnum=10001” yet it pulls up multiple lot #s, other than 10001. It’s as if the SQL commands doesn’t even work. i followed the same syntax as below. But, if i do just "select \* from table1 where lot\_num=10001, the queries come out correct. You do need to do the GROUP BY which I put in the example. The GROUP BY does the magic. Also make sure you do the ORDER BY. Servoy kinda requires it. To test your query put it in iSQL (if you use Sybase) or your favorite query tool so you know that the SQL works or not. > sammyzheng: > Does it have something to do with what fields i place on the existing form? or does the SQL query execute regardless of what fields I put in the form? You only select the primary key in the query so Servoy can load that foundset. Then it can show any field of the table so it doesn’t matter what field you put on the form. Hope this helps ---
**Author:** ![sammyzheng](https://avatars.discourse-cdn.com/v4/letter/s/53a042/32.png) [@sammyzheng](https://forum.servoy.com/u/sammyzheng)\ **Post date:** [September 23, 2005, 4:15pm UTC](https://forum.servoy.com/t/grouping-layout/4762/7 "2005-09-23T16:15:06Z")
> ROCLASI: > > > sammyzheng: > > This is the first time i used the getDataSetByQuery and it’s acting so wierd. > > > > I do a simple query such as “select serial\_no from table1 where lotnum=10001” yet it pulls up multiple lot #s, other than 10001. It’s as if the SQL commands doesn’t even work. i followed the same syntax as below. But, if i do just "select \* from table1 where lot\_num=10001, the queries come out correct. > > You do need to do the GROUP BY which I put in the example. > The GROUP BY does the magic. > Also make sure you do the ORDER BY. Servoy kinda requires it. > > To test your query put it in iSQL (if you use Sybase) or your favorite query tool so you know that the SQL works or not. > > > sammyzheng: > > Does it have something to do with what fields i place on the existing form? or does the SQL query execute regardless of what fields I put in the form? > > You only select the primary key in the query so Servoy can load that foundset. Then it can show any field of the table so it doesn’t matter what field you put on the form. > > Hope this helps Darn, it’s still not working. The funny thing is, i’m on SQL server and query works perfect on SQL server, but when i try to execute it on Servoy, it’s not working. I’m even doing something super simple. What works is : select \* from table1 where lot\_no = 1001 But once i put select serial\_no from table1 where lot\_no = 1001, that’s why it pulls lot\_no’s 1001, 1003,1005. WEIRD ---
**Author:** ![ROCLASI](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/roclasi/32/4371_2.png) [@ROCLASI](https://forum.servoy.com/u/ROCLASI)\ **Post date:** [September 26, 2005, 3:27am UTC](https://forum.servoy.com/t/grouping-layout/4762/8 "2005-09-26T03:27:33Z")
Can you show me the complete method you use to load the data ? ---
**Author:** ![sammyzheng](https://avatars.discourse-cdn.com/v4/letter/s/53a042/32.png) [@sammyzheng](https://forum.servoy.com/u/sammyzheng)\ **Post date:** [September 26, 2005, 4:27pm UTC](https://forum.servoy.com/t/grouping-layout/4762/9 "2005-09-26T16:27:33Z")
> ROCLASI: > Can you show me the complete method you use to load the data ? //var query = ‘select serial\_no, min(pass\_fail1), min(pass\_fail2), min(pass\_fail3) from dimension where lot\_num = ’ + globals.g\_main\_lot\_num + ’ group by serial\_no order by serial\_no’ var query = "select serial\_no from dimension where lot\_num = " + globals.g\_main\_lot\_num + " " globals.zgtest1 = query var ds = databaseManager.getDataSetByQuery(controller.getServerName(),query,null,1000); controller.loadRecords(ds); in Query Analyzer, the first //var query works perfectly. But doing that in servoy cuases really wierd results. I’m hoping someone in Servoy world can figure this issue out for me. ---
**Author:** ![cybersack](https://avatars.discourse-cdn.com/v4/letter/c/7cd45c/32.png) [@cybersack](https://forum.servoy.com/u/cybersack)\ **Post date:** [October 18, 2005, 4:12am UTC](https://forum.servoy.com/t/grouping-layout/4762/10 "2005-10-18T04:12:46Z")
With Robert and Maarten’s help I have almost achieved what I want. I have attached a solution. Import it. Open it. The form ''media\_application\_tab\_swapping" contains 3 basic features: 1. the 3 fields from media\_application\_tab\_swapping 2. a portal from the relationship ‘media\_application\_to\_media’. This has a relationship: media\_application.media\_application\_id=media.foreign\_key and media\_application.table\_name=media.foreign\_table 1. a relationless tab-panel showing a form named “\_BlueForm” (which shows data from table ‘media’) Now, when the solution is opened, the portal and the found set in the \_BlueForm inserted in the tab-panel are ‘in-sync’. As you click on a portal row, the \_BlueForm record selection is updated; as you navigate \_BlueForm, the portal row selection is updated. Now click ‘swap form in tab pane’. Select \_BlueForm. The series of methods invoked here can be easily traced starting with the global method ‘selectDemo\_form\_swapping’ (only global methods are used here). Now, however, navigation events in the \_Blue(or \_Green)Form do not update the portal; and like wise, portal row selection does not navigate the embedded form. How do I maintain this ‘sync’ behaviour. Obviously it has something to do with the found set, but I can’t figure it out. cheers J ---
**Author:** ![maarten](https://avatars.discourse-cdn.com/v4/letter/m/ecccb3/32.png) [@maarten](https://forum.servoy.com/u/maarten)\ **Post date:** [October 18, 2005, 7:38am UTC](https://forum.servoy.com/t/grouping-layout/4762/11 "2005-10-18T07:38:27Z")
Did you get my last mail Julian. (17 Oct) I made a suggestion in there, how to keep stuff in sync but I didn’t get a reply.