How would one go about designing a form that would display the result of an SQL query that uses UNION? The two tables in question are not related, and have similarly-named, but not identical fields. With MySQL you can do this on the fly with UNION, but how does one display the result in Servoy forms? Do you need to use global dataproviders for this? -pw
with one global field, put it on a layout, if the field name is sqlResult you can now do this:
var query = "select a,b from x UNION select b,c from d";
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, 1000);
globals.sqlResult = '<html>'+dataset.getAsHTML()+'</html>'
(1000 is the maximum returned rows, adjust as necessary)
mo_ca_mo.zip (1.33 KB)
Thanks Jan. Wow, very interesting. How can one do this so that the dataset is not in html, but rather as records with individual fields, as in a Servoy form row view? I’m guessing that I would need to use a global data provider for each field I want to display, but I’m not sure how to channel the query results into separate (global) fields. For example, displaying both the results of field a from table x and field b from table d into global field y, and the results of field c from table x and field e from table c into global field z, etc.. It’s probably right in front of me.javascript:emoticon(‘:?’)
-Peter
plugins.zip (1.68 MB)
Given the kind of query, your dataset is for viewing purposes only.
Why don’t you use an HTML table instead of globals?
in the 2.5 release we will try to introduce a special “portalcomponent” which you can drop in datasets and the portalcomponent will build itself from the dataset (columns and rows)
Of course this will be readonly! You will not be able to edit the data
In this case I need editable records. This is a purchase, billing, and reporting database that has tables for two different kinds of transactions that have a few different fields and, we think, need to be kept in separate tables. In our old FileMaker solution, we would ‘export’ the records ready for billing from each files to a new joint file using “Set Field.” Since MySQL can perform the union (and hence create a sort of “table on the fly”), I thought I could program Servoy global fields to simply provide data based on the individual columns returned by the SQL union query. Is this possible, and if so, how? tia, pw
It’s impossible to make a UNION view, editable in such a way that a change on the UNION view would be reflected in one of the 2 existing tables.
Based on the info you gave I would:
a) create a temp table where you can dump the UNION into as records,
making them editable.(assuming that editing doesn’t have to be reflected in the original tables…on second thought, maybe could be done by relating back to original tables?)
b) Change your datamodel. If the two tables don’t differ too much, you might consider using one instead. (which is of course easy said overhere)
maarten:
It’s impossible to make a UNION view, editable in such a way that a change on the UNION view would be reflected in one of the 2 existing tables.
Based on the info you gave I would:
a) create a temp table where you can dump the UNION into as records,
making them editable.(assuming that editing doesn’t have to be reflected in the original tables…on second thought, maybe could be done by relating back to original tables?)
b) Change your datamodel. If the two tables don’t differ too much, you might consider using one instead. (which is of course easy said overhere)
Thank you Maarten - these are very good suggestions. We are going to revisit the datamodel and see whether we can simplify things at the backend. Personally, I would much prefer to have all of the records in a single table, but the powers that be here have, shall we say, a ‘unique’ set of requirements for these respective sets of records. Time to go back to the drawing board and see what we can come up with.
one last question: when we tried the html table method, there was no apparent way to scroll through the table, even though scrollbars were enabled for the global field (actually a label) we set up to contain it. So only a small portion of the table was displayed; it just seemed static. Does anyone have any suggestions on how to make the display of the html table a bit more ‘user-friendly’ in this regard? Thanks again for your help. -pw
when we tried the html table method, there was no apparent way to scroll through the table, even though scrollbars were enabled for the global field (actually a label)
The scrolling only works with a global field.(not a label)
You can also use the following approach to make it editable.
- Create 2 forms, 1 that views data from the first UNION table, one that looks at the other one.
- Create a global that pulls in the unioned data renedered as html. Also alter your query, add an additional variable to your SQL so you can identify from which table your data came. for example if you old sql was: SELECT A,B FROM C UNION SELECT D,E FROM F change it to:
SELECT A,B,‘C’ FROM C UNION SELECT D,E,‘F’ FROM F, you can use this identifier (in this case the tablename) to know to which editform to go to. - Create a loop that generates more sophisticated html than the default getAsHtml function, this way you can include a trigger to a method that will allow editting of your record. Basicly you add an a href to for example the first column that triggers a servoy method, you pass it the the tablename and the primarykey value. The method goes to the corresponding form and loads the corresponding record for an edit
Thanks Jan. After reviewing our data model, and deciding to keep things as they are, what you describe is almost exactly the direction we decided to head in. I think this will work - I just didn’t initially expect that the solution would require html. But it does work!
Thanks again, pw.