Removing duplicates when searching many-to-many joining tables

[Ed727]

I can't figure out how to search based on many-to-many relationships without getting duplicate results.

To create a many-to-many relationship in Caspio, you have to use a joining table which connects the two other tables. Each of the other tables connects to the joining table via a one-to-many relationship.

Here's an example of the issue below, based on an Events (i.e. conferences) database that uses Subject Tags.   I have an Events table (list of conferences) and a Subject table (list of Subject Tags).

Each Event will have multiple Subject Tags assigned to it. I create a joining table, and each entry in the joining table consists of an Event and one of its Subject Tags. Therefore each Event is listed multiple times, once for each Subject Tag.   An example table and relationship are below:

To allow a user to search for an Event based on Subject Tags, I then create a view based on the joining table. The view pulls in all the other information for the Event (location, date, size, etc.). The search form for the user then refers to that view. However, problems arise:

1) Since each Event is listed multiple times in the joining table & view (once for each Subject Tag it has), I can't list all the Events, or all the Events filtered by something other than a subject tag, because an Event will show up multiple times.

2) I can't allow the user to search for Events using multiple Subject Tags, because if the Event has those same multiple subject tags, it will show up multiple times in the results.

3) If I use any other many-to-many relationships for Events, I need to create a separate joining table, view and search page. Thus I can't offer the user a unified searching experience.

 

I've searched all over for an answer to this issue without any luck.

 

 

 

[Hastur]

@Ed727

Hi!
You may use the Grouping feature to group the repeating values and show them just once.
Just check this article - https://howto.caspio.com/datapages/reports/advanced-reporting/data-grouping/

Hope this helps!!

[Ed727]

Thank you for taking a look.  I've tried the grouping and pivot table features -- they provide a fix in terms of organizing all the results by distinct results, however they still list each result as part of the grouping.  And all of the data I also need to show (event location, date, etc.) show up in the sublist of the duplicates, not on the main line. This approach would work OK when each result is meaningful in and of itself (for example invoices), but in a use case of subject tags it's not OK. 

I've asked around a lot and I think that unfortunately this is not an area Caspio can handle without some extensive custom coding which can be tricky even for outside developers.

From doing some research, it appears that other platforms handle this by allowing users to directly create many-to-many relationships between tables, and the platform deals with the linking table in the background.  Perhaps this will be something Caspio will address down the road, either directly or through expanding the functionality of the List datatype.

 

 

 

[Vitalikssssss]

Hi @Ed727,

You can try using a feature from R30 content called "Show distinct records only" which is available in List/Gallery/Tabular Reports.

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