Help with Calculations from a value List?

[keepemup]

I am working on a report that will give me the totals from value lists. First thing is that I know the data base is not set up right but I am trying to make it work this way. I am having trouble with a calculation that works for other fields just fine but not working for this particular field. I have found that I can get the total by doing different calculation. Here are a few ways that I am getting what I want:

 

Dog

If (animal type="Dog";1;0)

Sumdog

Total of dog

 

dog spay

Case(animal type="dog" and procedure="Neuter";1;0)

sumdogspay

Total of dog spay

 

I have also found that if I type things not equal to works.

 

cat neuter

Case(animal type ≠ "dog" and animal type ≠ "Pit Bull" and procedure="Neuter";1;0)

sumcat neuter

total cat Neuter

 

 

But the problem that I am having is some of these work and some of them don't when I change the fields.

 

Dog

Case(animal type="dog" and animal type ="Pit Bull";1,0)

 

This one won't calculate the total of dog and pit bull.

 

 

What is the best calculation for me to use to get the total of each value list if I need to use 2 or 3 different values?

 

I have also used PatternCount on some fields. Go ahead and slam away for using so many ways. But if I couldn't get one to work I would use one that did.

[touchMe]

oh my goodness!@ - you are putting this together running, aren't you?

 

a dog is a dog, a cat is a cat... just because something is not a dog, does not necessariy mean it is a cat, a pit-bull is not an animal type, it is a type of dog (although that may be debated [humor]), do you see where I am going with this?

 

take a moment and put on paper before putting into code... I promise it is worth the time invested up front, just to think about what you want

 

what I suspect you want may be real easy... just a bit of planning, write a specification, not a calculation

[Ender]

I'm guessing you'll need an Animal Type field and a Breed field to record the different types of critters you've got in your arc.

 

For a report, use a columnar list with sub-summary parts. The parts will look like this:

 

Header

---------

Sub-Summary by Animal Type (Leading)

---------

Sub-Summary by Breed (Leading)

---------

Body (optional, if you want to show each record that's included in the group)

---------

Footer

 

 

Put the Animal Type field in the Sub-Summary by Animal Type (Leading) part.

Put the Breed in the Sub-Summary by Breed (Leading) part.

Put a Summary Count of RecordID field next to those fields in each of those parts above.

If you're using the Body, add whatever fields you want to show details on in that part.

 

When the found set is sorted by Animal Type and Breed, and the layout is viewed in Preview Mode or printed, the Sub-Summary parts will show along with their counts.

[keepemup]

oh my goodness!@ - you are putting this together running, aren't you?

 

a dog is a dog, a cat is a cat... just because something is not a dog, does not necessariy mean it is a cat, a pit-bull is not an animal type, it is a type of dog (although that may be debated [humor]), do you see where I am going with this?

 

take a moment and put on paper before putting into code... I promise it is worth the time invested up front, just to think about what you want

 

what I suspect you want may be real easy... just a bit of planning, write a specification, not a calculation

 

 

I understand what you mean about dog, Pit Bull. That is the way it was set up and I can change it to put Pit bull in the breed field. It is a long story, but I acquired a database already set up and it is not set up the correct way and am just trying to make things work then work on setting it up correctly as I learn File maker. The database has a lot of issues to be used to the full extent that Filemaker works.

 

What do you mean about a specification? As the poster below stated summary fields, I understand that but I am working with totals from quite a few fields and it is all in a flat file instead of relationship database. I can get the fields that are a drop down or radio button to work in summary fields but I can't get it to work when a field can have multiple choices as in check box list.

 

Can you explain the difference in the IF statement and the Case statement and when they should be used?

[HBMarlowe]

Dog

Case(animal type="dog" and animal type ="Pit Bull";1,0)

 

This one won't calculate the total of dog and pit bull.

 

This one will always return "0" because animal type will never equal "dog" AND "Pit Bull" at the same time.

[HBMarlowe]

Can you explain the difference in the IF statement and the Case statement and when they should be used?

 

 

The "If" calculation returns one result if a condition is met, another if the condition is not met. "Case" allows you to define any number of conditions with one result for each, plus a default result.

 

Thus

Case (

animal type = "dog" ; 1 ;

animal type = "Pit Bull" ; 1 ;

0)

will return "1" if animal type equals "dog" or "Pit Bull" and "0" if animal type is empty or contains any other value.

 

Note that the calculation result must be set to "Number" for this to work. If the result were set to "Text" you would need to enclose the ones and zeros in the case statement in quotes.

 

Further reading in the "Help" file would probably be "Helpful."

 

Good luck!

[LingoJango]

This one will always return "0" because animal type will never equal "dog" AND "Pit Bull" at the same time.

 

That was my original thought, too, but if the field is formatted as a checkbox set I would check both items for a pit bull.

[HBMarlowe]

That was my original thought, too, but if the field is formatted as a checkbox set I would check both items for a pit bull.

 

And that will still return "0" because of the "=" in the calc. PatternCount could be used to return "1" if both boxes were checked.

[LingoJango]

Right, I'd lost track of that line of reasoning. :-)