Update parent record with totals/averages of values in joined records.

[Duke3D]

I have found half a dozen threads on the topic, but it seems none of them end with a publicly posted solution or working code example.
This is the closest example of the goal:
http://fabrikar.com/forums/index.php?threads/how-can-i-calculate-the-sum-of-the-joined-values.40619/

The Table Structure:
Parent table and list called Applications with PK Applicant_ID
One to many Child table and list called Evaluations with FK that contains a matching Applicant ID

Parent List setup with Join as Inner Join from Applicant___Applicant_ID to Evaluations___Applicant_ID with Repeating Group turned on and Display Mode set to Merge Rows.

Each parent record properly displays with it's multiple child records. Each child record has several scores.

Goal is to add Calc elements into the Parent table that display averages from the child record data, and posts those results.

From other forum posts I have tried:

Calculating from POST data but this is a List view so there is no POST data available.

return array_sum({fab_evaluations___score1});

but this returns only the first child record's score1 value, not the entire array.

$scores1 = explode(',', "{fav_evaluations___score1}");
return array_sum($scores1);

but this fails / returns zero

One comment suggested to dump the $data array and pick values out of it,, but
echo '<pre>'.print_r($data).'</pre>';
Revealed that the $data array is cleared and rewritten - it only has the data for one current child record instance. (But it also revealed the score values in _raw are identical to the non-raw score values so either works as source data.)

and

$db = JFactory::getDBO();
$query = "SELECT score1_raw FROM fab_evaluations WHERE fab_evaluations___applicant_id = fab_applications___applicant_id AND score1_raw > 0";
$db->setQuery($query);
$scores = $db->loadResultArray();
$scores[] = '{fab_evaluations___score1_raw}';
$scores_average = array_sum($scores) / count($scores);
return sprintf('%01.2f', $scores_average);

But in all variations of this I am getting zeros.

The Javascript solutions do not seem to work because they are Form based and when a user is editing and submitting their form, they do not have ACL access to view or calculate using the answers in someone else's form. This needs to be a server-side PHP process fired when the SuperAdmin displays the complete List of Applicants in order to display or print the combined average scoring results submitted from all users.

Can someone help suggest proper syntax? The CSV export of the Applicants in List View is exporting the full array of scores for each applicant so there must be a way to access them.

In case the plugin type in the child record matters, score1, score2 etc are all radiobuttongroups. (Hence using _raw)

This same approach will also be used to compute an average on a total_score column in the child table that is a calc element.

(I also suspect the results need to also be posted into the Applicant table via Post_Eval.)

I'm sure someone has figured it out before, but the two relevant Wink Fabrik tutorials are offline. If this is really something that has not been done before, then to me the easy UI option would be to take the List View calculation options and add them as a sub-tab of the database join element, or as a new SummaryOfJoin element. Apply the functionality for summing and averaging a parent table in List View to a joined / merged child table in List View.

 

[troester]

Did you try this?
http://fabrikar.com/forums/index.php?wiki/calculation-element/#retrieving-data-from-complex-elements

The DB way should do in any case, but

$scores = $db->loadColumn();

$scores_average = array_sum($scores) / count($scores);

https://docs.joomla.org/Selecting_data_using_JDatabase

 

[Duke3D]

Hi and thanks for responding -

I previously read the FabrikWorker description, but there was not enough there for me to understand how to implement it. Obviously from the context it is accessed somehow in the calc plugin but beyond that my clairvoyance is failing.

The "DB way" - I do not have enough familiarity with the inner workings to know where you would place the load column code such that it is bound to the correct column of filtered data. Is this also in a calc plugin or somewhere else? That is why I am looking for a tutorial or working example that I can deconstruct and see how something as simple as a sum of a child table's column is implemented with the current joins and filters active.

This issue has been left unanswered in so many threads that I'm beginning to think that it is more complex than it should be. The Invoice - Invoice Line Items setup resulting in invoice totals or counting the students in each class would be a helpful add-on to the basic tutorials. I keep checking to see if the 04 Bar Chart viz tutorial link has been fixed as the description says it has a Count example, but no joy - Wink data file is still 404.

 

[rob]

Hi
If you update from github (this commit https://github.com/Fabrik/fabrik/commit/9d32fafe26791ac4dd7fe94dcd97c704a20bd564) the following should work...
Say you have a list 'countries' joined to 'regions'
Each region has 2 elements:
* country_id - a database join element pointing at the countries table
* quality_of_life - a field recording the quality of life for that region

If you edit your quality_of_lifeelement, add a calculation (lets say an average calculation) and then select 'country_id' for the calculation's "split element" and save the element, and the refresh your list view.
You should see the average quality of life for each country.

 

[Duke3D]

Hi Rob -

So, to implement this, the calc field would contain:

$average = array_sum('{region_table___quality_of_life}') / array_count('{region_table___quality_of_life}');
return (float) $average;

and by having a split element identified it causes it to filter to/group by records subtotaled by country_id and to loop through them to get sum and count.
Is that a correct understanding? (I haven't come across splits before, I'm inferring it is analogous to Break-On in other languages.)

Thanks!