Using SUM in query for report

CalD

Hi Guys,

Have not attempted this before it seems like there should be a way to get it to work but I can't manage it...

Scenario is I have report which is finding records in one BO but I only want to display the each record IF the sum of records in another BO which relate to the first BO is not equal to zero.

I have attached conditions on the report to each field and this does hide the fields fine, but it does leave the whitespace of where the record would have been behind - so its not pretty.

My other thought was to add it to the query for the report but when I do this the query fails and I get nothing.

The query to find all the BOs for the report is like this :

FIND PCPosLocations WHERE PCPosLocations.Venue=LoggedInRegularUser.po_LoggedInVenue.ID

My attempt to add to it the SUM clause is like this :

FIND PCPosLocations WHERE PCPosLocations.Venue=LoggedInRegularUser.po_LoggedInVenue.ID AND SUM CashDec.TotalBeverage WHERE (CashDec.po_Location=PCPosLocations AND CashDec.ob_Daily.ob_Weekly=WeeklySummary)>0

Either I just can't use SUM in a Query like that, or something is wrong with the query.

Is there a way to fix this or do it another way in aware that I am not seeing?

Many Thanks

tford

FIND PCPosLocations WHERE PCPosLocations.Venue=LoggedInRegularUser.po_LoggedInVenue.ID AND SUM CashDec.TotalBeverage WHERE (CashDec.po_Location=PCPosLocations AND CashDec.ob_Daily.ob_Weekly=WeeklySummary)>0

I am confused by where WeeklySummary comes into context. Hard to help without knowing this.

BLOMASKY

why not add an attribute in the parent BO, and have a rule to do the sum when the child has changed. i.e.
Then, just condition the output on this field = 0?

Bruce

tford

why not add an attribute in the parent BO, and have a rule to do the sum when the child has changed. i.e.
Then, just condition the output on this field = 0?

That's where my mind was headed also, Bruce. Just didn't understand the data scheme fully.

CalD

Thanks guys I will try your suggestion - sounds like a good one, weeklysummary is in context because the report is run from a weeklysummary record... unless I am miss understanding that as well... still fully getting my head around Aware its been so long programming one tool!

Callum