9 Replies Latest reply on Jul 26, 2012 12:02 PM by philmodjunk

    Auto-Enter Field



      Auto-Enter Field



      I need Filemaker to calculate the date that old files need to be destroyed or moved to storage. This calculation will be based on two fields (Document Date and Years to Keep [how long the file is meant to be kept for]) and will affect two fields (Destroy Calculated Date [when to destroy it/move to storage] and location.

      In order to calculate when to destroy/move the files I have written the following for the calculated Value in the options for the field that is supposed to display the destroy/move date called "Destroy Calculated Date".(although this calculation is receiving an error from the program and is not correct). Also, "Destroy Calculated Date" is a date field, although (as you will see below) it may display text. Is that a problem?

      If (
      YearsToKeep = "Permanent"; Destroy Calculated Date = "Permanent")
      Else If (
      YearsToKeep = ""; Destroy Calculated Date = "Permanent")
      Else (
      Date (Month (Document Date) ; Day (Document Date); Year (Document Date) + Hold for::Years to keep)

      End If

      Now for some of the files, for some of tehe files I want a list of when to destroy them. Meanwhile for some I do not want a list of when to destroy them, but rather when to move them into storage (both are displayed in the same filed: "Destroy Calculated Date"). I have a field for file location, but certain files I want FileMaker to automatically change the file location to storage and also make a list of the destroy/move to storage so that the physical files can be destroyed' or moved accordingly.

      The logic is simple, but I do not know how to translate that into Filemaker language. Thanks.

        • 1. Re: Auto-Enter Field

          although (as you will see below) it may display text. Is that a problem?

          It is a problem. Date fields store a number counting the number of days elapsed since 12/31/0000 and trying to display text in this field will result in ?? as FileMaker has no way to display text in such a field.

          I would use two fields, one for the date and one for any text messages.

          The calculations might look like this:


          If ( Not IsEmpty ( GetAsNumber ( YearsToKeep ) ) ; Date ( Month ( Document Date ) ; Day ( Document Date ) ; Year ( Document Date ) + YearsToKeep ) )


          If ( IsEmpty ( GetAsNumber ( YearsToKeep ) ) ; "Permanent" )

          You should also look into using the case function as this is a very good function to use in place of If when you need more than two outcomes.

          I don't see sufficient detail in your post to answer the rest of your questions.

          • 2. Re: Auto-Enter Field

             Each file has a few fields associated with it (Document Date, Document Location, Destroy Calculated Date, Heading name, category name, etc) . And whatever is in Filemaker is associated with a physical file (either in the office or in storage)

            I want Filemaker do to the following for the "Document Location" Field that differs based on the properties of the file. For example, if the file's heading is "Governance" I want it to act different. So here are the steps I need....

            1) Calculate Destroy Calculated Date (as you showed me in the above post)

            ..then have a button with the following sccript function: once we are on or past the Destroy calculated date for a specific file...

            if heading = "governance", 2) change Document location to "IM" 3) create a report with the list of all the documents that were just changed to IM, so that an office employee can physically move the files to IM (storage)


            if heading is NOT "governance" 2) create a report with the list of all the documents whose Destroy Calculated date has past (to create a list of all files whose Destroy calculated date is on or before today's date) 3) save this list to excel, so that there is a record of what was deleted 4) delete those records that have reached their destroy calcuclate date from the FileMaker Database

            • 3. Re: Auto-Enter Field

              Do you have only one table in your database?

              I ask this because your said:

              if heading = "governance"

              I would guess that this is the same field as "Heading name" mentioned at the start of your last post, but this also raises questions. One record may have heading name = "governance" and another something else. I think you want a script that pulls up all records for documents due to be moved into Storage and also all records that need to be destroyed in two reports, one for each disposition.

              The location field could be a calculation field that automatically changes the location shown to "IM" if destruction date is greater than or equal to the current date and the header name field equals "governance" or you could use a script to perform a find for the same records and then update them with Replace Field Contents--though this can be problematic if there is any chance that another user might be editing on of the records to be updated at the time the script is performed.

              3) why do you need to export the data to an excel file? This report can also be printed or saved as a PDF.

              4) I do not recommend that you delete the records. It would be safer to mark them as "deleted" in a status field so that they can be excluded from view, but still stored in the database. That allows you to better respond to possible errors where a document might be listed incorrectly for destruction or moving to storage due to a data entry error. (You can then bring back the record just by changing the value in this status field.)

              • 4. Re: Auto-Enter Field

                here is what I came up with and it's giving me trouble:

                I do have different tables. The heading name, category name, and file name for each file is from a drop down menu and comes from a different place.



                I don't want the user to worry about memorizing where the files should go.

                So here is a new step….as they enter new data, filemaker will do the work for them based on what type of file it is.


                File Location calculation:

                Case (

                Heading = "governance" and

                Category = "council" and

                Destroy Calculated Date > Get (CurrentDate);

                File Location = "BR";


                Heading = "advocacy" and

                Category = "federal government" and

                Destroy Calculated Date > Get (CurrentDate);

                File Location = "NO";


                Heading = "advocacy" and

                Category = "municipal government" and

                Destroy Calculated Date > Get (CurrentDate);

                File Location = "NO";


                ***The Destroy calculated date is only > because it when entered it will never have passed already***


                Now I have programmed a button that triggers the following script.


                If [Enter Filing Date::Heading = "governance" and Enter Filing Data::Destroy Calculated Date <= Get (CurrentDate)]

                   Set variable [$TodayDate; value: Get (CurrentDate)]

                  Perform Find*

                  Go to Layout ["Move Report" (Enter Filing Data)]

                  Save Records as PDF [Restore; Records being browsed]


                  Perform Find [Restore]***

                  Go to Layout ["Destroy Report" (Enter Filing Data)]

                  Save Records as PDF [Restore; Records being browsed]

                End If



                * Enter Filing Data:: Heading = "governance"

                AND Enter Filing Data:: Destroy Calculated Date <=$TodayDate


                ** Enter Filing Data::Destroy Calculated Date <= $TodayDate

                • 5. Re: Auto-Enter Field

                  "giving me trouble" is a bit vague. Wink

                  File Location calculation:

                  Case ( Heading = "governance" and Category = "council" and Destroy Calculated Date > Get (CurrentDate); File Location = "BR";

                  Is incorrect syntax if you want "BR" instead of a 1 or 0 returned.

                  It should read:

                  File Location calculation:

                  Case (Heading = "governance" and Category = "council" and Destroy Calculated Date > Get (CurrentDate); "BR";

                  And you can't use an expression such as Enter Filing Data:: Destroy Calculated Date <=$TodayDate

                  In a stored find request. See this link for some ways to do this in a scripted find:


                  • 6. Re: Auto-Enter Field

                     you mentioned that I can hide some files, so that they are not deleted, but made invisible from view. How can I make such a function as part of a script?

                    • 7. Re: Auto-Enter Field

                      Say you add a number field named 'deleted' to your table and putting a 1 in the field marks it as "deleted".

                      Scripts that find records can set up find requests that omit all records where there is a 1 in this field.

                      Enter Find mode []
                      Set Field [YourTable::YourField ; //put criteria for records that you want to find here]

                      #add as many set field steps as you need here

                      New Record/Request
                      Set field [Yourtable::Deleted ; 1 ]
                      Omit Record
                      Set Error Capture [on]
                      Perform Find []

                      You can also use the OnModeExit trigger to perfom this script whenever the user exits find mode:

                      Enter Find Mode []
                      Set field [Yourtable::Deleted ; 1 ]
                      Omit Record
                      Set Error Capture [on]
                      constrain Found set []

                      And this will enable users to perfom their own finds on the layout, but when the find is performed, the script trigger kicks in and constrains the found set they have produced to only those records not marked as "deleted".

                      If you have FileMaker Advanced, you can also use custom menus where your own scripts replace the Show All Records and Show Omitted Only actions and these scripts can also omit the "deleted" records.

                      • 8. Re: Auto-Enter Field

                         ok, so I am trying to mark the found sets as deleted. here is what I have. However, the "deleted" field still remains unchanged.

                        Aside from doing the Insert Text function (as I have here), I tried to do "Set Field" and had the deleted field as a Number field that was meant to have a "1" inserted, but it still did not work. What am I doing wrong?


                        • 9. Re: Auto-Enter Field

                          Insert Text--a step I only use if I have no alternative, and Set Field will only modify one record. You have a set of records that all need to be marked as "delete".

                          Use Replace Field Contents to mark all the records as deleted.

                          But please note that there appears to be other issues with this script.

                          Set Field [Enter Filing Data::Heading_Code ; Enter Filing Data::Heading_Code  ≠ "GOV" ]

                          Will enter a 1 into that field as find criteria as it will always evaluate as True.


                          New Record/Requst
                          Set Field [Enter Filing Data::Heading_Code ; "GOV" ]
                          Omit Record

                          and move these lines to be just above Set Error Capture so that the lines that specify a date range come before these.

                          You also have a set variable step that looks like it should be located before the Enter Find Mode step instead of after.