Summarizing Value Lists

[Veronica]

I have looked all over for a solution that should be fairly simple. I don't know if I am asking the wrong questions or not understanding the posts I've viewed. What is so hard about summarizing a simple two value value list? It is for specifying gender. The value list is "F" or "M". If you can't summarize a value list what is the point of a value list? I have spent hours and hours on this problem without success. I have tried extracting "F" to an intermediate field called "Female" and "M" to an intermediate field called "Male" but this no longer works and I don't know why. It worked in an earlier 8.5 version of the database.

Shouldn't this be a simple operation?

[Genx]

What do you mean "Summarise a value list"?

If it's too hard to explain, draw a picture.

[Veronica]

All I want is the total number of "F's and the total number of "M"s. The source field is called "Gender" and it is a radio button display of "F" and "M".

[fabriceN]

Using a self join, you can try PatternCount ( List ( selfJoin::gender ) ; "M" )

[comment]

I have tried extracting "F" to an intermediate field called "Female" and "M" to an intermediate field called "Male" but this no longer works and I don't know why. It worked in an earlier 8.5 version of the database.

It should work the same way in version 9 as it did in previous versions (I am assuming you are referring to version 9, even though you indicate version 8.5 in your profile and in the original post). If you describe in more detail how you went about it, or attach a sample file, we might be able to tell where you went astray.

See also a note about Boolean fields here:

http://www.fmforums.com/forum/showtopic.php?tid/181943/post/228583/#228583

[LaRetta]

Hi Fabrice!

Call me a TO-saturated developer but I am no longer so quick to add a relationship just to get a count when an easy grouped report would do it.

Hi Veronica! Do you have an ID field? If not you should. And you can use it here ...

Create a summary field based upon this ID. Count it. Call this summary s_count (for summary count of IDs).

