hi all,
I'm curious if it's possible to develop a script that generates a monthly report. For instance, I envision a screen where the user is prompted to input the desired month and year. Upon submission, the script checks for available records; if they exist, it generates a PDF-format report. However, if no records are found for the specified month, a message is displayed indicating that there are no records for that month.
That's very easy to do. You collect the month and year and pass them as parameters to a script.
You might consider storing the PDF reports on the server. If the information will not change there is no need to recreate them . Your script could starting by looking for the report on the server, if it is found, then display it, if not, then create it and then display it.
Thank you for your reply. Could you provide an example to illustrate your explanation further? Additionally, it would be perfect if the example includes the functionality of inserting the month and year as per my request.
Hi, @Saeed , Happy New Year to you!
As @Malcolm mentions, this is not difficult to do, but guidance from The Soup will require a bit more information. For example: Can you provide what data you wish to report on? And how do you need it presented upon output?
For your user input for selecting the date range (month or year or whatever) may I suggest you consider having your users select month and year from drop-downs and/or calendar pickers. On your screenshot above you present a Custom Dialog requesting users to input month and year, but this is prone to errors as they can enter typos, reverse the month and year (not paying close attention), etc.
Here's a screen shot of an example case. It uses a popover with selectors. I also often do this in a card window instead of popover, depending on UI needs. In this case it was a "Reports" card window that allows for selecting various reports where Button Bars as popover are used.
The month and year selectors use defined Value Lists, Start Date/End Date use calendar pickers.
All four of these fields are from my Globals table (all fields use global storage, and there is only one record in the table: g_month ; g_year; g_startDate; g_endDate. These fields can be reused across the entire solution, and are unique to each user (per session).
If a month or year field is populated and the user then decides to enter a start date, the other fields are cleared, and vise versa.
If they enter only the year, the report is prepared for the entire year; If they enter only a month, sometimes I ask them to include the year, in other cases (per client option) I assume current year in the script.
Edit to add: I don't always like popovers for dialogs, so I use card windows as mentioned above. Here are a couple examples of how Card windows might be helpful. (Note: screenshots from different monitors so a bit inconsistent. (And once posted they look huge. LOL )
For an example of how you might create your monthly report, @Saeed , a common method is to navigate to the relevant table occurrence for proper context (if you're not sitting on it already), execute your Find based on the user input, then move to the layout that is designed for printing or Save as PDF step (it would be a print-type of layout, typically List View).
If it's a multi-page report, I prepare the "Page 1 of 7" footer stuff [1], then Print directly, or Email or Export to Desktop (or a folder by path) a PDF, etc.
[1] Actually, our printed/PDF output for reports/invoices, etc. have the footer page count even for single page output. For laboratory reports of a type that are always single page, it's omitted.
Thank you, @daleallyn , for consistently introducing new techniques. In fact, I've been utilizing the feature of selecting the date range and generating a PDF in my workflow. However, I'm interested in implementing the same technique you've described.
The data is within the same table, and I've captured the screen just as you suggested, using a script to facilitate searching by date range. Additionally, I've created a field called g_month with a drop-down list and g_year.
Now, I'm looking to develop a script specifically related to the month and year.
A common practice is to create 2 calculated fields.
Month ( DateFieldToEvaluate )
Year ( DateFieldToEvaluate )
Then your finds, filters, and even global match fields across relationships, are far easier to deal with for dates.
A calculated concatenation of the year and month field helps in searches across year boundaries.
Good morning, @Saeed .
For reports generated from a layout like I see in your screenshot background (or a dedicated layout for selecting reports) I typically open the report in a new card window so the user can simply view the report and from that position either close it, print, export it as PDF, email, whatever via options on the layout. Of course, if there is no data to report, the user is simply informed of that.
If the choice is to print/export we navigate to another layout that is similar to the first report (on the card window) but without the header part where the choice of actions are; so a print/export layout. This done via the print/export script which I control by having a script parameter on each button choice of action, such as "print" and "export". Then within the script direct the action based on the parameter (IF conditional or CASE function, depending on complexity). Well, sometimes the two layouts are different as noted at the bottom of this post (and illustrated in the screenshots.)
I'll see if I have an example handy or can create one for you later.
The suggestion by @Kirk is a good one as well, however it's not typically my approach. Probably just due to old habits. I leverage my Globals table very heavily and tend to manipulate data there via scripts. (A modest file I have open at the moment has 60 fields in the Globals table. Others probably have 100 or so.) Kirk's method can certainly be used to populate the global fields, but I often place the global field directly on the card/popover (or control them within scripts). In FM, like other software platforms, there are many ways to get to the same destination. 
Edit to add a couple screenshots of something I had handy. Not intended as aesthetic beauty, just for visualization. First is summary report provided based on date selection; second is the print preview. In this case, the header of the first doesn't provide for export, but easily could.
In some cases the print layout and the prior "preview" layout are essentially the same except the the prior "report preview" one has a header part with some controls for print, export, email, etc. One can also use a Top Navigation part on the report layout instead of a Header part, as the Top Navigation part does appear in Preview mode or on the printed output. Doing so allows for using a single layout to view data, then print. For various reasons I typically separate them into two layouts.
HTH
@Saeed , here are a couple scripts to consider (see FM file attached). It's only two scripts, and no supporting layouts. There's some cruft, but I added to comments a bit so you might see what you need to consider. These two scripts relate to the report screenshots above.
These are from a development project – a prototype/example – from last year (or before?), so I don't recall some of the dependencies (if any) without digging through the layouts and such. I removed some sections which would just add confusion/noise without seeing context. Hopefully, I left enough for you to get what you need working.
Script triggers are used to clear global fields when a "dialog" card is opened (or another time in the process, as needed). Script triggers on report layouts also cleanup global $$variables for housekeeping at various times, but typically on layout exit (depending on the $$toPrint flag status).
There's some sloppy conditionals, but I noted it and left it for you to try to adjust. I haven't much time today to review the file and fix it at the moment.
Also, since we set global fields we could use them in the scripts for setting up the Find, but my habit is to populate variables first within the script. This is partly just preference, and partly because it's not uncommon for longer processes to involve resetting those global fields for another task within the same script(s).
Please post back with any questions.
ReportScripts.fmp12 (392 KB)
I will attempt to implement the changes and will provide you with an update soon. I truly appreciate your time in posting and creating a sample file to illustrate.
@Saeed
Not necessarily intuitive but to search on say, a range of dates, the SET FIELD specify structure is
First date & “…” & second date
The triple dot connects A to B as a range selection
If this was already understood, ignore this message
Yes, I understood, and I appreciate your clarification
A tip on monthly reporting date range selections.
The first of the month is easy - alway one, but the end of the month is a tougher nut.... or is it.
From the Claris help file on DATE - note in particular the use of 0 or even minus numbers.
Date ( 10 ; 10 ; 2019 ) returns 10/10/2019.
Date ( 13 ; 1 ; 2019 ) returns 1/1/2020 (one month after December 1, 2019).
Date ( 6 ; 0 ; 2019 ) returns 5/31/2019 (one day before June 1, 2019).
Date ( 6 ; -2 ; 2019 ) returns 5/29/2019 (three days before June 1, 2019).
Date ( 7 ; 12 ; 2019 ) - Date ( 7 ; 2 ; 2019 ) returns 10.
I think you need a field with the Month(date) function calc autoenter.
So..whenever you change a date in this Field the name of the Month shows..
