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

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Don't miss the call this month on June 16th - 8a PDT

Top Solution Authors
Top Kudoed Authors
Users online (94,628)