11 Replies Latest reply on May 2, 2016 11:20 PM by farm

    How can I find Invoices for every 3 months

    farm

      Hi everyone,

      I need to create a script that finds all the invoices that have a creation date within every quarter of the current year. For example, all the invoices from 1/1/2016 to 31/3/2016. I cannot figure out how to do it.

      Any help appreciated.

       

      Thanks.

        • 1. Re: How can I find Invoices for every 3 months
          keywords

          1.     Enter Find mode.

          2.     In the date field enter "1/1/2016 … 31/3/2016"

          3.     Perform Find

          • 2. Re: How can I find Invoices for every 3 months
            farm

            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.

             

            Thanks.

            • 3. Re: How can I find Invoices for every 3 months
              Alquimista

              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

              or

              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.

              • 4. Re: How can I find Invoices for every 3 months
                keywords

                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:

                Let (

                [ 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.

                • 5. Re: How can I find Invoices for every 3 months
                  erolst

                  Loop

                    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 ]

                    Perform Find

                    # [ do stuff with found records ]

                    Exit Loop If [ Let ( $i = $i + 1 ; $i = 4 ) ]

                  End Loop

                  • 6. Re: How can I find Invoices for every 3 months
                    danielfarnan

                    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):

                     

                    Let (

                    [

                    _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

                     

                    )

                    • 7. Re: How can I find Invoices for every 3 months
                      Alquimista

                      How much code to do a simple thing!

                      • 8. Re: How can I find Invoices for every 3 months
                        farm

                        Thanks everybody for your replies.

                        I will make some testing and let you know.

                        • 9. Re: How can I find Invoices for every 3 months
                          danielfarnan

                          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.

                          • 10. Re: How can I find Invoices for every 3 months
                            Alquimista

                            Oh, Ceiling, I always forget Ceiling...  

                            And yes, it's more elegant, sometimes i'm a little rough.

                            Thank you.

                            • 11. Re: How can I find Invoices for every 3 months
                              farm

                              Hello everybody!

                               

                              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.