8 Replies Latest reply on Jul 27, 2011 12:05 PM by philmodjunk

    Script for "Destroy by" date



      Script for "Destroy by" date



      I am creating a filing system where we enter the information for all the office documents into the FM database. Every file has a different shredding date. For every type of file I have assigned how many years to keep it for (1, 5, 10, permanent).

      Based on the document date and how long to keep it for I want to create an automatic script in another field called "destroy by".

      script function would be something like: Document Date + how many years (1/5,10) = shredding date

      if how many years = "permanent"

      then shredding date = "permanent"

      how would i write such a script?


        • 1. Re: Script for "Destroy by" date

          This is not a script, but a calculation.

          Date ( Month ( Document Date ) ; Day ( Document Date ) ; Year ( Document Date ) + YearsToKeep )

          • 2. Re: Script for "Destroy by" date


            That calculation returns the orginal document date it does not add YeasToKeep to it.

            Also, the years to keep is based on what the file type is which is determined by a set of inputs the user selects from a few drown menus (HEADINGname, CATEGORY name, FILE name, and SUBFILE name). FM randomly takes one of the YEARSToKeeps associated with one of these and put its in. but I want it to put in the YearsToKeep after the user has had a chance to enter in the above 4.

            Thanks PhilModJunk

            • 3. Re: Script for "Destroy by" date

              Yes it does. Make sure that Document Date is of type Date and YearsToKeep is of type number. In my last post, I assumed that YearsToKeep was a number manually specified by the user and that value is added to the year value of the document date.

              Do you have a related table of these values?

              If so, please describe the relationship between your documents table and this other related table.

              If not, how did you record the different years to keep numbers in your system?

              • 4. Re: Script for "Destroy by" date

                I have the following. I will show connection with >>>

                Enter Filing Data - where the data to be inputed is saved to

                All Document Locations - where all the HEADING, CATEGORY, FILE, SUBFILE, YearsTo Keep, etc is store

                I also have tables: HEADING, FILE, SUBFILE to help with the conditional value lists

                Enter Filing Data (field: HEADING)>>> All document locations (field: HEADING)

                Enter Filing Data (field: category) >>> File (field:category)

                Enter Filing Data (field: file)>>> subfile (field:file)

                Enter Filing Data (field:yearsToKeep) >>> All document locations 3 (field:yearsToKeep)


                • 5. Re: Script for "Destroy by" date

                  and the different years are just another field (YearsToKeep) connected to all the different possibilities for the document type.

                  In "All Document Locations" I have listed every type along with its respective HEADING, CATEGORY, FILE, SUBFILE, YearsToKeep, etc.

                  • 6. Re: Script for "Destroy by" date

                    I don't see a relationship that will link the correct record in Enter Fileing Data to the correct record in All document locations so that you can refer to the yearsToKeep field in your calculation. And in what table did you define this calculation?

                    You need this relationship, If I understand your setup correctly:

                    Enter Filing Data::Heading = All Document Locations::Heading AND
                    Enter Filing Data::category = All Document Locations::category AND
                    Enter Filing Data::file = All Document Locations::file AND
                    Enter Filing Data::subfile = All Document Locations::subfile

                    With that relationship, you can refer to All Document Locations::YearsToKeep in the calculation I suggested and the correct YearsToKeep value should be used in the calculation.

                    Note, you can greatly simplify this by adding a serial number field to All Document Locations and Selecting a serial number in a field in Enter Filing Data in order to link the two. Then you would not need to have all four of these fields defined in both tables. (And you could still use the conditional value lists we discussed in another thread here by using global fields for the value list formatted fields and a script that uses the values selected to copy the correct ID number  from All document locations to Enter Filing data.)

                    • 7. Re: Script for "Destroy by" date

                      So one more question:

                      Some of the YearsToKeeps say "permanent". Can I put an "if" statement in the calculation?

                      If YearsToKeep="permanent" then DestroyByDate="permanent"


                      DestroyByDate: Date ( Month ( Document Date ) ; Day ( Document Date ) ; Year ( Document Date ) + YearsToKeep )

                      • 8. Re: Script for "Destroy by" date

                        I recommend keeping text such as "permanent" separate from dates. Keeping them in the same field requires using a field of type text or a calculation that returns text and if you put a date in a text field, you can no longer search or sort on this field like you often need to do when working with dates.

                        I'd put "permanent" in a different field of type text. Selecting a value for the date field might be a bit of a challenge depending on the different ways you might need to use this date. You could assign a date in the far past or a date in the far future (say 2000 years in the future). You could assign an invalid date such as the text "permanent". This will display as a ?, but you can then test for this value and look for "permanent" in the associated text field to confirm that this record should not ever be destroyed.