filter with 2 sharepoint lists

[jc-roux]

Hi

I have 2 sharepoint lists
an event list and a participant list
the event list has an unique id field
the participants list has an id field  corresponding to the event id (many participants for an event)
How to make an event form that displays the list of participants of this event?

thank you

[yashag2255]

Hi @jc-roux 

If I am getting this correctly, you have the id of event stored in the participants list. To retrieve all the participants in a specific event, you can use the expression on a gallery control as:

Filter(participants,EventIDCol = "EventID")

This will return all the participants of the selected Event. Here,EventIDCol is the name of column and EventID is the ID for specific event.

If this differs, please share a screenshot of the setup, so that we might be able to help you better.

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[jc-roux]

Thank you for that answer
But what I can not do is retrieve idevent from the events list to display the list of participants
I made a screen with a form from the event list and a data table with the list of participants
How to write "Filter (participants, EventIDCol =" IDevent from list event ")?

[yashag2255]

Hi @jc-roux 

It is not necessary that you use the ID column, whatever column you want to match can be passed in the Filter query as:

Filter (participants, ColumnToMatch="ValueToMatch")

Note: Since, you are working with the Edit forms, which using the controls please make sure that you are referring to correct control, something with a name like DataCardValue2,DataCardValue212 etc.

If you need specific help with the formula implementation, please share more details about the schema and the screen implementation of the app, so that we might be able to help you better.

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[jc-roux]

I can not do it 
Since I do not know how to put "ValueToMatch" which is my ID in Filter (participants, ColumnToMatch = "ValueToMatch") I tried "Filter (participant; ID_event = DataCardValue53)" where "DataCardValue53" is the ID of my form event but I have an "invalid type argument" error
I tried "Filter (participant; ID_event = Form7.DataCardValue53)" where form7 is my form event but I have the same message
I also tried "Filter (participant; ID_event = Form7.ID_event)" but it does not work either
I do not understand how to pass a value in "ValueToMatch"

[yashag2255]

Hi @jc-roux

If you are looking for a value to be passed from a control inside the form, then you may want to update the expression as:

If DataCardValue53 is a text input:

Filter (participant; ID_event = DataCardValue53.Text)

If DataCardValue53 is a dropdown:

Filter (participant; ID_event = DataCardValue53.Selected.Value)

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[jc-roux]

hey, thank you very much!
It works by adding ".text"
I now know how to filter based on a form field

But is it possible to filter based on a field from another sharpoint list?
Because there, I must include a field (ID) that I do not wish to see appear in my form
I tried "Filter (participant; ID_event = Event.ID_event.Text)" but it does not work (where "Event" is the name of my list and "ID_event" the name of my column in this list)
(Surely still a syntax problem)

[yashag2255]

Hi @jc-roux 

If you wish to get the value from another sharepoint list, then you may want to use the lookup formula as:

Filter (participant; ID_event = LookUp(Event, EventName = DataCardValue10.Text).ID_event)

Here, EventName is the name of column in Event List which stores the event name and DataCardValue10 is the form control where event name is stored.

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[jc-roux]

Hello
Obviously, I can not do it
I do not understand how to look for a value in another list without using "datacardvalue"
How to do it, like with SQL: SELECT column_name FROM table_name WHERE condition

In my case,  SELECT "name" FROM participant WHERE "id participant list" = "id event list"