Showing results for 
Search instead for 
Did you mean: 
New Member

Two Data Sources in Filter Formula

Hi there,


I have encouted an issue with my PowerApps and I am wanting some help.


Our PowerApps was created as a tempory solution for Sales Reps to Log Customer Visits (Sales Calls) whist we implement Dynamics 365. It saves data back to a SharePoint online list in our Office 365 instance. The first page of the app is a filterable list that the employee can refine based on their branch and employee ID. 























Today, the list for the branch reached its maximum viewable threshold of 5000. To resolve this issue, I created a second SharePoint list for the data to save into. This was the quickest return to service option that I could come up with for the App. 



So that the Sales Reps can still see their historical entries I want them to be able to filter both SharePoint lists. Here is the formula I was using when it was just one list:



SortByColumns(Filter('Auckland Visit Data',Branch=Dropdown2.Selected.Value),"Created",Descending),
SortByColumns(Filter('Auckland Visit Data',Sales_x0020_Rep_x0020_ID=TextInput2.Text),"Created",Descending))


How do I update this formula so that is also references the new data that is being saved into 'Auckland Visit Data No.2'?


Any help you can provide would be greatly appreciated. Our company is still relateively new to PowerApps so we don't have this skill in house yet and Google wasn't helping in this instance. 

New Member

I have found one work around. I have added a drop down box that allows the user to select which data source they want to filter:























This uses the following:

If(Dropdown3.Selected.Value="14 July - Current",(If(IsBlank(TextInput2.Text),
SortByColumns(Filter('Auckland Visit Data No.2',Branch=Dropdown2.Selected.Value),"Created",Descending),
SortByColumns(Filter('Auckland Visit Data No.2',Sales_x0020_Rep_x0020_ID=TextInput2.Text),"Created",Descending))),(If(IsBlank(TextInput2.Text),
SortByColumns(Filter('Auckland Visit Data',Branch=Dropdown2.Selected.Value),"Created",Descending),
SortByColumns(Filter('Auckland Visit Data',Sales_x0020_Rep_x0020_ID=TextInput2.Text),"Created",Descending))))



Community Champion
Community Champion

Hi @VanessaNZ


From your question, I supposed you wish to

- Back-Up your data into a 2nd SPL.


In this case, I would advise that you

- Filter the data which you wish to back-up

- Patch into 2nd SPL

- Then, remove (delete) from current SPL.


So, you can continue with 1st SPL for Sales Calls data entering, and

use 2nd SPL for viewing historial data.




Back-Up icon & button: Manual or Auto (based on data condition)Back-Up icon & button: Manual or Auto (based on data condition)

Share with you the example formulat I used to use:


UpdateContext({savegif_backup: true}); ..... Just a gif file to show saving is going on
ClearCollect(BackUp, Filter(SPL1, Condition_Formula)); ...Temporary collect into a BackUp collection, based on formula (e.g.: Filter(SPL1, !IsBlank(Date)).. because once Sales Call was done, "Date" will definitely be updated
ForAll(, Patch(SPL2, {FieldTitle1: Label1.Text, FieldTitle2: Label2. Text,..........})); more tricky.

create a Gallery1.Items = BackUp, insert labels to show ALL the fields (Label1, Label2,.... MUST MATCH All), and Patch (Save) into SPL2. Please make the Gallery1.Visible = False (hidden as it is only used for ForAll and Patch purposes.)
RemoveIf(SPL1, Condition_Formula)); ... Delete SPL1 data with Date, Condition Formula =  !IsBlank(Date))

UpdateContext({savegif_backup: false, dataupdated: true}) .... Just a gif file to show saving has completed, and prompt a message box (Label.Visible = dataupdated)


All the above will run in sequence as they are end with ";".


Hope the above helps.



I am doing this, too, to back-up DELIVERED Products into another database (as History, for search and view), and keep current list showing upcoming goods pending delivery.

Back-up action can either be done manually (much safer, and you can use CountRows(SPL1) to show no of data to decide when to back-up) or using IF condition to detect No of data (e.g.: CountRows (SPL1) = 4900, insert a Timer1, and OnTImerEnd, trigger the above formula).


Hope the above helps and have a nice day.



Helpful resources

New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

New Power Super Users


We are excited to announce the Power Apps Super Users!

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

Top Solution Authors
Top Kudoed Authors
Users online (31,572)