count() that counts a value...

[Himitsu]

I have been trying to find a way to count the records that have a certain value in a field. I tried to run a count( case(field = "active")) but it didn't work. It might be easy... but if anyone knows, that would be great. Thanks.

[Himitsu]

I have been trying to find a way to count the records that have a certain value in a field. I tried to run a count( case(field = "active")) but it didn't work. It might be easy... but if anyone knows, that would be great. Thanks.

[Himitsu]

I have been trying to find a way to count the records that have a certain value in a field. I tried to run a count( case(field = "active")) but it didn't work. It might be easy... but if anyone knows, that would be great. Thanks.

[Fenton]

I assume that you want to count how many times the field = "active" in the whole file? The Count() function is in the Aggregate section. From FileMaker Help:

Returns the number of valid, non-blank values in field where field can be any of the following:

a repeating field (repeatingField).

a field in matching related records specified by (table::field), whether or not these records appear in a portal. <-- This one (me)

several non-repeating fields in a record (field1;field2;field3...).

corresponding repetitions of repeating fields in a record (repeatingField1;repeatingField2;repeatingField3), if the result is returned in a repeating field with at least the same number of repeats.

several fields in the first matching record specified by (table::field1;table::field2;...). You can include fields from different tables (table 1::field A;table 2::field B...).

If you want to count in the current file, you'd need a self-relationship (table occurrence); otherwise a relationship. On the left side of the relationship you'd need the word "active." Since you want to see the count from any other record (or table?), you'd want this value accessible. I generally use an Unstored calculation field, = "active" (the word "active", in quotes in the calculation dialog, Storage options, "Do not store").

It could also be a global field, if you want to toggle this to see other values for that field.

Then you need a relationship (or self-relationship) between this unstored field and the field you want to count "active" in. Then it's simple:

Count (Active relationship::field to count)

[Fenton]

I assume that you want to count how many times the field = "active" in the whole file? The Count() function is in the Aggregate section. From FileMaker Help:

Returns the number of valid, non-blank values in field where field can be any of the following:

a repeating field (repeatingField).

a field in matching related records specified by (table::field), whether or not these records appear in a portal. <-- This one (me)

several non-repeating fields in a record (field1;field2;field3...).

corresponding repetitions of repeating fields in a record (repeatingField1;repeatingField2;repeatingField3), if the result is returned in a repeating field with at least the same number of repeats.

several fields in the first matching record specified by (table::field1;table::field2;...). You can include fields from different tables (table 1::field A;table 2::field B...).

If you want to count in the current file, you'd need a self-relationship (table occurrence); otherwise a relationship. On the left side of the relationship you'd need the word "active." Since you want to see the count from any other record (or table?), you'd want this value accessible. I generally use an Unstored calculation field, = "active" (the word "active", in quotes in the calculation dialog, Storage options, "Do not store").

It could also be a global field, if you want to toggle this to see other values for that field.

Then you need a relationship (or self-relationship) between this unstored field and the field you want to count "active" in. Then it's simple:

Count (Active relationship::field to count)

[Fenton]

I assume that you want to count how many times the field = "active" in the whole file? The Count() function is in the Aggregate section. From FileMaker Help:

Returns the number of valid, non-blank values in field where field can be any of the following:

a repeating field (repeatingField).

a field in matching related records specified by (table::field), whether or not these records appear in a portal. <-- This one (me)

several non-repeating fields in a record (field1;field2;field3...).

corresponding repetitions of repeating fields in a record (repeatingField1;repeatingField2;repeatingField3), if the result is returned in a repeating field with at least the same number of repeats.

several fields in the first matching record specified by (table::field1;table::field2;...). You can include fields from different tables (table 1::field A;table 2::field B...).

If you want to count in the current file, you'd need a self-relationship (table occurrence); otherwise a relationship. On the left side of the relationship you'd need the word "active." Since you want to see the count from any other record (or table?), you'd want this value accessible. I generally use an Unstored calculation field, = "active" (the word "active", in quotes in the calculation dialog, Storage options, "Do not store").

It could also be a global field, if you want to toggle this to see other values for that field.

Then you need a relationship (or self-relationship) between this unstored field and the field you want to count "active" in. Then it's simple:

Count (Active relationship::field to count)

[Himitsu]

I figured it was something like that but wouldn't it be great if it could contain a calc...

Well, I did that but nothing. I notice that my "active" field in the line items is a calc and is set to unstored. and the "active" field I made in the classList is an unstored global that = "active". I have a new relationship set from the classLIst to a new occurance called lineItemsCount and have that equal the classID and active field. But nothing... I have done this type with lots of other things such as year... rank... and it always works... but this didn't... wondering if it is because it is starting from a global and = a calc... any hints would be great..

