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

search and filter from 2 different sites sharepoint list

Hi all..

 

i need to make this 2 seperates SP List from 2 different sites to work together and displaying the item i need in a single gallery.

 

i use this formula, and i already mofied it hundreds times and its still get blank gallery (perhaps the formula is positively wrong):

 

Gallery Items property:

Sort(Filter('_Onboarding List', If(Gallery1.Selected.Title2.Text = "All", true, Status = Gallery1.Selected.Title2.Text)), Filter('Candidate List', Status3.Value = "Converted")) && If(IsBlank(TextInputSearch.Text), true, TextInputSearch.Text in 'Candidate Name')), 'Candidate Name', Ascending)

 

as you can see that i want to filter from 2 different sites sharepoint list and search it through a gallery.

the idea is, i want to sort and filter from the first SP List (Onboarding List) so i can browse throught it by it status, whilst also i can see at the same gallery the second SP List items (Candidate List) which have "Converted" Status.

 

i dont know if this possible to achive, any insightful work around for solving this is a life saver for me.

 

thanks,

 

regards.

pytbyt

 

1 ACCEPTED SOLUTION

Accepted Solutions
leyburn19
Level 10

Re: search and filter from 2 different sites sharepoint list

The Collect Statement may need all the names in each list. Unless they can be used as one

 

You will see I have renames the field to match but also added an extra column in the first for status and set contents to ""

 

In theory this will give a simple list to search on.  If you want seperate columns you would need to have every column names in both collections as teh collections must match.  

 

EG:

 

ForAll('_Onboarding List', Collect(TesCol, {Name1: 'Candidate Name', Site1: 'Site Destination', Speciality1: Specialization, Status:""}));

ForAll('Candidate List', Collect(TesCol, {Name1: 'Candidate Name', Site1: 'Site Name', Speciality1: Specialist, Status: Status3}))

View solution in original post

20 REPLIES 20
jhall
Level 8

Re: search and filter from 2 different sites sharepoint list

Just a general comment for troubleshooting even simple data queries is to try assigning the result to a Collection and then viewing the Collection by assigning it to the Items value for a Data Table control.  Then click on the Data Table control and select the fields you want to display. 

This is a good gut check to verify if your data is what you think it is.  Gallery is nice, but it can make some assumptions you might not prefer.

If you see the columns that you expect to see, then you at least know that you're connecting to the data sources OK, but your query is not returning what is expected.

 

Super User
Super User

Re: search and filter from 2 different sites sharepoint list

Baby steps. You have way too much going on there to start. A single Gallery cannot be bound to two different. If you want to combine the two lists into one Collection then you can display and search that collection in one gallery. 

 

If I was you I would try to get the solution working with one list. Then with the other list. Then try to figure out if you can merge the two lists.

 

 

Shane - Microsoft MVP, YouTube, and PowerApps Consulting for when you are in a bind to get this fixed quickly. And finally we now have PowerApps Training
PytByt
Level: Powered On

Re: search and filter from 2 different sites sharepoint list


@jhall wrote:

Just a general comment for troubleshooting even simple data queries is to try assigning the result to a Collection and then viewing the Collection by assigning it to the Items value for a Data Table control.  Then click on the Data Table control and select the fields you want to display. 

This is a good gut check to verify if your data is what you think it is.  Gallery is nice, but it can make some assumptions you might not prefer.

If you see the columns that you expect to see, then you at least know that you're connecting to the data sources OK, but your query is not returning what is expected.

 


Hi @jhall 

 

Thanks for your reply. i wil ltry this work around.

And i just know by now that the Data Table control is can be clickable just like creating menu with gallery (?).

 

Can you show me how its done or any guidance recommends? I only ask this because i start this powerapps things and got so much on my plate already. 

 

thanks,

pytbyt

 

 

PytByt
Level: Powered On

Re: search and filter from 2 different sites sharepoint list


@Shanescows wrote:

