Forum Discussion

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

Relationships

reports and charts

MarkShnier__You

Qrew Legend

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

PaulEaston

Qrew Assistant Captain

3 years ago

Thank you. 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. 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
------------------------------

MarkShnier__You
Qrew Legend
3 years ago
Paul,
We need to get our terminology correct :)

When you are picking record form a parent table in a drop down, 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. That is set in the Advance Settings for that table. 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. For exampl maybe you are selecting an employee and you want the drop down list to only show Active Employees.

The lookup fields come in off the relationship once the parent record is selected.

------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------
- PaulEaston
  Qrew Assistant Captain
  3 years ago
  Thanks for your help Mark.
  
  ------------------------------
  Paul Easton
  ------------------------------
PaulEaston
Qrew Assistant Captain
3 years ago
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
  Qrew Legend
  3 years ago
  .... 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
  ------------------------------