search and filter from 2 different sites sharepoint list

(0) Share

Report

Posted on by PytByt

943

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

Categories:

Building Power Apps

All responses (20)

Answers (1)

Sort by

jhall 636 on at

Like
a
(1)

Report
Copy link

Link copied!

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 2,218 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!

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.
PytByt 943 on at

Like
a
(0)

Report
Copy link

Link copied!

@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 943 on at

Like
a
(0)

Report
Copy link

Link copied!

@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 12 on at

Like
a
(1)

Report
Copy link

Link copied!

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 636 on at

Like
a
(1)

Report
Copy link

Link copied!

@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 943 on at

Like
a
(0)

Report
Copy link

Link copied!

@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 943 on at

Like
a
(0)

Report
Copy link

Link copied!

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 2,157 on at

Like
a
(1)

Report
Copy link

Link copied!

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
PytByt 943 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @leyburn19

Where should i put those formula?
I mean, i already try it OnStart property of the App and its gets blank collection.

Anyhow, can you make it more abovius for me for the Allrecords? is that a variabel?
I'm a professionally noob with this.

I'm sorry, and thank you.
regards,
pytbyt