2 Replies Latest reply on Aug 24, 2012 9:31 AM by philmodjunk

    calculations and auto entries



      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:

      Case (
      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.

        • 1. Re: calculations and auto entries

          Try changing it to a text field instead of a date field.

          • 2. Re: calculations and auto entries

            What is the name of the field with the first calculation? Document Date or Destroy from Office? I'd guess Destroy From Office, but I'm not sure here.

            You have a syntax problem that is needlessly complicating things.

            In the first expression, you can use: Date ( 0 ; 0 ; 7 ) ;  Date ( 0 ; 0 ; 1000 )

            and get the same results, but I doubt that you want the values these will return as these will be dates that are centuries in the past. What value do you want returned by this calculation? And what return type have you selected for this calculation? Number or Date?

            In the second expression, you will get the same results if you use:

            Document Date + Destroy From Office

            Please note that in both your original expression and this much simpler version, both fields must be of type date ( or a calculation with Date as its Return type) in order for the calculation to work.

            Would I be correct that you have a whole series of heading and category codes for your document records? and each has their own data for how long they should be retained, when to destroy or move etc?

            If so, I'd create a table of this information instead of using calculation fields. This makes it easier for a non database developer to update and modify this information as needed without having to make a bunch of changes to different calculation fields. Instead, your database can use relationships to compute and copy these dates into each new document record by looking up data from this table. (This would be much like an invoice system where entering a product's ID code looks up it's unit price which is then used to compute the total cost for a specified quantity of that item--only with a date as the result instead of a cost value.