AudioFreak Posted January 7, 2004 Share Posted January 7, 2004 Hello, I would like to script a find to find all records for a month. I understand that searching for 1/1/2003...1/31/2003 will find all records for January 2003. But is there an elegant way of doing this? I only need to build reports for each month from last year and this year. I could do this with 24 scripts but there has to be a better way. Any suggestions or ideas would begreatly appreciated. P.S. Happy New Year Everyone!!! Michael Link to comment Share on other sites More sharing options...
-Queue- Posted January 7, 2004 Share Posted January 7, 2004 Why don't you use a calculation of Month( Date ) & "_" & NumToText( Year( Date ) )? You can then sort and summarize by it. Link to comment Share on other sites More sharing options...
AudioFreak Posted January 7, 2004 Author Share Posted January 7, 2004 Queue, I should have known you would have a calc that would make this easier . How do you deal with capturing the start and end date for the report? Thanks, Michael Link to comment Share on other sites More sharing options...
Vaughan Posted January 7, 2004 Share Posted January 7, 2004 The end of the month is the day before the first of the next month. So, you could have the user enter the month and year into two global fields (gMonth and gYear) and use them in a calculation DateToText(Date(gMonth,1, gYear) & "..." & DateToText(Date(gMonth + 1, 1, gYear) - 1) Here the user enters the *number* of the month (1 to 12). A nicer interface can be made whereby they select the month name, however this will require another calculation to conver the monthname into the month number. The script will need to change to a layout with the date field on it, and use the Insert Calculated Result step to enter the date search string calculation. Link to comment Share on other sites More sharing options...
-Queue- Posted January 7, 2004 Share Posted January 7, 2004 Do you mean for the first and last day of each month? You can use a text calculation: DateToText( Date( Month( date ), 1, Year( date ) ) ) & "..." & DateToText( Date( Month( date ) + 1, 1, Year( date ) ) - 1 ) and put this on the header or subsummary part. Link to comment Share on other sites More sharing options...
Vaughan Posted January 7, 2004 Share Posted January 7, 2004 Cool, Queue... the same calculation used for two different purposes! Link to comment Share on other sites More sharing options...
-Queue- Posted January 7, 2004 Share Posted January 7, 2004 I guess that means we know we got it right. Kind of like 'checking your work' in high school. Link to comment Share on other sites More sharing options...
Biomass Posted January 7, 2004 Share Posted January 7, 2004 Hi Vaughan, I am seeking a way to find all the months represented between & including the Start Date and End Date. For example, if a project starts in January and is to finish in March, what calculation would I need to write so a find picks up the project to appear in discreet reports for January, February and March. Can't work out how to get it to recognise February. Hope this makes sense. Thanks for any suggestions. Cathy Link to comment Share on other sites More sharing options...
AudioFreak Posted January 8, 2004 Author Share Posted January 8, 2004 Thanks Queue and Vaughn! Your suggestions sent me in the right direction. Link to comment Share on other sites More sharing options...
-Queue- Posted January 8, 2004 Share Posted January 8, 2004 Have you tried Insert Calculated Result ["date field", "DateToText( gStartDate ) & "..." & DateToText( gEndDate )"]? The bold quotes are ones that FileMaker will automatically enter, so you won't need to. I show it this way because this is how it will look in ScriptMaker. Link to comment Share on other sites More sharing options...
Vaughan Posted January 8, 2004 Share Posted January 8, 2004 "what calculation would I need to write so a find picks up the project to appear in discreet reports for January, February and March" Have you tried a summary report, using a "month" field as the break? Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 6786 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now