Most Recent Filter, With If Condition on two dropdowns

[MollyWeasley89]

Hello, 

I have a gallery that is controlled by 2x filters (with All  and "-" options) by the below code in Items. This was working fine (aside from the fact I couldn't get the gallery to show all upon visiting the page for the first time) - However, now I need to add a First condition to get the Most recent record of each type in a certain category. So, instead of my gallery showing absolutely everything, it will show the most recent of each type selected in the dropdown. I have looked at other threads but have an issue when trying to do multiple conditions. The error it shows me is at the semicolon between a First(Sort(Filter function I was trying and my If statement before. It says operator error, expecting "&, *," etc. I lost this formula part but it was something like First(Sort(Filter(My Data Source, My column), Date Modified, Descending)

This is the current formula I have to control the dropdowns and give me the All and - options. It is missing the component that only shows me the most recent record of each type in a column.

If(
SCDropdown_1.Selected.Value = "-" And PLDropdown_1.Selected.Value = "All",
'MyDataSource',
SCDropdown_1.Selected.Value = "-" And PLDropdown_1.Selected.Value <> "All",
Filter(
'MyDataSource',
'Strategy_Product Line' = PLDropdown_1.Selected.Value
),
SCDropdown_1.Selected.Value <> "-" And PLDropdown_1.Selected.Value = "All",
Filter(
'MyDataSource',
Strategy_Category = SCDropdown_1.Selected.Value
),
SCDropdown_1.Selected.Value <> "-" And PLDropdown_1.Selected.Value <> "All",
Filter(
'MyDataSource',
Strategy_Category = SCDropdown_1.Selected.Value And 'Strategy_Product Line' = PLDropdown_1.Selected.Value
)
)

[TheRobRush]

Try this?

First(
If(
    SCDropdown_1.Selected.Value = "-", 
    Switch(
		PLDropdown_1.Selected.Value,
		"All", 
		'MyDataSource',
		Filter('MyDataSource','Strategy_Product Line' = PLDropdown_1.Selected.Value)
	),
    PLDropdown_1.Selected.Value = "All", 
    Filter('MyDataSource',Strategy_Category = SCDropdown_1.Selected.Value),
    Filter('MyDataSource',And(Strategy_Category = SCDropdown_1.Selected.Value, 'Strategy_Product Line' = PLDropdown_1.Selected.Value))
)
)	

Or

First(
Sort(
If(
    SCDropdown_1.Selected.Value = "-", 
    Switch(
		PLDropdown_1.Selected.Value,
		"All", 
		'MyDataSource',
		Filter('MyDataSource','Strategy_Product Line' = PLDropdown_1.Selected.Value)
	),
    PLDropdown_1.Selected.Value = "All", 
    Filter('MyDataSource',Strategy_Category = SCDropdown_1.Selected.Value),
    Filter('MyDataSource',And(Strategy_Category = SCDropdown_1.Selected.Value, 'Strategy_Product Line' = PLDropdown_1.Selected.Value))
), ID, SortOrder.Descending)
)		

[MollyWeasley89]

Thanks for your response!

That has me close but it is only giving me the most recent for Strategy Category and I want the most recent of the Strategy Product Line that falls underneath Strategy Category. 

So there may be 3 most recents under a strategy category because there are multiple Strategy Product Lines within the Strategy Categories. As it is now, it is only showing me the most recent entry within strategy category and I am missing the most recent of each type within that - the Strategy_Product Line

To simplify the picture:

Strategy Category - is types of pets

Strategy Product Line is refined pet breeds (Yorkie, lab, goldfish etc. ) I want the most recent instance of each pet type (Yorkie, lab, etc) coming into the vet. 

So even if there were 3 yorkies brough in this week - I want the most recent only

Yorkie - 10/15/2023 9am

 Goldfish - 10/14/2023 10am

[TheRobRush]

would you mind using snip on like 5 records of your splist and scribbling out any sensitiveinfo, and then also sending me a snip of the gallery and controls that filter it on screen so i can get a better idea of what you are needing? Makes it easier if I see the data structure. (and notate if any of the columsn are not single line of text)

[MollyWeasley89]

Thanks so much for your response, I sent you a DM of my gallery screenshot and all of my columns are single text, except Modified on (which I am sorting on from date) which is Date and TIme

[TheRobRush · Accepted solution]

Ok per the email, here is an example of the methods you will need to use.

Welcome to Happy Vets, 

Above you will see our client list in descending order by their most recent visit ('Modified On') 

Our client list starts off showing ALL clients with no filtering by default.

I recommend creating this test set on a blank screen so you can play around with the formulas used and really learn how they work

First off I created our data set, Place this code in the onselect of a button to recreate it for your own test

ClearCollect(happyVetsClientList, 
                {'Strategy Category': "Cat", 'Strategy Product Line':"Birman", 'Client Name': "Zelda", 'Modified On': DateValue(Text("08/14/2023"))}, 
                {'Strategy Category': "Cat", 'Strategy Product Line':"Ragdoll", 'Client Name': "Clive", 'Modified On': DateValue(Text("08/14/2023"))}, 
                {'Strategy Category': "Cat", 'Strategy Product Line':"Maine Coon", 'Client Name': "Simba", 'Modified On': DateValue(Text("08/14/2023"))}, 
                {'Strategy Category': "Cat", 'Strategy Product Line':"Maine Coon", 'Client Name': "Bruce", 'Modified On': DateValue(Text("08/14/2023"))}, 
                {'Strategy Category': "Cat", 'Strategy Product Line':"Domestic Short Hair", 'Client Name': "Lucky", 'Modified On': DateValue(Text("08/14/2023"))}, 
                {'Strategy Category': "Cat", 'Strategy Product Line':"Domestic Short Hair", 'Client Name': "Mr Binx", 'Modified On': DateValue(Text("08/14/2023"))}, 
                {'Strategy Category': "Cat", 'Strategy Product Line':"Domestic Short Hair", 'Client Name': "Arthur", 'Modified On': DateValue(Text("08/14/2023"))}, 
                {'Strategy Category': "Cat", 'Strategy Product Line':"Domestic Short Hair", 'Client Name': "Layla", 'Modified On': DateValue(Text("08/14/2023"))}, 
                {'Strategy Category': "Dog", 'Strategy Product Line':"Mixed Breed", 'Client Name': "Chuck", 'Modified On': DateValue(Text("08/14/2023"))}, 
                {'Strategy Category': "Dog", 'Strategy Product Line':"Dachsund", 'Client Name': "Geoff", 'Modified On': DateValue(Text("08/14/2023"))}, 
                {'Strategy Category': "Dog", 'Strategy Product Line':"Doberman", 'Client Name': "Kal", 'Modified On': DateValue(Text("08/14/2023"))}, 
                {'Strategy Category': "Dog", 'Strategy Product Line':"Poodle", 'Client Name': "Bowser", 'Modified On': DateValue(Text("08/14/2023"))}, 
                {'Strategy Category': "Dog", 'Strategy Product Line':"Mixed Breed", 'Client Name': "Rover", 'Modified On': DateValue(Text("08/14/2023"))}, 
                {'Strategy Category': "Dog", 'Strategy Product Line':"Mixed Breed", 'Client Name': "Spot", 'Modified On': DateValue(Text("08/14/2023"))}, 
                {'Strategy Category': "Dog", 'Strategy Product Line':"Mixed Breed", 'Client Name': "Marshall", 'Modified On': DateValue(Text("08/14/2023"))}, 
                {'Strategy Category': "Dog", 'Strategy Product Line':"Mixed Breed", 'Client Name': "Joe", 'Modified On': DateValue(Text("08/14/2023"))}, 
                {'Strategy Category': "Fish", 'Strategy Product Line':"Beta", 'Client Name': "Bubbles", 'Modified On': DateValue(Text("08/14/2023"))}, 
                {'Strategy Category': "Fish", 'Strategy Product Line':"Clown", 'Client Name': "Marlon", 'Modified On': DateValue(Text("08/14/2023"))}, 
                {'Strategy Category': "Fish", 'Strategy Product Line':"Beta", 'Client Name': "Mr Sparkle Pants", 'Modified On': DateValue(Text("08/14/2023"))}, 
                {'Strategy Category': "Fish", 'Strategy Product Line':"Gold", 'Client Name': "Blingy", 'Modified On': DateValue(Text("08/14/2023"))}, 
                {'Strategy Category': "Fish", 'Strategy Product Line':"Gold", 'Client Name': "Standard", 'Modified On': DateValue(Text("08/14/2023"))});

UpdateIf(happyVetsClientList, 'Modified On'= DateValue(Text("08/14/2023")), {'Modified On': DateAdd(DateValue(Text("4/1/2023")), Min(Left(Last(Split(Rand(), ".")).Value,2),120), TimeUnit.Days)})

This places them all in a collection then randomizes their dates all between August 1 and April 1

My two dropdowns up top have the items property of 

Species dropdown

ForAll(
        Sequence(CountRows(Distinct(happyVetsClientList,'Strategy Category'))+1,0,1) As placeMent, 
        Switch(placeMent.Value,
              0, {Value: "-"}, 
              {Value: Last(FirstN(Distinct(happyVetsClientList,'Strategy Category'), placeMent.Value)).Value}))

Breed Dropdown

Switch(
        speciesDropdown.Selected.Value,
        "-",
        
ForAll(
        Sequence(CountRows(Distinct(happyVetsClientList,'Strategy Product Line'))+1,0,1) As placeMent, 
        Switch(placeMent.Value,
              0, {Value: "-"}, 
              {Value: Last(FirstN(Distinct(happyVetsClientList,'Strategy Product Line'), placeMent.Value)).Value})),

ForAll(
        Sequence(CountRows(Distinct(Filter(happyVetsClientList,'Strategy Category'=speciesDropdown.Selected.Value),'Strategy Product Line'))+1,0,1) As placeMent, 
        Switch(placeMent.Value,
              0, {Value: "-"}, 
              {Value: Last(FirstN(Distinct(Filter(happyVetsClientList,'Strategy Category'=speciesDropdown.Selected.Value),'Strategy Product Line'), placeMent.Value)).Value})))

This Automatically generates the dropdowns and adds a default Value of "-" that is selected by default

Our gallery itself uses GroupBy AddColumns and ForAll to narrow down each set as needed, group them by breed, then return the msot recent record from that group only... Here is its code

If(
    speciesDropdown.Selected.Value = "-" && breedDropdown.Selected.Value = "-",
    Sort(happyVetsClientList,'Modified On',SortOrder.Descending) ,
    breedDropdown.Selected.Value = "-", 
        ForAll(
                GroupBy(Filter(Sort(happyVetsClientList,'Modified On',SortOrder.Descending),'Strategy Category'=speciesDropdown.Selected.Value),"Strategy Product Line", "BreedType") As breedGrab, 
                First(AddColumns(breedGrab.BreedType, "Strategy Product Line",breedGrab.'Strategy Product Line'))
                ),
    speciesDropdown.Selected.Value = "-", 
        ForAll(
                GroupBy(Filter(Sort(happyVetsClientList,'Modified On',SortOrder.Descending),'Strategy Product Line'=breedDropdown.Selected.Value),"Strategy Product Line", "BreedType") As breedGrab, 
                First(AddColumns(breedGrab.BreedType, "Strategy Product Line",breedGrab.'Strategy Product Line'))
                ),
         ForAll(
                GroupBy(Filter(Sort(happyVetsClientList,'Modified On',SortOrder.Descending),'Strategy Product Line'=breedDropdown.Selected.Value),"Strategy Product Line", "BreedType") As breedGrab, 
                First(AddColumns(breedGrab.BreedType, "Strategy Product Line",breedGrab.'Strategy Product Line'))
                )
    )

Only cat selected, shows most recent visitor per cat breed 

Selecting a single breed will show you most recent visitor from that breed only

can repeat this general idea to narrow down stuff to most recent only in a group. If it were most recent of a single breed only etc vcould just show last of a filter, but when there are 4 breeds being shown and only want one of each it gets more tricky. so we use groupby