2, two, 2 tables in 1!

Classic Servoy
1

I want to show rows from two tables in one list, hanging out cheek to jowl in various sort orders. One could do this by having a third table that contains the other tables’ foreign keys. But then it would seem that calc fields would be required in the new table for display.

For example: table A has names of persons, table B has names of groups, and the form will show a list of people and groups, appearing to be in the same column and sorted by alpha (or by type (i.e. person or group) then alpha).

The solution I see is to have all in one table, with a marker for type. Or, just go ahead and have the third table and the calcs. But I wonder, as a general question, if there is another way within Servoy to show data from two tables in one column. The problem seems to pop up from time to time.

Thanks…

Jim

2

You can sort on related fields. So you don’t have to use a third table that for that.
In the sort dialog you can select the available relations for that table you are looking at and sort on those related fields.

In a method you can do this as well with:
controller.sort(‘relationshipname.columnname asc’);

Or even 3 tables deep:
controller.sort(‘relationshipname1.relationshipname2.columnname asc’)

I hope this answers your question.

3

Or you can write a custom SQL query using the UNION command and then display the results in html.

I do this in my job management solution to display one report for a job which combines sales order lines, sales invoice lines, purchase order lines, purchase invoice lines and internal timesheet entries.

4

Or you can write a custom SQL query using the UNION command and then display the results in html.

Can methods be triggered from this list, or would it be just for display? E.g., can the user click on one of the items in the list and run a method based on one or more aspects of the underlying data (like go to a particular record on a particular form)?

5

Absolutely. Basically you run a method to

  1. Create the SQL query as a variable called ‘query’

  2. Convert to a dataset (using var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, maxRecords)

  3. Loop through the dataset and create html table rows for each row of the dataset. The format for this is something like…

for( var i = 1 ; i <= dataset.getMaxRowIndex() ; i++ )
{
	dataset.rowIndex = i
	html += '<tr><td><a href=\"javascript:load_record('+dataset[1]+')\">'+dataset[2]+'</a></td>'+
	'<td><a href="javascript:load_record('+dataset[1]+')">'+dataset[3]+'</a></td>'+
	'</tr>';
}