Counting Number of Values in Datapage

[DAG4Debt]

I need a small table that essentially summarizes detail from a View. All I need to do is count the number of fields in a table with particular values and display them in a table summary. For example, X number of records with Y value in one field, Z number of records with A value from a different field in the same table.

It seems that this should be such an easy task, but its a tremendous feat and I can't figure it out. I've tried Totals and Aggregates, but I don't need all the detail, and even when modifying the style to eliminate some rows, it still doesn't do what I need it to do. Similarly, I've tried working with java to pull and calculate the data in an HTML block, but can't et this to work either. Here's what I need:

Value A Value B Value C

Field X Count A Count B Count C

Field Y Count A Count B Count C

Field Z Count A Count B Count C

There has to be an easier way to manipulate the data in Caspio. Please let me know. Thanks.

[gsgriffin]

Just did something similar where this might help someone else.

 

Create a list report that has the items you want, but hide them all.

 

In the header, add "<script type="text/javascript">var count=0;</script>" to start a counter.  If you want to add more values, create more values like "var countX=0;" and such

 

Create an HTML block in the Configure Results Page Fields with a script like

"<script type="text/javascript"> count++;</script>"

if you want to just count how many come up, in the footer, put

 

<script type="text/javascript">

 

document.write(count);

 

</script>

 

You can display any combination of any values by adding to the HTML block something like

countX=countX+[@valuex];

 

Neat trick I just figured out.  Now I'm able to authenticate a URL for visitors based on matching enough variable to a table results.  If they don't match, the report show "0" and the script keeps the important stuff hidden.  If I get something, I display the info.  Validation on the fly without a form.

[MayMusic]

Have you tried to group by the category and have aggregation on first sub group to count the number of records? http://howto.caspio.com/datapages/reports/advanced-reporting/data-grouping/

[Bangalore]

If you are looking to create a table with information from a view, it is not possible to do with standard features. But it may be possible with a backend trigger that will collect information into the table. If you are interested in going ahead with this option, please forward your requirements and our Solution Advisors can provide you with a quote for the custom backend programming.

If you want to show the information on the Results page, you may use Aggregation feature. Please refer to this article: http://howto.caspio.com/datapages/reports/advanced-reporting/totals-and-aggregations/

You could use SQL function like this for the aggregation:

count(case when [@field:@current]<>0 then 1 else NULL end)
Where @current is a field in your table that you would like to use aggregation on.

[Xiang]

Wow!

 

Thank you for the solution! It's amazing!

And you can add any comparison, if you want to calculate records by any condition.

So simple, so cool!

 

I can only add, that I hide all data with the following code in the Header:

<style>
.cbResultSetData {
display:none;
}
</style>

[roattw]

Old post, but can you clarify?  When I create a list report of the values I dont see an option to hide.

If I have a basic form with basic dropdown and I just want to tally number of times each option was chosen, would this work?

Example dropdown field:

Favorite Alcoholic Beverage:  Wine, Beer, Mixed drinks

Over time just want to know how many chose Wine, how many chose Beer, etc.

 

Would it code like this:

HEADER:     <script type="text/javascript">var countWine=0;var countBeer=0;var countMixed=0;</script>

FOOTER:

<script type="text/javascript">

document.write(count);

</script>

 

 

[roattw]

No luck.  As usual, Caspio hates the syntax:

 

count(case when [@field:CME_Category]<>0 then 1 else NULL end)  and

 

count(case when CME_Category <>0 then 1 else NULL end)

Edited October 30, 2017 by roattw

[edujobs2k18]

is it possible to count in table? Like in a table which contain students info. I want a table which will contain count of how many are from grade 1? how many have books? How many have Bag? like wise... or is it possible with report?

[Kronos]

Hi @edujobs2k18,

I think this forum thread is similar to your needs