3 Replies Latest reply on May 2, 2012 9:26 AM by BruceHerbach

    Reiterated calculation not working - can u help me?

    powell

      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: http://dl.dropbox.com/u/133574/test.fp7