I am currently evaluating Servoy. I am a 4D developer. I used to work with FMPro too. I am shopping for an alternate tool with standard languages.
I just read the ‘Servoy Developer.pdf’ doc. and I have a couple of questions to start with.
I saw nothing concerning SQL Queries. It is a must-have for me. I managed with lots of work to mimick queries in 4D but it’s not the real thing. Is there anything to edit queries spanning more than one table and its related child tables? If so how to view them? If not is there a plan to implement queries in the near future? I read somewhere about temporary tables but it’s not clear.
The portal feature looks pretty much like in FMPro. 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. If so it’s very FMPro like. And it’s quite a no-no for me.
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 = ‘’+dataset.getAsHTML()+‘’;
//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])
I too was curious about Servoy methods for accessing related data several tables “away”, and also not interested in continuing to ‘shunt’ data through calculating fields as is commonly done in Filemaker 6 and below.
In additon to Maarten’s notes above, it’s interesting to note that non-stored calculations in Servoy do not appear to create a column in the database (and are stored only in the repository and evaluated when needed, I assume). To my mind, this is an advantage over Filemaker as your schema should stay more “pure”.