1 Reply Latest reply on Dec 3, 2010 9:45 AM by philmodjunk

    Change a field's contents by Date calculation?



      Change a field's contents by Date calculation?


      Hi - I need some assistance on the following please:

      We take calls/enquiries and log them into a database.

      The enquiry is datestamped.

      All enquiries are then displayed (via a portal) for users to action upon.

      However, not all enquiries are fully dealt with, or customers may lose interest.

      Therefore, we need them removing from the portal (but record kept) so I have set a "status" field to LIVE or CLOSED.

      I wish to have an automated script or function which automatically sets all "LIVE" records which are 14 days or older to "CLOSED".

      In words my calculation would be:

      IF Status = LIVE, AND Creation Date is old than 14 days, then set field to CLOSED.

      How can this be done please?  Examples welcome.

        • 1. Re: Change a field's contents by Date calculation?

          Are you using FileMaker 11?

          The following solution works with FileMaker 11, not older versions:

          Define a calculation field, cStatusFilter as:

          If ( Creation Date + 14 < get ( CurrentDate ) ; "Closed" ; Status )

          Make this an Unstored calculation or it won't update correctly as time passes.

          Set a filter on your portal to limit the visible records to those that are open:

          PortalTable::cStatusFilter = "Open"

          If you aren't using FileMaker 11, let me know and I'll describe a different approach.