calculations and auto entries
Ok, so this is the simplest thing, but I cannot figure it out.
So I have files that all have different retention schedules. So some will be destroyed after x number of years, some will be moved to storage after x number of years, then destroyed after another x number of years.
I have three different fields where the destroy, storage, and destroy from storage number of years are stored("years to keep"). There is another 3 fields where these dates will be produced (ie. "document date" + "years to keep"= "expiry date"). Just to stress, there are different fields that state when it will be moved to storage, removed from storage, or destroyed without going to storage. So if its being destroyed right away, then the other two fields are empty (for the to/from storage moves).
My problem is that I cannot get Filemaker to produce this calculation. With all the different options date/number/calculation field or whether to do auto-enter calculation, etc.
So I need it to evaluate based on properties of the file (ex. if field A = "BEN" and field B = "Insurance") then number of "years to keep" = x. So far I have this as a calculation for the field that stores how long somethng will be there (which is calculated based on the type of file when it should be moved:
Heading_Code = "BEN" and CAtegory_Code = "Insurance";
Date ( Month (0); Day (0); Year (7));
Date ( Month (0); Day (0); Year (1000))
Then I have this as an auto-enter date field. This will resutlt in the so-call "expiry date":
Date ( Month (Document Date); Day (Document Date); Year (Document Date) ) + Date ( Month (Destroy from Office); Day (Destroy from Office); Year (Destroy from Office))
Right now what I have is returning "?" as the result.
Thanks in advance.