valuelist containing only certain projects

Classic Servoy
1

I am building a contact log in Servoy (using MySQL) that references data in an application called Ajera (based in Microsoft SQL).

The Ajera project table contains two types of records: projects and project phases. The contents of a particular field (call it parent.project) indicate if the record is a project or a phase. Parent.project field is empty if the record is a project. If the record is a phase, parent.project contains the key of the associated project.

I would like to have a Servoy valuelist look at the Ajera project table and show me a list of just the project records (omitting the phase records).

I imagine something like one of these two ideas…
valuelist = records from Ajera.projects where parent.project = empty
valuelist = records from Ajera.projects where parent.project < 1

Is this possible using a relation and the Related Values valuelist option or do I need to build an SQL query to popluate the valuelist?

2

When it is possible to create a relationship you can always base the valuelist on that relationship.

Then nice thing of Servoy is that you can create a relationship between two tables in different databases. When you want to do this with queries that’s also possible of course but it involves a lot more work…

3

I appreciate the feedback.

I’ve tried creating a relation but must be missing something because it is not working.

Relationship is defined as contact.log_to_ajera.projects where zl_1 > parent.project. (zl_1 = integer field that contains 1)

My goal is for the relation to result in all ajera.project records where parent.project is empty.

4

Figured it out. The relationship does work but I had a typo in there.

Thanks!

5

Correction…it does not work.

I can get the valuelist to show ajera.project records where parent.project has a value. However, I cannot get it to show records where parent.project is empty. This means that option 1 below works, but option 2 does not.

  1. contact.log_to_ajera.project when zl_1 < parent.project
  2. contact.log_to_ajera.project when zl_1 > parent.project

(zl_1 = integer field = 1)

6

You can always generate the valuelist directly from a SQL query. There should be some sample code in Servoy, possibly in databaseManager, getDataSetByQuery()

7

I appreciate the replies, great to get help.

Just as a followup for others with this question…I used the SQL query method and below is the code that achieved what I wanted.

var valuelistName = forms.log_detail.elements.project_field.getValueListName();

//Get a dataset based on query
var maxReturnedRows = 10000;//useful to limit number of rows
var ajeraServer = forms.axproject.controller.getServerName();
var ajeraTable = forms.axproject.controller.getTableName();
var queryTable = 'axproject';
var query = 'select prjkey, prjdescription from ' + queryTable + ' where prjparentproject is null'
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, maxReturnedRows);

var keyArray = dataset.getColumnAsArray(2);
var nameArray = dataset.getColumnAsArray(1);

application.setValueListItems( valuelistName, keyArray, nameArray);