X

The Visual Builder Cloud Service Blog

Managing Aggregation for Business Objects with Triggers

Shay Shmeltzer
Director of Product Management - Oracle

If you have data stored in business objects in Visual Builder you might want to present some information that requires aggregating data from multiple records. For example, if you have a list of employees and their salary, you might want to know the total number of employees and the total of the salaries.

If you have a parent/child relationship you can do these type of calculations using the aggregated field functionality. For example if you have a departments table and each employee belongs to one department you can do the department level totals using aggregated fields. (You can see how to define this type of fields in this video around minute 3:45).

Doing aggregations when there is no direct parent/child relationship is a bit trickier. Below I'm going to show you an approach to handling this using triggers on business objects that keep your totals always up to date. Note that one advantage of this approach is that the totals are not calculated on the fly each time you query them - rather they are kept already calculated in a dedicated business object.

In the sample, I created a business object specifically for this tracking called "stats". This business objects has one record that contains the totals I need to track. I then use triggers to catch insert/update/delete operations on the employee object and update the stats object accordingly.

As you'll see, we needed to resort to a bit of Groovy coding to achieve this type of updates to none related objects. You can get the base groovy code needed to update an object and a field in it by looking at the code that the visual "update record" operation creates. You can then modify this code to the exact logic you require. In the video you'll also see how you can access not just the current value of a field but also the old value of that field using the getOriginalAttributeValue(‘fieldName’)  groovy function.

The code used in my update trigger is:

def view1 = newView('Stats');
while (view1.hasNext()) {
  record1 = view1.next();
    record1.totalSalary = {
  record1.totalSalary+salary-getOriginalAttributeValue('salary');
  }.call();
}

Note that in the video I didn't add the trigger for a delete event - but you can easily add this and simply have the following code in there:

def view1 = newView('Stats');
while (view1.hasNext()) {
  record1 = view1.next();
    record1.totalSalary = {
  record1.totalSalary-salary
  }.call();
  record1.empCount = {
  record1.empCount-1
  }.call();
}

Join the discussion

Comments ( 2 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha
Oracle

Integrated Cloud Applications & Platform Services