13 Replies Latest reply on Feb 26, 2013 2:59 PM by philmodjunk

    years of service date found between specific months

    KevinO'Neill

      Title

      years of service date found between specific months

      Post

           I need to find the the years of service for our members but our year is from May 1st to April 30th of the next year.

           I want to fill in a report that will show me members who celebrate 50, 55,60,65,  years of service they get a pin at 50 years and a wreath every 5 years after. 

           I have a field that shows their start date so i could base it on that.

        • 1. Re: years of service date found between specific months
          philmodjunk

               Is the day and month different for each member's start date?

               ( Get ( Currentdate ) - StartDate ) / 365.25

               is one calc for computing the elapsed years.

          • 2. Re: years of service date found between specific months
            philmodjunk

                 You can also treat their start date like a birthday:

                 Let ( [ M = Month ( StartDate ) ;
                            Y = Year ( StartDate ) ;
                           D = Day ( StartDate ) ;
                           Yt = Year ( Get ( CurrentDate ) ) ];
                           Yt - Y - Date ( M ; D : Yt ) > Get ( CurrentDate )
                          )

                 All such calculations that reference a get function should be unstored.

            • 3. Re: years of service date found between specific months
              KevinO'Neill

                   Yes each members start date is different

                    

                   Here is what I was playing with. I did a script and entered this as the calc

                   (Date ( 5 ; 1 ; Year(Get(CurrentDate))-50 ))...(Date ( 4 ; 30 ; Year(Get(CurrentDate))-49 ))

                    but the "..." in the middle isn't recognized. I want the script to find all members whose start is between 5/1/1963...to 4/30/64

                   your calc will give me the current number of years but I need to be more specific to the range I show. 

              • 4. Re: years of service date found between specific months
                philmodjunk

                     (Date ( 5 ; 1 ; Year(Get(CurrentDate))-50 )) & "..." & (Date ( 4 ; 30 ; Year(Get(CurrentDate))-49 ))

                     This assumes that you are either using set field while in find mode or setting a variable to this value for use in a stored find request.

                • 5. Re: years of service date found between specific months
                  KevinO'Neill

                       OK I think I got it

                       (Date ( 5 ; 1 ; Year(Get(CurrentDate))-50 )) & "…" & (Date ( 4 ; 30 ; Year(Get(CurrentDate))-49))

                       Now I need to convert this to a report where I get 50, 55, 60, 65 etc

                  • 6. Re: years of service date found between specific months
                    KevinO'Neill

                         yes got this right before you posted. Yes using a set field in the find

                    • 7. Re: years of service date found between specific months
                      KevinO'Neill

                           one last thing

                           my script is

                           Enter Find mode[]
                           SetField[Member-data:: MMDate;(Date ( 5 ; 1 ; Year(Get(CurrentDate))-50 )) & "…" & (Date ( 4 ; 30 ; Year(Get(CurrentDate))-49))
                      Set Error Capture [On]  (what does this do?)
                           Perform Find/Replace [No dialog; Fin Next]

                            
                           but then I need to omit deceased members I have a field that is "Status" and it gets set to "removed" I tried numerous things and they don't seem to work
                      • 8. Re: years of service date found between specific months
                        philmodjunk

                        Set Error Capture [On]  (what does this do?)---> this keeps the "no records found" error dialog from interrupting your script
                             Perform Find/Replace [No dialog; Fin Next]--->this is not the correct script step. it should be Perform Find []

                             

                                  but then I need to omit deceased members I have a field that is "Status" and it gets set to "removed" I tried numerous things and they don't seem to work

                             Replace Field Contents can be used to assign a value to every record in your found set in one step.

                        • 9. Re: years of service date found between specific months
                          KevinO'Neill

                               I think I misstated what i was trying to achieve. 

                               In the above find the date range finds all members whose service dates fall between the dates 50 years ago, but it also finds all deceased mebers who would have had thse service dates as well. I want to omit those found that are deceased.

                          • 10. Re: years of service date found between specific months
                            philmodjunk

                                 Create an Omit request as part of your find and enter "removed" in the status field.

                                 In a script, New Record/request when performed in Find Mode will create a new request and Omit record will turn that request into an omit request.

                            • 11. Re: years of service date found between specific months
                              KevinO'Neill

                                    OK the scriptis working well and doing what I want. The only thing that isn't real tidy is for instance: There are no members with 70 yrs service. So the Set Error capture does not return the error but it also stays in the screen without doing anything. If I turn Error capture on I get Modify find, Cancel or continue. None of which are tidy. So I suppose I need a CASE to leave the script and return to show all records. Any suggestions.

                                    

                                   Also is there a way to copy the script to paste it in here so I can label it with the check for Best answer? Doesnt seem to want to paste it.

                                    

                                   Thank you for your help as always!

                              • 12. Re: years of service date found between specific months
                                KevinO'Neill

                                     Can I use the "Last message choice" when the dialog comes up or does that only work with a custom dialog?

                                • 13. Re: years of service date found between specific months
                                  philmodjunk

                                       You should see an empty found set if your find does not find any records.

                                       You can use one of two options immediately after the Perform Find[] step:

                                       If [ Get ( FoundCount ) = 0 // no records were found]

                                       or

                                       If [ Not Get ( LastError ) // no records were found ]

                                       with either if step, you can then show All records, display a custom dialog explaining that no records were found etc.

                                  To post a script to the forum:

                                         
                                  1.           You can upload a screen shot of your script by using the Upload an Image controls located just below Post A Answer.
                                  2.      
                                  3.           You can print a script to a PDF, open the PDF, then select and copy the script as text from the opened PDF to your clipboard for pasting here.
                                  4.      
                                  5.           If You have FileMaker Advanced, you can generate a database design report and copy the script as text from there.
                                  6.      
                                  7.           If you paste a text form of the script, you can use the Script Pretty box in the Known Bugs List database to paste a version that is single spaced and indented for a more professional and easier to read format.