Running Count of Certain Records?

[rsglewin]

Hi,

I am trying to figure out how to keep a running count of certain records. I would like to have a global field that keeps a running count of all the records of "Type A" and another global field that keeps a running count of all the records of "Type B." So far, I've tried a portal and another method that counts the Number of Like States. Both methods provide a running count of all the records of "Type A" and "Type B." However, the result changes depending on which type of record is selected. What I'm hoping to find is a way to keep a running count of "Type A" in one global field and a separate running count of "Type B" in a second global field that are INDEPENDENT of which type of record is currently selected. Further, I'm hoping to do this without having to use a sub-summary, as I need this count to be displayed regardless of how the records are sorted or how the layout is displayed.

Any help would be greatly appreciated.

Thanks![color:blue][color:blue]

[-Queue-]

Create a global text field with a value of "Type A" and relate this to the type field. Then create an unstored calculation (not a global) of Count(relationship::serial).

Then repeat the process for 'Type B'.

[rsglewin]

I think I follow you up until (relationship::serial) ... What exactly is "serial"? Then would the calculation field be where the count is returned? Also, will this work if "Type A" and "Type B" are text and are taken from the same field?

[-Queue-]

Serial is the unique id field in your table. You can use any field which must always have a value; I just use the serial because it is basically foolproof.

Yes, Count(relationship::serialField) will return the number of related records for the given relationship.

The global text fields will indeed both be related to the same Type field, using two different relationships.

[JanetMermaid]

I wanted to be able to keep track of how many total ponds we've built. I found an easy solution.

I defined a Summary field called Total Ponds for my database. I selected "Total of" and another field called Number of Ponds in the Options window for the Summary field. Number of Ponds is a numeric field we complete for each client (we've built more than one pond for some clients).

The Summary field displays the total of Total Ponds only for displayed records, so if you want to know the total for ALL records, be sure you have them displayed.

Doing it this way also lets me easily check a summary by sub-type. For example, if I want to know how many ponds we've built out of town, then I need only do a find for records that aren't in our home town. The Total Ponds field shows only that total.

Hope this helps.

[-Queue-]

That is one option, but not for the OP, since it was stated "I need this count to be displayed regardless of how the records are sorted or how the layout is displayed", which means that summary fields are not a viable option here.

[rsglewin]

Queue - Thanks for your help. I've tried your suggestions and come close, but not all the way. Instead of Count(relationship::Serial Number) returning a total of "Type A", I'm getting a total of all the records with the Type field (which is all the records). Perhaps it's because the same Type field stores both "Type A" and "Type B"? The logic of relating the Type field to a global field with a value of "Type A" (which is text, not numeric) seems like it should work, but I'm not sure why the resulting count is the total of the records instead of the total of the records that relate to the global field with a value of "Type A". Also, I need the resulting count to be displayed globally, as it is in the Header in one layout and is not in the Body of any of the layouts. Will switching the count field to global storage solve this part?

Thanks again!

[-Queue-]

The global is related to the Type field; not the other way around, since no field can be validly related to a global. The Count field should reference the relationship FROM the global TO the Type field, as in the attachment.

An unstored calculation acts as a global when the value is the same for all records, but will update when the related values change, whereas a global may not.

TypeCount.zip

[rsglewin]

Perfect. Thank you! Is there a way to make it return "0" instead of leaving the field blank if there are no records of "Type A" or "Type B"?

Also, is there a way to create a sum field using these relationships that returns a total of all of the Total Duration (time) fields of "Type A"?

Thanks again.

Edited October 20, 2005 by Guest

[-Queue-]

1. Deselect 'do not evaluate if all referenced fields are empty' in the 'Total' fields' definition.

2. Try Sum(relationship::TotalDuration).

[rsglewin]

1. Ahh. I have a lot to learn.

2. This returns a number rather than a time and it adds like numbers instead of time (base of 100 rather than 60). Is there any function that acts like a "Total of" summary field?

Thanks again.

Edited October 20, 2005 by Guest

[-Queue-]

Set the result as time and it should be displayed as time.

[rsglewin]

Yes. So obvious now. Thanks for your help!