count distinct in datapage based on a view

[Allison]

I need help figuring out how to use SELECT DISTINCT (or something similar) in a calculated field in a datapage based on a view. I am mostly unfamiliar with SQL and can't figure out how to implement the syntax.

Datapage is based on a 3-table view "LocusArtifactFragment" where Locus is parent table, Artifact is child1 (one-to-many relationship with parent Locus), and Fragment is Child2 (one-to-many relationship with parent Artifact). I want to count distinct related records from Parent (Locus) in both related tables in the view (Artifact = Child1 AND Fragment = Child2). The view includes columns for LocusID, ArtifactID, and FragmentID (pk in each table) plus the foreign keys that make the relationships.

I can easily count related records of fragments by counting FragmentID, but I need to count Distinct Artifact ID (because if I just count Artifact ID, I get equal numbers of fragmentID and artifactID, since artifactID appears for every related record in Fragment). This syntax from the howto site seems like it is more or less what I need, but I can't get it functional when I sub in my field and view names: "SELECT Count(Country) FROM Customers WHERE Country=’Canada’"

Any thoughts on how to implement this would be very welcome. I need to verify students' data entry is correct and complete before moving forward with other parts of the project. Many thanks.

 

[TellMeWhy]

If it's a Reports DataPage, you can try the following

SELECT COUNT(ColumnName) FROM _v_LocusArtifactFragment WHERE ViewColumnName=target.[@field]

If it's a View, you will have to put _v_ at the beginning of the View Name when using SQL Statement.

the target just means it will get the value of that field of the specific row that the Calculated Field is in.

If you have multiple WHERE conditions, just use AND (i.e. ViewColumnName=target.[@field] AND ViewColumnName2=value AND... etc)

[Allison]

Thanks for the help! This didn't quite get what I wanted from the report, but now I understand how to refer to views so that's definitely a step in the right direction. I'll keep trying.

[Kurumi]

Hi everyone! Just an update: Caspio has finally released a feature to show DISTINCT records on Reports (Tabular/List/Gallery).

It is available in R30 version: https://howto.caspio.com/release-notes/caspio-30-0/