Solved: search and filter from 2 different sites sharepoin...

PytByt · ‎07-08-2019

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

leyburn19 · ‎07-08-2019

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

jhall · ‎07-08-2019

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.

Shanescows · ‎07-08-2019

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 · ‎07-08-2019

@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 · ‎07-08-2019

@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 · ‎07-08-2019

Happy to help 🙂

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 · ‎07-08-2019

@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 · ‎07-08-2019

@PowerApps911 wrote:
Happy to help 🙂

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 · ‎07-08-2019

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 · ‎07-08-2019

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,{Fd1:Source1Field,Fd2:Source1Field});

ForAll(Source2,Collect(Allrecords,{Fd1:Source2Field,Fd2:Source2Field})

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