Hi Nicolas,
Welcome to Servoy talk!
Is there anything to edit queries spanning more than one table and its related child tables? If so how to view them?
Go into the method editor and check out the Databasemanager tree.
You can do lot's of SQL stuff here, including locking and transaction control.
rightclicking on a method gives you some sample code to figure them out.
Here's an example:
//get a dataset based on query
var maxReturedRows = 10;//usefull to limit number of rows
var query = 'select c1,c2,c3 from test_table';//do not use '.' or special chars in names or aliases if you want to access data by name
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, maxReturedRows);
//place in label: elements.myLabel.text = '<html>'+dataset.getAsHTML()+'</html>';
//example to calc a strange total
global_total = 0;
for( var i = 1 ; i <= dataset.getMaxRowIndex() ; i++ )
{
dataset.rowIndex = i;
global_total = global_total + dataset.c1 + dataset[3];
}
//example to assign to dataprovider
//employee_salary = dataset.getValue(row,column)
In case of joins (or so-called Many to Many relationships) how is it possible to display fields from the other Many table? For example in an Order form, how to display the product name, reference, etc. in the portal lines. From page 71 I have the impression it's Calculations that make it possible.
when using portals you can indeed only go 1 level deep. (unless you use calcs of course)
But you also have tabpanels in Servoy, where you can go down multiple levels, with complete scripting freedom within each level.
In your example I would:
build a tabpanel in the orderform that displays an other form (line items list) based on orders_to_orderitems relation.
In the line_items list you can show product stuff by putting in related fields to products. You can nest as many forms as you wish.
In scripting, you can also go down multiple levels by "chaining" relations to each other in order to get some value:
var x = relationA_to_B.relationB_to_C.myColumnInTableC
Valuelists can also depend dynamically on SQL.
Check out the Application tree/samplecode>>
application.setValueListItems( name, display_val_array/dataset, [real_values_array])
Just let me know if you need more help here.