Calculate values from multiple content items

Question

I have a Drupal 7 website that will be used to track automobiles in a fleet, as well as their engines and a few other items. Regarding the engines, I will be tracking the number of hours the engine is used, and the number of miles put on the engine in each use. To do so, I have an "Engine Usage" content type set up with fields for "hours used this trip" and "Mileage this trip". Each time the Engine is used, a new Engine Usage content item will be created manually, and the hours/miles will be entered into those fields. The Engine Usage content type is linked to the Engine content type/item by way of the Node Reference module.

I now need to create a View that will pull in the information for the Engine content item (its specifications, etc), as well as the total usage for that Engine by way of its related/linked Engine Usage content items. Instead of displaying each of the usage items separately, I need to apply a calculation to them, in the form of "Engine Usage for content item Engine123 'Hours driven'" multiplied by "Engine Usage for content item Engine123 'Mileage this trip'". I have looked into Views Calc, bu I cannot figure out how to first combine all of the Hours for the Engine Usage content item for any given engine, and then combine all of the Mileage for the Engine Usage content item for any given engine. If I can get to that point, I think I can then figure out the rest of the calculation on my own.

The end result will be a View block with a contextual filter that will pull the info in based on the ID of the content item being viewed. So when viewing the content item Engine123, the block with the calculated hours * mileage for Engine123's usage content items would be displayed.

Any ideas? Please let me know if any of this is too vague.

Answer 1

did you try to use the Aggregate option (use SUM field aggregation option) and Views Calc at the same time ? That might do the trick.

---

If not, you can use a PhP block (you have to send it the engine node id, I hope you use Panels or node.tpl.php).

Then, the code would be (I guess) ;

$engine_usages_nids = db_select('yourentityreferencefield', 'n')
  ->condition('entity_id', $the_engine_nid, '=')
  ->field('n', 'field_yourentityreferencefield_nid')
  ->execute()
  ->fetchCol;

$myfields_values = $db_select('field_data_hoursuagefield', 'h')
    ->condition('entity_id', $engine_usages_nids, 'IN')
    ->join('field_data_mileagefield', 'm', 'h.entity_id = m.entity_id)
    ->fields('h', array('hoursuagefield'))
    ->fields('m', array('mileagefield'))
    ->groupBy('h.entity_id')
    ->execute()
    ->fetchAssoc;

$total = 0;
while($record = $myfields_values) {
  $total = $total + intval($record['hoursuagefield'])*intval($record['mileagefield']);
}

echo($total);

This is all made by heart, I advise you to double-check :)

Have fun !