Select Distinct Records

[bbeshlian]

Hello,

I have created a view which is returning duplicates of each record. Is there a way to prevent this from happening like the SELECT DISTINCT does in SQL in either the view or when I use this view as a source for a datapage?

Thanks.

[kpcollier]

What kind of page are you using? I know there is a "Distinct Options" checkbox in the Configure Search Fields Wizard.

*Nevermind. I believe that checkbox is to limit duplicates in a dropdown used in search. I'm not sure if it affects results.

[bbeshlian]

Maybe I did not make myself clear enough. The view that I built and want to use as the datasource for my datapage has duplicate records. I do not wish to have these duplicate records either when I run the view or displayed in the datapage. Is there a function similar to the SELECT DISTINCT in SQL which will either stop the duplicate records in the view or when I use the view as the datasource for my datapage.

Thanks.

[GlennFL]

The short answer is no, SELECT DISTINCT is not available for a view (or datapage), or GROUP BY aggregate functions.  If you search deeper here you'll find some creative ways to get the job done.  Somewhat clunky but I have used grouping on the datapage along with some css hacking to hide the child rows.  Also clunky but effective could be adding a "placeholder" table (or view) that is distinct to base your report datapage on and then use a keyID field and calculated fields for the report row fields where you can define the SQL to create a distinct result.  Feel free to add some votes to the ideabox submission for this.

Can you provide an example of the data structure?

[BaySunshine]

Hi @bbeshlian,

The solution for this issue is to use a table which has ID field marked as unique field in the table. Use this field as the data source of your report DataPage.  To show the rest of the fields in the report, use calculated fields with SQL queries to query the view based on the same ID value. Be sure to include a Where condition which will help only pull a certain record for the respective ID value.

When using a view in the calculated field, add '_v_' to the name of the table like shown below:

select field_name1 from _v_viewname where field_name2='..........'

I hope that helps.

Regards,

[Vitalikssssss]

@GlennFL, @bbeshlian good news!

Caspio has introduced DISTINCT criteria filtering in R30 version for Tabular/List/Gallery reports.

https://howto.caspio.com/release-notes/caspio-30-0/