Javascript to display Select count of table column

[techguy]

I have a table and I want a simple code to display in html the count of a column with conditions using the UserID and data in column. As an example. I want to create an html datapage or report page that only shows the count I want. Since I can not use Select count () statements within html datapage ( I need something that only shows the total of the calculation without a row etc in html.)

the statement below works properly, however even in a basic report page it returns the rows that meet the conditions with the count, when I only want the count..

Select count(Contact_Type) from Ninja_Contacts where User_ID='[@authfield:User_ID]' AND Contact_Type = "Other"

 

Any way to create some javascript code to display the count of a table (with conditions) within an html datapage or a regular report page and ONLY show the count, literally one number...

 

thanks

 

 

[Vitalikssssss]

Hi @techguy ,

The easiest way I see is to use a standalone Details page with a Calculated field in which you can write SQL statement. 

https://howto.caspio.com/datapages/reports/advanced-reporting/calculations-in-forms-and-reports/

You can insert a value from the Calculated field into HTML block with a parameter picker.

You may use technic with HTML block described in the following article to hide the Calculated field from a user:

https://howto.caspio.com/tech-tips-and-articles/common-customizations/how-to-hide-fields-in-datapages/

Hope this helps.

Regards,

vitalikssssss

[DefinitelyNot31337]

Hi techguy,

 

 

Vitalikssssss solution is great but I'd like to present a different approach.

 

1.) You can add an Other_Contacts_Count field to your Users table; then

2.) Define a Trigger on your Ninja_Contacts table that says:

OnInsert > 

Update Users 
  SET Other_Contacts_Count = COUNT ninja_contacts.id 
  WHERE ninja_contacts.Contact_Type = "Other" 
  INNER JOIN #inserted ON users.id = ninja_contacts.User_ID

Notes:

(You will need to setup the JOIN first before you can select the fields.)

 The Trigger actually fires before the record is inserted so you will need to revise your syntax a bit. Refer to the related image attached.

 

Ninja_Contacts = Child

Users = Parent

Ninja.Other_Contacts_Count = count_ready

Contact_Type = Status

 

==

 

After then, you can access the contacts_count as an Authentication Field.

I hope this makes sense and I hope I explained it well.

 

I also attached the DataPage where I implemented this workflow.

 

Cheers,

DN31337

 

Tables_2018-Dec-18_0841.zip