This is not a script, but a calculation.
Date ( Month ( Document Date ) ; Day ( Document Date ) ; Year ( Document Date ) + YearsToKeep )
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.
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?
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)
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.
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.)
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 )
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.