Yes, you can do this. Create a couple of calculation fields:
ActiveFlag = Case ( Status = "ACTIVE" ; 1 ; GetAsNumber ( "" ))
InactiveFlag = Case ( Status = "INACTIVE" ; 1 ; GetAsNumber ( "" ))
(Note that I'm assuming that your ACTIVE and INACTIVE are contained within the same field.)
Then, you can use a pair of summary fields to count up the total number of records that have ActiveFlag of 1 to give you the number of ACTIVE records, and the total number of InactiveFlag records of 1 will give you the number of INACTIVE records.
Good tip Mike for pulling Status text data into numericial form if the entry is in the same field. A lot depends on how Active and Inactive are defined in the table.
Instead of summary fields to count or total the number of Active and Inactive records, I would use a calc and the Sum function. Summary fields would be affected by the found count, and could show less than the total of each category, but perhaps this is what inov8pro needs to do.
remember that we have the function "Get(TotalRecordCount)"
Although, I don't know if that helps you.
On 22 Feb 2012, at 2:56 AM, inov8pro asked in whole or in part:
I have a membership list that has ACTIVE and INACTIVE members. I want two calculation fields in this table - one field for total ACTIVE count and one field for total INACTIVE count. Apparrantly, Get (FoundCount) only works with the current Found Set which can change. Is it possible to have both or perhaps there is another calculation that I can use.
Yes, good point, Michele. Indeed, the difference would be whether we want to total the number of active / inactive records in the found count or across the entire table.
Create in the Table a global calculation field with value 1 (Table::ONE)
In manage database>relationships duplicate the Table to create a join relationship relating the Table::ONE field from the first Table equal to the Table 2::Active field
In manage database>relationships duplicate the table to create a join relationship relating the Table::ONE field from the first Table not equal to the Table 3::Active field
Be sure to relate Table::ONE to the other field and not vice versa
Create two calculation fields:
Table::Active: Count(Table 2::ACTIVE)
Table::Inactive: Count(Table 3::ACTIVE)
This will give you real time calculation
Get (FoundCount) only works with the current Found Set which can change. Is it possible to have both or perhaps there is another calculation that I can use.
I am confused by your question: do you want the two numbers to reflect the current found set or to ignore it?
If you want two pieces of info returned regardless of the actual current Found Count, you could build relationships based on a couple of constants (K fields) and count across those relationships for the Count of related records.
That way you will see a count of Active and a count of Inactive (or whatever those values are) at anytime without regard for the current found count or how the found set was obtained.
Thanks Gianandrea. I'll have to try your solution when I'm more advanced. As a "newbie" I am still learning about Table relationships and use of Table instances.
Mike and Michele O,
Mike's solution worked perfectly. However, I am trying to incorporate Michele Olsen's SUM calculation solution instead of the Summary Field. I prefer Calcs vs Summaries. I'm not sure if I unterstand the SUM Calc. "Newbie"
Having used your solution for Active as follows:
- I created a field called ACTIVE_FLAG and introduced the calculation Case ( STATUS = "A" ; 1 ; GetAsNumber ( "" )) This worked.
- Now I want to total that using the SUM calc so I created a Field called TOTAL_ACTIVE and used the SUM Calc. Sum (ACTIVE_FLAG) Nothing happened. What is the proper proper calc.
Thanks for all great replies from everyone. This has been my first experience using the forum.
Thanks for the help Beverly. I had come across "Get(TotalRecordCount)" and I use that in my TOTAL_MEMBERS field.
if you need a more detailed explanation, just let me know.
it is not a complex solution and solves your problem in any situation (it is independant on how many records are displyed, when you change status of a person it chenge both totals automatically)
Preferably I would want these fields to ignore any Found Set from a search but it appears that the solution above may not do this at least using the Summary solution part. Perhaps the Sum calc solution would provide this ability. It may not be possible to have a calculated field outside the Found Set if using the same table. Anyway I would prefer that the these fields: TOTAL_ACTIVE and TOTAL_INACTIVE ramain constant. The data in the table itself will not change and will be read only. I'll have to start a new discussion on comparing tables but in essence I want to do the following:
- Ultimately, I want to compare two tables: Table 01 and Table 02. Both tables are read only and no changes are to be made to the original data. Essentially I'm building a comparitive history and I want to define the changes between the two tables.
- Eventually, Table 02 witll be compared to a New Table or Table 03 and so on.
I'll start a new discussion for this topic.
Yes, this is what I'm trying to do. Though Mike and Michele have a solution to the immediate question it would seem your solution is my ultimate goal. Are "(K fields)" standard terminology for constants in the database world? Rember, I'm a newbie.
If you want results that ignore any found set, you must count over a relationship. There are many ways to do this, I'll mention one:
Define an unstored calculation field cActive (result is Text) =
and a relationship of the Members table with another occurrence of itself as:
Members::cActive = Members 2::Status
Once you have that in place, the number of active members is given by =
Count ( Members 2::Status )
and the number of inactive members is =
Get ( TotalRecordCount ) - Count ( Members 2::Status )