cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
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

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
New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

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