3 Replies Latest reply on Dec 20, 2009 8:42 AM by imgaryshap

    subtracting years

    imgaryshap

      Title

      subtracting years

      Post

      Hello - I use FMP 8 on a mac.

       

      I have a script that prompts the user to insert dates for a search.

       

      those dates are incorporated into the required fields (pending the sort) for which the user wants to get information.

       

      One of my set ups is a chart that incorporates a per year analysis.

       

      for example.

       

      If the user types in 6/1/09 and 8/31/09, all the records will show for 2009 for those three months.

       

      i also want the records for  6/1/08 thru 8/31/08 to show up as well in order to compare and contrast.

       

      what is a calculation/funtion for dates so in my script it will automaticall insert a date that is one year before the user input date in my find?

       

      this is probably simple, but i'm having trouble getting this to work.

       

      thanks!

       

      -G.

        • 1. Re: subtracting years
          littledog
            

          You say:

          "If the user types in 6/1/09 and 8/31/09, all the records will show for 2009 for those three months.

           

          i also want the records for  6/1/08 thru 8/31/08 to show up as well in order to compare and contrast."

          To compare and contrast the best I can see is to make 2 self-joined relationships  for date periods and reflect returned values in 2 portals.

          2 "Find" requests (one for each period) in this case isn't the best - I mean "compare". 

          • 2. Re: subtracting years
            StellaLuna
              

            You are probably right, Peter, but we don't have enough information to know how the information is even being used.  And the person says they want to find so I'll offer that option.

             

            If you want to find the prior year, you can by using 'duplicate record/request' or even 'new record/request.'

             

            You didn't say how you user was enter the dates so I will give you two ways.  First way is if the user enters into global dates a start and end while in browse mode then hits button to run script 1.  The second method assumes the user is presented with the date to search. They must click button called Find which puts them into find mode.  They type a range directly while the find is paused as 6/1/09...8/31/09 then the script will continue after they hit enter.

             

            1) using global start and end dates

             

            Enter Find Mode []

            Set Field [ yourDate ; globalStartDate & ".." & globalEndDate ]

            New Record/Request

            Set Field [ yourDate ; Date(Month(globalStartDate);Day(globalStartDate);Year(globalStartDate)-1)  
            & "..." &
            Date(Month(globalEndDate );Day(globalEndDate );Year(testdates::globalEndDate)- 1) 

            Perform Find

             

            2) taking find User enters

            Enter Find Mode [pause] ... user then types their date range

            Duplicate Record/Request

            Set Field [ yourDate ;

            Let([start = LeftWords(yourDate;1) ;end = RightWords(yourDate;1)] ;
            Date(Month(start);Day(start);Year(start)-1)  & "..." & Date(Month(end);Day(end);Year(end)-1)) ]

            Perform Find


             this is just another possible option. my calcs could probably be shorter or better but I'm afraid to make dates text because they can break if not treated respectfully as dates. :smileysad:

             

            I forgot to add the last perform find. Also you should error trap. Right before your perform find, add line Set Error Capture[on]. Then after the perform find add

            If [ not Get(foundcount)]

            ...do whatever you wish then...

            exit script

            End If

            • 3. Re: subtracting years
              imgaryshap
                 date function is what im looking for with that "-1" at the end. thanks!