cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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. 

2017-07-17_09h49_14.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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:

 

 

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))

 

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. 

2 REPLIES 2
Highlighted
New Member

Re: Two Data Sources in Filter Formula

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:

 

2017-07-17_10h37_10.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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))))

 

 

Highlighted
Community Champion
Community Champion

Re: Two Data Sources in Filter Formula

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.

 

Example:

 

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(Backup.gallery.AllItems, Patch(SPL2, {FieldTitle1: Label1.Text, FieldTitle2: Label2. Text,..........})); ...here 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.

 

NB:

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.

TQ

hpkeong

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

secondImage

Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

secondImage

Super Users Coming in August

We are excited for the next Super User season.

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (6,912)