Report grouped by specific age ranges

[4Justme2]

I am using FMPro 11 Advanced. New to FM reports. I want to count the number of children by three categories: 1) by certain age ranges (0-3, 4-6, and the number of children over 7 years of age) 2) by the type field (I only want to see Children and not include other types in the report and 3) by fiscal quarter/year. I'm use to building queries in MS Access where I build the criteria into the query for the enduser... or add parameter values so they can input values themselves. You'll see from the attached example that I don't know what I'm doing here. Any help would be greatly appreciated. Thank you... mp

AgebyRanges.zip

[LaRetta]

Create a calculation (result is text) with:

Case (

Age < 4 ; "0-3" ;

Age < 7 ; "4-6" ;

"Over 6"

)

Create a report which groups by this calculation as the leading part and sorts by this field as well.

[eos]

And/or have a look at this; it shows how to use globals and a script to build a dynamic report.

AgebyRanges_eos.fp7.zip

[4Justme2]

And/or have a look at this; it shows how to use globals and a script to build a dynamic report.

That's a real nice example of exactly what I was hoping to do. Thank you so much for sharing.

[LaRetta]

Take a look at your report and see if this fits for you ... you had everything but a summary field to count.

That is overly complicated (eos). All you need to do in your file, 4justme2, is add the summary field, find only child records, sort your report as indicated.

Also add the AgeGroup as a part and include it in the sort.

AgebyRanges2.zip

Edited June 4, 2012 by LaRetta

[eos]

That is overly complicated (eos).

I don't think that a couple of global fields and a brief script are “overly complicated” when the OP states: “I'm use to building queries in MS Access where I build the criteria into the query for the enduser... or add parameter values so they can input values themselves.” Why not show how to do the same thing in FileMaker with very little effort?

[4Justme2]

The eos zip file was exactly what I need for this particular utility; however, I appreciate getting the example of a more simplified version as I'm sure I can put it to good use on other reports. Thank you both, very much, for your time, and support. Your help has made it possible for me to finally get behind the initial stages of report development. I was procrastinating (and missing MS Acess) up until this point. It feels good to be able to move forward. So thanks again...mp

[eos]

You're most welcome. Hope you'll enjoy working with FileMaker in the future and won't miss Access one bit! ;)

[4Justme2]

I'm going to have a variety of field criteria that I'll need to write into my FM reports. In the above example, if I understand correctly, I'm using a the field AgeRange to store a calculation that draws on a Let function to identify a variable, and then the case statement to draw on the specific groupings. (Please feel free to correct here if you would).

An additional level of complexity in this report will be needed when my endusers also want to see only those children by age range/fiscal quarter which have a value of 1 in the AllActivity field (which is a boolean 0 or 1 field). Simply placing the AllActivity field on my report layout won't allow me to restrict the criteria to just values of 1. It seems like such a simple question but when I sit down to implement the solution everything I try fails to work.

If I were going to generate this report criteria in Access I'd simply include the field in my query, type the number 1 in the field criteria and run my report. In Filemaker Pro how do I force this so I only get the "1" values on my report?

[eos]

I'm going to have a variety of field criteria that I'll need to write into my FM reports. In the above example, if I understand correctly, I'm using a the field AgeRange to store a calculation that draws on a Let function to identify a variable, and then the case statement to draw on the specific groupings. (Please feel free to correct here if you would).

Let () is used to assign one or more variables which you then use in the subsequent calculation (or merely as result expression, since the variable assignments themselves can be arbitrarily complex expressions; the example in your solution is rather trivial). The actual work is done by other calculations, namely Case (). You could do anything you do with Let () without it, but once you get to appreciate it (and know how calculations looked & felt in the Dark Ages), you won't look back.

An additional level of complexity in this report will be needed when my endusers also want to see only those children by age range/fiscal quarter which have a value of 1 in the AllActivity field (which is a boolean 0 or 1 field). Simply placing the AllActivity field on my report layout won't allow me to restrict the criteria to just values of 1. It seems like such a simple question but when I sit down to implement the solution everything I try fails to work.

If I were going to generate this report criteria in Access I'd simply include the field in my query, type the number 1 in the field criteria and run my report. In Filemaker Pro how do I force this so I only get the "1" values on my report?

If you perform a find where you take the user to a layout with the appropriate fields and Enter Find Mode, you could simply add any fields on that layout you wish the user to fill in for the search; this would be similar to the way you do it in Access (if I understand correctly). If you use a dynamic find with a dashboard-style search layout and globals, you need to create a new global field, include it in the search layout and in the search script (and add a check in the script if the presence of a value for the criterion is crucial).

I couldn't find an “AllActivity” field in your database, so I created one and filled it with random data to show how you can extend the search script. Proceed in a similar fashion for any additional search criteria. Remember that the way the data is stored doesn't restrict the way you present the corresponding search criteria to your users, since you have all the time in the world (or at least an entire script) to parse and manipulate the search input.

AgebyRangesv1.1_eos.fp7.zip

[4Justme2]

I appreciate your patience with me because I am not quick to grasp many of these concepts have taken me some time to get my arms around. So another couple of questions, if I could. I don't see how the tie is to the AllActivity field and the gSearchAllActivity global field. The global points to the value list of "1". The AllActivity field is simply an indexed number field. So how does FM understand that when you check the gSearchAllActivity global field you are saying that you want to meet that same criteria in the AllActivity field in the People table?

