cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Dave_31415
Frequent Visitor

Retrieve and Display Data From Multiple Sharepoint lists via ID

Hello,

I have the following sample sharepoint lists:

 

Material Cut Records

ID    Material Roll Number     . . . . .

54        123

55        234

56        345

.

.

.

100      123

101      234

102      345

 ------------------------

Produced Unit Records  

ID    Cut ID   UnitNum  . . . . .

11      100      AB444

12      101      AB555

13      102      AB666 

 ------------------------

Spare Material Records

ID    Cut ID   Size . . . . . 

1       54         0.5

2       55         0.7

3       56         0.4

 ------------------------

 

What I would like to do in powerapps is something like: 

 

Show all: (Produced Unit Records and Spare Material Records) that came from (Material Roll Number: 123).

 

I have been trying a variety of formula in powerapps but have not got this right yet. I could display the resulting data from this query however will work easily enough. I have been currently trying to make this query and display the result in a DataTable but if there is an easier method to query this and display it, please suggest. If I can make this query work, I may next look to export the query from powerapps to an excel sheet.

 

I appreciate any help offered.

 

Thank you much.

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-yuxima-msft
Community Support
Community Support

Hi @Dave_31415 

 

You can use one collection to union the Produced Unit Records and Spare Material Records firstly, and then use filter.

Firstly, add one button control,

Button.OnSelect=ClearCollect(test,Ungroup(Table({MyTables:ProducedUnitRecords},{MyTables:SpareMaterialRecords}), "MyTables"))    (test is the collection name)

 

Then add one Gallery control or Data Table Control, Gallery/Data Table.Items=Filter(test,ID in Filter(MaterialCutRecords,MaterialRollNumber=123).ID)

 

You can also use this formula expression in the Items not without use colleciton:

Filter(Ungroup(Table({MyTables:ProducedUnitRecords},{MyTables:SpareMaterialRecords}), "MyTables"),ID in Filter(MaterialCutRecords,MaterialRollNumber=123).ID)

 

Best Regards.

Yumia

 

View solution in original post

3 REPLIES 3
v-yuxima-msft
Community Support
Community Support

Hi @Dave_31415 

 

You can use one collection to union the Produced Unit Records and Spare Material Records firstly, and then use filter.

Firstly, add one button control,

Button.OnSelect=ClearCollect(test,Ungroup(Table({MyTables:ProducedUnitRecords},{MyTables:SpareMaterialRecords}), "MyTables"))    (test is the collection name)

 

Then add one Gallery control or Data Table Control, Gallery/Data Table.Items=Filter(test,ID in Filter(MaterialCutRecords,MaterialRollNumber=123).ID)

 

You can also use this formula expression in the Items not without use colleciton:

Filter(Ungroup(Table({MyTables:ProducedUnitRecords},{MyTables:SpareMaterialRecords}), "MyTables"),ID in Filter(MaterialCutRecords,MaterialRollNumber=123).ID)

 

Best Regards.

Yumia

 

View solution in original post

Thank you Yumia. This worked for me. I really appreciate your help - I was stuck on this all day yesterday. 

Porsche88
New Member

Hi there, 

 

I am trying to do something similar but I want to create a filter option/box where the users can search the material number instead of the default being 123. Any suggestions? 

 

Thanks in advance, greatly appreciated! 

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Top Kudoed Authors
Users online (3,660)