[Himitsu]

I figured it was something like that but wouldn't it be great if it could contain a calc...

Well, I did that but nothing. I notice that my "active" field in the line items is a calc and is set to unstored. and the "active" field I made in the classList is an unstored global that = "active". I have a new relationship set from the classLIst to a new occurance called lineItemsCount and have that equal the classID and active field. But nothing... I have done this type with lots of other things such as year... rank... and it always works... but this didn't... wondering if it is because it is starting from a global and = a calc... any hints would be great..

[Himitsu]

I figured it was something like that but wouldn't it be great if it could contain a calc...

Well, I did that but nothing. I notice that my "active" field in the line items is a calc and is set to unstored. and the "active" field I made in the classList is an unstored global that = "active". I have a new relationship set from the classLIst to a new occurance called lineItemsCount and have that equal the classID and active field. But nothing... I have done this type with lots of other things such as year... rank... and it always works... but this didn't... wondering if it is because it is starting from a global and = a calc... any hints would be great..

[Himitsu]

actually when I switched it to unstored, it removed the global. But it still won't show.

[Himitsu]

actually when I switched it to unstored, it removed the global. But it still won't show.

[Himitsu]

actually when I switched it to unstored, it removed the global. But it still won't show.

[Fenton]

Well, if you have an unstored field on the right side of a relationship, it will not work. It has to be a stored field. Why is it unstored if it is data?

[Fenton]

Well, if you have an unstored field on the right side of a relationship, it will not work. It has to be a stored field. Why is it unstored if it is data?

[Fenton]

Well, if you have an unstored field on the right side of a relationship, it will not work. It has to be a stored field. Why is it unstored if it is data?

[Himitsu]

well, it is an unstored calc. It is a case() that returns "active" if the students is still with our school or "stopped" if the student has stopped. That way, it displays in the class who is not active. I don't want to remove them from the class because I want to see them forever who was in the class. Like a history. Let me remove the untored area from the lineitems and see what happens.

[Himitsu]

well, it is an unstored calc. It is a case() that returns "active" if the students is still with our school or "stopped" if the student has stopped. That way, it displays in the class who is not active. I don't want to remove them from the class because I want to see them forever who was in the class. Like a history. Let me remove the untored area from the lineitems and see what happens.

[Himitsu]

well, it is an unstored calc. It is a case() that returns "active" if the students is still with our school or "stopped" if the student has stopped. That way, it displays in the class who is not active. I don't want to remove them from the class because I want to see them forever who was in the class. Like a history. Let me remove the untored area from the lineitems and see what happens.

[Himitsu]

well, couldn't do that either. It is using another calc field. So I will have to think of some other way around this... Maybe I could create an inclass field that has an auto enter calc and attach that to the open script. But I wonder how long that will take... there are over 2000 records for all the classes... do you think if I add a search function for a field that states "updated" and then the script would search for "updated" in that field, perform, then loop, go to first record, set field(inClass=activeField) and set field (updated "updated". Do you think that sounds too much work for just a count function?

[Himitsu]

well, couldn't do that either. It is using another calc field. So I will have to think of some other way around this... Maybe I could create an inclass field that has an auto enter calc and attach that to the open script. But I wonder how long that will take... there are over 2000 records for all the classes... do you think if I add a search function for a field that states "updated" and then the script would search for "updated" in that field, perform, then loop, go to first record, set field(inClass=activeField) and set field (updated "updated". Do you think that sounds too much work for just a count function?

[Himitsu]

well, couldn't do that either. It is using another calc field. So I will have to think of some other way around this... Maybe I could create an inclass field that has an auto enter calc and attach that to the open script. But I wonder how long that will take... there are over 2000 records for all the classes... do you think if I add a search function for a field that states "updated" and then the script would search for "updated" in that field, perform, then loop, go to first record, set field(inClass=activeField) and set field (updated "updated". Do you think that sounds too much work for just a count function?

[Himitsu]

I meant search the updatedField for "=" to return an empty field...

[Himitsu]

I meant search the updatedField for "=" to return an empty field...

[Himitsu]

I meant search the updatedField for "=" to return an empty field...

[Himitsu]

Ok, that worked. A little much for just counting... but I thought my staff would like to quickly see the actual number of active students so they can tell new students which classes are available. Thanks Fenton for pointing me that direction. I will just attach that script to the open script to activate when it is opened.

[Himitsu]

Ok, that worked. A little much for just counting... but I thought my staff would like to quickly see the actual number of active students so they can tell new students which classes are available. Thanks Fenton for pointing me that direction. I will just attach that script to the open script to activate when it is opened.

[Himitsu]

