Combine fields into valueList - getting error

Classic Servoy
1

I have a valuelist named “blank”. I am trying to create a value list by method which contains the first and last name, but returns the personID, of employees of companies and vendors that match a given invoice.

The SQL query works and the variables are set properly, but there seems to be an error in the getDataSetByQuery line.

The error I get is:

org.mozilla.javascript.EvaluatorException: Can’t find method com.servoy.j2db.dataprocessing.JSDatabaseManager.js_getDataSetByQuery(string,string,string,number). (evt_getCoVendor_peopleNames, line 4)

The code follows:

var names = "SELECT CONCAT(pNameF, ' ' , pNameL) AS fullname FROM person WHERE companyID = " + companyid + ' OR companyID = ' + cvendorid + ';';
var ids = 'SELECT personID FROM person WHERE companyID = ' + companyid + ' OR companyID = ' + cvendorid + ';';
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), names, ids, 50);

application.setValueListItems('blank',dataset);

Any ideas of what I am missing, or a better way to handle this?

2

Hi Andrew,

You’re messing up the getDataSetByQuery command.

You’re trying to combine two different queries into one. Try this code - it should work:

var query= "SELECT personID, CONCAT(pNameF, ' ' , pNameL) AS fullname FROM person WHERE companyID = " + companyid + ' OR companyID = ' + cvendorid + ';'; 

var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, 50);

var ids = dataset.getColumnAsArray(1)

var names = dataset.getColumnAsArray(2)

application.setValueListItems("blank", names, ids)

Hope this helps.

3

Thanks Bob,
That did the trick.