AnsweredAssumed Answered

Reiterated calculation not working - can u help me?

Question asked by powell on May 1, 2012
Latest reply on May 2, 2012 by BruceHerbach

I've got an Items table with a related ItemsYear table, on which I need to store per year data.

Among manually entered data, I need to show what happened during the year. So, for the items I will have:

  • Sum of qty coming from documents (I have here two tables: documentsHeaders and documentsRows)
  • Sum of qty coming from other stock movements (simple single table: otherMovements)


So, overall, I have 5 tables.

In order to be able to read data splitted into years:

  • I have created two aliases of the ItemsYear table and called them ItemsYear_alias_mov and ItemsYear_alias_doc
  • I have put a field called cYear (calculated year) in otherMovements and documentHeaders (on which I've got a date)
  • I have added the cYear field also in documentsRows (Warning! The field is calculated from the related documentHeaders date field. This works fine but won't work afterwards...)
  • among the others, I have created two specific relationships:
    • OtherMovements with ItemsYear_alias_mov on Item=Item and cYear=Year
    • DocumentRows with ItemsYear_alias_doc on Item=Item and cYear=Year


On the ItemsYear table we have these two calculated fields:

  • QtyMovements = from ItemsYear_alias_mov, = OtherMovements::sQuantity (where sQuantity is Summary - Total of quantity). This works perfectly.
  • QtyDocuments = from ItemsYear_alias_doc, = DocumentRows::sQuantity (where sQuantity is Summary - Total of quantity). This doesn't work as far as cYear (describerd above) refers to the related DocumentHeaders table (so, here we have the reiteread calculation indicated in the subject). If I change the filed to a local manually entered field, it works.


Can anyone help me finding a workaorund for this?

It's just driving me crazy!!!


Thank you.


If anyone wants my test database, it can be found here: