We use many two part dialogs, where the upper value list is used to filter the lower value list. For example a list of trades, i.e. plumber, electrician, carpenter. When you click on one trade you get a list of all workers who are related to that trade. In Omnis we sometimes add a line to the value list that isn’t drawn from a table, using ‘add line to list’. It will contains ‘All trades’ for example. When ‘All trades is selected’, the bottom value list is not filtered at all and all workers are displayed. We have not been able to find a solution on how to accomplish this in Servoy as it seems everything is so tightly bound to a table. In Omnis the upper list is an array that can be populated from data but also can have rows added manually that aren’t bound to a table. Any suggestions on how to do this in Servoy?
Hello Gary,
With Servoy you can set al your valuelists dynamicly using the function:
application.setValueListItems(valuelistname, [dataset/displayarray], [realarray])
So you can control what is in the valuelist, this could be data from tables you queried, as well as items that you add manually
For example:
var _display = ['adventure', 'romance', 'history']
var _real = [1,2,3]
//extra all values
_display.unshift('No specific category');
_real.unshift(-1);
application.setValueListItems( 'book_types', _display, _real)
If the user selects “no specific category”, the field will contain a -1. Using the onDataChange of the field you could change the other valuelist to contain all values when -1.
And if he does select a value you could filter the other valuelist on that value.
Hi Sanneke,
The problem is not that we cannot get the data into the valuelist. The problem is actually the presentation of the data to the user.
We are trying to use this in our standard popup windows. For example, show area types and also have the All types choice added.
The only options we have found is to use a valuelist, a radio button or check list. The value list will only show one rows at a time. We would like the data to show in a list so the user can scroll thru the choices. Since we could not find a way to show all lines in a valuelist, we tried the radio buttons. This is better, however we would like to remove the radio button icon.
Lenore
Hi Lenore,
Why not use a popup dialog then with a table/list view representation of the values you want to present?
Or do I miss your point?
Hi Marcel,
The solution will have to work on the web as well. This is pretty common for our application. I’m not familiar with the popup dialog.
We have not problem building the data. We are using a getDataSetByQuery then an unshift to put the All types at the top of the dataset. The problem is getting the data to the screen in a list or table view form.
The user must be presented with the list of ‘All Types’ and then the actual type data. They should be able to select one by double click or selecting and clicking the ok button.
Lenore
With popup dialog I mean a formindialog. That should work with a web solution as well…
Marcel,
We are currently opening all our popup windows with formInDialog.
The popup window is comprised of the main from and 2 table view forms. The top list holds All Types and all building types in the database. Once the user selects the building type, the bottom list loads with all the buildings of that type. If the user selects All Types, then the building list would show all buildings.
Lenore
It is possible to temporarily “disconnect” records from the background table. This technique duplicates sanneke’s dynamic value list technique except it uses “dynamic record lists” instead. Works best in a modal form in dialog like your situation.
- Form dialog onShow event: turn off Servoy’s autosave:
databaseManager.setAutoSave(false)
-
Create any new records that you need like “All” and a divider line.
-
Record onSelect event or field onDataChange event or whatever: attach your record selection code. In your case, show the appropriate records in another list showing below.
-
Do whatever else you need to do while the form is in dialog.
-
Form dialog onHide event: roll back new and edited records and turn Servoy’s autosave back on:
databaseManager.rollbackEditedRecords()
databaseManager.setAutoSave(true)
TIP: check useSeparateFoundSet property on the dialog form so the background form doesn’t show record changes if they are based on the same table.
I think this i what you are looking for.
We tried this as a possible solution. The only problem is that the rollback rolls back everything including changes that we would want to keep on the main table.
This popup would be accessed while the user is adding or editing the main table. The user would access the popup to link type to the main table be populating the foreign key. So you would not want to rollback data already entered by the user on the main table.
Maybe there is a way to prevent the main table from being include in the rollback.
Lenore
Lenore:
Maybe there is a way to prevent the main table from being include in the rollback.
You can’t do a partial rollback, but instead you can just delete all the “dynamic records” yourself by doing deleteAllRecords().
There have been a number of times when we’ve wanted setAutoSave() to only apply a specific table or form as well!
In those situations we’ve gone with loading and displaying the dataset in client memory. There are a number of ways you can do this:
- Use a global html_area field and use html to write out your rows and columns. You can attach methods via href links.
Several downsides: html_area fields redraw quite slowly the more data you put in them; limited CSS support; and the vertical scroll bar will snap to the top when you modify any of the contents.
- Create a table with just a PK and any number of unstored calculations. Put “//return” in each of the unstored calcs.
Create as many records as you need in this holding table and then process row by row pushing in source data into the unstored calcs.
The unstored calcs theoretically act as global record variables done this way.
The downside with this approach is that with larger datasets that require scrolling, data will get dropped for random rows. If we could figure out a way to stop this, we would use this technique all over the place.
-
Use the inMemDataGrid bean that ships with Servoy. Downside: not a whole lot of display functions available.
-
Patrick’s Table Bean:
It’s a thing of beauty. If you really want to do the in-memory thing well, this is your ticket.
Thanks for your suggestions David.
We have been successful using custom value lists to get the ‘All Items’ in the list, but we have to apply the custom value list to a checkbox or radio button in order to see them without dropping them down.
We are under a mandate to produce a robust web client as well as a smart client solution and that is where the problem lies. In the browser we have to resort to HTML and then suddenly the web client no longer has sortable columns (w/o a lot of hard work), and the line don’t show as selected (you can underscore the selected line or column, but again more HTML work).
Hand coding HTML is tedious and error prone and totally contra to Servoy’s paradigm (LLOC). HTML columns can’t be re-arranged, etc. Using HTML to display the data from joins is hard work, moving to Servoy we were looking for ‘easy’. We also loose the advantage of style sheets, because this hand coded HTML can’t be updated when the style sheet changes for items in a table view.
Keep the suggestions coming though, we are always open to any new ideas and will give your tips a shot.
Thanks!
What about this one:
Build a form based on a table into which you insert the entries of the valuelist as records, adding a hidden column with your clients clientid so he only sees his own records, by applying a tableFilter on that column?
Then you can just use a normal tableView form.
If you do not want the overhead of a “dummy” table in your database, you can use Java’s inMem database hsqldb.
Paul
Thanks Paul. That’s sound like it could work, we just didn’t want to thrash a table add and deleting rows each time we used a popup and also didn’t want to lose all the great functionality Servoy provides in the table view. One of our engineers is exploring this path this afternoon and we’ll let you know if we had success.
Gary
Paul, I have been working with the inMem solution you suggested. It seems like it could work, but I have a few issues that I am dealing with. I initially created the DBServer withouth the “mem” in the URL, so it created a “database” on my local machine. I created a table, added two calculations to it and one relation. When other developers launch, they get warnings; ovbiously, this is bedcause the “database” is on my local machine, which they cannot access.
So, I changed the DBServer to include “mem” in the URL. I then created the table, the two calculations and the relation. When I opened a different solution, errors come up about calculations and relations that attach to a non-existent table.
I can opt to create the table using a “create table” statement and populate it with the results from an SQL query, but I think I still need the relation on the table to restrict only the rows that apply to my session (the “hidden” field you suggested). The remaining question I have after all this is solved is, “Can I display this on a form?” Since it is no longer a “real” dataprovider, can it be attached to a form?
Hi JT,
You have to make sure that the table is created before the form is touched in code or shown on screen. Then it works fine in the client.
When the table is located Serverside in an in memory database, I guess the first developer that opens the solution after a restart of the server is likely to get the warnings, because the table isn’t there. (if that is an issue, you can add something in the startup script of your server to create the tables maybe?)
I do not think you need a relation to restict the records your user sees: just add a tablefilter or foundset filter to the table/foundset, in which you restrict to records containing value x in column y.
Because this all is a real table in a real database, you can display it on a form. The fact that in fact it’s all in memory somewhere doesn’t matter: to Servoy it’s just a database.
Paul
Our internal notes on using hsqldb:
- “mem” setup (jdbc:hsqldb:mem:db_name)
- this creates the database in memory on the server, not on the client.
- you cannot base any forms on this database as there are no tables when the server starts up.
- you can create tables, columns and rows methodically that all clients can access via methods.
- “file” setup (jdbc:hsqldb:file:db_name)
- database is created on the server. developer is considered a server.
- you can base forms on this database as all table structure and data is retained from one session to the next.
- several files are created to store the database information. if you don’t give a directory path in the url, these files will be in the Servoy root.
- you can send these files to other developers with a solution export.
- Servoy will data broadcast forms based on hsqldb tables per normal behavior. Servoy treats hsqldb databases no differently than other jdbc database connections.
- “local” hsqldb databases
We wrote a plugin to do this. You can’t base forms on local databases and it doesn’t work in web client. So everything needs to be accessed methodically.
This approach is useful if you want a SQL way to store client state information from one session to the next.
The big use for this is that you can connect to other application’s hsqldb databases on the smart client’s computer. Open Office…
Well, I have tried a couple things, and they lead to a couple questions. Overall, this looks like a viable solution to the problem, albeit not an elegant solution. It does seem to “bypass” many of the built-in behaviors that make developing in Servoy so fast and easy–especially the process of getting data into the table, which is anything but “less lines of code.”
I created the table at the time the solution is opened. This “seems” to prevent any errors from occurring during the Servoy launch, including the checking for unattached calculations. I didn’t create any relations, but I expect that I could without errors.
When I am accessing the form related to the table, in the onRecordSelection method, I make a call to getDataSetByQuery which I loop through and populate the in-memory table with the results. The resultant foundset displays on the form as expected, including calculations created on the table.
-
Dave, you indicated that you should not create any forms based upon in-memory tables. Can you elaborate as to why? I have done so, and it seems to be working pretty well. However, I don’t want to get down the road and discover some unforseen problems.
-
Since this is memory-only, I expected that once I shut Servoy down (Servoy eveloper running on my local machine) that the database and/or tables would cease to exist. This is not the case I looked in the list of process (Windows Vista) and didn’t see any names HSQLDB, but the process must still be running, or the tables would certainly cease to exist. Do you know of the name of the process?
-
Exactly what happens during the launch of Servoy and opening a solution"? I know that Servoy validates all tables, calculations, aggregates and relations, and, at some point, runs the “On open method” for the solution. Where in the process does Servoyrun this “On open method?”
Thanks for all the help.
In my limited testing I was not able to base a form on a jdbc:hsqldb:mem:db_name connection. But reading Paul’s notes it might have been because I was hitting the form at startup.
From what I can tell, jdbc:hsqldb:mem:db_name databases (and hence tables) get dropped completely when the Server shuts down. jdbc:hsqldb:file:db_name databases persist. With either connection type (file or mem), if the database specified in the connection string is not there, it will be created for you when Servoy starts up.
We only tested for hsqldb to see what the possibilities were. Except for using our plugin to connect to preexisting hsqldb databases on a client machine, we haven’t battle tested this whole approach.
In the end, I’m not really sure what an hsqldb database would do for you that another database in Sybase wouldn’t accomplish just as easily. hsqldb databases are not in the client memory space – they are on the server so they behave just like regular jdbc database connections.
David, thanks for the follow-up. I think the important thing for us is that the in-memory database will always contain transient data. Having and actual table in the database has the potential to confuse our clients. Many of them create their own reports, outside our product. In the end, this may or may not be a solution we want to continue using, but it is at least viable from what I have seen so far.
Thanks for you help.