9 Replies Latest reply on Nov 21, 2009 7:27 PM by HWRE

    Current Date = Expiration Date

    HWRE

      Title

      Current Date = Expiration Date

      Post

      Data base is on Windows XP and using FM Pro 9 Advanced  setup on IWP

       

      I have a value list (Field name "Status"). The value list has a dropdown list with the terms Active, Pending, Sold, Cancelled, Withdrawn, and Expired. I also have a date field with the Expiration date previously entered. How can I have the Status field automatically change from ACTIVE to EXPIRED when Current Date = Expiration Date.

       

      I would also want it to change Contingent to Expired on the = date

       

      And Pending to SOLD on a given date

        • 1. Re: Current Date = Expiration Date
          philmodjunk
            

          You need an unstored calculation field, but you need to be able to edit the value also--that's how I read your description anyway.

           

          Lets Add a calculation field, cStatus

           

          If ( ExpirationDate < get ( currentdate ) and IsEmpty(status) ; "Expired"; status)

           

          This field will return return the user selected status value in status unless no value has been selected and the expiration date has been reached.

          • 2. Re: Current Date = Expiration Date
            HWRE
              

            Windows XP and FileMaker Pro 9 Advanced

            Previous request clarification Current Date = Expiration Date

            I have the following fields.

             

            Expiration Date (Date Field)

            Listing Date (Date Field)

            Closing Date (Date Field)

            Status (A Value List ) With the following values:

             1 Active

             2 Contingent

             3 Pending

             4 Sold

             5 Expired

             6 Cancelled

             7 Withdrawn

            The Status field is never empty.

            The value list auto enters 1 Active when record is created.

            The Listing Date also auto enters when record is created.

            The Expiration Date and all of the othe items in the value list is entered by the user EXCEPT 5 Expired.

            1.  I need the Status field to change from 1 Active to 5 Expired on the Expiration Date.

            2.  I need the Status field to change from 2 Contingent to 1 Active on Closing Date + 10 Days.

            3.  I need the Status field to change from 3 Pending to 4 Sold on the Closing Date + 10 Days.

            All help appreciated

            HWRE

            • 3. Re: Current Date = Expiration Date
              philmodjunk
                

              Hmmm,

               

              In that case, I think a script would work better:

               

              Find all records with status = "Active" and currentdate > Expiration date.

              Use replace field contents to change status to "Expired"

               

              Find status = "contingent" and currentdate > closing date + 10

              Use replace field contents to change status of these to "Active"

               

              Find status = "Pending" and currentdate > closing date + 10

              Use replace field contents to change status to "Sold"

               

              Depending on your system there are several different ways to run the above script once every morning before you open for business.

              • 4. Re: Current Date = Expiration Date
                HWRE
                  

                Where am I going wrong?

                I have the following Script

                 

                Enter Find Mode [Restore]

                Perform Find [Restore]

                If [Get (CurrentDate) > MLSDirect::Expiration Date

                Replace Field Contents [No dialog; MLSDirect:: Status; "5 Expired"]

                 

                Thanks HWRE

                • 5. Re: Current Date = Expiration Date
                  philmodjunk
                    

                  Try it this way:

                   

                  Enter Find Mode []

                  Set Field [MLSDirect:: Status; "Active"]

                  Set Field [MLSDirect:: Expiration Date; "< " & get (Currentdate)]

                  Set error capture [on]

                  Perform Find []

                  Set error capture [off] 

                  If [Get(foundCount) > 0]

                    Replace Field Contents [No dialog; MLSDirect:: Status; "5 Expired"]

                  End If

                   

                  Notes:

                  You needed to specify the date expression as part of your find and it can't be set up in a Restored Find request.

                  Set Error Capture prevents filemaker from interrupting your script with a dialog if no records are found

                  Replace Field Contents should only be done in "after hours" type circumstances when there are no other users editing records. If a user edits one of the records your script is trying to modify with Replace Field Contents, Record locking will prevent it from being updated and you'll see a generic message that one or more records weren't updated but not which records those were.

                  • 6. Re: Current Date = Expiration Date
                    HWRE
                      

                    The below script returns 0 Records Found.

                    When I enter "1 Active" in the Restored Find the script returns the records but does not change field to "5 Expired"

                     

                    Enter Find Mode[]

                    Set Field [MLSDirect::Status= "1 Active"

                    Set Field [ MLSDirect::Expiration Date= "<" & Get (CurrentDate)]

                    Set Error Capture [On]

                    Perform Find []

                    Set Error Capture [Off]

                    If [Get (FoundCount)>0]

                    Replace Field Contents [MLSDirect::Status; Replace with calculation "5 Expired"]

                        [No dialog]

                    Thanks for your help

                    HWRE

                    • 7. Re: Current Date = Expiration Date
                      philmodjunk
                        

                      Take a close look at both set field script steps. Judging by your posted example, they are not set up correctly.

                       

                      For the first step, click "specify target field" and select MLSDirect::Status as your target field. Then click the second specify... button (calculated result) and enter "1 Active".

                      For the second step, click "specify target field" and select MLSDirect::Expiration Date as your target field. Then click the second specify... button (calculated result) and enter "<" & Get (CurrentDate).

                       

                      Now these two script steps will look like:

                      Set Field [MLSDirect:: Status; "1 Active"]

                      Set Field [MLSDirect:: Expiration Date; "< " & get (Currentdate)]

                       

                      Instead of:

                      Set Field [MLSDirect::Status= "1 Active"]

                      Set Field [ MLSDirect::Expiration Date= "<" & Get (CurrentDate)]

                      • 8. Re: Current Date = Expiration Date
                        HWRE
                          

                        Thank you for all your help.  It works perfect!!

                        HWRE

                        • 9. Re: Current Date = Expiration Date
                          HWRE
                            

                          Need help Solution for IWP

                          FMP 9 Advance on Window XP

                           

                          I have a value list field with ACCEPT and DECLINE (radio buttons)

                          I have layout named Registration

                          And a layout named Terms of Service

                          On Terms of Service

                          If Accept is checked I would like the client go to the layout Registration and enter data in New Record

                          and If Decline is checked give message Must accept Term of Service to Register for Service (IWP)

                           

                          Also how do I set radio button to default to Decline.  The default works on host computer but not on IWP