3 Replies Latest reply on Mar 29, 2011 9:55 AM by philmodjunk

    Calculating when a Date is coming up

    MarcKatz

      Title

      Calculating when a Date is coming up

      Post

      I am using Filemaker 7.0 and and new to Filemaker.  I have a question concerning adding a calculation field that will tell me that a date is coming up.  As an example, we are visiting numerous project sites at different times.  I have created fields that tell me the dates for example that we must go to Company A for Meeting X, Meeting Y, Meeting Z and I want to write a calculation to create a new field that will remind me when a meeting is coming up.  The output might say something like, "Call Company X to confirm Meeting Y".  How would I create this?

      Thanks,

      Marc

        • 1. Re: Calculating when a Date is coming up
          philmodjunk

          Do you just need the message or do you also need to control when the message appears? If you need the message to appear at a specific time what is the criteria for that? (1 day before the meeting, 1 week?, or?)

          • 2. Re: Calculating when a Date is coming up
            MarcKatz

            Hi Phil,

            In most cases I will want the message to appear one week before the meeting, but also, if possible, to remove the message when the date is past, if that's possible or maybe then I can just create another message like "Date has passed" so I don't have a bunch of messages saying a date is due when it no longer is.

            Thanks,

            Marc

            • 3. Re: Calculating when a Date is coming up
              philmodjunk

              It will be easiest if you design a table for all your meeting dates so that each record has just one meeting date. Make sure that the meeting date field is of type date, not text. This table can be linked to your company record so that a portal on the company layout can display a list of all pending meeting dates. (You may have already done this.)

              You are using a pretty old verion of FileMaker that I do not have, so test carefully and let me know if you hit something that your version of FileMaker does not let you do.

              You can use File Options (may have a different name in your copy) to set up a script to run when the file is opened. This script can perfrom a find for all up coming meeting dates and display them in a list for you to see. This find can be set up to only warn of meetings that will take place one week or sooner to avoid warning you too soon about meetings still a ways in the future.

              Go To Layout [MeetingDates]
              Enter Find Mode[]//clear the pause check box
              Set Field [MeetingDates::MeetingDate ; Get ( CurrentDate ) - 7 & "..." & Get ( CurrentDate ) ]
              Set Error Capture [on]
              Perform Find []
              Sort [Restore ; No dialog ] //sort in ascending order by meetingDate so that closest meeting date is listed first
              If [get ( FoundCount ) = 0 ]
                 Show Custom Dialog ["There are no meetings scheduled in the next 7 days"]
                 Go To Layout[//select a layout where you want to go next]
              End If

              The MeetingDates layout can be a list view layout designed to list each meeting in rows with a button that takes the user to that company record on a company layout. You can include fields, such as the company name, from the company table in these rows to provide more information.