cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
VanessaNZ
Level: Power Up

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
VanessaNZ
Level: Power Up

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

 

 

hpkeong
Level 10

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:

 

backup.PNGBack-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
Better Together’ Contest Finalists Announced!

'Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

thirdimage

Power Apps Community User Group Member Badge

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

sixthImage

Join THE global Microsoft Power Platform event series

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

thirdimage

Microsoft Business Applications Virtual Launch

Join us for the Microsoft Business Applications Virtual Launch Event on Thursday, April 2, 2020, at 8:00 AM PST.

thirdimage

Community Summit North America

Innovate, Collaborate, Grow - The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (10,345)