10 Replies Latest reply on Dec 27, 2011 6:07 PM by LaRetta_1

    Get Date for a gStartdate and gEndDate

    projay

      Title

      Get Date for a gStartdate and gEndDate

      Post

      I have two global fields:
      gStartDate    gEndDate

      I would like to set up a script to automate the searches for these fields.

      For example Sales for this month.  I would like to get the date for 1st day of month (12/01/11)
      and the Last day of month (12/31/11).

      Also I would need to set up the following as well. 
      1. Sales This Week
      2. Sales Last Month
      3. Sales Last Week
      4. Sales Last Month
      5. Sales Last Year

      Hopefully I make some sense. Much thanks to everyone for all your help.

      -J

        • 1. Re: Get Date for a gStartdate and gEndDate
          LaRetta_1

          Hi Jay,

          If you wish to automate it then you will not need the global fields at all.  The script might look like:

          Freeze Window
          Go To Layout [ layout based upon Sales ]
          Enter Find Mode [ uncheck pause ]
          Set Field [ yourSaleDate ; (calculation below)
          Set Error Capture [ On ]
          Perform Find [ ]
          If [ Get ( LastError ) ]
          Show Custom Dialog [ OK ; "No records found" ]
          Go To Layout [ Original layout ]
          Else
          ... do whatever you wish with the found set
          End If

          Last Week
          Let ( [
          end = Get ( CurrentDate ) - DayOfWeek ( Get ( CurrentDate ) ) ;
          start = end - 6
          ] ;
          start & ".." & end
          )

          Last Month
          Let (
          d = Get ( CurrentDate ) ;
          Date ( Month ( d ) - 1 ; 1 ; Year ( d ) ) & ".." & d - Day ( d )
          )

          Last Year
          Year ( Get ( CurrentDate ) - 1 // this produces 2010 and is valid search criteria in a date field

          Some of your items were duplicates so I was unsure what else you might want. :-)

          LaRetta

          • 2. Re: Get Date for a gStartdate and gEndDate
            philmodjunk

            On the other hand, sometimes you want/need the flexibility of a date range based search as this can let you pull up records for other intervals besides the last week, last month and last year intervals.

            Enter Find Mode []
            Set field [YourTable::SalesDate ; YourTable::gStartDate & "..." & YourTable::gEndDate ]
            Set Error Capture [on]
            Perform Find[]

            This script will only work if gStartdate and gEndDate have global storage specified.

            • 3. Re: Get Date for a gStartdate and gEndDate
              projay

              That is correct...to be able to have both ways scripts as LaRetta mentioned and have the flexibility as Phil mentioned.  I need the scripts to enter the beginning dates Lets say for This Month gStartdate=12/01/11  gEndDate=12/31/11...I need to figure out how to get the those values...see attached setup for my layout...and it will make some sense hopefully.

              -J

              • 4. Re: Get Date for a gStartdate and gEndDate
                projay

                 Correction for attached pic, it should be cOrderTypeINVOICE = order_type

                • 5. Re: Get Date for a gStartdate and gEndDate
                  philmodjunk

                  Hmmm, I was describing how to perform a find, you have a relationship here.

                  If by "last week" you mean "previous 7 days from today", You could use Get ( currentDate ) - 7 for startdate and Get ( CurrentDate ) for End date.

                  If you mean last week starting with Sunday, you can use LaRetta's calculations to put in start and end dates--just capture the individual 'start' and 'end' values in your two global fields instead of constructing a date range expression.

                  The same works for a "last month" interval.

                  For "last year", you'd use something like this:

                  Date ( 1 ; 1 ; Year ( Get ( currentDate ) ) - 1) for start date and

                  Date ( 12 ; 31 ; Year ( Get ( currentDate ) ) - 1) for end date

                  • 6. Re: Get Date for a gStartdate and gEndDate
                    LaRetta_1

                    Oh, you are using a relationship and not performing find.

                    Then user can enter dates into the globals or you can have buttons next to the fields.  For each of the buttons, create a script which sets the globals with the results.  Scripts would be:

                    Last Week
                    Set Field [ gStart ; Get ( CurrentDate ) - DayOfWeek ( Get ( CurrentDate ) ) - 6 ]
                    Set Field [ gEnd ; Get ( CurrentDate ) - DayOfWeek ( Get ( CurrentDate ) ) ]

                    Last Month
                    Set Field [ gStart ; Date ( Month ( Get ( CurrentDate ) ) - 1 ; 1 ; Year ( Get ( CurrentDate ) ) ) ]
                    Set Field [ gEnd ; Get ( CurrentDate ) - Day ( Get ( CurrentDate ) ) ]

                    Last Year
                    Set Field [ gStart ; Date ( 1 ; 1 ; Year ( Get ( CurrentDate ) ) - 1  ]
                    Set Field [ gEnd ; Date ( 12 ; 31 ; Year ( Get ( CurrentDate ) ) - 1 ]

                    • 7. Re: Get Date for a gStartdate and gEndDate
                      projay

                      Ok all of it works great...I figured by getting those scripts I should be able to figure out the script for
                      "This Month"...I guess my small brain is just having a tough time grasping the date scripts...

                      -J

                      • 8. Re: Get Date for a gStartdate and gEndDate
                        LaRetta_1

                        Hi Jay, not sure if you were asking for a nudge on this month but it would be:

                        Set Field [ gStart ; Month ( Get ( CurrentDate ) ; 1 ; Year ( Get ( CurrentDate ) ) ]
                        Set Field [ gEnd ;  Date ( Month ( Get ( CurrentDate ) + 1 ) ; 0 ; Year ( Get ( CurrentDate ) ) ) ]

                        • 9. Re: Get Date for a gStartdate and gEndDate
                          projay

                           Thanks so much...jus't don't understand the reason for +1 and the 0 and how that comes out to last day of a particular month.

                          -J

                          • 10. Re: Get Date for a gStartdate and gEndDate
                            LaRetta_1

                            If you put Date ( Month ( date ) + 1 ; 0 ; Year ( date ) ) then FileMaker interprets Month ( date ) + 1 and moves the month increment forward by one month. But since the day is 0 instead of day 1, it drops back another day and ends on the last day of the prior month, which in this case, since we jumped ahead one month actually determines the last day of THIS month.

                            You can incrementally move each piece (month day and year) by this method and FM will properly adjust.  If you use true dates and date functions, they will always properly account for leap year as well.

                            Date - Day ( date ) does the same thing ... if you deduct the number of days from a date, you end up on the last day of the prior month because the day is '0'.

                            Edited wording a bit