Hiding Duplicate Records with Same Field Value

[Scribblescraw]

Hello!

I have a search and report page in which users can search based on multiple non-mutually-exclusive attributes. After selecting the attributes they want to search for, the report then pulls up records that correspond to any of those attributes. The results page is very simple-- it uses the ID for the record to display, and then there is a link that passes to a more elaborate details page that essentially just shows all of the information for that record, sort of like a card. However, because each attribute has a separate record that links to the same ID, it can show multiple results with the same ID. 

I want to write a script or something that will make it so that if there are multiple records with the same field value (the ID), it will only display one (by combining all of the records into one line or by hiding the others). Is this possible? 

[douvega]

Hi,

You have two options at this stage.

1. If you have multiple records that share the same ID, then you need to reestructure your table as there must be a one to many relationship between them. Once you do that, you base your report in the unique records table. 
   Please review the following articles to make sure you understand table relationships and its importance:
   * Relationships
   * Relationship settings
   * Managing relationships
2. If you are not keen of getting your database properly configured as described in step one, then you can use Group option in the report datapage. This article will guide you through the steps.

Regards.

[Kurumi]

 Hi - If you want to hide duplicate records/rows in the Results Page, you can use this workaround:

1. Insert a Calculated Field for the formula to check if the record is a duplicate or has multiple records.

ROW_NUMBER() OVER (PARTITION BY [@FIELDNAME] ORDER BY [@FIELDNAME] DESC)

2. Insert an HTML Block to hide the rows that will be greater than 1. The solution comes from this post: 

<div id="hide-this-[@cbRecordIndex]"></div>

<script>
var isi = document.getElementById("hide-this-[@cbRecordIndex]");
if('[@calcfield:1]' > 1){
 isi.parentNode.parentNode.style.display = 'none';
}
</script>

3. Hide the column Calculated Field. The solution comes from this post: 

 

[telly]

Hi @Scribblescraw
 

To insert the header and footer on the DataPage you may refer to this link

https://howto.caspio.com/datapages/datapage-components/datapage-header-and-footer/#:~:text=Steps to add a header and footer&text=Select your DataPage and click,Click on Header or Footer. 

While adding the calculated field you may check this link

https://howto.caspio.com/datapages/reports/advanced-reporting/calculations-in-forms-and-reports/#:~:text=To add a Calculated field,fields in Results%2FDetails Pages. 

[Tubby]

If I understood that correctly, you want to do something like if there are 3 records with the same ID like this:

ID    attribute
1        water
1        grass
1        fire

You want it to show up as:
ID   attribute
1     water, grass, fire

If my assumption is correct, you might want to enable distinct records (https://howto.caspio.com/datapages/reports/creating-a-report-datapage/#:~:text=Show distinct records only.) and just use a Calculated Field rather than the actual fields themselves and use this nifty SQL solution from this forum post: 

That way it only shows the ID once because of Distinct and combines all the records into 1 single row by displaying the values separated by comma using the calculated field.