Then create a new layout (let FM's Layout Assistant walk you through it), type columnar with grouped data, include subtotals. Specify your fields (be sure you select Gender). Organize by Category Gender. If you don't need the detail (the list of people in each category) I'll show you how to delete it later. Be SURE to then sort by Gender. If it isn't sorted by the category, your report will not work.

Specify Subtotals - here you select that new summary field you created. Then let FM create the script - it is critical that it be sorted so let FM do it for you.

Now here are variations:

If you don't want a body section at all, delete it. It will delete all the fields within it as well. You can have a report which is only leading part. You can also delete the trailing part which was created. But before you do, grab that new summary field and drag it up next to Gender field in the leading part. Then delete the trailing part.

In this way, you will end up with two lines in your report:

M 36

F 22

Your report will be instantaneous and you can get your totals whenever you wish. It's just another way to go.

UPDATE: I forgot to mention that this s_Count_IDs will come in handy for many value list counts. For instance, if you create a report then asking for leading part (grouped) report by age, this same field will count each age group and so forth. It will also always give you a count of children when the summary is used from a parent table.

LaRetta :wink2:

Edited May 18, 2008 by Guest
Added update

[fabriceN]

Hi LaRetta,

call me a field-layout-script-saturated developer then :

Edited May 18, 2008 by Guest

[LaRetta]

Oh, hey, I understand that as well. But a script sitting there unused; a layout sitting there unused, takes no resources. Every table occurrence requires resources to build the dependencies not to mention cluttered graphs which make working in them a headache. :wink2:

[comment]

This can be done without scripts and/or relationships, by turning Gender into a Number field (Boolean). A summary field defined as Total of Gender provides the count of one gender. Subtract the result from Get (FoundCount) to get the other number (assuming no one is neutral).

The total cost is 2 fields, it works in Browse mode and it respects the found set.

[LaRetta]

All good points, Michael. The Gender field could still be 1/0 and it wouldn't affect my use of the summary field for the report. Your suggestion restricts the calc's use to only Gender. If you then want the same flexibility for another value list, you will need to duplicate your efforts. It will not work for multi-value value lists either ...

If you use the unique ID and count it, the same summary field can be used for many grouped reports for everything from age to gender to location etc. just by placing it in the same part on the report.

And it is the same field which can be used to count children so it is a summary field which most Developers already use. It seems far more flexible when the only cost is a sort and preview mode.

Well I DO like it being available in browse mode but how long does it take to click a button and view it in Preview? Rarely worth the cost, in my opinion only.

LaRetta

Edited May 18, 2008 by Guest
Added sentence

[comment]

I am not dismissing your suggestion, on the contrary: it's the default Filemaker method, and it should always be the preferred choice, unless there are other considerations - such as the need to view the summary "live" in Browse mode.

It will not work for multi-value value lists

Not this one, but there's another that will...

[Ugo DI LUCA]

Even one calculated repeating field with two iterations, one for male the other for female :

One way could be :

Let ( [

$R =  Get ( CalculationRepetitionNumber ) ;

$target = Case ( $R =1 ; "M" ; "F" ) ;

$CL = CustomList ( 1 ; Get ( FoundCount ) ; "Let ( [ $Value = GetNthRecord ( Extend (Gender ) ; [n] ) ] ; Case ( $value = $target ; $value ) )" ) ] ;

ValueCount ( $CL ) )

where the count of males would go into the first repetition and the count of females in the second.

Well, just kidding...I'd rather set a repeating variable and set the result of a global repeating field with this result by script :

Edited May 18, 2008 by Guest

[comment]

:idunno:

A summary field is fast, simple to implement, unlimited in the number of records it can summarize, and it updates automatically.

What are the advantages of the suggested alternative?

[Veronica]

Thank you all for your advice and support. I just don't understand why this should have been so difficult. I am using a summary report with a couple of sub-summary parts already and I am summarizing other value lists successfully without individual sub-summary parts. I have one sub-summary part where I am summarizing Grade Level, Age Group and Ethnicity and it is producing accurate results for these categories. Just the gender value list wasn't working and couldn't understand. I will try another sub-summary part first before the other suggestions. Like LaRetta, I am for simplicity.

Again, thank you all. :smile2:

[comment]

I am summarizing other value lists successfully without individual sub-summary parts

You don't say how you do this, so it's difficult to be specific.

One point to bear in mind about sub-summaries is that they are hierarchical. If you have a sub-summary by Ethnicity and another one by Gender, you can produce a report where each Ethnicity group is broken down by Gender, or a report where each Gender group is broken down by Ethnicity. But you cannot get grand totals by Gender in the first case, or grand totals by Ethnicity in the second - at least not when using a single summary field to count the record ID.

[Veronica]

This is so simple but I can't figure out what is wrong.

I have a field: "Gender". It is a drop down value list consisting of "F" & "M".

I have two other calculated fields with a text result (and stored values). The if statements look like this:

Female field: If (Gender = "F"; "F"; "")

Male field: If (Gender = "M"; "M"; "")

Neither of them are working. No values are returned in any record. I have used this type or style of calculation field in several other fields and databases successfully and I cannot figure out why it is not working here. This is so frustrating.

[Lee Smith]

If I'm understanding you correctly,

You are you using the response in Gender for the If Statement in a different Field?

If so, why not just use Case ( Gender = "F" ; "F" ; "M" )

It will be blank if nothing is entered.

HTH

Lee

[comment]

The most likely reason is that the actual values are not "F" and "M", but perhaps "F " and "M " (trailing spaces), or such.

See also:

http://www.fmforums.com/forum/showtopic.php?tid/195481/post/291279/#291279

Wait a minute - that's YOUR thread! What's going on here?

[Lee Smith]

We have a double post.

I have merged the two topics.

Edited June 5, 2008 by Guest

[LaRetta]

Wow. This thread is SOOOO confusing. Well, another possibility ... if you are using a dropdown or popup ( or checkbox ) then you can have BOTH values in the field so neither calculation would work because they both are based upon =. You should use radio button so only one value can ever be selected.

[Vaughan]

I like adding the option to mark something "unknown". I usually make it the default option, so unless the user selects "m" or "f" the gender is "unknown".

I've worked for organisations where a trans-gendered option was requested. In a fit of exasperation I suggested a slider control with "m" at one end and "f" at the other, so all possible options are covered. (Sometimes, my genius is not appreciated.)

"... if you are using a dropdown or popup ( or checkbox ) then you can have BOTH values in the field..."

Auto-enter calculations are great for avoiding this (same problem with radio buttons, grrrr). If the field is empty, enter the default value, otherwise strip out all except the right-most value.

[LaRetta]

I like the slider idea! There have been many times where, when looking at someone but too sheepish to ask, I'd wish for a slider ... a little to the left. Nah, a little to the right.

As for using auto-enters, sure. But setting something up properly to begin with is better than correcting for it using auto-enter. Sometimes one can over-use auto-enters ... and then wonder why their imports ( and other actions ) take so long. Use it if needed but don't design with the correction in mind ( if one can help it ). At least that's my philosophy today. Ask me tomorrow and it might be different.

LaRetta :wink2:

[Vaughan]

"setting something up properly to begin with is better than correcting for it using auto-enter..."

I've got to disagree there... IMHO using the auto-enter to clean the data entry *is* good setting up. I combine it with field-level validation too.

It's the only way to ensure that radio-button fields ONLY have one entry. Ditto for menus. It's great for removing white space and formatting text in fields that are cut+pasted from other programs.

I had a support call a couple of hours ago, in an old system were two text fields are concatenated to create a relationship key. The user had entered one code with a trailing carriage return, and the resulting "new" record appeared to have heaps of existing related records. I found out when they asked me to delete them all! I solved the problem by an auto-enter calc to Trim4() the entry.

[LaRetta]

MHO using the auto-enter to clean the data entry *is* good setting up. I combine it with field-level validation too.

You may know when and when not to use it but many newer people do not. And you missed the point that I agreed with you on the examples you gave. I was adding a caution that it is NOT the SAVE-ALL for every field and every situation. But I do NOT agree with your quote above. One can restrict carriage returns at the layout level. One can restrict entries in a value list by using popup. You can validate using script at times. To properly protect from cut/paste from other programs? There are other methods to control what is copy/pasted into a program without overly protecting every field by applying auto-enter. It can easily be handled in one whack instead of investing the time to modify every field. There is no blanket right or wrong here - THAT was my point.

If one dismisses good design and other options FIRST and always simply applies auto-enter then you are doing a dis-service to your solution. Auto-enters on every field; protecting til the cows come home slows a system down. As for validaton? There are times that field-level validation IS important. But it can also fire when switching to another layout. FM does NOT handle it properly in all instances and it is harsh to Users. I would rather validate myself with proper UI design and, although I have used it in extreme instances, I'll go through hoops NOT to use it. So I guess we'll just have to agree to disagree.

LaRetta :wink2:

[mr_vodka]

I like the slider idea! There have been many times where, when looking at someone but too sheepish to ask, I'd wish for a slider ... a little to the left. Nah, a little to the right.

Yes especially if it was a person such as PAT.