How do I count the number of different entries in one field? (NOOB Question!)

[Guy Smith]

I have a bunch of different facilities that each have a (variable) number of production lines.

Each production line record has a field (called "LineStatus") to indicate whether the line is active, undergoing maintenance, idle, or shut down.

I need to be able to print a management report with each facility listed and how many production lines are currently active, undergoing maintenance, etc.

I tried to use a calculation field: Count(Case(ProductionLines::LineStatus="Active";"")) [and so on for all LineStatus field choices], but all i get for an answer is the infamous question mark.

Any help would be greatly appreciated.

Thanks,

Guy

[Vaughan]

Case( ProductionLines::LineStatus="Active" ; "" )

This expressions says "if the status is active then return nothing" which is probably not what you meant it to say.

[Guy Smith]

Hmmm...Good point. Unfortunately, I changed it to Case( ProductionLines::LineStatus="Active" ;1; "" ), thinking it would then count all of the ones and give me what I'm looking for, but I still get just the question mark. Any ideas on how to correctly count the number of Active/undergoing maintenance/idle production lines?

Thanks again.

[comment]

I need to be able to print a management report with each facility listed and how many production lines are currently active, undergoing maintenance, etc.

Filemaker has no CountIf() function, and you cannot place a condition inside the Count() function. Try instead =

ValueCount ( FilterValues ( List ( ProductionLines::LineStatus ) ; "Active" )  )

Edited March 29, 2012 by comment

[Guy Smith]

_{Aha! That explains why my clever use of the Count() function fell flat on its face!!}

_{Thank you very much for your help on this, Comment - it exemplifies why I enjoy this community so much: People with real brains actually take the time to teach the unteachable!}

_{Best Regards,}

_Guy

[Guy Smith]

AAAARRRRGH!!! I still can't get it to work!

I can never get any results to be greater than 1, even though there are many facilities with multiple production lines that are active and multiple ones that are idle, etc.

I even modified the calc formula from Comment to: ValueCount ( FilterValues ( List ( ProductionLines::LineStatus) ; "Active" ) ) and I put the choices (active, idle, etc.) in a value list...Still won't work.

Any other ideas?

Thanks,

Guy

[Vaughan]

Check that the line status is actually "active" and not "active " <-- with a space at the end, or the beginning.

[Guy Smith]

Vaughan: Thanks much for your response. I checked to make sure that there are no extra spaces and the problem pops up for every status type - the calc is only returning 1 for each type, regardless of how many there really are. Another field is showing the total number of status types correctly: A summary field in the body of the layout: count(LineStatus) and, on another layout, a portal is displaying all of the production lines and their status correctly.

A piece of slate and some chalk is starting to sound pretty tempting right about now!

[Genelia]

Create different calulation field for each type and then count each individually.

for example:Field 1:Case(ProductionLines::LineStatus="Active";1)-----------------stored calculation field

Field2:Case(ProductionLines::LineStatus="Maintenance";1)---------------Stored caluculation field

Field3:Case(ProductionLines::LineStatus="Shut Down";1)------------------Stored calculation field

Now Count_field1_Active: Sum(Field1)

2, Count_field2_maintenance: Sum(field2)

3.Count_field3_ShutDown: Sum(Field3)

Now you will get individula total of each category from all records for production meand No. of active , No. of maintenance, No. of Shut...

All the Best!!!!!!!!!!!

[comment]

Case(ProductionLines::LineStatus="Active";1)-----------------stored calculation field

For one thing, a calculation field that references a related field cannot be stored.

For another, you are testing only the first related record.

[comment]

I even modified the calc formula from Comment to: ValueCount ( FilterValues ( List ( ProductionLines::LineStatus) ; "Active" ) )

Sorry, there was a typo in my earlier post: one of parentheses was misplaced. However, your correction is spot on and should work - see the attached:

Countem.zip

[Guy Smith]

Thank you all so much for walking me through this seemingly simple but actually quite complicated calc! It was very enlightening to see how different developers can come up with different solutions from different angles to solve the same problem.

Comment: Your "Countem" file not only solved the problem, but taught me a number of processes I can use in future calculations - you rock!

Best Regards to all and thanks again,

Guy

[Slater]

should work - see the attached:

Thank you Comment - the number of times that something you've written has helped me is crazy. This did the trick.

Many thanks for your help.