Count of records when a field meets certain conditions

[AlanP]

Good afternoon,

Looking to find out how to create a calculation field that:

1) Will show the total records that meet a certain criteria based on a value field (i.e. if 15 of 100 records say 'Active', the calculation will show '15'; if 50 of 100 records show 'Closed', it will show '50', etc.

2) Also, is there a way to do this in a related table. For instance I want to create a 'splash' or start page in the database that will show these counts based on the main table. Too much?

Thanks!!!

[LaRetta]

Since you only want to show it at the splash and don't need it in Browse mode while working in regular layouts, I would use a columnar report as the splash layout itself.

Here's a rough concept. The tables don't even need to be related because you can use the related table for your splash ( depending upon what else might be on it ). Regardless, a report will be simplest without requiring additional calculations or relationships. The summary field in the related table will come in handy for counting many things ( although it will have a few limits ).

The splash in this demo is kinda ugly - I just woke up. :)

StartupCounts.zip

[AlanP]

Thank you! Wow, that's pretty good for just waking up

Is there a way to show that data without the splash screen using a report layout? Right now our splash screen has other components on it so a report style display won't work well.

[AlanP]

For example, I've attached a screenshot to show what we are looking for since we have two other modules on the same page.

Is there a way to just have calculation fields display this number, or is using a report style layout the only way?

Edited June 2, 2008 by Guest

[LaRetta]

No, report layout isn't the only option. You can have these figures appear on your Splash layout OR any layout by using filtered relationships and a single calculation in your records table. It would help to know your tables, relationships etc but here's a file showing how it works.

Ignore the splash which starts up. I put the 'browse mode totals' on your main layout. But you can put them on your splash layout just by using your splash layout in place of your main. I left the startup out of laziness but, since there was no prior relationship, I had little to change and the graph will show you exactly what you need. Also note that each field with the counts belongs to a different relationship.

I hope this helps you; if not, please ask.

UPDATE: Oh. I see I didn't match your field labels exactly but I've no doubt you can handle that difference just fine. I also left the prior summary field in Related which is identical. That's what happens when I get in a hurry and just create the 'new' functionality without caring what the old showed. Use either one you wish.

LaRetta

StartupCountsREV.zip

Edited June 3, 2008 by Guest

[AlanP]

Brilliant, thank you :)