5 Replies Latest reply on Apr 29, 2016 5:52 AM by disabled_morkus

    How many in the past year?

    EssexBiker

      Dumb Head here again, sorry, I have exhausted almost all the combinations of search strings and my trusty books!

       

      All I am trying to do, with little success , is to calculate how many absences a staff member has had during the past year ( 365 days) It needs to be dynamic based on the current date.

       

      It needs to be dynamic based on the current date, in other words, those more than a year ago, fall off and aren't included.

       

      I have and Absence Start Date  ( date field) and that's all I will be basing the count of absences on.

       

      Dynamically I am trying to find the count of the start dates during the past year, using the system date ( Get date)

       

       

      First correct answer received a post card from Sunny (not) Blighty

       

       

      All the best x

        • 1. Re: How many in the past year?
          jaysayers

          To be dynamic, you are going to want to stay away from Perform Find with set criteria. Instead:

           

          1. Enter Find Mode [ ]

          2. Go to Layout [Your Layout]

          3. Set Field [Your Table Name::Your Date Field]; ">=" & Get ( CurrentDate ) - 365]

          4. Perform Find [ ]

           

          Notice that you don't "pause" in step 1 and you don't "store" a particular find criteria in step 4.

           

          Run the script once to see if it finds what you are looking for. After that, use the Modify Find menu command (command + R / control + R) to show you what the script above enters for the find criteria.

           

          Obviously this is a very simple example. You'll probably get into error trapping later if no records match the found set.

           

          In addition, the Find criteria above would also include Today if the staff member was absent Today. If you do not want to include Today's date, you would have to alter the criteria just a bit more.

          1 of 1 people found this helpful
          • 2. Re: How many in the past year?
            rgordon

            Create a start date calc field (Get(Currentdate)-365). Create a relationship where the start date calc <= absence start date.  Then use Count(your new relationship::absence start date) in a calc to get the number.

            1 of 1 people found this helpful
            • 3. Re: How many in the past year?
              alquimby

              Try the attached. If I've done it right, the 4-29-2015 absent date should go from 1 to 0 in the Count It field tomorrow. And the summary field should decrease by 1.

              1 of 1 people found this helpful
              • 4. Re: How many in the past year?
                EssexBiker

                Jay, Alllen, rgorden, you are all legends.

                 

                Note to self, think simple in future.

                 

                One very happy Limey here.

                 

                 

                • 5. Re: How many in the past year?

                  This sounds to me partly like a database design problem.

                   

                  I may be saying more or less the same thing as others, but since a staff member could have 0 or many absences, I would model this as a 1:zero or Many relationship.

                   

                  Each absence for a particular staff member gets a new child record with date of absence in the absence table.


                  Then, it's a SIMPLE matter to count the absences for any period for any or all staff members or view them with a portal, or whatever.

                   

                  Does this make sense?

                   

                  HOPE THIS HELPS.

                   

                  - m