need help figuring how to do total with drop down

[solarpunk]

Hello,

i was wondering can give me a hand with this. basically i have a field call Status which is a drop down menu. The list of value is Applied, Pending, Enrolled, Funded. I want to find out what the total is for each of those values. I am doing a report base on those value for each program a student is part of. the database is student database. if you have any questions, please let me know.

Thank you

victor

[QuinTech]

If you create a relationship between the Status field and itself, and call that relationship "SelfStatus", then the following calculation will probably give you what you're looking for:

Sum ( SelfStatus::whateverTheNameOfTheFieldYoureTotallingIs )

If this doesn't work, please give a little more detail about your file structure. Namely, is Status a global? Is it in a different file than the recordsto be totalled?

Jerry

[spb]

Try making a self-relationship based on the Status field. Then make a calculation field where the calc is a Sum function that summarizes your number field based on this relationship:

Sum(NewRelationship::Number)

Steve Brown

[QuinTech]

Is there an echo in here?

[Lee Smith]

Hi Steve,

Please update your profile to reflect your current operating system, platform and the version of FileMaker you are using. The link is just below your name on the Left (in blue) "profile"

Lee

[solarpunk]

The database is just one file. They are not multiple databases at all. Status is not a global field at all. i try the selfrelations but it doesnt seen to work.

This how i set up the calculation..I call the field TotalStatus and the calc is:

Sum( selfstatus::Status )

[QuinTech]

Don't sum the status, that's a text field. If you are just trying to return a count of the different statuses, try Count ( selfStatus::status ) instead.

J

[solarpunk]

Quintech,

it seen to work but it show a whole total for all the status. now, i just want to get the total for each individual status: pending, enrolled, applied, funded. how do i go about it. i am new when come to creating calculations.

thanks

victor

[QuinTech]

Hmmm... it works in the attached example. Try to duplicate this in your database. Is selfstatus a relationship from Status to Status? If so, i'm not sure why it wouldn't work.

J

solarpunk.zip

[solarpunk]

Thanks it work. i just need to figure out some other part of the report which i didnt mention before. Maybe you know how i can do it. the report is base on program so each program i need find out what what total of the status is.

Example of program is Austin AM Fall 2004.

thank you for all you help. i appreciate it.

victor

[QuinTech]

Two ways you could do this:

1. Similar to how you did status.

Make a calculated field equal to ( Status & "|" & Program ). The "|" is the pipe character, which is Shift-, and is frequently used as a delimiter in a concatenated field such as this one. Make a relationship from this field to this field, just like you did with status, and then your calculation is Count ( theNewRelationship::theNewCalculatedField ).

2. With sub-summaries.

Put a sub-summary when sorted by Program on your layout, then in that part put another sub-summary when sorted by Status. In the sub-summary by Program part, put the Program field; in the sub-summary by Status part, put the Status field and also a Summary field which is just equal to Count of SomeUniqueIDNumber. Now when you sort by Program and then Status, and enter Preview mode or print, you will see a summary of statuses for each program.

J

[solarpunk]

QuinTech,

Thanks for the info. I endup changing the way i did the counting. here is what i did.

Basically, i create two Calc field for each status.

I have:

c_Pending=If(Status = "Pending", 1, 0)

c_cnt_Pending=Total c_pending

So i did that for each status, it work fine. now, i need to break those numbers by Program.

Basically i have around 15 program going under Drop list.

Now for each Program, i need to find out how many total there is for each status. i got part in which i can find the total for each status but i need to match it to the program basically.

it would be Something like this:

Austin Spring AM 2004, Pending 0, Funded 4, Enrolled 5, Applied 10

Austin Fall AM 2004, Pending 2, Funded 3, Enrolled 6, Applied 15

Here is attachment of the DB.

[QuinTech]

Is c_cnt_Pending a summary field? If not, make it a summary field equal to total of c_Pending. Then make a subsummary part when sorted by Program and put the fields you currently have in the body into this subsummary. Sort by Program, go to Preview mode, and there you have it.

Minor style point: This calculation for c_Pending will be more efficient and produce the same result:

( Status = "Pending" )

J

[spb]

Lee: I updated my ;profile, but left platfom blank. I work equally in classic Mac and Win2000, depending on what I need to do on my network. They didn't have a choice for mixed platfornm.

Jerry: Sorry about the echo, even though my post came after yours, I didn't see your post until the day after I posted. You did a far more thorough job of answering the question than I could, anyway.

Steve Brown

[solarpunk]

Quintech.

it didn't work for me with teh summary report. the c_cnt_field are summary fields. i still need figure out how to break down without doing a preview mode.

I want to see a report in layout like this:

Austin Spring AM 2004, Pending 0, Funded 4, Enrolled 5, Applied 10

Austin Fall AM 2004, Pending 2, Funded 3, Enrolled 6, Applied 15

El Paso Fall WE 2004, Pending 1, Funded 5, Enrolled 10, Applied 5

Thanks for all you help.

Victor