Show distinct records

[cbaker10]

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

 

Thanks,

Chris

[Vitalikssssss]

Hi @cbaker10,

You would need to use separate tables:

Table 1 for courses with assigned teachers

Table 2 for enrollments 

These tables should have a one-to-many relationship.

For your report, you should use Table 1 and Calculated field with SQL expression if you would like to display a count of enrollments from Table 2.

Alternatively, you can use your existing View and Collapsible Group of records by teacher name.

Here is the link to an article about :

https://howto.caspio.com/datapages/reports/advanced-reporting/data-grouping/

Hope this helps.

Regards,

vitalikssssss

 

 

[cbaker10]

Vitalik,

Thanks for the response!  I already have three separate tables, Teacher, Enrollment, and course tables.  Still get multiple records.

Chris

[cbaker10]

I've been try to find the SQL statment you suggested.  Can you supply an example?

 

Regards,

Chris

[cbaker10]

I'm getting closer.  Can I use a select statment on a View instead of a table?

 

Chris

[Franchiser]

11 hours ago, cbaker10 said:

I'm getting closer.  Can I use a select statment on a View instead of a table?

 

Chris

Hi Chris,

I'm afraid you can't do Select Statement in Views itself however you can reference VIEW in an SQL statement just by placing   _v_     before the view name. For instance:

SELECT DISTINCT Firstname FROM _v_NameOfYourView where ID=[field:ID]

 

-Franchiser-

[cbaker10]

So I am using SELECT DISTINCT([@field:Courses_Course]) FROM  _v_Teacher_Courses WHERE Personnel_ID=target.[@field:Personnel_ID] which works, but it lists the name of the first course for all courses instead of each individual course.  The second column is the calculated column and the last column is the actual course.  I am so close....any ideas?

 

Bob Sanders	English Language Arts, Grade 7	2017-2018	3	English Language Arts, Grade 7
	Bob Sanders	English Language Arts, Grade 7	2017-2018	3	English Language Arts, Grade 7
	Bob Sanders	English Language Arts, Grade 7	2017-2018	3	English Language Arts, Grade 8
	Christopher Baker	English, Language and Composition, AP	2017-2018	2	English, Language and Composition, AP
	Christopher Baker	English, Language and Composition, AP	2017-2018	2	English, Literature and Composition, AP

 

 

[ColBasicuser]

CBaker10 - Did yo ever solve this conundrum? If so how 

[Vitalikssssss]

Hi All,

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/