Combining Data from 2 different SharePoint List and performing serach

[Sidhant_02]

Hi everyone,
I had a requirement where I had to check if it is possible to combine the data from 2 different document libraries in two different sites. So in my case I had a site named: Pune -> in which I have created a document library named Miscelaneous and other SP site named DEMO_PowerApps -> in which I have created another document library named TrialLibrary. In both these list I created a Choice column named 'Tag' which had few common choices and some which are different.
So to combine the data from the both the list I used a Collection on the On-Start of the app:
ClearCollect(merging, TrialLibrary,Miscelaneous) and then created a new screen with a text input (To search records based on the Tag column) and a vertical gallery to display records. So on the Items property of the gallery, if the search field is blank then it displays the new collection else based on the input in search it filters the records. So the issue I am facing is that for the TrialLibrary document library for the Tag column it is not retrieving the values for it

So to cross check if there is any issue I added another just beside my main gallery and set its Item property to the TrialLibrary and there I saw all the values were getting retrieved properly.

I tried refreshing both the data sources and ran the On-Start property again, but no change. Please let me know what needs to be resolve this.

Regards,
Sidhant.

[v-jefferni · Accepted solution]

Hi @Sidhant_02 ,

I would say this issue is probably due to different settings of the two choices columns, they have different content of selections. Please try and use below formula to make the two column the same:

ClearCollect(merging, DropColumns(AddColumns(TrialLibrary As T1,"Tag1",T1.Tag.Value),"Tag"),DropColumns(AddColumns(Miscelaneous As T2,"Tag1",T2.Tag.Value),"Tag"))

Best regards,

[Sidhant_02]

Hi @v-jefferni ,

Thanks for the reply now I am able to see the tags for the records in both Document library. If possible could you please break it down and explain it. Also now what I want to do is based on the Tags I want to filter out the records
I tried the following:
If(
IsBlank(TextInput4.Text),
Merged,
Filter(Merged,TextInput4.Text in Tag1
);  //But this does not work, I have added another textInput on the screen which is named: TextInput4

And also provide an option to vie the file like the file will open in a new tab (if the user wants to check it).

Regards,
Sidhant.

[v-jefferni]

Hi @Sidhant_02 ,

Which part of the formula you don't understand well?

ClearCollect(
    merging, 
    DropColumns(                       //Remove the origianl Tag column
        AddColumns(                    //Add Tag1 column using Tag column values
            TrialLibrary As T1,
            "Tag1",
            T1.Tag.Value
        ),
        "Tag"
    ),
    DropColumns(AddColumns(Miscelaneous As T2,"Tag1",T2.Tag.Value),"Tag")
)

For the filtering in Gallery, I suggest you use Search function instead:

Search(Merged, TextInput4.Text, "Tag1")

Best regards,

[Sidhant_02]

Hi @v-jefferni ,
Let me know if this is correct:
1. So both the document libraries which are different sites even if the column name is the same (Tag) as they are in different sites the content will be different (even if some values are common in both).
2. Then to have common values in both the columns (of the Document Libraries i.e. Miscelaneous and TrialLibrary) we are dropping the Tag column (choice column) using DropColumns and then using AddColumns to add a new column.
In this we are giving an Alias name to our source i.e. TrialLibrary as T1 -> then giving the new column name here "Tag1" and then adding values of the Tag column to T1 and later dropping the Tag column. Similarly we have done for the next document library in other site.

Is this correct?.
One last thing now that we are able to display the items from both the document libraries in different sites. If the user wants to view them like there is a PDF file uploaded and the user wants to check that on click, can we give an option to open the file in a new tab or in the application itself?.

Regards,
Sidhant.

[Sidhant_02]

Hi @v-jefferni ,
I was looking to implement this: (In one site itself)

Anything that you can help?.

Regards,
Sidhant.

[v-jefferni]

Hi @Sidhant_02 ,

The main difference between the two lists is the two Choice columns. They have different choices, so we can think they are all different but just have the same column name, they look the same but totally different. So, in PowerApps, you can use AddColumns function to add a text type column where only include selected values.

Best regards,

[v-jefferni]

Hi @Sidhant_02 ,

It seems like a file management system. It can be done, but will have difficulties. Anyway, please post a new thread with more details and requirements, so that more users can see it and help you. 

Best regards,

[Sidhant_02]

Sure, will create a new thread for it.