Selecting stored calc in .getDatasetByQuery

Classic Servoy
1

Hi all,

I am trying to extract a set of fullname calculations to use as display values in a valuelist. I have the problem that I can not seem to grab the calculated values with my sql query but can select other dataproviders.

SELECT DISTINCT n.fullnameb, c.contact_id FROM contact c INNER JOIN names n ON c.name_id = n.name_id WHERE c.caregiver_id='" + globals.gContactID + "'";

“fullnameb” is also a calculation on the names table.

var fullname = "";
if(title != null) {
	fullname += names_to_title.title
}
if(fname != null && fname != "") {
	fname + " ";
}
if(mi != null && mi != "") {
	fullname += mi + " ";
}
if(lname != null && lname != "") {
	fullname += lname + " ";
}
if(suffix != null) {
	fullname += names_to_suffices.suffix;
}
return fullname;

Prior to calling .getDatasetByQuery() I call:

databaseManager.recalculate(forms.names.foundset);
databaseManager.refreshRecordFromDatabase(forms.names.foundset, -1);

However, I keep getting null values returned in my dataset, while if I select n.fname I get the first names.

Am I missing something?

Jon

2

JSharp:

SELECT DISTINCT n.fullnameb, c.contact_id FROM contact c INNER JOIN names n ON c.name_id = n.name_id WHERE c.caregiver_id='" + globals.gContactID + "'";

Just from the top of my head: you are only selecting from the table called “contacts” but your fullnameb is on table “names”, try:

SELECT DISTINCT n.fullnameb, c.contact_id FROM contact, names c INNER JOIN names n ON c.name_id = n.name_id WHERE c.caregiver_id='" + globals.gContactID + "'";

It should work as long as the calculation is a stored one.