Adding total field amounts based on child fields

[Galaxy]

I have a site that is a directory of Recipients that receive Grants.  I have set up a template that lists all children (Recipients) of a parent page and need to output the total Grants of combined fields (each child page has a repeatable field called "amount_approved") 

The following works on an individual child page to display a running amount of grants received and their total, plus the date of the most recent date a payment was made

     <?php
        $total = 0;
        echo "<ol>";
        foreach ($page->grants as $grant) {
          echo "<li>{$grant->cheque_date} ";
          echo "<span>\${$grant->amount_approved}</span></li>";
          $total += $grant->amount_approved;
        }
        echo "</ol>";
        echo "<h3>Total grants: <span>\${$total}</span></h3> ";
        $last_cheque_date = $page->grants->sort('-cheque_date')->first()->cheque_date;
          echo "<p>The most recent cheque date is <span>{$last_cheque_date}</span>.</p>";
     ?>
 

That works fine... but what I am having trouble with is adding the total amounts approved into the parent of this page (the parent page is a listing of all child pages with a summary of a few things - the total of all amounts approved being one of them, and also whether the organisation has supplied their logo.

The following successfully loads a graphic "yes.png" if a logo has been uploaded, and "no.png" if it hasn't

foreach($result as $child) {
        echo "<li>";
        echo "<a href='{$child->url}' class='list-title'>{$child->title}</a>";
               $image = $child->logo;
            if (count($child->logo)){
                echo "<a href='{$child->logo->url}'><img src='/site/assets/uploads/yes.png'></a>";
            }
            else{
                echo "<img src='/site/assets/uploads/no.png'>";
            }
    
        echo "</li>";
    }
 

My question is, how do I also add the total of all amount_approved fields into the parent as I successfully did in the actual child page? I want it to display the total as a $ amount after the image has loaded.

[Galaxy]

Is no one there?

[kongondo]

Maybe change the topic title to be more descriptive? E.g. "Adding total amounts based on child fields" or something similar

[Galaxy]

Okay but how do I change the topic title?

[kongondo]

click edit, then click use full editor, then edit topic title

[Galaxy]

Thanks kongondo.

[kongondo]

Have you tested with something similar to...

$total = 0;
foreach ($pages->find("template=recipients, limit=10") as $grant) {
   $total += $grant->amount_approved;
}

 echo "<h3>Total grants: <span>\${$total}</span></h3> ";

//or, find using parent="/path/to/parent/" or parent=123

This works for me using normal integer fields in child pages. From what I recall, repeaters are accessed the same way as fields in other pages...

[Galaxy]

Thanks kongondo. I've done this below and all totals are $0

echo "<ul class='nav'>";
    foreach($result as $child) {
        echo "<li>";
        echo "<a href='{$child->url}' class='list-title'>{$child->title}</a>";
           $image = $child->logo;
            if (count($child->logo)){
                echo "<a href='{$child->logo->url}'><img src='/site/assets/uploads/yes.png'></a>";
            }
            else{
                echo "<img src='/site/assets/uploads/no.png'>";
            }
        
        $total = 0;
            foreach ($pages->find("template=recipients, limit=10") as $grant) {
               $total += $grant->amount_approved;
            }

             echo " Total grants: <span>\${$total}</span>";
        
         echo "</li>";
    }
    echo "</ul>";
 

[kongondo]

You need to implement that similar to the examples found here...about repeaters..

For example:

foreach ($pages->find("grants.amount_approved>0") as $grant) {  

//do stuff
}
//where, grants = the name of your repeater field; and amount_approved is the name of the field within the repeater field containing the amounts $ 

Hope this helps...it is just an example

Edited June 27, 2013 by kongondo

[Galaxy]

Thanks again for your help on this but the following error has happened

    echo "<ul class='nav'>";
    foreach($result as $child) {
        echo "<li>";
        echo "<a href='{$child->url}' class='list-title'>{$child->title}</a>";
           $image = $child->logo;
            if (count($child->logo)){
                echo "<a href='{$child->logo->url}'><img src='/site/assets/uploads/yes.png'></a>";
            }
            else{
                echo "<img src='/site/assets/uploads/no.png'>";
            }
        
        $total = 0;
            foreach ($pages->find('grants.amount_approved') as $grant) {
               $total += $grant->amount_approved;
            }

             echo "<span class='total'>\${$total}</span>";
        
         echo "</li>";
    }
    echo "</ul>";
 

Error:     Exception: Unknown Selector operator: '' -- was your selector
value properly escaped? (in
/var/www/vhosts/acumulus.co.nz/subdomains/legacytrust/httpdocs/wire/core/Selectors.php
line 165)

#0
/var/www/vhosts/acumulus.co.nz/subdomains/legacytrust/httpdocs/wire/core/Selectors.php(190):
Selectors->create('grants.amount_a...', '', '')

#1
/var/www/vhosts/acumulus.co.nz/subdomains/legacytrust/httpdocs/wire/core/Selectors.php(63):
Selectors->extractString('has_parent=1001...')

#2
/var/www/vhosts/acumulus.co.nz/subdomains/legacytrust/httpdocs/wire/core/Pages.php(143):
Selectors->__construct('has_parent=1001...')

#3
/var/www/vhosts/acumulus.co.nz/subdomains/legacytrust/httpdocs/wire/core/Wire.php(271):
Pages->___find('has_parent=1001...', Array)

#4 /var/www/vhosts/acumulus.co.nz/subdomains/legacytrust/httpdocs/wire/core/Wire.php(229): Wire->runHooks(Array, Array)

#5 [internal function]: Wire->__call('find', Array)

#6 /var/www/vhosts/acumulus.co.nz/subdomains/legacytrust/httpdocs/wire/core/Page.php(786): Pages->find('fi

This error message was shown because you are logged in as a Superuser. Error has been logged.

[Galaxy]

        $total = 0;
            foreach ($pages->find('grants.amount_approved>0') as $grant) {
               $total += $grant->amount_approved;
            }

             echo "<span class='total'>\${$total}</span>";
 

I am having trouble with this. Please can anyone see why this might not be working?

[kongondo]

Hi Galaxy,

The following should work (tweak the example). You need to get arrays within arrays...hence, nested foreach....

$total = 0;
//grant = name of repeater; grants = name of an integer field in the repeater "grant"
$monies = $pages->find("grant.grants>0");//grab pages whose repeater field "grants" value are more than 0

foreach ($monies as $peso) {

	$u = $peso->grant;

	foreach ($u as $z) {

		$total += $z->grants;
	}
}

echo "<h3>Total grants: <span>\${$total}</span></h3><hr> ";

[Galaxy]

Sorry kongondo this isn't working.

Here's my complete code for the template - I know I've frobably fckd up some nesting

grants= repeater

amount_approved = field (it's text not an integer though - and it's working on the actual page so I'm presuming it doesn't have to be an integer format)

if($page->numChildren) {
    $result = $page->children("limit=200");
    
    
    // render the pager
    echo $result->renderPager();
    echo "<div style='padding-bottom:10px;'>";
    echo "<span style='width: 300px; float:left; padding-left:60px;'>Recipient</span>";
    echo "<span style='width: 160px; float:left;'>Logo</span>";
    echo "<span style='width: 160px; float:left;'>Grants total</span>";
    echo "</div>";
    
    // render the children
    echo "<div class='scrollholder'>";
    echo "<div class='scrollpanel no4'>";
        echo "<ul class='nav'>";
    foreach($result as $child) {
        echo "<li>";
        echo "<a href='{$child->url}' class='list-title'>{$child->title}</a>";
           $image = $child->logo;
            if (count($child->logo)){
                echo "<a href='{$child->logo->url}'><img src='/site/assets/uploads/yes.png'></a>";
            }
            else{
                echo "<img src='/site/assets/uploads/no.png'>";
            }
        
            
            
          $total = 0;
            //grants = name of repeater; amount_approved = name of field in the repeater "grants"
            $dollars = $pages->find("grants.amount_approved>0");//grab pages whose repeater field "amount_approved" value are more than 0

            foreach ($dollars as $dollar) {

                $u = $dollar->grants;

                foreach ($u as $z) {

        $total += $z->amount_approved;
        }
    }

echo "Total grants: <span>\${$total}</span>";  
                  
         echo "</li>";
    }
    echo "</ul>";
    echo "</div>";
    echo "</div>";
    // render pager again? ok
    echo $result->renderPager();
}
 

Error:     Exception: Unimplemented operator in
DatabaseQuerySelectFulltext::match() (in
/var/www/vhosts/acumulus.co.nz/subdomains/legacytrust/httpdocs/wire/core/DatabaseQuerySelectFulltext.php
line 100)



#0
/var/www/vhosts/acumulus.co.nz/subdomains/legacytrust/httpdocs/wire/modules/Fieldtype/FieldtypeText.module(117):
DatabaseQuerySelectFulltext->match('field_amount_ap...', 'data',
'>', '0')

#1
/var/www/vhosts/acumulus.co.nz/subdomains/legacytrust/httpdocs/wire/core/PageFinder.php(286):
FieldtypeText->getMatchQuery(Object(DatabaseQuerySelect),
'field_amount_ap...', 'data', '>', '0')

#2
/var/www/vhosts/acumulus.co.nz/subdomains/legacytrust/httpdocs/wire/core/Wire.php(271):
PageFinder->___getQuery(Object(Selectors))

#3 /var/www/vhosts/acumulus.co.nz/subdomains/legacytrust/httpdocs/wire/core/Wire.php(229): Wire->runHooks(Array, Array)

#4 [internal function]: Wire->__call('getQuery', Array)

#5
/var/www/vhosts/acumulus.co.nz/subdomains/legacytrust/httpdocs/wire/core/PageFinder.php(145):
PageFinder->getQuery('getQuery', Arra



This error message was shown because you are logged in as a Superuser. Error has been logged.

[kongondo]

To use it with > or < it has to be an integer I believe. I think it works better with integers if you are comparing numbers . If you still want to use a text area, you can check if the text area is empty or you can do something like below. I am not sure if it will work correctly though...

$dollars = $pages->find("grants.amount_approved !=0");

//OR

$dollars = $pages->find("grants.amount_approved!=''");

Briefly tested...

[Galaxy]

Getting closer

this doesn't through up an error

$dollars = $pages->find("grants.amount_approved!=''"); 
 

- but does make ALL items have Total grants: $798684

...and, I can't seem to change that field to integer - only options are Datetime, Email, Text, Textarea, URL

[kongondo]

Strange...and without the ' '? i.e. != instead of !=' '....just guessing here...

Edit:

What about as standalone? Does the totals code work?

Edited June 28, 2013 by kongondo

[Galaxy]

yeah I took everything out except

       
         $total = 0;
            //grants = name of repeater; grants = name of an integer field in the repeater "grant"
            $dollars = $pages->find("grants.amount_approved!=''");//grab pages whose repeater field "grants" value are more than 0

            foreach ($dollars as $dollar) {

                $u = $dollar->grants;

                foreach ($u as $z) {

        $total += $z->amount_approved;
        }
        }

        echo "Total grants: <span>\${$total}</span>"; 
 

and it created 1 output only with

Total grants: $798684

[kongondo]

You've checked your fields are not empty? What type of text field is that? What do you mean it created one output? How many outputs do you want? I thought you wanted one grand total on the parent page? Is $798684 the correct amount for the grand total?

[Galaxy]

no I want totals for each item on the list (each item is a page).

You've checked your fields are not empty?

On the individual page, the following successfully shows all grants and the total of all the grants. Which prooves the fileds are not empty.

    $total = 0;
        echo "<ol>";
        foreach ($page->grants as $grant) {
          echo "<li>{$grant->cheque_date} ";
          echo "<span>\${$grant->amount_approved}</span></li>";
          $total += $grant->amount_approved;
        }
        echo "</ol>";
        echo "<h3>Total grants: <span>\${$total}</span></h3> ";
 

when you are on the list page I want it to show a summary beside the link to each individual page.

What do you mean it created one output?

It looks like it ahs totalled ALL the amounts_approved from all child pages. So yeah, I can use this as the grand total, but it's not what I want beside each page listing.

[kongondo]

OK...I am now getting you partially...My code does the grand total across the whole site...

I am not getting what you mean by list page.please explain..What is on that list? A list of ALL grants across the site? A list of ALL grants in a category (e.g. international grants)....an illustration would help I have a feeling it is about choosing the right selector (e.g. templates="xyz"  or parent="123"). Try adding the parent of the listed pages (assuming I get you correctly) to the selector..., i.e. parent=ID of parent or path/to/parent

Edit: if there are various parent pages that will have various listings of grants of their child pages, then try to add this to your selector..

$dollars = $pages->find("grants.amount_approved!='', parent=$page "); 

[Galaxy]

A list of all Recipients - which are child pages of the Recipient (what I call) category. Sorry I though my first post explained the scenario

http://legacytrust.acumulus.co.nz/recipient-list/

[kongondo]

OK,

Is the following the situation?

So, your tree goes something like...

recipient

acorn foundation

althorp trust

aglow int

abilities in action

1st O. scouts

Each of those child recipient pages (aglow, etc.). have several repeater fields.

And, adding the totals on each child page (e.g. aglow) adds up fine with the code on your first post

And, now you want to pull those child totals and list them as shown in the link to recipient list?

E.g.

aglow $1000

acorn foundation $4000

althorp trust $7000

Right?

[Galaxy]

Right. I couldn't have said it better myself

[Galaxy]

so I've got this

$total = 0;
            //grants = name of repeater; amount_approved = name of a field in the repeater "grant"
            $dollars = $pages->find("grants.amount_approved!='', parent=$page");

            foreach ($dollars as $dollar) {

        $total += $dollar->amount_approved;
        
        }

        echo "Total grants: <span>\${$total}</span>"; 
 

but all items output $0

[kongondo]

Change the following code...

$total = 0;
//grants = name of repeater; grants = name of an integer field in the repeater "grant"
$dollars = $pages->find("grants.amount_approved!=''");//grab pages whose repeater field "grants" value are more than 0

foreach ($dollars as $dollar) {

     $u = $dollar->grants;

     foreach ($u as $z) {

        $total += $z->amount_approved;
     }
}

echo "Total grants: <span>\${$total}</span>"; 

to

//grants = name of repeater; grants = name of an integer field in the repeater "grant"
$dollars = $pages->find("grants.amount_approved!=''");//grab pages whose repeater field "grants" value are more than 0

foreach ($dollars as $dollar) {

      $u = $dollar->grants;
      $total = 0;

      foreach ($u as $z) {

           $total += $z->amount_approved;
      }
echo "Total grants: <span>\${$total}</span>"; 
}