Further Drill Down Filtering

[KimberlyM]

My goal is to be able to display Status="Not Started", Status="In Progress", Status="Dead Deals", etc. based on button click. 

On my Screen my OnVisible property is set to filter already to pull data for my pie chart:

If(!IsEmpty(Filter(Admin_LeadData,FullName=varUser)),
     ClearCollect(colDash_StatusTrack,{Status:"Not Started",Count:CountIf(Lead_Data,Status.Value="Not Started")});
     Collect(colDash_StatusTrack,{Status:"In Progress",Count:CountIf(Lead_Data,Status.Value="In Progress")});
     Collect(colDash_StatusTrack,{Status:"Dead Deal",Count:CountIf(Lead_Data,Status.Value="Dead Deal")});
     Collect(colDash_StatusTrack,{Status:"Completed Deal",Count:CountIf(Lead_Data,Status.Value="Completed Deal")}),
     
     ClearCollect(colDash_StatusTrack,{Status:"Not Started",Count:CountIf(Lead_Data,Status.Value="Not Started"&&Or(AssignedTo.Value=varUser,LeadMember1.Value=varUser,LeadMember2.Value=varUser,LeadMember3.Value=varUser))});
     Collect(colDash_StatusTrack,{Status:"In Progress",Count:CountIf(Lead_Data,Status.Value="In Progress"&&Or(AssignedTo.Value=varUser,LeadMember1.Value=varUser,LeadMember2.Value=varUser,LeadMember3.Value=varUser))});
     Collect(colDash_StatusTrack,{Status:"Dead Deal",Count:CountIf(Lead_Data,Status.Value="Dead Deal"&&Or(AssignedTo.Value=varUser,LeadMember1.Value=varUser,LeadMember2.Value=varUser,LeadMember3.Value=varUser))});
     Collect(colDash_StatusTrack,{Status:"Completed Deal",Count:CountIf(Lead_Data,Status.Value="Completed Deal"&&Or(AssignedTo.Value=varUser,LeadMember1.Value=varUser,LeadMember2.Value=varUser,LeadMember3.Value=varUser))})
)

My gallery Items property is set to filter my SharePoint list data based on the user logged in. 

If(!IsEmpty(Filter(Admin_LeadData,FullName = varUser)), Lead_Data, 
Filter(Lead_Data, AssignedTo.Value = varUser || LeadMember1.Value = varUser || LeadMember2.Value = varUser || LeadMember3.Value = varUser || LeadMember4.Value = varUser))

What would be my best way to accomplish this without breaking the way my app is working aready? Is it possible? With this in mind I also want to next create a button on my red banner that will sort by the date created. 

[cwebb365]

Just have a variable that is set called TabSelected and set it with on select of each on. You can use Set or update context. Set is easier to track IMo.

Then in your dataset filter just put all the existing comparisons in parentheses and then put a && (statusColumn = TabSelected)

So something like
If(!IsEmpty(Filter(Admin_LeadData,FullName = varUser)), Lead_Data,
Filter(Lead_Data, (AssignedTo.Value = varUser || LeadMember1.Value = varUser || LeadMember2.Value = varUser || LeadMember3.Value = varUser || LeadMember4.Value = varUser)&&(StatusTrack = TabSelected)))

[KimberlyM]

