Delegation issue while joining 2 SP lists with 2000+ records with condition

[anujk-singh]

Hello everyone,

I am stuck in a situation where I need to join 2 SP Lists with one of them having 7000+ records.
Detailed explanation:

1. SPListA, it has more than 7K records and is primary while doing the join

2. SPListB, 500+ records

Methods tried:

1. Tried to collect the data in Collection. Since ClearCollect isn't a delegable function, it doesn't pickup all the records from SPListA(i.e. it picks first 2000 records if I increase the Data Row limit to 2000)

2. Tried to do join with AddColumns as follows:

AddColumns(
           SPListA,
            "Data",LookUp(
                         SPListB,
                         And(DOC_ID = SPListA[@TSD_DOC_ID]
                         No = SPListA[@Title]
                    )
               )
      );

This one is same issue again as AddColumns is also partially delegable hence didn't work in this case either.

Is there any other work around available? Looking forward for your kind help.

Thanks,

Anuj

[365-Assist]

I have 2 suggestions:

1 - Use Power Automate (rather than PowerApps) to join SP or lists or

2 - Use Dataverse as a data store. Its better to use for data table\list relationships.

[anujk-singh]

Hi @365-Assist ,

Thanks for quick response.

1 - Use Power Automate (rather than PowerApps) to join SP or lists or - I have actually tried but the Flow keeps running, attached

2 - Use Dataverse as a data store. Its better to use for data table\list relationships.-  Unfortunately, can't use due to licensing limitations

Thanks,

Anuj

[365-Assist]

I can see where your flow is going wrong. Try this:

The key is using the Filter Query in the 2nd Get items to match. In your case its TSD_DOC_ID (From 2nd Get Items) eq TSD_DOC_ID (From 1st Get Items)

[iAm_ManCat]

Do you HAVE to join the list or are you doing this because you're used to doing it from traditional dev languages?

I would just have an individual lookup to the second column wherever its needed, then let Power Apps handle the getting of it. What's the context such that you need it to be joined - where are you using it?

[anujk-singh]

Thanks for detailed explanation @365-Assist .

I have tried it but don't think it's serving my purpose. When I am doing comparison in 2nd GetItems task, how do I relate the columns in 1st GetItems and result of 2nd GetItems? I need the columns of both lists to be mapped 1:N in most of the cases.

Thanks,

Anuj

[anujk-singh]

Hi @iAm_ManCat ,

I have a screen where at 10-15 columns of each SPList need to be displayed and there might be 1:N relationship as well. That's why I am trying to use gallery to display data.

If I use too many LookUp, the performance may go down?
I am open for any other recommendations to sort this out

Thanks,

Anuj

[iAm_ManCat]

Well, you'll already be seeing an impact with the AddColumns, so I imagine it would be the same or better depending on how its implemented - galleries only pull the items that are currently needed, so usually about a hundred at a time rather than the 7k (2kmax) that your addcolumns tried to do - if you have a gallery with lots of lookups, yes it will have to do lots of lookups as you scroll through, but you can use ShowColumns within the lookup to only have to get the columns it needs, that way the impact will ne negligible compared to the Addcolumns way you are using now.