Hi, I think servoy needs and export function built-in. It HAS TO be made buy servoy because no one else can do it completely in a clean and fast way and I'll prove my point. Contrary to the import method that can be made by us or third parties.
But first let me tell you what I feel is a decent export function.
My wished export function would allow to export all fields, regardless they nature/type, from a particular form/table and all its related values (or just the first one).
Let's take a simple example
I have 3 Tables
'products' with 2 fields : SKU and p_name (SKU is the primary key)
'categories' with 2 fields : cat_id and cat_name (cat_id is the pk)
'products_to_cat' with 3 fields : SKU and Cat_id (both SKU and cat_id are pk) and a calculation my_cat_name that gets the the field cat_name of categories through the relation this_cat_name. That calculation helps to display the name of the categorie on the form.
The goal of that solution is to affect one or multiple categories to a bunch of products.
We have a one to many relation called 'this_products_categories' that links 'products' to 'products_to_cat'
and we have a one to one relation between 'products_to_cat' and 'categories' called 'this_cat_name'
Don't bother to criticize the structure of that example, it's just an example.
So, in that example I've 2 products.
Table products goes like this :
SKU Name
AAA Product A
BBB Product B
CCC Product C
Table categories goes like that
cat_id cat_name
1 hard drive
2 Microphone
3 Headset
4 camera
table products_to_categories goes like that
SKU cat_id my_cat_name
AAA 1 hard drive
BBB 2 Microphone
BBB 3 Headset
CCC 4 Camera
What I want from servoy is an export method like this
ExportMethod(source_form_name_foundset,fields_to_export_separated_with_a_;,export_all_related_fileds_boolean)
I don't want this method to rely on fields present or not on the current form, because I could want to export more or less fields that I see on my form, and I don't want to create forms just for the purpose of doing exports.
The goal is to export all my products with there categories names
In my foundset I've only product A, and product B
So I want to launch the ExportMethod
ExportMethod (form_products_founset,SKU;name;this_products_categories.my_cat_name;true)
and get the following file : file_1
SKU Name cat_name
AAA Product A hard drive
BBB Product B Microphone;headset
if I'd ran ExportMethod (form_products_founset,SKU;name;this_products_categories.my_cat_name;false)
I would have got this file_2
SKU Name cat_name
AAA Product A hard drive
BBB Product B Microphone
But what I really want is file 1
Before jumping on your keyboards telling me that it's obvious to do, and that IT2be excellent data plug-ing is there for that, just consider thoroughly te hurdles we have there, and you'll see we and even IT2Be can't do it.
What I need here is an ExportMethod that will work in any case, with just the parameter I gave. some of those parameters refers to servoy objects owned by servoy and arent't stored in thhe backend database. The relationship and the calc field. Those two are completly opaque to the SQL database, especially the calculation. You can deduct the sql off the relation though but not in a clean way.
The speedy and efficient way would be to it in pure sql. I did it and it was very speedy, BUT it doesn't work for the calc field.
Moreover the calc field has to be refreshed. If you want to access it in sql, it has to be a stored calc. But that stored calc needs to be refreshed. Refreshing it consists at looping through all records, which is slow, and doesn't work (doesn't refresh) for all the related calc names (only the first).
I really needs updating, dataset.recalculate doesn't work, and looping through the form, even if you have a portal containing the calculation, won't refresh all the related records (only the first one)
The other way, is too loop trough all records and export them. That's very slow, and there's still the refresh problem of the remaining related records.
So the servoy company should put in their export function something we can't do :
Making sure the calc is evaluated upon calling by the export function, so we get up to date calculation (the calc could be much more complicated than that example)
There's 2 ways that servoy inc can help us :
A. Write the export function themselves from scratch to make sure it can be as speedy as possible. This is the easy way
B. Or, make sure that when the sql engine asks for a stored calc filed, servoys is asked to calculated it, since the sql engine can't calcultae it itself, and to refresh the stored calc.
Or, more probably, it could be the getsqlquery servoy function that would refresh the result of the stored calc before returning them.
With that, and a clean way to get the relevant parts of a relation, we could create the exportmethod ourselves.
I think that the B way, even if this is a single function, is more difficult to do, but much more powerful and useful for the community.
BUT THERE'S A CATCH. I don't think we should rely on stored calc, because a calc can be dependant on a global. And you don't want that a global values (which is local) affects all the database. So I think we should be able to use unstored calc that will be calculated on the fly whenever asked by sql, but unstored calc doesn't exist. So I think we have to stick to plan A.
More generally, I thing that servoy inc should offer us the way to replicate all servoy objects that can be in sql
And now let's see why I think it's servoy inc's responsibility to create such an export function :
The purpose of servoy is to free us as much as possible of mundane tasks (that would be sql queries, but I'm glad you can do them if you want to), so servoy invented some servoy objects : relations, valuelist, calculations fields, aggregation, etc
Those object are meant to be used to help us code more quickly.
and using those objects shouldn't put us in dead ends.
BUT, if you're required to not use those hi-level object just because you can't export them (and that you need or even might need to export them), then all the point of those objects, and ultimately servoys itself loose its point.
As servoy created those objects, and wrote somewhere in their doc, that 'it's better not to store the solution logic by relying on the sql backend' which makes senses because servoy is meant to be sql databse agnostic, I thinks it's servoy goal to make us use the objects it provides without glaring limitations.
In the next post of this thread I'll post the export method I came up with, but it still faces the limitation of non refreshed remaining stored calc fields (and again relying on stored cal isn't good : local vs database wise changes)