Only showing result once

[NikkiC]

Hi there,

I have a tabular report datapage, that I am using to display calculations from my table.

I want to show week number, and what has happened during that week.  So far I have 2 columns other than week number, number of cards updated, and total number of updates.  The calculations I am using are working fine, but it's showing each number as a separate row.  So if the calculation for week 6 is 7 updates, it shows 7 rows.  See attached screenshot.  Below is my code.  Anyone know how I only show each line once (so there's only 1 row for each week number)

Number of cards updated
SELECT COUNT (DISTINCT [@field:Card_ID]) FROM Card_comments_and_updates WHERE Week=target.[@field:Week]

Total number of updates
SELECT COUNT([@field:Card_ID]) FROM Card_comments_and_updates WHERE Week=target.[@field:Week]

 

[MayMusic]

Since you are doing the calculation using SELECT statement, you can create a lookup table with one field: "Week". Then use this lookup table as the source of your report DataPage.

[NikkiC]

This works perfectly, thank you so much @MayMusic!  This issue has been with Caspio support for a week and they haven't been able to help 

[MayMusic]

You are very welcome Glad I could assist

[cbaker10]

I have a similar issue, could you explain the solution a little more in depth?  Here is my example:  I have a Teacher, Enrollment, and course tables.  The enrollments tie to teacher with teacher ids and courses with course ids.  The teacher may have multiple enrollment for one course.  I want to create a view to be used in a datapage that would list once the Teacher and Course.  How would I go about this?

Here is what it looks like now and want it to only have 1 record for Bob sanders, but because he has 2 enrollments in his class, it lists twice.  Any Ideas?

Courses_Course                                                      Personnel_Full_Name

English Language Arts, Grade 7                     Bob Sanders

English Language Arts, Grade 7                     Bob Sanders

English, Language and Composition, AP   Christopher Baker

 

[MayMusic]

As I understand you have these tables:

Teacher: Teacher_ID

Course: Course_ID

Enrollment: Teacher_ID, Course_ID

to not get repeated info, what you can have is to create a report based on your Course table. Create a view to join Enrollment and Course so you can get the name of the teacher, we call it: 

CourseEnrollment

And then use this formula: 

 

SELECT TOP 1 Teacher_Full_Name FROM _v_CourseEnrollment WHERE Teacher_ID = target.[@field:CourseEnrollment_Teacher_ID] ORDER BY Teacher_Full_Name DESC

 

But this will only give you one teacher per teacher, so if you have more teachers for the same course it will only show you the top 1

[autonumber]

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

It is available in R30 version you may check this link: https://howto.caspio.com/release-notes/caspio-30-0/