Suppose there’s a simple lookup-list table that has the following fields:
rec_id
name
description
cancelled
Where ‘cancelled’ is a flag that indicates if the recordis active or that it’s cancelled. Then in a valuelist I want to display only those records that are active (i.e. cancelled is 0 / false)
I tried something with making it a fixed-value list and then writing code to fill the list at runtime by doing an SQL select that went like ‘select name + ‘|’ + rec_id from …’ (because the pipe (|) is normally used to separate what shows in the list with the value that’s passed back to the field.)
Anyway, this didn’t work, I probably overlooked something. It also seems that data-views can’t be used in Servoy? Otherwise I could just define a data view that only shows active records..
You don’t need to have a separator when doing a setValueList by query. Just fill it with the usual ‘select rec_id, name from…’. Or you can just show the active records by relationship (set a global_variable = 1, set a relationship global_variable_to_cancelled)
Aaaaahhhh.. thanks!!! I always thought there was more to that relationship option for valuelists than met the eye..
However… In the first solution you mention, i.e. filling the value list with a 2-column dataset, this doesn’t seem to work; I just get the first column, not the second one. (I’d like the first column to be returned to the field by the valuelist, and display the second column). It seems this is what the third parameter in SetValueListItems is for, but I can’t get it to work..
The other sulution, i.e. making a relationship to a global variable results in the problem that for existing data the cancelled value doesn’t show anymore. Say if you have an existing sales order with a now cancelled product on it, then you’d want to show the cancelled product for an existing order, but not allow adding this cancelled product to a new order so you’d exclude it from your valuelist..
Here is an example from one of my solutions using the setValueListItems:
var maxReturnedRows = 200;
var sitename_q = "select site_descr, site_code from sitename ";
var sitename_vl = databaseManager.getDataSetByQuery(controller.getServerName(), sitename_q, null, maxReturnedRows);
application.setValueListItems(‘g_check_box’,sitename_vl);
The valuelist ‘sitename_vl’ is a Custom Valuelist (defined with no values) and set, in this case, to the global dataprovider ‘g_check_box’ (although it could be any type of dataprovider).
What shows for the user are the values in ‘site_descr’, what is returned are the values in ‘site_code’. The way the SetValueListItems works is that in your query you MUST have the item you want to display as your FIRST column and the item you want to return as the SECOND column in your select query. As long as you remember that then there is of course no problem because you can obviously set the column order in your query any way you want.
By the way, as a side note and not being sure where you are at in your ‘Servoy development’, I would HIGHLY recommend purchasing all of the Servoy written documentation. You will find it extremely helpful and save you hours of ‘spinning your wheels’! I recommend that everybody just leisurely read Developer Edition 1 and 2 (as well as the Advanced Programming guide for FileMaker Developers if you have a FileMaker background). Not to try to learn or cram stuff in but just to get a good overall feel for things. It makes it a lot easier rather than just trying to learn the things you ‘need’ right then.