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

Question

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
------------------------------

markshnier__you · Answer

Something is confusing in your question.

for this question here
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.

You already have the set up for this in place and when you made those relationships to the drawing table the relationships would have created a report link field so you can embed the Participant-Event Join record on both the Participants Form (showing columns for the Event Name) and also embed the Participant-Event Join records on the Events Form (showing columns for the Participant Name).

Then separately you ask this

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.

You would just to the same thing you did for Events by setting up a third table called Request Participants Join.

(unless I'm not understanding and in fact you want to select not multiple Participants but multiple Participant Event Joins for a Request. In which case you still need a middle join table between Participant Event Joins and Requests.)

------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------

markshnier__you · Answer

Paul,&nbsp;We need to get our terminology correct :)When you are picking record form a parent table in a drop down,&nbsp; in the absence of taking specific action to control that drop down list, the system will use the default record picker fields for that table.&nbsp; That is set in the Advance Settings for that table.&nbsp; The record picker fields help human users pick records, so it is up to you to make them as useful as possible.You can also create a report to be used for the drop down list i you need to add more columns or control the sort of the filter.&nbsp; For exampl maybe you are selecting an employee and you want the drop down list to only show Active Employees.&nbsp;The lookup fields come in off the relationship once the parent record is selected.------------------------------Mark Shnier (YQC)mark.shnier@gmail.com------------------------------

pauleaston · Answer

Thank you.&nbsp; I got it working. What I'm still struggling with is what determines which fields are shown in the lookup? If I click "add participant" in the event form, it will pull up a selector that show three fields. It seems to the first three fields in the default report for that table. Changing the default form to put the three fields as the first three gives me control over what is displayed in the lookup, but some fields seem to be ignored. For example, I can't use the Record ID# (key) field, it is just ignored.&nbsp; Also, I have a formula text field that concatenates a several fields, and it is also ignored in the lookup field. Is there documentation on which kinds of fields can be used in a lookup field?------------------------------Paul Easton------------------------------

pauleaston · Answer

Not sure what I did wrong the first time, but I just deleted the relationships and started from scratch and it worked this time. I also figured out where the settings to select fields for the embedded reports are. For the join table Report Link field that is automatically created, I needed to click on the field in form design elements tab and click on change settings link. Just posting in case it helps anyone else.------------------------------Paul Easton------------------------------

markshnier__you · Answer

.... right, put the report link on the form, set for to show the embedded report and and select or create a report to use.------------------------------Mark Shnier (YQC)mark.shnier@gmail.com------------------------------

Forum Discussion

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

6 Replies