Jump to content
  • 0
DAG4Debt

Counting Number of Values in Datapage

Question

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.

Share this post


Link to post
Share on other sites

7 answers to this question

Recommended Posts

  • 1

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.

Share this post


Link to post
Share on other sites
  • 0

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.

Share this post


Link to post
Share on other sites
  • 0

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>

Share this post


Link to post
Share on other sites
  • 0

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>
 
 

Share this post


Link to post
Share on other sites
  • 0

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 by roattw

Share this post


Link to post
Share on other sites
  • 0

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?

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

×
×
  • Create New...