Forum Discussion

PaulEaston's avatar
PaulEaston
Qrew Assistant Captain
3 years ago

Need guidance on setting up lookups and reports with a many-to-many setup

I have the following many-to-many scenario: an event can have multiple participants and a participant can have many events.

I set up three tables:
1. Participants
2. Events
3. Participant-Event Join

1 and 2 each have a one to many relationship with 3.

I have another tables for Requests.  I need to be able to select multiple participants on the request form and have certain fields from the participants table displayed in an embedded report on the Request form.

I also want to display all events a participant has participated in on the participant form and all participants who participated in an event on the event form.

How do I go about doing this?  Do I need to add a one to many relation ship between the Requests table and the Join table?  E.g. have the join table join three other tables?

------------------------------
Paul EASTON
------------------------------
Relationships
Tables and fields
  • EvanMartinez's avatar
    EvanMartinez
    Quickbase Staff
    3 years ago
    Hi Paul,

    Since you already have the records in your Join table that show if a participant has gone to an event or an event was attended by a participant you should just be able to make a report link field on both of those tables to match things up and show a report.

    For example in your participant table you would create a report link field and then in the field settings you would go in and set it to match the record ID in the participants table to the related participant field in your Participant-Event table. Then you can set up the report being used for the report link field to show the fields you would like and it should show you all your join records related to that participant. Then you could do the same for Events matching up the record id in the events table and the related events field in your joins table. If you use a custom key field in either of those tables you may need to use those key fields instead of the default record ID.

    ------------------------------
    Evan Martinez
    ------------------------------