cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted

Get Matching records in child list to parent and put it in a gallery

I have  a parent list and a child list. I want matching records in child list to parent list so that even if it exceeds 2000 items the filter condition should work. Attached screenshot  is the data of my SharePoint 2 lists and the outcome .

 

I was able to filter for the FirstList  list like below . In the same Gallery i want to get the Second list Columns as well.(Attached screen shot)

Filter('FirstList', Location.Value = DrpDown.Selected.Value || If(DrpDown.Selected.Value = "-Select-",true,false)).

 

Can any one help me on this please. This might be the common scenario for everyone but could not figure it out.

 

 

3 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User II
Super User II

Re: Get Matching records in child list to parent and put it in a gallery

Hi @santukondapally 

 

So, you've filtered your gallery to only show items from the first list that match these conditions:

Filter( 
       'FirstList',
       Location.Value = DrpDown.Selected.Value
       ||
       If(DrpDown.Selected.Value = "-Select-",true,false)
)

Which you can actually write as:

Filter( 
       'FirstList',
       DrpDown.Selected.Value = "-Select-"
       ||
       Location.Value = DrpDown.Selected.Value
)

(as either the dropdown has 'select' chosen, or else filter by what is chosen)

 

Now your gallery contains a filtered list of items from List1. This means that if you want to get items from list two, all you have to do is reference ThisItem.'SERVICE TAG' (as that's your linking field)

 

So, insert a new label into the gallery row, then make its Text:

 

Filter(
       'List2',
       AssetID=ThisItem.'SERVICE TAG'
).ColumnName

 

Replace 'List2' with your list name, and make sure 'SERVICE TAG' is the name of the column in List1.

Replace .ColumnName with the column you want to return, and you should be able to include items from the second list that match that item.

 

Let me know if you run into any issues, happy to help further,

 

Cheers,

Sancho


@iAm_ManCat

Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!


Thanks!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP


View solution in original post

Highlighted
Community Support
Community Support

Re: Get Matching records in child list to parent and put it in a gallery

Hi @santukondapally ,

Could you please share a bit more about the "AssetID" column and "ProductID" column in your second List? Are they both LookUp type columns, which reference values from your First List?

Does the single one record in your First List could match multiple records in your second List?

 

If single one record in your First List could match multiple records in your second List, I afraid adding extra Labels in your Gallery could not achieve your needs.

 

As an alternative solution, I think a nested Gallery could achieve your needs. You could consider add a nested Gallery inside your original Gallery. Then set the Items property of this nested Gallery to following:

Filter(
       'Your Second List',
       AssetID.Value = ThisItem.'SERVICE TAG',
       ProductID.Value = ThisItem.ID
)

Note: I assume that the "ProductID" column in your second List reference values from the ID column in your First List.

Within this nested Gallery, you could add several Labels, to display related column values from your second List. Set the Text property of these labels in your nested Gallery to following:

ThisItem.Status.Value
ThisItem.'Assigned Start Date'

...

...

 

For the issue mentioned in your screenshot, it is related to your formula. The Text property of the Label is required to provide a Text value, but the Filter(...).'Allocated To' formula returns a Table value. If the 'Allocated To' column is a Text type column, you could consider modify your formula as below:

Concat(
       Filter(
              'Your Second List',
               AssetID.Value = ThisItem.'SERVICE TAG'
       ),
       'Allocated To' & ";"
)

If the 'Allocated To' column is a Choice or LookUp type column, please modify above formula as below:

Concat(
       Filter(
              'Your Second List',
               AssetID.Value = ThisItem.'SERVICE TAG'
       ),
       'Allocated To'.Value & ";"
)

Please try above formula, check if the issue is fixed.

 

More details about adding a nested Gallery inside a Gallery, please check the following video:

https://www.youtube.com/watch?v=ZzQ1t2sQvj8

 

Regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted

Re: Get Matching records in child list to parent and put it in a gallery

@iAm_ManCat  thanks for showing me the path to resolve the issue .... Awsome

 

@v-xida-msft  You made my day so simple Thanks for your help. You have given the formulae exactly what i needed. 

Concat(Filter(ITASSETS,AssetID.Value=ThisItem.'SERVICE TAG'),'Allocated To'&";"). Thanks a lot. one last question one AssitID in the Second list has so many records. how should i get the latest record value from It. I mean where should i use LASTN function on this.

 

Any how once again thanks a lot for your help. This really made my day so simple

View solution in original post

5 REPLIES 5
Highlighted
Super User II
Super User II

Re: Get Matching records in child list to parent and put it in a gallery

Hi @santukondapally 

 

So, you've filtered your gallery to only show items from the first list that match these conditions:

Filter( 
       'FirstList',
       Location.Value = DrpDown.Selected.Value
       ||
       If(DrpDown.Selected.Value = "-Select-",true,false)
)

Which you can actually write as:

Filter( 
       'FirstList',
       DrpDown.Selected.Value = "-Select-"
       ||
       Location.Value = DrpDown.Selected.Value
)

(as either the dropdown has 'select' chosen, or else filter by what is chosen)

 

Now your gallery contains a filtered list of items from List1. This means that if you want to get items from list two, all you have to do is reference ThisItem.'SERVICE TAG' (as that's your linking field)

 

So, insert a new label into the gallery row, then make its Text:

 

Filter(
       'List2',
       AssetID=ThisItem.'SERVICE TAG'
).ColumnName

 

Replace 'List2' with your list name, and make sure 'SERVICE TAG' is the name of the column in List1.

Replace .ColumnName with the column you want to return, and you should be able to include items from the second list that match that item.

 

Let me know if you run into any issues, happy to help further,

 

Cheers,

Sancho


@iAm_ManCat

Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!


Thanks!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP


View solution in original post

Highlighted

Re: Get Matching records in child list to parent and put it in a gallery

@iAm_ManCat  thanks for your reply. I tried it and  ran in to the  attached error. Can you please help me.

Highlighted
Community Support
Community Support

Re: Get Matching records in child list to parent and put it in a gallery

Hi @santukondapally ,

Could you please share a bit more about the "AssetID" column and "ProductID" column in your second List? Are they both LookUp type columns, which reference values from your First List?

Does the single one record in your First List could match multiple records in your second List?

 

If single one record in your First List could match multiple records in your second List, I afraid adding extra Labels in your Gallery could not achieve your needs.

 

As an alternative solution, I think a nested Gallery could achieve your needs. You could consider add a nested Gallery inside your original Gallery. Then set the Items property of this nested Gallery to following:

Filter(
       'Your Second List',
       AssetID.Value = ThisItem.'SERVICE TAG',
       ProductID.Value = ThisItem.ID
)

Note: I assume that the "ProductID" column in your second List reference values from the ID column in your First List.

Within this nested Gallery, you could add several Labels, to display related column values from your second List. Set the Text property of these labels in your nested Gallery to following:

ThisItem.Status.Value
ThisItem.'Assigned Start Date'

...

...

 

For the issue mentioned in your screenshot, it is related to your formula. The Text property of the Label is required to provide a Text value, but the Filter(...).'Allocated To' formula returns a Table value. If the 'Allocated To' column is a Text type column, you could consider modify your formula as below:

Concat(
       Filter(
              'Your Second List',
               AssetID.Value = ThisItem.'SERVICE TAG'
       ),
       'Allocated To' & ";"
)

If the 'Allocated To' column is a Choice or LookUp type column, please modify above formula as below:

Concat(
       Filter(
              'Your Second List',
               AssetID.Value = ThisItem.'SERVICE TAG'
       ),
       'Allocated To'.Value & ";"
)

Please try above formula, check if the issue is fixed.

 

More details about adding a nested Gallery inside a Gallery, please check the following video:

https://www.youtube.com/watch?v=ZzQ1t2sQvj8

 

Regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Super User II
Super User II

Re: Get Matching records in child list to parent and put it in a gallery

Hi,

 

If 'Assigned To' is a people field, then its a complex object, so you need to append 'Assigned To'.DisplayName or similar - if you type a . after 'Assigned To' it will give you a list of options to choose from - DisplayName will show that person's name in the table,

 

Cheers,

Sancho


@iAm_ManCat

Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!


Thanks!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP


Highlighted

Re: Get Matching records in child list to parent and put it in a gallery

@iAm_ManCat  thanks for showing me the path to resolve the issue .... Awsome

 

@v-xida-msft  You made my day so simple Thanks for your help. You have given the formulae exactly what i needed. 

Concat(Filter(ITASSETS,AssetID.Value=ThisItem.'SERVICE TAG'),'Allocated To'&";"). Thanks a lot. one last question one AssitID in the Second list has so many records. how should i get the latest record value from It. I mean where should i use LASTN function on this.

 

Any how once again thanks a lot for your help. This really made my day so simple

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Watch Now

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (5,702)