Another question...There's a field in the People table called ParticipantStatus. This field identifies if the person is considered a Child or a Participant because the status of Child isn't determined by age alone. If the Child is also a parent with children of their own we classifiy and consider them them a Participant rather than a Child. On my report, in addition to all the other criteria, I want to weed out those people who are not classified as a Child.

In an effort to accomplish this, I added a new record: a person named Judy, age 13. Judy recently had a child so we have her classified her as a Participant (rather than a Child). My goal is that Judy does not appear on my report. I then created a calculation field called ChildOnly with a case statement, saying

Case (

ParticipantStatus="Child" ;1;

ParticipantStatus="Participant";0;

)

In addition to a global field called gSearchChildrenOnly.

I placed the gSearchChildrenOnly field in the form of a checkbox on my Prepare Report layout and pointed the field to a value list called ChildOnly containing the number "1".

When I try to generate a report with a checkbox marked in the gSearchChildrenOnly checkbox Judy still appears on my report. What is it I'm missing here? I did intentionally make Judy have a 1 in the AllActivity checkbox as a test... since Judy meets that criteria. But if any of the criteria is not true I don't want them to appear on my report. Can I trouble you one more time to take a look at the attached?

Thank you again for your patience in helping me to understand.

AgebyRangesv1.1_eos.zip

[eos]

Let me begin with answering the second question: Creating a global field and placing it on a layout for the user to check doesn't do anything in itself. The crucial part in generating the report is played by the script that you start by clicking the button. Here's a brief rundown: The script checks for errors (missing input) and exits if necessary; then it navigates to the report layout and searches for matching records (again checking for errors, this time “no records found”, again with an exit option), based on your selections; then it sorts the records (because the summary parts and the summary fields only work when sorted by the indicated fields) and finally sets the report title according to your selection.

As to the calculation you set up, this is likely connected to your first question: The “fact” that gAllActivity and AllActivity have the same data is partially an artifact of the data used. Simply put: Since you either want to include records with all AllActivity or not, the field on the search layout is simply formatted with a value list 1, because your choice is Boolean. This has nothing to do with the fact that AllActivity also holds a 1 or 0 (except that it is Boolean, too); it could also hold one of two different text strings. The point is: AllActivity is included in the search only if you have checked gAllActivity, and then the script sets a value which will yield the desired result - which, as noted, could as well be a text. The check in the script simply reads “If [ gAllActivity ]”, because the result of an If must be an expression with a result of Boolean.

Therefore your creating a field which parses ParticipantStatus into 1 and 0 is, strictly speaking, unnecessary, because you can program the script to set ParticipantStatus itself to "Child.” On the other hand, it's often desirable to parse a field which can hold different text values into numbers because it can make dealing with them programmatically more straightforward; but then again it may be easier to do this with a utility table.

Check the AgeGroups table to see that it holds a human readable string as well as one which you can feed directly into FileMaker's search engine. The script compiles a return-separated list of the checked age groups in the selection portal, then, during the search, loops through this list, creating as many search requests as there values. Each time it sets the ageGroup field and possibly others, according to your selected search criteria, and finally performs the search (or, as FM, Inc. prefers to call it, the Find). The advantage is that within this utility table, a part of your “business rules” is clearly laid out, not hidden in a value list or a calculation. Modifications or additions in this one central place let you easily modify your interface, and a script which uses them simply continues to work (ideally …).

OK, that's a lot to digest, and I can only assume this is all very un-Access-ish (and hope that it makes a modicum of sense), but you had to ask!

Here's an improved version of your database. Be sure to read the report script; since you have 11 Advance, I suggest you fire up Script Debugger and Data Viewer and watch it do its tricks in slo-mo.

AgebyRangesv1.2_eos.fp7.zip

[Fitch]

If I were going to generate this report criteria in Access I'd simply include the field in my query, type the number 1 in the field criteria and run my report. In Filemaker Pro how do I force this so I only get the "1" values on my report?

FileMaker can do this easily. What eos is presenting is a method where the search process is tightly controlled. It sounds like maybe you want an interface that acts more like a "native" FileMaker Find. A basic method is to create a layout that has the fields you want the user to see for the Find, and a script:

Enter Find mode

Go to layout[your nice find layout]

Pause

Set Error Capture(on)

Perform Find

// handle it if none found or bad request -- that's another topic...

Go to layout[report]

Sort

Put a Find button on the Find layout that simply resumes the script.

If you want your users to choose the option, you could include your ChildOnly field on the Find layout, format it as a checkbox with a value list of 1, and to set it as the default option, before the Pause do:

Set Field[ ChildOnly ; 1 ]

If you don't want the user to choose the option, you could put a script step after the Pause:

Set Field[ ParticipantStatus ; "Child" ]

Hope that gives you some ideas.

[4Justme2]

eos, You're patience in explaining to that detail is greatly appreciated... along with the example--it REALLY helped me understand! Fitch, thank you for the example and explanation--it's helps me to see the contrast between the two methodologies and to know there's a real simple script I can generate if the control isn't necessary in a given situation. Thank you both for taking the time to digest my issues and address all of my concerns. I am so grateful for the help. :hug: