Using Relations in Calculations

Questions and answers on designing your Servoy solutions, database modelling and other 'how do I do this' that don't fit in any of the other categories

Using Relations in Calculations

Postby jdbruijn » Sat Mar 02, 2019 10:43 pm

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:
Code: Select all
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?
Jos de Bruijn
Focus Feedback BV
Servoy Certified Developer
Image
jdbruijn
 
Posts: 492
Joined: Sun Apr 11, 2010 6:34 pm

Re: Using Relations in Calculations

Postby mboegem » Sun Mar 03, 2019 2:31 pm

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)
Code: Select all
function tags(){
     var fs = databaseManager.convertFoundSet(books_to_booktags,'booktags_to_tags');
     return databaseManager.convertToDataSet(fs,['name']).getColumnAsArray(1).join();
}


Hope this helps
Marc Boegem
Solutiative / JBS Group, Partner
• Servoy Certified Developer
• Servoy Valued Professional
• Freelance Developer

Image

Partner of Tower - The most powerful Git client for Mac and Windows
User avatar
mboegem
 
Posts: 1743
Joined: Sun Oct 14, 2007 1:34 pm
Location: Amsterdam

Re: Using Relations in Calculations

Postby jdbruijn » Mon Mar 04, 2019 9:41 am

Thanks Marc,
Will look into a non-calculation approach.
Jos de Bruijn
Focus Feedback BV
Servoy Certified Developer
Image
jdbruijn
 
Posts: 492
Joined: Sun Apr 11, 2010 6:34 pm


Return to Programming with Servoy

Who is online

Users browsing this forum: No registered users and 8 guests

cron