Valuelist of text returning integer

Classic Servoy
21

Hi John,

Thanks for the response.

Have tried what you suggested but it still does not work. What happens is that the names i select in the drop down are returned to the form field and not the record id value which is what i need to store in the database column.

This is the method as how you suggested:

var maxRows = 100
var query = “select (cast(id as varchar(10)) + ’ ’ + firstname + ’ ’ + lastname) as ‘Name’, id from dbo.Members with (nolock) where leaderId = " + memberId + " order by firstname, lastname”
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, maxRows)

application.output(‘recs=’+dataset.getMaxRowIndex())

if (dataset.getMaxRowIndex() > 0)
{
application.setValueListItems(‘memberid’, dataset)
}

This is the method as how is in the documentation:

var maxRows = 100
var query = “select (cast(id as varchar(10)) + ’ ’ + firstname + ’ ’ + lastname) as ‘Name’, id from dbo.Members with (nolock) where leaderId = " + memberId + " order by firstname, lastname”
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, maxRows)

var labelArray = dataset.getColumnAsArray(1)
var idArray = dataset.getColumnAsArray(2)

application.output(‘labelArray=’+labelArray)
application.output(‘idArray=’+idArray)

application.output(‘recs=’+dataset.getMaxRowIndex())

if (dataset.getMaxRowIndex() > 0)
{
application.setValueListItems(‘memberid’, labelArray, idArray)
}

In either of these two cases the 2nd col (id) is not returned back to the form field, the 1st col (‘Name’) is, which is no good as the database col is an int col to hold the rec id.

Please confirm what I am doing wrong!

22

Ok. What I suggest is to take a simple example. Use the ‘example’ database that comes with Servoy. There is a table there called product and it has columns, two of which are productid and productname. Set up a form based on that table with just those two columns. Set up a custom valuelist on productid and call it ‘productvaluelist’. Set the productid column to be a ‘Combobox’. Go to editor and set up this method:

var maxReturedRows = 100;
var query = 'select productname,productid  from products';
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, maxReturedRows);
application.setValueListItems('productvaluelist',dataset);

Switch out of Design mode and run the method. Scroll through the records and you should see the same thing in both productid and productname. Once you have established that that works, then I would go ahead and tinker with your query. Simplify it so that you just have ‘lastname, id’ and make sure that that works and go on from there.

One little thing occurs to me. You know that by running this valuelist it will APPEAR as though you have a NAME in your id field when looking at the form but in fact you are actually storing the ID in the backend database. But you won’t know that in any way by looking at the form. It is as though you did a non-custom valuelist based on the database and selected ‘id’ as ‘return in field’ and ‘name’ as ‘show in display’. You probably know all that but it is something that can appear confusing when you are starting out.

23

Here is a method which works for me. This is for a physio practice. They setup a referral (parent record) for each case which has a number of treatments (child records). Each treatment needs to be assigned a service (i.e. treatment, report, discharge etc) and the drop down list of services is determined by the product which is set on the referral record (i.e. services are the children of products).

This is method is called onShow the list of treatments on a related tab panel as follows…

var mr = 200;
var ri = referral_id
var query = 'select s.service_name, s.service_id, s.sort_order from services s, treatments t, referrals r'+
' where (t.referral_id = '+ri+' and t.referral_id = r.referral_id and r.product_id *= s.product_id) or s.product_id is NULL'+
' group by s.service_name, s.service_id, s.sort_order order by s.sort_order';
dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, mr); 
var labels = dataset.getColumnAsArray(1);
var ids = dataset.getColumnAsArray(2);
//plugins.dialogs.showInfoDialog('Info', labels + '\n'+ ids,  'OK');
application.setValueListItems('services_custom',labels,ids);
elements.service_id.requestFocus();

You need to make sure that you setup a new custom value list with the same name as that in the method and that this is assigned to the field on the form and that this field is set to be non editable.

24

Thanks Chris,

We have a tab panel displaying many records (recordview) and there is a dropdown valuelist on one of the columns in the record.

That seems to be working now but… I notice that it only updates the db with the value I have chosen after I select another value from a dropdown list on another record? Why?

Also, two questions:

  1. Why when I load the screen - the valuelist items do not show as I last saved them? or am I missing the point? Do I need to run another method or something to repopulate the valueslist from the db?

  2. Why when on loading the form and you click on the drop down list - no records show, then you click on another record valuelist and the records appear? Is there a delay in connecting to the db or something?

Roger

25

I’m waiting for a meeting to happen and was notified of your reponse on this post so might as well fill in the time!

That seems to be working now but… I notice that it only updates the db with the value I have chosen after I select another value from a dropdown list on another record? Why?

By ‘updates’ I am presuming you mean the ‘UPDATE’ sql that is sent to your backend database. The reason that update statement is sent is that Servoy by default ‘saves’ all changes. What triggers those ‘automatic’ saves, amongst other things, is switching records.

  1. Why when I load the screen - the valuelist items do not show as I last saved them? or am I missing the point? Do I need to run another method or something to repopulate the valueslist from the db?

The reason is that you are using a ‘custom’ valuelist set dynamically at runtime, i.e. an ‘application.setValueListItems’. The beauty of those are that they can be specifically for a particular record, time or form. If you have ‘stable’ valuelist that could be for all records (whether ‘database values’ - which can of course change - or ‘custom’) and you want it to always be available and independent of a method, then just set a ‘normal’ valuelist. If you want a dynamic valuelist but one that will be the same throughout a clients session then just have the method run on starting up. If the valuelist can change from record to record, form to form or whatever, then just have some ‘generic’ action such as onRecordSelection trigger the method.

  1. Why when on loading the form and you click on the drop down list - no records show, then you click on another record valuelist and the records appear? Is there a delay in connecting to the db or something?

Not sure I quite understand this. You click on the drop down list (i.e. you click on the column in a record and the dropdown/value list appears that is tied to that column, is that right?). ‘No records show’, i.e. no drop down list appears or an empty list appears? Then when you click on ‘another record valuelist’ (same column?) the ‘records appear’. Do you mean the valuelist appears? I’m a little lost as to what is the record(s), what are the column(s) or field(s) and what is the valuelist. Are there different valuelists?

John

P.S. It sounds like creating the valuelist with the id and value is working now at least. Did you work out what the problem was? I’m always curious with all things Servoy!