Conditional function with calculated result is not working

[userfmfo]

Hello,

 

In a table called "ITEMS" I have a field called "ItemCode".  Beside it is a portal that references a related table called "ORDERS".  It shows the OrderYear and QtyProduced fields for every record in the "ORDERS" table that has the same ItemCode as the record being browsed.  The ItemsCode field is what links the "ITEMS" and the "ORDERS" tables together.

 

I have been able to get a summary of the total QtyProduced for all the records in the "ORDERS" table that have the same ItemCode as the record I am browsing, regardless of the year.  For this, the formula I am using is " =Sum(Orders::QtyProduced) ". and it is working.

 

But I'd like to break it out by year as well.  My formula for this is " =if(Orders::OrderYear="2011",sum(Orders::QtyProduced);0) ".  But it is returning a qty of 0 even though there are definitely records of this item being made in 2011.

 

Any ideas what I am doing wrong?  The OrderYear field in the "ORDERS" table is a calculation that is pulling just the year from a date field called "OrderDate".

 

Thanks very much,

Sara

[Fitch]

First, here's why that didn't work. Your formula is in the items file. It looks at the related orders and gets the FIRST related record (the first one that was created, unless your relationship is sorted). Is OrderYear 2011 in that first record? If not, you get zero.

 

Are you wanting one year at a time, or will you display multiple years? Is this going to be in a table or list view, or one item at a time? The answers to these questions might affect the approach you take, since these type of calculations can be slow, depending on the number of related records.

 

You might want to do this using ExecuteSQL, e.g. (off the top of my head -- not tested):

ExecuteSQL( "SELECT sum( QtyProduced ) FROM Orders WHERE OrderYear = ?" ; "" ; "" ; 2011 )