4 Replies Latest reply on Aug 7, 2015 9:35 AM by Nicolo'Bello'

    Find records older than a month

    Nicolo'Bello'

      Title

      Find records older than a month

      Post

      Hello everybody, 

      I need a script to find all the records from the oldest to 30 days ago, but I want to see them only if the "paid" or "cancelled" checkboxes are unchecked.

      I'm going to use this to take the outstanding records (older than a month) under control.

      I tried hard to write a working script, but I'm new at this and I can't find the solution! 

      Can anybody help me? Thank you a lot!!!

       

        • 1. Re: Find records older than a month
          philmodjunk

          To find all records dated from 30 days or older do this:

          Enter Find Mode []
          Set Field [YourTable::YourDateField ; "<" & Get ( CurrentDate ) - 30 ]
          Set Error Capture [on]
          Perform Find []

          To limit the results to those with a specific value in a field, include another set field step to specify that value. But the fact that you mention a check box format might complicate this process a little bit.

          Do you have two fields with single check boxes for "Paid" and "cancelled" or do you have one field with multiple check boxes?

          • 2. Re: Find records older than a month
            Nicolo'Bello'

            Thank you for the quick answer!

            I tried the script and appears to work but the results are wrong! I have 3 records older than 30 days but only two appears and there's an other one with today's date. I suspect this is happening because my calendar is set as DD/MM/YYYY but the script interprets it as MM/DD (and in this case the search result would be right!). How can I fix this? 

            Let's move on checkboxes: I have two field with single check boxes. But I can change them e.g. into radio buttons and single field, just tell me the easiest way!

            • 3. Re: Find records older than a month
              philmodjunk

              Is your field a field of type date or text? For this to work, it should be of type date. And your locality based format of either MM/DD/YYYY or DD/MM/YYYY should not make a difference here.

              This needs to be correct before adding additional criteria.

              Enter Find Mode []
              Set Field [YourTable::YourDateField ; "<" & Get ( CurrentDate ) - 30 ]
              Set Field [YourTable::PaidCheckBox ; "=" ]
              Set Field [YourTable::PaidCheckBox ; "=" ]
              Set Error Capture [on]
              Perform Find []

              This should find all records older than 30 days but only if both of these check box fields are empty.

              • 4. Re: Find records older than a month
                Nicolo'Bello'

                You're right, the calendar was ok but I forgot to switch the field type into date…

                Now the script works perfectly fine! Thank you so much for your help!!!