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

Re: Join 2 Sharepoint lists and fetching the matching records

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

Highlighted

Re: Join 2 Sharepoint lists and fetching the matching records

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

Highlighted
Community Support
Community Support

Re: Join 2 Sharepoint lists and fetching the matching records

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

Highlighted

Re: Join 2 Sharepoint lists and fetching the matching records

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?

Highlighted
Community Support
Community Support

Re: Join 2 Sharepoint lists and fetching the matching records

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
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors
Users online (5,916)