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.
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.
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.
(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.
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
yes got this right before you posted. Yes using a set field in the find
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
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.
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.
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.
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!
Can I use the "Last message choice" when the dialog comes up or does that only work with a custom dialog?
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]
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:
- You can upload a screen shot of your script by using the Upload an Image controls located just below Post A Answer.
- 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.
- If You have FileMaker Advanced, you can generate a database design report and copy the script as text from there.
- 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.