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

# years of service date found between specific months

### 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

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

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

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

(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

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

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

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

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

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

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

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

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

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.