cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Jaos291
Level: Powered On

Fill data table from two sharepoints

Hi everyone, I was wondering if there is any way to connect two sharepoint lists into a data table and then apply a Filter

For example List A has an ID and List B has its own ID obviously but it has an ID reference from the A List and I need to filter List A with List B. 

Lets say List A has ID and Name, List B hast Last Name and a Number Column that references ID from A, and if I apply a filter I need ID and Name from A and Last Name from B, is this possible ? 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Fill data table from two sharepoints

Hi @Jaos291

Do you want to connect two SP lists into single one Data Table and then apply a Filter?

Could you please share a bit more about the Number column that you mentioned in your List B?

Based on the needs that you mentioned, I think you need to simulate a join operation on the two tables based on ID column, then connect the combined result to the Data Table and apply a Filter.

I have made a test on my side, please take a try with the following workaround:9.JPG

Set the Items property of the Data Table control to following:

AddColumns(
'20181129_case8_ListA',
"LastName",
LookUp('20181129_case8_ListB', Value(ReferenceID.Value) = '20181129_case8_ListA'[@ID], LastName) /* ReferenceID is LookUp type column*/
)

On your side, you should type the following:

AddColumns(
    'YourSPListA',
     "LastName",
     LookUp('YourSPListB', Value(NumberColumn.Value) = 'YourSPListA'[@ID], LastName)
)

Note: The NumberColumn and LastName column are both columns in your SP listB. I assume that the NumberColumn in your SP listB is LookUp type column.

After that, you could apply a Filter condition to the above combined result as below:

Filter(
   AddColumns(
    'YourSPListA',
     "LastName",
     LookUp('YourSPListB', Value(NumberColumn.Value) = 'YourSPListA'[@ID], LastName)
   ),
   FilterCondition1,
   ...
)

In additon, the LookUp column could not be delegated within SP list, so above AddColumns function would cause a Delegation issue on your side. In order to get rid of this Delegation issue, please take a try with the following workaround:

Set the OnVisible property of the first screen of your app to following:

ClearCollect('ListACollection', 'YourSPListA');
ClearCollect('ListBCollection', 'YourSPListB');

Set the Items proeprty of the Data Table control to folowing:

AddColumns(
     ListACollection,
     "LastName",
     LookUp(ListBCollection, Value(NumberColumn.Value) = ListACollection[@ID], LastName)
)

More details about joining multiple data source within an app, please check the response within the following thread:

https://powerusers.microsoft.com/t5/General-Discussion/How-to-join-2-data-sources/td-p/75631

 

Best regards,

Kris

 

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 REPLY 1
Community Support Team
Community Support Team

Re: Fill data table from two sharepoints

Hi @Jaos291

Do you want to connect two SP lists into single one Data Table and then apply a Filter?

Could you please share a bit more about the Number column that you mentioned in your List B?

Based on the needs that you mentioned, I think you need to simulate a join operation on the two tables based on ID column, then connect the combined result to the Data Table and apply a Filter.

I have made a test on my side, please take a try with the following workaround:9.JPG

Set the Items property of the Data Table control to following:

AddColumns(
'20181129_case8_ListA',
"LastName",
LookUp('20181129_case8_ListB', Value(ReferenceID.Value) = '20181129_case8_ListA'[@ID], LastName) /* ReferenceID is LookUp type column*/
)

On your side, you should type the following:

AddColumns(
    'YourSPListA',
     "LastName",
     LookUp('YourSPListB', Value(NumberColumn.Value) = 'YourSPListA'[@ID], LastName)
)

Note: The NumberColumn and LastName column are both columns in your SP listB. I assume that the NumberColumn in your SP listB is LookUp type column.

After that, you could apply a Filter condition to the above combined result as below:

Filter(
   AddColumns(
    'YourSPListA',
     "LastName",
     LookUp('YourSPListB', Value(NumberColumn.Value) = 'YourSPListA'[@ID], LastName)
   ),
   FilterCondition1,
   ...
)

In additon, the LookUp column could not be delegated within SP list, so above AddColumns function would cause a Delegation issue on your side. In order to get rid of this Delegation issue, please take a try with the following workaround:

Set the OnVisible property of the first screen of your app to following:

ClearCollect('ListACollection', 'YourSPListA');
ClearCollect('ListBCollection', 'YourSPListB');

Set the Items proeprty of the Data Table control to folowing:

AddColumns(
     ListACollection,
     "LastName",
     LookUp(ListBCollection, Value(NumberColumn.Value) = ListACollection[@ID], LastName)
)

More details about joining multiple data source within an app, please check the response within the following thread:

https://powerusers.microsoft.com/t5/General-Discussion/How-to-join-2-data-sources/td-p/75631

 

Best regards,

Kris

 

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.