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

Join 2 Sharepoint lists and fetching the matching records

I have 2 sharepoint lists (Products,Assetinfo). From powerapps i am Assigning a product for an assignee in the Asset info List.i.e 
One product from products list can be assigne to multiple users.
I want to show the history of a product report when it was assigned to and to whom. for that i want to join these 2 tables.

 

AssetTAG from Products  is a Look up field in AssetInfo(i.e AssetID).
ID from Products is a lookup field in AssetInfo(i.e ProductID).
How to Combine these 2 lists in to one collection. Attached is the Screen shot of my 2 lists

 

 

5 REPLIES 5
v-bofeng-msft
Community Support
Community Support

Hi @Santosh_143 :

How do you want to join the two tables?Merge two tables or Embed'PRODUCTS LIST (DERVICE TAG IS UNIQUE)' as a sub-table in'ASSET INFORmation'?

I'v made a test for your reference:

7.JPG8.JPG1.JPG

Case1:Merge two tables

 

ClearCollect(
    test1,
    ForAll(
        'ASSET INFORmation',
        {Location: Location,
         'SERVICE TAG': LookUp('PRODUCTS LIST(DERVICE TAG IS UNIQUE)',ID =Value(ProductID.Value)).'SERVICE TAG',
         'SERIAL NO': LookUp('PRODUCTS LIST(DERVICE TAG IS UNIQUE)',ID = Value(ProductID.Value)).'SERIAL NO'
        }
    )
)

 

3.JPG

Case2:Embed'PRODUCTS LIST (DERVICE TAG IS UNIQUE)' as a sub-table in'ASSET INFORmation'

 

ClearCollect(
    test2,
    ForAll(
        'ASSET INFORmation',
        {
            Location: Location,
            'PRODUCTS LIST(DERVICE TAG IS UNIQUE)': LookUp('PRODUCTS LIST(DERVICE TAG IS UNIQUE)',ID = Value(ProductID.Value))
        }
    )
)

 

2.JPG

Best Regards,

Bof

santukondapally
Helper II
Helper II

Thanks for your reply. Its working but it gives delegation warning and says it does not work for larger data sets. Can you please help me in this regard. I am just new to this Powerapps. Thanks for your help. Screen shot attached

Hi @santukondapally :

Because ProductID is a lookup type field, delegation warning is difficult to avoid.

Please try this code:

 

ClearCollect(
    test1,
    ForAll(
        AddColumns('ASSET INFORmation',"TheID",Value(ProductID.Value)),
        {Location: Location,
         'SERVICE TAG': LookUp('PRODUCTS LIST(DERVICE TAG IS UNIQUE)',ID =TheID).'SERVICE TAG',
         'SERIAL NO': LookUp('PRODUCTS LIST(DERVICE TAG IS UNIQUE)',ID = TheID).'SERIAL NO'
        }
    )
)

 

 In addition,you can set the value of 'Data row limit for non-delegagle queries' to 2000.(This will not eliminate the delegation warning, but will increase the limit)

1.JPG

 

Best Regards,

Bof

Thanks this Worked for Merging of 2 tables. So one last question will there be any problem if my list rows increases more than 2000 for this query in feature?

Hi @santukondapally :

The maximum limit of this function is 2000, so even a larger number than 2000 is useless.

I think this link will help you a lot:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview#changing-the-limit

Best Regards,

Bof

Helpful resources

Announcements
User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Power Apps Community Call Jan. 2022 768x460.png

Power Apps Community Call

Please join us on Wednesday, January 19th, at 8a PDT. Come and learn from our amazing speakers!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Solution Authors
Top Kudoed Authors
Users online (2,689)