@cwebb365  Thank you! I almost understand this (I am coming off of 3 day stomach flu and not feeling top game yet, couldn't remember my  computer password this morning either...). 

&& (statusColumn = TabSelected)  what should statusColumn be referencing? I have tried to reference my column name (Lead_Data.Status as well as Lead_Data.Status.Value) and my gallery display label (Dash_Status as well as Dash_Status.Text). 

On each of my buttons I have put this on my onSelect property: 
Set(TabSelected, Dash_Status.Text = "Not Started")
Set(TabSelected, Dash_Status.Text = "In Progress")
Set(TabSelected, Dash_Status.Text = "Dead Deal")
Set(TabSelected, Dash_Status.Text = "Completed Deal")
Nothing on my Display All button as I haven't figured out how to show this yet.

[yashag2255]

Hi @KimberlyM 

If you wish to sort the data based on created date, then you can set the following config as:

Screen -> OnVisible -> Set(SortByDate,true)

Sort Button -> OnSelect -> Set(SortByDate,!SortByDate)   ->>>>> this will toggle the true and false value for sorting it.

Gallery Items -> Sort(If(!IsEmpty(Filter(Admin_LeadData,FullName = varUser)), Lead_Data, Filter(Lead_Data, AssignedTo.Value = varUser || LeadMember1.Value = varUser || LeadMember2.Value = varUser || LeadMember3.Value = varUser || LeadMember4.Value = varUser)),"createdon",If(SortByDate,Ascending,Descending))

Here, createdon is the name of column in the datasource.

Also, if you wish to add the functionality of sorting this based on selected tab, the expression can be modified to:

Set(TabSelected, SelectedStatus = "Not Started")
Set(TabSelected, SelectedStatus = "In Progress")
Set(TabSelected, SelectedStatus = "Dead Deal")
Set(TabSelected, SelectedStatus = "Completed Deal")

Set(TabSelected, SelectedStatus = "All")

Dash_Status.Text , this is not the correct way to set the property of any control, you have to write the expression on the property itself.

The below expression can be used to show selected tab and the sort by date feature.

Gallery -> Items  -> 

Sort(Filter(If(!IsEmpty(Filter(Admin_LeadData,FullName = varUser)), Lead_Data, Filter(Lead_Data, AssignedTo.Value = varUser || LeadMember1.Value = varUser || LeadMember2.Value = varUser || LeadMember3.Value = varUser || LeadMember4.Value = varUser)),If(SelectedStatus = "All",true,statuscol = SelectedStatus)),"createdon",If(SortByDate,Ascending,Descending))

Here, statuscol is the name of column in the datasource.

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[KimberlyM]

@yashag2255  Am I defining SelectedStatus somewhere? My brain is just not firing today. I am not understanding what I am missing.

[cwebb365]

No just having the Set command anywhere will create the variable. As for sort, you can add Sort() around all your filters for each gallery and plug in another variable that you set on screenload, and have the button on the top bar set that sort ascending / decending. 

[KimberlyM]

Here is what I am seeing (screencap below) - SelectedStatus is unhappy. It keeps saying that it is not recognized. This is set on my gallery's Items property and on each of my tab buttons I have Set(TabSelected, SelectedStatus = "Not Started") respectively (tab button In Progress will be set to "In Progress", etc.) set on the OnSelect property. 

Thank you so much for the time and help on this. I am so close but so lost at the same time right now! 

[cwebb365]

Can't really decipher that, but you aren't using your variable as the status here, you are trying to test that it = "All". You should just have in the filter an && "Whateveryoustatuscolumnis" = SelectedStatus

This way you get a dynamic return to your gallery. You can put all your users in ( ) with the or's then && and another () with the status filter to get your and / or's to work right.

[KimberlyM]

So if I go with this:

If(!IsEmpty(Filter(Admin_LeadData,FullName = varUser)), Lead_Data,
Filter(Lead_Data, (AssignedTo.Value = varUser || LeadMember1.Value = varUser || LeadMember2.Value = varUser || LeadMember3.Value = varUser || LeadMember4.Value = varUser) 
&& (Status = TabSelected))
)

The = has the error "Invalid argument type." So my column is called Status in my SharePoint list and if I add the Lead_Data.Status (my sharepoint list name), or a collection I have  being called as colDash_StatusFilter.Status I get delegation warnings.  I 90% understand what I am doing but I get confused on what to call where yet. Hence my trying of various logical test equations.

[cwebb365]

what column type is Status? If it's choice it should be Status.Value = TabSelected also, you need to make sure to define TabSelected with a Set(TabSelected,"Default") in your onVisable of that screen. "Default" being whatever tab you want to be showing by default.