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!!!
If anyone wants my test database, it can be found here: http://dl.dropbox.com/u/133574/test.fp7