Join 2 Sharepoint lists and fetching the matching records

[Santosh_143]

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

[v-bofeng-msft]

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:

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'
        }
    )
)

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))
        }
    )
)

Best Regards,

Bof

[santukondapally]

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

[v-bofeng-msft]

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)

Best Regards,

Bof

[santukondapally]

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?

[v-bofeng-msft]

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