Table View and Group By

Classic Servoy
1

Hi,

Was wondering how I get my form to show a table view based on SQL as per below such as when the user clicks a button it shows the Hour and Count as per the SQL.

Is it soemething like … however how do I get the table view to display the Hour and Count, do I create 2 global variables Hour and Count ? Can’t see how I’d then assign the SQL’s Hour and Count to the global variables.

		var query = "select hour(mor_time_t) as Hour, count(*) as Count "+
			"FROM from_message WHERE exit =" + exitVal + "group by Hour"
		
		var dataset = databaseManager.getDataSetByQuery(controller.getServerName(),query, null, 1000);
		controller.loadRecords(dataset)
2

There’s 2 ways of doing this using the SQL you mentioned.

  1. create a view in your DB.
  2. create a datasource in Servoy and build a form with the solutionModel using binding with the dataSource.

both solutions have their pro’s / con’s:
as for 1:

  • you need to have a pk in your result.
  • it’s a database result, so Servoy won’t be aware of any changes, therefore you need to refresh the foundset from the database evertime you show the form
  • view’s are not created when importing a solution on the server: do this manually BEFORE you import your solution.
  • creation is fast
  • building a form / attaching a view is as simple as using a normal table.

as for 2:

  • it’s a database result, so Servoy won’t be aware of any changes, therefore you need to recreate your datasource every time you show the form
  • solutionModel can give you a hard time as you have to play exactly by the rules. Once you know the trick it’s not so bad, though more work than building a form with formdesigner
  • solutionModel is very powerful
  • as you need to build your datasource from a dataset, you have the opportunity to alter your dataset AFTER the actual query, but BEFORE creating the datasource. This case complex queries that need some advanced SQL knowledge can be skipped and split into several single queries, which you can join in a new dataset.

Last but not least: you can try this using aggregates in Servoy.

Hope this helps

3

Option 3 use a html field and display as html
Option 4 You mention on a click. You could indeed popupate the results in a global if the result is for one set of data

4

Tried using a View as suggested:

  1. create view swift.v_from_message as select Route as R, count(*) as Count
    FROM swift.from_message group by R"

  2. created a new form to display the data using table view and assigned the v_from_message view as the table for the form

Now when form loads I get an error Cannot work without primaryKey on table ‘v_from_message’

Suggestions, I can use HTML etc but was hoping to do display the results without having to write code.

5

Yummy:
Cannot work without primaryKey on table ‘v_from_message’

You can solve this by setting a ‘row indentifier’ on your view from the database node within Servoy.