6 Replies Latest reply on Jul 22, 2013 10:20 AM by DrewTenenholz

    How can I located records with no activity within last 30 days

      I would like to locate all records with no posted times and/or activities within the last 30 days using the FIND feature. Can anyone assist me please? Thank you.

        • 1. Re: How can I located records with no activity within last 30 days
          GaryTate

          First off you need to hold the information to serach on. Set yourself up a date (or timestamp) field that has an auto enter calc set to modification date (it's one of the options near the top of the field definition screen).

           

          You can then search on this field.

           

          Hope that helps.

           

          Gary

          • 2. Re: How can I located records with no activity within last 30 days

            Gary, I have just started using FMP.  Could you please provide more detailed instructions; would really appreciate your patience and guidance.  Thank you..

            • 3. Re: How can I located records with no activity within last 30 days
              GaryTate

              No problem.

               

              1. Enter the Manage Database area (where you set up your fields etc)
              2. Add a date field (you can do the same with a timestamp field)
              3. Click Optiuons (bottom right)
              4. Ensure Auto-Enter tab is selected
              5. Click Modification check box (second down)
              6. OK the selection

               

              I hope that makes sense ;-)

               

              Gary

              • 4. Re: How can I located records with no activity within last 30 days

                Gary, as mentioned, I am new to FMP.  Is it appropriate to ask if I could phone you (or you could phone me)? 

                 

                I have been locating information by going into the Leaders section and then clicking on FIND.

                 

                I then select ACTIVE: Is there a way to browse/code the date mm/dd/yy field, time hrs field and activity field to locate those records that have no activity within the last 30 days?

                 

                Thank you.

                 

                7-22-2013 11-57-03 AM.png

                • 5. Re: How can I located records with no activity within last 30 days
                  breezer

                  If I understand you correctly, you may want to script it as follows:

                   

                  Enter Browse Mode[]

                  Go to layout["your_layout"]

                   

                  #set the date range for search criteria

                  Enter Find Mode[]

                  Set Field[myTable::Date; Get(CurrentDate) - 30&"..."&Get(CurrentDate)]

                  Omit Record

                  Perform Find[]

                   

                  #verification for found records

                  If[Get(FoundCount)=0]

                  Show Dialog ["Message"; "No records have been inactive for the past 30-days. ALL records will be displayed."

                  Show All Records

                  End If

                  #

                   

                  Message was edited by: breezer needs the "Omit Record" within the find criteria

                  • 6. Re: How can I located records with no activity within last 30 days
                    DrewTenenholz

                    Mick --

                     

                    I think your question might be better worded,

                    "How can users SIMPLY and EASILY locate records where a DATE FIELD has been updated within the last 30 days using the FIND feature"

                     

                    There's a lot to say about how to do that, actually.  There are numerous tricks and ideas out there for all sorts of simplification of the user interface.

                     

                    Without ANY tricks, though, what one can already do in FileMaker is this:

                     

                    Enter FIND mode

                    Click into the DATE FIELD

                    type the symbols for greater than and equals (or select from the toolbar)

                    type the date on or after which you want to find records (e.g. today is 22 July, so type 22 June 2013 in your local system setting whether that be U.S.-style or European-style dates.)

                    Hit the 'enter' key or click the Find icon in the toolbar.

                     

                     

                    So, for the total newcomer, you should note that although date fields don't let you store information that is not date-appropriate, in FIND mode, you can enter comparison operators like > or < or >= or <= or even ... for a range from date1...date2.

                     

                     

                     

                    OK, then comes the part I always hate.  What does anyone mean when they say 30 days ago?  Do they mean EXACTLY 30 days, or four weeks, or the same date last month (what about 30 days prior to March 29?  is that 28 February or 29 February when applicable or what?)  Please provide some clarity.

                     

                     

                     

                    Then comes tha part about searching on a field that auto-enters the date the record was last updated and usually doesn't let users click into that field at all.  You can handle this well in FileMaker 11/12 with going into layout mode, selecting the field and using the inspector under appearance to check ONLY the box for 'Find mode' , which does exactly that.  You could also display that date field with a calendar popup, which would allow the user to select the data from a calendar (if that's helpful), and it would only work in Find Mode too.

                     

                     

                    After that comes all sorts of tricks, like scripting a search where the developer of the database has more control over the entire process, but you may not need to go there.  You can even set up a pop-up menu with words like 'This Month','Last Month', 'Q1', etc. and have the correct dates calculated and then appear in the field.  It all depends on how much flexibility you want users to have and what mischief you need to prevent them for doing.

                     

                    Let us know if this helps,

                    Drew Tenenholz