Baby steps. You have way too much going on there to start. A single Gallery cannot be bound to two different. If you want to combine the two lists into one Collection then you can display and search that collection in one gallery. 

 

If I was you I would try to get the solution working with one list. Then with the other list. Then try to figure out if you can merge the two lists.

 

 


Hi Powerapps Guru @Shanescows 

 

Its an honor to me that you wanna reply such a noob thread. 

 

I wanna try that collection work around, can you show me how its done for my case?
And for the solution working with only one list that you mentioned, i still dont know how its dont. Though i wanna do it at the first place. Again, if there any guidance you can give for my case it will be a huge life saver.

 

thank you,

 

regards,

pytbyt


PowerApps911
Level: Powered On

Re: search and filter from 2 different sites sharepoint list

Happy to help Smiley Happy 

 

What columns do you have in each list? If they are exact same the code would look like this.

 

Collect(CombinedCollection, SharePointList1);Collect(CombinedCollection, SharePointList2)

jhall
Level 8

Re: search and filter from 2 different sites sharepoint list


@PytByt wrote:

And i just know by now that the Data Table control is can be clickable just like creating menu with gallery (?).

It's not really a workaround, it's just as @Shanescows started his post with:  baby steps.  Putting things in a Data Table just gives you an easy way to look at what you've imported.  You can view it like an Excel table essentially.  

Also, you primarily want to test things in chunks, again, as @Shanescows mentioned and @PowerApps911 is implying here.  You first should likely merge your two sources into a single Collection.  You can test this by slaving the command @PowerApps911 shows to a button and clicking it.

What you're doing is creating a "merged Table" in memory for your application (which PowerApps refers to as a Collection).  

Once you have that, you can then use that as an input for your Gallery, Data Table, etc.  You could potentially do this in a single line on a single control (e..g Gallery's Input setting), but you want to chunk this up so you can test one piece at a time.  Get one source, verify.  Get second source, verify.  Merge two sources, verify.  Link to control.

PytByt
Level: Powered On

Re: search and filter from 2 different sites sharepoint list


@PowerApps911 wrote:

Happy to help Smiley Happy 

 

What columns do you have in each list? If they are exact same the code would look like this.

 

Collect(CombinedCollection, SharePointList1);Collect(CombinedCollection, SharePointList2)


Hello @PowerApps911 

 

I really want a help from you.

I want to show this field in each records from 2 seperated site of my sharepoint list:

1. Candidate Name (from sp list 1 & 2, same field name and data type)
2. Site Name (from sp list 2)

3. Site Destination (from sp list 1)

4. Status (from sp list 2)

5. Status3 (from sp list 1)

 

What should i write as input in the formula above?

 

thanks,

pytbyt

PytByt
Level: Powered On

Re: search and filter from 2 different sites sharepoint list

Hi @jhall 

 

thanks for the explaination.

i really get the big picture there.. however, i still need a reference for my case. i still confuse for the formula im gonna use. 

any guidance would be a great life saver for me.

 

regards,

pytbyt

leyburn19
Level 10

Re: search and filter from 2 different sites sharepoint list

Are the fields the same?  If So I would create a simple collection that does:

 

Collect(Allrecords,Source1);Collect(Allrecords,Source2)

 

The second collect would just append to the first but all fields need to match.  If the dont then do a Forall process:

 

EG:

Clear(Allrecords)

ForAll(Source1,Collect(Allrecords,{Fd1Smiley Frustratedource1Field,Fd2Smiley Frustratedource1Field});

ForAll(Source2,Collect(Allrecords,{Fd1Smiley Frustratedource2Field,Fd2Smiley Frustratedource2Field})

 

This second process may run into delegation issues depending on number of records in list.  You can also filter the source first toi get the status you want in bothe cases

Helpful resources

Announcements
firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Demo Extravaganza Championship Voting Open

Voting Ends: October 30, 2019!

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

Top Kudoed Authors
Users Online
Currently online: 446 members 4,732 guests
Please welcome our newest community members: