Grouping Records Help Needed

[scott]

I’ve read the articles here about grouping and watched the DMXZone video on the subject, but I am stuck and need some assistance. I am not sure if it is a database issue or if I am missing a step on how to group properly.

Here is my database setup:

• Table A - Events
• Table B - Contacts
• Table C - Links Table A and B together

An Event (Table A) may have more than one Contact (Table B), which is why I have the third table to link them together.

What I am trying to accomplish:
I am trying to list the events on a page and have the corresponding contacts listed below each event.

• Event 1
  – Contact 1
  – Contact 2
• Event 2
  – Contact 1

Currently, I can list the events on a page and have each contact listed as well, but it results in duplicate event listings. For example:

• Event 1 - Contact 1
• Event 1 - Contact 2
• Event 2 - Contact 1

When I watch the video on DMX Zone, https://www.dmxzone.com/go/32971/using-the-group-by-filter/ I am able to group the Events so that they only appear once, but I am not able to list the Contacts under the Events.

Any assistance would greatly be appreciated.

[ben]

Can you show the contents of the third table (table C)?

[scott]

[ben]

That means that, if an event has two or three contacts, there will be two or three entries to the table?

[scott]

Yes, that is true. As I mentioned, it might be a database issue, but I am not sure how to better organize the entries.

[ben]

The database structure is fine. In the video, the table that is used is called the Products table. This is the equivalent to your Table_C.

To make sure that this works, use Table_C when following the video that you referred to.

Once you have that going, we can insert the names instead of the ID’s.

[scott]

Ok, if I understand correctly, just use a simple query to retrieve the Table C results and then after the results are pulled, group them as shown in the video.

I have that setup on the test page. So each Table C.Event_ID_FK is listed once.

Not sure how to do the last part of inserting the names instead of IDs.

[ben]

In the query builder, add the other two tables and add the names in the query column.

As an example, in the following I have added three more tables to get the name of the model, the series and the make.

[scott]

@ben Thank you so much for your help.

Let us back up a step because what I am seeing when I add the Table C.Contact_ID_FK under the Table C.Event_ID_FK brings be back to where I was, only showing one Contact entry per Event.

Here is the test page, (removed test link as no longer needed). The Event IDs are title fields and the Contact IDs are paragraph fields. The fourth one down, Event ID 24 should have two entries, Contact ID 24 and Contact ID 23.

That is where I am stuck at, the repeating Contact ID within the Event ID area.

[scott]

Ok, I got it to work. The solution was that in the first repeat region, use the Unique key of $key.

For the second repeat region, you would use the expression $value.

[UKRiggers]

Sounds good @scott, can you show some screen shots of the results to help us picture what you have been doing?

[scott]

@UKRiggers Sure! The initial concept was very basic, just using id numbers. The final result has event names and contact names associated with it.

Here is a screenshot of the initial concept.

So, the Event ID is warpped in a title tag and the corresponding Contact ID is in a paragraph.