1. Enter Find mode.
2. In the date field enter "1/1/2016 … 31/3/2016"
3. Perform Find
Thank you Keywords for replying.
I am aware of search date ranges. Let me be more accurate.
What I need is to create a script that automates the procedure of setting these date ranges and also to automatically get the current year, so that it will correctly get the invoices every year from now on, not only 2016.
Then, I will assign this script to a button so that when the user presses it gets all the invoices between 1/1and 31/3 of the current year, be it 2016, 2017 etc.
I hope I made it more clear now.
I use this formula -quite dumb- to calculate the quarter. You can add the year to differentiate quarter of each year:
Round ( ( ( Month ( InvoiceDate ) / 3 ) + 0,49 ) ; 0 ) --> 1,2,3,4
Round ( ( ( Month ( InvoiceDate ) / 3 ) + 0,49 ) ; 0 ) & Year ( InvoiceDate ) --> 12016, 22016, etc.
You can use the same formula to calculate the quarter of the current date and / or to search for that field.
Fair enough. Two approaches I suggest you consider are:
1. Create two global date fields called, say, searchFROM and searchTO, set up with drop down calendars; put these on a utility layout; user enters dates required for the search, then clicks a button to run your FIND script, which would follow basically the steps I previously indicated, but enter the date range as a calc (searchFROM & "…" & searchTO). That would enable you to script any search period the user chooses. You could extend this method a bit to have script variations for current quarter, current year, current financial year, etc.
2. Create calc fields to generate values such as quarter, fin year, etc,and then find by those fields. To implement this sort of thing I created a series of custom functions, such as this on to assign a given date field to a quarter:
[ theMonth = Month ( theDate ) ;
theYear = Year ( theDate ) ;
theQuarter = Case (
theMonth = 1 or theMonth = 2 or theMonth = 3 ; "Mar" ;
theMonth = 4 or theMonth = 5 or theMonth = 6 ; "Jun" ;
theMonth = 7 or theMonth = 8 or theMonth = 9 ; "Sep" ;
theMonth = 10 or theMonth = 11 or theMonth = 12 ; "Dec" ;
"ERROR" ) ] ;
theQuarter & " " & theYear )
Feel free to use it if it helps you along.
Enter Find Mode
Set Field [ Table::theDate ; Let ( [ cy = Year ( Get ( CurrentDate ) ) ; m = $i * 3 + 1 ] ; Date ( m ; 1 ; cy ) & ".." & Date ( m + 3 ; 0 ; cy ) ) ]
Set Error Capture [ on ]
# [ do stuff with found records ]
Exit Loop If [ Let ( $i = $i + 1 ; $i = 4 ) ]
As keywords said, you can have the script enter Find mode, set the creation date field to a date range and then perform the find.
To get the appropriate date range values, you want to calculate:
1. the first day of the quarter
Date ( 1 ; 1 ; Year ( Get ( CurrentDate ) ) )
2. the last day of the quarter
Date ( 4 ; 1 ; Year ( Get ( CurrentDate ) ) ) - 1
You can generalize a bit here to get the month numbers given the quarter number (passed in as a script parameter, for example):
_qtr = Get ( ScriptParameter ) ;
_startMonth = ( (_qtr - 1 ) * 3 ) + 1 ;
_endMonth = _startMonth + 3 ;
_yr = Year ( Get ( CurrentDate ) ) ;
_startDate = Date ( _startMonth ; 1 ; _yr ) ;
_endDate = Date ( _endMonth ; 1 ; _yr ) - 1
_startDate & "..." & _endDate
How much code to do a simple thing!
Thanks everybody for your replies.
I will make some testing and let you know.
Yes, a fair amount of code. But it is easy to follow for beginners.
Overall it would be easier to re-work the process the way you did, and calculate the quarter in a field:
InvoiceQuarter = Ceiling ( Month ( InvoiceDate ) / 3 )
Have a field to store the year of the invoice:
InvoiceYear = Year ( InvoiceDate )
And then the search script can set the InvoiceQuarter field to the parameter passed in and set the InvoiceYear field to Year ( Get ( CurrentDate ) ) and then perform the find.
Not only that, these two calculation fields will make it easier to create reports that break down the invoices by year and quarter in a subsummary report, and can be helpful in other situations too.
However, if you come from a more conventional database design background, including "unnecessary" fields in the schema is a difficult idea to adjust to (because the calculation logic is stored in the view layer, not the model layer).
Both approaches work; depending on the environment one will be more elegant than the other.
Oh, Ceiling, I always forget Ceiling...
And yes, it's more elegant, sometimes i'm a little rough.
I finally managed to make it work combining Keyword's and Danielfarnan's proposals. I used two global fields for each quarter of the year. The first field calculates the start day and the second the end day. For calculation I used the simple formula provided by danielfarnan. The resulting script has the following structure:
Go to layout [orders]
Enter Find Mode [ ]
Set Field [ Invoice Date ; Quarter1_startdate & "..." & Quarter1_enddate ]
Perform Find [ ]
So far it works fine. If you think that I have to add any complementary piece of code, please let me know.
I can see the potential of the other methods (although I did not understand erolst's Loop script), but I am not so experienced to easily and correctly implement them. I 'll keep them in mind for future projects. For now I prefer to keep things simple.
Again, many thanks everyone for your contribution.