How can I count the number of records with @DbColumn?

Question

I am trying to get a column value for some computed text fields on a page using @DbColumn.

Say I have a view called "By Status", like so:

+-------+--------+-----------+----------+------------+
| Count | Status |  Created  |  Author  |  Comments  |
+-------+--------+-----------+----------+------------+
|  134  |  Open  |           |          |            |
+-------+--------+-----------+----------+------------+
|       |        | Records with "Status" = "Open"... |
+-------+--------+-----------------------------------+
|  101  |  Hold  |           |          |            |
+-------+--------+-----------+----------+------------+
|       |        | Records with "Status" = "Hold"... |
+-------+--------+-----------------------------------+
...

Pretty self explanatory - it displays all of the records for the database by status and has a count (number of records) for each status.

On my page I have a computed text field for each of the statuses; Open, Hold, etc. I am trying to get the count by status. So for example:

@Text( @DbColumn(  "Notes" : "ReCache" ; "" : "" ; "By Status"; 1 ) );

This returns 134, which is the count for the status "Open". I know this is correct for my above statement, however, it's not what I'm going for.

How can I get the value of "Count" for each "Status"? Using @DbLookup perhaps..?

Answer 1

There are two different ways of doing, what you want to do. First would be to do a @DbLookup for every value of your status. That would look like this (example for status Open):

@DbLookup( "":"ReCache"; ""; "By Status"; "Open"; 1)

Depending on the number of status that might become slow.

The other way would be to do a dbcolumn in a computed field (that would mean to take a form for that purpose instead of a page, but that is not really an issue):

Field named "allTotals", number, computed for display, allow multiple values, formula:

@DbColumn( "":"ReCache"; ""; "By Status"; 1)

And then in each value for the total for a special status you would need its position in the view. First Status:

@Subset( @Subset( allTotals; 1 ) ; -1 )

Second status:

@Subset( @Subset( allTotals; 2 ) ; -1 )

...

And of course, there might be other, more complex ways of handling this...