7 Replies Latest reply on Dec 13, 2016 6:54 PM by OCDilla

    How to make a layout to setup dates for a report


      I have been stuck for a couple weeks trying to find a way to make a layout where I can set a to and from date to search and make a report.


      I am trying to make a system for a Church that is using  Quickbooks.  I would like to keep it working the same way as they use now but cannot find a way to make it work.


      When you go to reports a window opens with some fields like "Dates", "To", and "From".  Each of these field can be modified with a drop-down calendar to choose dates.  I have a lookup in the Dates field that inserts the correct dates for things like, Today, First Fiscal Quarter, etc. and that is working BUT, I cannot choose "custom" and modify the To or From fields so that other dates can be searched.  I have tried global fields and regular fields but they are not editable on screen.


      I cannot find any examples that show this being done.  I must be able to modify the To and From dates so the correct records can be listed or printed.  Sometimes the entry is a couple days after Sunday and I need data for the previous Sunday so that reports are correct.


      Anybody have any ideas on how to do this? Examples?



        • 1. Re: How to make a layout to setup dates for a report




          I guess I'd simply allow the user to type in the start and end date into global fields and then use those fields in your find script. Let's say you have the two globals: DateStart_g, DateEnd_g


          User enters:

          1/1/2016 for DateStart_g

          12/12/2016 for DateEnd_g


          Enter Find Mode

          Set field [ YourTable::DateField]  DateStart_g &"…" &DateEnd_g  //This does an inclusive search of the entire date range.


          Perform Find.

          Make sure the global fields are editable (Inspector / data tab).


          Let me know if you'd like to see an example, or if I'm misreading what you're asking.

          • 2. Re: How to make a layout to setup dates for a report

            There are a couple parts to getting this to work the way you want to :

            Permissions--make sure the user has the ability to edit the field.

            Layout--ensure the field can be entered in browse mode.
            Use global date fields--This will be important if you end up having multiple users at some point. (NO calculations or auto-enters)
            Script--You will want to set the field using a script .

            File Script Triggers--You will want to call the script from OnFirstWindowOpen in the File Options


            The goal is to end up with a script that runs when you open the file that sets the global fields to the date that you want. It sounds like it is a bit trickier because you don't want to set the to and from fields to a specific data range (30 days) but rather to a day of the Week (Sunday). So you will need a calculation that is something like this to return the previous Sunday:


            Get(CurrentDate) - (DayofWeek(Get(CurrentDate)-1)


            Best of Luck!

            • 3. Re: How to make a layout to setup dates for a report

              Here's a simple find, follow the red instructions.

              Edit: had to fix something

              • 4. Re: How to make a layout to setup dates for a report

                Thanks for the responses, I found that the "SimpleDateSearch.fmp" from Steve Martino very helpful.  This Log Reports layout does just about what I wanted BUT, This works but mine will not.  My problems are that after setting the lookup for Dates = Custom that I cannot change either the start or end dates manually.  The cursor will let me change these fields.


                I am changing my scripts to similar to the working scripts but it still doesn't work.  Trying to find the differences now.


                Below are screen captures from Quickbooks (works the way I want) and my layout that does not work.  Their are scripts not included to save space.




                Report screen.bmp


                FM screen.png

                • 5. Re: How to make a layout to setup dates for a report

                  I found the problem, Just as OCDilla had mentioned.  Oversite on my part has cost weeks of time and frustration.  I did not make the start and end dates editable.  Now at least I can set custom dates and keep moving.


                  Thanks everyone for the OBVIOUS solutions.



                  • 6. Re: How to make a layout to setup dates for a report

                    I forgot to ask in Steve's example, each field starts with "/* and ends with */?  What is this and what does it do?  I cannot find this online or my my books.


                    In C programming it means to comment out from /*........... to */ but what is it for and where does it come from in the example layout.



                    • 7. Re: How to make a layout to setup dates for a report

                      It means the field has been commented out when you imported the script or calculation. This is probably because you don't have the exact same table and field names. Remove the /* and the */ and FileMaker will let you know the problem.