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
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

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 (956)