Using Relations in Calculations

Classic Servoy
1

Consider the following table (columns) setup:
books (id, name), tags (id, name), booktags (book_id, tag_id)
When showing a list of books, I also want to show the tags that apply to that book. So I added a calculated field ‘tags’ to the books table:

function tags(){
	var aTags = [];
	var rec
	var cnt = books_to_booktags.getSize();
	for(var i = 1; i <= cnt; i++){
		rec = books_to_booktags.getRecord(i);
		aTags.push(rec.booktags_to_tags.name);
	}
	return aTags.join();
}

The cnt value is the correct number for the selected book. But in the for loop the rec variable is always the first record of the related foundset. I end up with an array of the correct length but all items are the first tag in the list.
Is it not possible to use relations inside calculations in this way?

2

Hi Jos,

I try to avoid any relation stuff in calculations, since this often results in performance issues (sooner or later)
If you store data using save buttons in your application, I’d advice to calculate the tags at that point and store them in the field.
You can also use table events for that purpose.
Both ways will only calculate the result once instead of over and over again using a calculation.

If that is not what you want to/can use, this code will gain performance (although it will give you build markers since databaseManager functions are officially not allowed in calculation (and that has a reason…)
To get around the buildmarkers, you can wrap the code below in a separate function and call that from the calculation (passing a record as parameter)

function tags(){
     var fs = databaseManager.convertFoundSet(books_to_booktags,'booktags_to_tags');
     return databaseManager.convertToDataSet(fs,['name']).getColumnAsArray(1).join();
}

Hope this helps

3

Thanks Marc,
Will look into a non-calculation approach.