1 2 Previous Next 21 Replies Latest reply on Sep 8, 2011 12:50 PM by KennySolway

    Date range specific report

    KennySolway

      Title

      Date range specific report

      Post

      Good afternoon,

      I'm interested in learning how to create a report that is date specific.  For example, if I want to see what my sales are for any given time frame or date range (i.e. last year, this year, this quarter, etc.), how do I create a report to do this?  Your assistance is greatly appreciated.

        • 1. Re: Date range specific report
          Sorbsbuster

          If you start by finding the records manually, by performing a Find and entering the criteria you want, eg: in the Sales Order Items Table, in Sales Date:

          7/9/2011 for orders on the 7th September 2011 (I'm Irish)

          1/7/2011...30/9/2011 for orders in the 3rd quarter of 2011

          1/1/2010...31/12/2010 for orders last year

          or

          */*/2010 for orders last year, hence:

          */9/2011 for orders in September 2011

          */9/* for orders in a September

          7/*/* for orders the 7th of the month

           

          ...then you could devise scripts to find the ranges you are looking for using the same methods.

          You can generate the search criteria by, for example, knowing that the Year ( Get ( CurrentDate ) ) is 2011, hence last year is 2010.

          If Month ( Get ( CurrentDate ) ) is 9, then this is September, etc.

          • 2. Re: Date range specific report
            KennySolway

            Thank you.  As you can probably tell, I'm a novice at best with FileMaker.  How does one create a script as you suggest above?  I assume then I would create a report referencing that script - is that correct?

            • 3. Re: Date range specific report
              Sorbsbuster

              Let's say you want to find all orders for this year.  Create a script like this:

              Go To Layout (Sales Order Items)

              Enter Find Mode

              Set Field ( SalesDate , "1/1/" & Year (Get (CurrentDate )) & "...31/12/" & Year (Get (CurrentDate ))  )

              Perform Find

               

              This will find you all sales in this year.

              It is just entering Find Mode and then building the search criterion as the "1/1/[this year]" to the "31/12/[this year]"

              Put a button on your layout and attach this script to it.  When you click the button it will find all order lines this year.

              • 4. Re: Date range specific report
                Sorbsbuster

                To find today's orders, create a script like this:

                Go To Layout (Sales Order Items)

                Enter Find Mode

                Set Field ( SalesDate , Get (CurrentDate ) )

                Perform Find

                • 5. Re: Date range specific report
                  KennySolway

                  Wow!  I appreciate you trying to help, but I don't really know what to do with the coding you gave me.  Certainly gives greater value to a live voice conversation.

                  I've tried to cut and paste and replace the fields with my own, but I get syntax errors.

                   

                  The field name that I'm trying to 'script' is called 'Delivery Date'.  Is there anyway you can create the script so I just copy and paste it?  Please and thank you.

                  • 6. Re: Date range specific report
                    Sorbsbuster

                    Open Scripts --> Manage Scripts.

                    Click the 'New' button to create a new script

                    Name the script 'Find This Year's Orders'

                    Add each of those script lines from the pane on the LHS

                    Save and close (or close and save - it'll prompt you)

                    You will now have that script, by default, under the 'Script' menu.  Run it and see what happens...

                    • 7. Re: Date range specific report
                      KennySolway

                      nothing happens...is there a way to show you a screen grab of share my screen with you?

                      what is LHS?

                       

                      • 8. Re: Date range specific report
                        Sorbsbuster

                        Sorry: LHS is left hand side.

                         

                        Have you opened Scripts --> Manage scripts and tried what I suggested?  What stage do you get stuck at?

                        • 9. Re: Date range specific report
                          KennySolway

                          i've entered the commands but there is no specific fields or calculations

                          • 10. Re: Date range specific report
                            Sorbsbuster

                            Sorry, I told you I'm Irish: you have to go very slowly for me.  Which steps do you have the problem at, please:

                            Step 1:  Open Scripts --> Manage Scripts.

                            Step 2:  Click the 'New' button to create a new script

                            Step 3:  Name the script 'Find This Year's Orders'

                            Step 4:  Add each of those script lines from the pane on the LHS

                            Step 5:  Save and close (or close and save - it'll prompt you)

                            Step 6:  You will now have that script, by default, under the 'Script' menu.

                            Step 7: Run it and see what happens...

                             

                            • 11. Re: Date range specific report
                              KennySolway

                              I don't know how to 'run' it - Step 7

                              as i said for step 4 should i be entering any data or adding any fields or calculations?

                              • 12. Re: Date range specific report
                                Sorbsbuster

                                If you got to Step 3 then you are looking at the script-writing screen.  On the left are all the script steps you can choose to build into the script.  On the right pane will be the script steps you have chosen (by double-clicking in the left pane).

                                In Step 4 choose each of these script steps from the left side:

                                Go To Layout (Delivery Items) <- choose from the list available bottom right when you click the arrow at 'Specify'.  Choose a layout with your deliveries on it

                                Enter Find Mode

                                Set Field ( DeliveryDate , Get (CurrentDate ) )  <--- click the 'Specify' button, bottom right to choose the date field you want.  Click 'Calculated Result' and type in the Get (CurrentDate ) calculation

                                Perform Find

                                 

                                In Step 7 you can select the script you have just written under the 'Scripts' menu.  It will be the last one listed.  Selecting it will run the script.  You can also attach the script to an object on your layout.  Type 'This Year's Orders' on the layout, right-click it, select 'Button set up', choose 'Perform Script' (from almost the top of the list) and set it to be that script.  Now when you click that button it will find all this year's orders (deliveries - sorry)

                                • 13. Re: Date range specific report
                                  KennySolway

                                  I totally appreciate you trying to help me, but is it always this time consuming to support what i beleive is a simple report question?  you mush find it frustrating trying to understand what people like me are looking for.

                                  unfortunately, i've done exactly what you've directed me to do, but i'm still not getting the data that i'm looking for.  on top of which, i ultimately want to create a graph report of my monthly sales (i.e. to see which months peak, etc.).  is that even possilble with thias software?

                                   

                                  there has to be a more efficient way for you and i to communicate as with all this syntax and you not having my file, there is bound to be errors.

                                   

                                  any other suggestions, again, i truly appreciate you trying to help, but i don't even know that we're heading in the initial direction of my report request.

                                  • 14. Re: Date range specific report
                                    Sorbsbuster

                                    Yes, FM 11 supports charts.

                                    Can I clarify what I think you are trying to achieve?

                                    - you have a file with lots of records in it, each record being one delivery

                                    - you want to be able to easily search for only those records whose delivery date is this year (for example)

                                    - you would expect to see a list of many records, all of which have a delivery date in this year (in this example)

                                    If that is the case I will post up a file which will do that.  You will be able to see how the scripts work, and I will add some other calculations also.

                                    1 2 Previous Next