Ok, that worked. A little much for just counting... but I thought my staff would like to quickly see the actual number of active students so they can tell new students which classes are available. Thanks Fenton for pointing me that direction. I will just attach that script to the open script to activate when it is opened.

[Fenton]

Somehow I think you're making this more complicated (hence slower) than it needs to be. But unless we see more of your structure we can't really say what to do about it.

If you're counting the number of active students, then you would just target that field in the Students table. You can create the left side of the relationship in any table, because it's just an unstored calculation field with the word "active." So the count calculation can be anywhere.

What do "active" students have to do with classes exactly?

[Fenton]

Somehow I think you're making this more complicated (hence slower) than it needs to be. But unless we see more of your structure we can't really say what to do about it.

If you're counting the number of active students, then you would just target that field in the Students table. You can create the left side of the relationship in any table, because it's just an unstored calculation field with the word "active." So the count calculation can be anywhere.

What do "active" students have to do with classes exactly?

[Fenton]

Somehow I think you're making this more complicated (hence slower) than it needs to be. But unless we see more of your structure we can't really say what to do about it.

If you're counting the number of active students, then you would just target that field in the Students table. You can create the left side of the relationship in any table, because it's just an unstored calculation field with the word "active." So the count calculation can be anywhere.

What do "active" students have to do with classes exactly?

[Himitsu]

there is actually a lot to do with it. They can be active, but out of class if they moved to another class. This field is used to print roll call sheets too. It will only show students who are active and in the class. Trust me, over the 3 years, this solution has so many occurances it would drive you mad. Those fields run case statements to read if they are active and if they are in the class or another class. So studnets statuses change sometimes if their home schedule changes and they need to come to a different class. I want to keep their records so we can see all the classes that student has been in and what year too. I just finished up the last of the find funtion putting in an if found count=0 ect... the first seting of that update field going through those records only took about 4 min. Now when it runs that script on close or open, it only needs to find the empty updated fields. Which should only be a couple at the most. I ran it about 10 times just now and it didn't show much difference in speed over my network. I won't forget about this calc just in case I think of something easier... but to sum it up, even the student table's active field is based on a calculation. So any direction I turned, I was hitting a calc field... thanks for your time, I would post the files but you would be pulling your hair out if you even had a view of the relationship areas. It was first designed in FMP 4 and modified on up through the versions... messy.

Thanks fenton. You have helped me countless times. Can I buy a CD or DVD from Amazon for you?

[Himitsu]

there is actually a lot to do with it. They can be active, but out of class if they moved to another class. This field is used to print roll call sheets too. It will only show students who are active and in the class. Trust me, over the 3 years, this solution has so many occurances it would drive you mad. Those fields run case statements to read if they are active and if they are in the class or another class. So studnets statuses change sometimes if their home schedule changes and they need to come to a different class. I want to keep their records so we can see all the classes that student has been in and what year too. I just finished up the last of the find funtion putting in an if found count=0 ect... the first seting of that update field going through those records only took about 4 min. Now when it runs that script on close or open, it only needs to find the empty updated fields. Which should only be a couple at the most. I ran it about 10 times just now and it didn't show much difference in speed over my network. I won't forget about this calc just in case I think of something easier... but to sum it up, even the student table's active field is based on a calculation. So any direction I turned, I was hitting a calc field... thanks for your time, I would post the files but you would be pulling your hair out if you even had a view of the relationship areas. It was first designed in FMP 4 and modified on up through the versions... messy.

Thanks fenton. You have helped me countless times. Can I buy a CD or DVD from Amazon for you?

[Himitsu]

there is actually a lot to do with it. They can be active, but out of class if they moved to another class. This field is used to print roll call sheets too. It will only show students who are active and in the class. Trust me, over the 3 years, this solution has so many occurances it would drive you mad. Those fields run case statements to read if they are active and if they are in the class or another class. So studnets statuses change sometimes if their home schedule changes and they need to come to a different class. I want to keep their records so we can see all the classes that student has been in and what year too. I just finished up the last of the find funtion putting in an if found count=0 ect... the first seting of that update field going through those records only took about 4 min. Now when it runs that script on close or open, it only needs to find the empty updated fields. Which should only be a couple at the most. I ran it about 10 times just now and it didn't show much difference in speed over my network. I won't forget about this calc just in case I think of something easier... but to sum it up, even the student table's active field is based on a calculation. So any direction I turned, I was hitting a calc field... thanks for your time, I would post the files but you would be pulling your hair out if you even had a view of the relationship areas. It was first designed in FMP 4 and modified on up through the versions... messy.

Thanks fenton. You have helped me countless times. Can I buy a CD or DVD from Amazon for you?