What version of FileMaker?
Your Reporting layout should use global fields to accept the User values. Create regular fields but then in Options > Storage, check 'use global storage'. So you might have gReportName (text) and gStart (date) and gEnd (date). Then place a button to the right of the fields called Run Report. Script would do several things depending upon the report but here is basic approach:
# make sure there are values
If [ IsEmpty ( gReportName ) or not gStart or not gEnd ]
Show Custom Dialog [ “You must select a value for each field. Please try again.” ]
This assumes you want something in every field. But, you can also design it so that, if User does not enter an end date, script can assume it will find all dates past (and including) a start date entered. Same with start date … if empty but there is an end date, script can assume that it will find all records up to and including the end date. Change your portion in bold to test accordingly. Either way, the script will work the same. At this point then, the script is continuing because values were correct so continue with:
Allow User Abort [ Off ]
Go To Layout [ layout name by calculation ; gReportName ]
Enter Find Mode [ uncheck pause ]
Set Field [ yourDate ; gStart & “..” & gEnd ]
Set Error Capture [ On ]
If [ not Get ( FoundCount ) ]
Show Custom Dialog [ “No records found” ]
# decide whether to include a Show All Records here (that is developer/user decision)
Go To Layout [ original layout ]
# records found so sort your report, go into preview, pause, print or whatever else you wish to do.
- If FM receives a range search on a field as 1/1/2010.. and no ending, it assumes all greater records. There is no need to insist on a date on each end of a span.
- If you change a layout name, remember to change the Report Name value list (if it is custom).
- This generic script assumes you are always searching the same table:::Date. If each report uses a different base table and field, you will need to include a branch in your script to handle each report (or group of reports) differently.
- If your reports include a lot of aggregates, it is sometimes best to switch first to a null layout (no fields) and perform your find and sort there before switching to your final report.
- You might wish to test that the end date is greater than or equal to the start date. If vs. 11, you can use trigger to stop User immediately.
I hope this gets you moving. Ask questions as needed.
Thanks alot for your support about the reporting option....One questions...in the reporting layout, when you say my reporting layout should use global fiels,, am assuming that in i have to created a table for the reporting layout, since this field show records from other tables..I have not understood how to set my report layout field to global in that they get records from other table..please see screenshots below..
Tracking Activities Form Layout
Tracking Activities Report Layout
My idea is when a user has keyed in all the information in the form and save...he can go to the reporting tool layout and have a drop down list with all the reports names...and can choose date range and when presses find report...he is taken to the report layout showing the particular report he/she is interested in and within the date range....and if possible all the information to be in one single report....
thanks in advance..
...in the reporting layout, when you say my reporting layout should use global fiels,, am assuming that in i have to created a table for the reporting layout, since this field show records from other tables..
The global fields can be placed in any table, Nickey, because global fields can be accessed from anywhere. And the Reports layout can also be based upon any table because it will only be used as a stopping-point for the User, i.e., viewing the report options in form view, selecting their criteria, and then script taking them to the requested report in the Tracking Activities table where the data resides (or taking them to other tables depending upon the report requested).
Most Developers create a one-record table which holds preferences, admin, globals and that works nicely for Main Menu or Reports Menu. If there are many reports (and they have complex criteria), I create a Reports table to hold Report Name, base table and other information instead of custom value list. It allows more flexible dynamic scripting of the reporting process. I usually also have a virtual reporting table. Creating all your global fields in a single table allows easy administration by the Developer. Unless global is needed within a specific table occurrence for a relationship, they do not need to be in your data tables at all.
nickeywalter wrote:My idea is when a user has keyed in all the information in the form and save...he can go to the reporting tool layout and have a drop down list with all the reports names...and can choose date range and when presses find report...he is taken to the report layout showing the particular report he/she is interested in and within the date range....and if possible all the information to be in one single report....
You envision correctly ... and the script I provided will give you what you wish. So, once you decide where to put your globals and get them created, go to your Reporting Tool (your last screen shot) and re-specify the dates (double-click them) to the new global fields. Then create your script. If you get stuck again, let us know. :^)
EDITED: For some reason, it was messing up the quoted portions of the post so I removed them.
Message was edited by: LaRetta
Am using filemaker pro version...
LaRetta....sorry for the bother once again....can u please send me your email address so i can attach my database and have you to have a look at it and may be help me with the first report generation which i can use as an example and work through to the rest....
Thanks in Advance,
Walter, you can send a private message to anyone on this forum (there may be exceptions). Click on the name and in their Profile, there is an Actions link "Send private message" in the rightmost column. Click this and compose your message. They will be emailed a notice that they have a private message and must retrieve in the forum.
This does not seem to allow attachments, but is a way for you to communicate email addresses privately, should you so desire.
I've sent to you a private message as a test.
Great post, LaRetta.
I have done it along the same lines. I just changed the script (and layout names) to gol along the principle of:
"Go To Layout [ layout name by calculation ; gReportName ]"
I am in the process of combining the report specification layout with this:
Because until now, we [the users, that is] have been satisfied with looking at the preview window that I pop up (flipped to the last page, showing grand total), and then just saving to Excel from the toolbar. The sub-summaries (groups) did not go into Excel, but I just used the FMP12 trick to include those same fields off-screen, to the right of the body part.
However, the summary field used to count the (sub)totals goes into Excel as well. And the column names are internal field names.
Hope it works out well.
Your notes are great too. I did what you suggest, making users able to use the same date selection for different report groups (TOGs).