Searching in Gallery with values coming from another list.

(0) Share

Report

Posted on by Unknown123

115

Hello Everyone.

I have a Main list which I am showing in a Gallery called Overview.

I have 2 more lists called Personnel and Performance.

Both are connected to Overview List with a column called OID.

Its not a lookup its just a number column. So the ID coming from Overview, is the OID for the other two lists.

Now, Performance will have a unique entry for Overview that is If there is one item/row in overview list with ID = 1, there will be only 1 item/row with OID = 1. The Performance has around 12 fields. And all are single line of text. Some fields are Time, Budget, Quality.

Now comes Personnel List. It can have multiple values related to Overview List. So, if ID in overview list is 1. Then there can be five rows with OID = 1. The fields in this table are Role, Name and Email.

Now when I search in the gallery with a record in either Performance or Personnel list it should filter out in the Main Gallery.

I have no clue how to do this. My current Items property looks like this:

Search(
If(
IsBlank(ComboBox2_1.Selected) && IsBlank(Dropdown1_13.Selected.Value) && IsBlank(DatePicker1.SelectedDate) && IsBlank(DatePicker1_1.SelectedDate),
Filter(Overview, User().Email = 'Created By'.Email || ID in Filter(Personnel,Email = User().Email).OID || Email=User().Email),
If(
DatePicker1.SelectedDate <= DatePicker1_1.SelectedDate,
Filter(
Overview,
(IsBlank(ComboBox2_1.SelectedItems) || ComboBox2_1.Selected in Sector) &&
(IsBlank(Dropdown1_13.Selected.Value) || Status.Value = Dropdown1_13.Selected.Value) &&
(IsBlank(DatePicker1.SelectedDate) || StartDate >= DatePicker1.SelectedDate) &&
(IsBlank(DatePicker1_1.SelectedDate) || EndDate <= DatePicker1_1.SelectedDate) &&
User().Email = 'Created By'.Email,
ID in Filter(Personnel,Email = User().Email).ProjectID
),
Filter(Overview, false) // Show no results when date range is invalid
)
)
,
TextInput1_2.Text,"Title","Disciplines","Client","EndClient","SiteName","State","Country","Description",
"Contract","SalesForce","Type","Challenge","Solution","Outcomes","Perspective"
)

So whatever I search in the search box, whether its in the 1st, 2nd or the 3rd list it should filter that out. Currently I have also not filtered fields with choice.

I hope someone would be able to provide a solution.

@WarrenBelz @FLMike @Amik @ANB @SpongYe @BCBuizer

Categories:

Building Power Apps

All responses (3)

Answers (1)

Sort by

v-jefferni Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @Unknown123 ,

Could you please share more details about your scenario? What do you mean by "when I search in the gallery with a record in either Performance or Personnel list it should filter out in the Main Gallery"? What columns would you like to filter in the 3 lists? I haven't seen any columns from performance and personnel lists in the Search function.

Best regards,
Unknown123 115 on at

Like
a
(0)

Report
Copy link

Link copied!

@v-jefferni

I have a main gallery which shows data from the 1st list which is overview.

2nd list is Personnel and 3rd is Performance.

Personnel Fields are: Role, Name and Email which are single line of text.
Performance Fields: Time, Budget, quality and some more but all are multi line of text.

Yes I have not added it in the search as they are different lists and I dont know how I can add them in the above function. That is my query, how can I do that.
Verified answer

v-jefferni Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @Unknown123 ,

Directly search in different lists is not possible. The solution I can think of is using a collection where includes all relative columns from the three lists and then you will be able to search from all columns.

So, firstly you can create a collection of all the info from all the three lists based on log-in user OnVisible of Screen:

ClearCollect(colAllInfo, AddColumns(Filter(Overview, User().Email = 'Created By'.Email || Email=User().Email || ID in Filter(Personnel,Email = User().Email).OID), "Role", Filter(Personnel, Email = User().Email).Role, "Name", Filter(Personnel, Email = User().Email).Name, "Time", LookUp(Performance, OID = ThisRecord.ID, Time), "Time", LookUp(Performance, OID = ThisRecord.ID, Time), "Time", LookUp(Performance, OID = ThisRecord.ID).Time, "Budget", LookUp(Performance, OID = ThisRecord.ID).Budget, "quality", LookUp(Performance, OID = ThisRecord.ID).quality, ...))

Next step, you can use this collection as Gallery data source and filter it using those controls.

Best regards,