cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
simms7400
Post Prodigy
Post Prodigy

Issue with SortByColumns and Filter; can someone explain this behavoir?

Hi Folks -

 

I have the following logic for my Gallery and it works as expected:

 

 

SortByColumns(
        Filter(rdActivity, Parent_Node = PFPParent,
			Or(
				StartsWith(Alias, PFPSearchBox.Text) ||
				StartsWith(Name, PFPSearchBox.Text)
            )),
"Alias", If(SortDescending1, Descending, Ascending)
)

 

 

 

Note, the rdActivity table has 9K+ records but I included the Filter on the PFPParent as that reduces the total scope to less than 100 records.   

 

So, there are other fields in the List I also want to filter on.  So when I added the additional fields, nothing is returned in the gallery:

 

 

SortByColumns(
        Filter(rdActivity, Parent_Node = PFPParent,
			Or(
				StartsWith(Alias, PFPSearchBox.Text) ||
				StartsWith(Name, PFPSearchBox.Text) ||
	            StartsWith(RequestStatus.Value, PFPSearchBox.Text)  ||
                StartsWith(Status.Value, PFPSearchBox.Text) ||
                StartsWith(Created, PFPSearchBox.Text) ||
                StartsWith(Modified, PFPSearchBox.Text) ||
                StartsWith(Time_Tracking.Value, PFPSearchBox.Text) ||
                StartsWith(Research_DDU.Value, PFPSearchBox.Text) ||
                StartsWith(PrePostCS.Value, PFPSearchBox.Text) ||
                StartsWith(Direct_Indirect.Value, PFPSearchBox.Text) ||
                StartsWith(Phase.Value, PFPSearchBox.Text) ||
                StartsWith(Project_Type.Value, PFPSearchBox.Text) ||
                StartsWith(Model_Flag_CMC.Value, PFPSearchBox.Text) ||
                StartsWith(Model_Flag_DEV.Value, PFPSearchBox.Text) ||
                StartsWith(Model_Flag_CMC.Value, PFPSearchBox.Text) ||
                StartsWith(Model_Flag_DEV.Value, PFPSearchBox.Text) ||
                StartsWith(Model_Flag_PRD.Value, PFPSearchBox.Text)
            )),
"Alias", If(SortDescending1, Descending, Ascending)
)

 

 

Even though I have the Parent_Node = PFPParent lgic, is it still choking on the total table records? I just don't get it.   Any suggestions?

1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User
Super User

@simms7400 

Please consider changing your Formula to the following:

SortByColumns(
    With({_items:
        Filter(rdActivity, Parent_Node = PFPParent)
        },
        Filter(_items,
            StartsWith(Alias, PFPSearchBox.Text) ||
            StartsWith(Name, PFPSearchBox.Text) ||
            StartsWith(RequestStatus.Value, PFPSearchBox.Text)  ||
            StartsWith(Status.Value, PFPSearchBox.Text) ||
            StartsWith(Created, PFPSearchBox.Text) ||
            StartsWith(Modified, PFPSearchBox.Text) ||
            StartsWith(Time_Tracking.Value, PFPSearchBox.Text) ||
            StartsWith(Research_DDU.Value, PFPSearchBox.Text) ||
            StartsWith(PrePostCS.Value, PFPSearchBox.Text) ||
            StartsWith(Direct_Indirect.Value, PFPSearchBox.Text) ||
            StartsWith(Phase.Value, PFPSearchBox.Text) ||
            StartsWith(Project_Type.Value, PFPSearchBox.Text) ||
            StartsWith(Model_Flag_CMC.Value, PFPSearchBox.Text) ||
            StartsWith(Model_Flag_DEV.Value, PFPSearchBox.Text) ||
            StartsWith(Model_Flag_CMC.Value, PFPSearchBox.Text) ||
            StartsWith(Model_Flag_DEV.Value, PFPSearchBox.Text) ||
            StartsWith(Model_Flag_PRD.Value, PFPSearchBox.Text)
        )
    ),
    "Alias", If(SortDescending1, Descending, Ascending)
)

 

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

6 REPLIES 6
RandyHayes
Super User
Super User

@simms7400 

Please consider changing your Formula to the following:

SortByColumns(
    With({_items:
        Filter(rdActivity, Parent_Node = PFPParent)
        },
        Filter(_items,
            StartsWith(Alias, PFPSearchBox.Text) ||
            StartsWith(Name, PFPSearchBox.Text) ||
            StartsWith(RequestStatus.Value, PFPSearchBox.Text)  ||
            StartsWith(Status.Value, PFPSearchBox.Text) ||
            StartsWith(Created, PFPSearchBox.Text) ||
            StartsWith(Modified, PFPSearchBox.Text) ||
            StartsWith(Time_Tracking.Value, PFPSearchBox.Text) ||
            StartsWith(Research_DDU.Value, PFPSearchBox.Text) ||
            StartsWith(PrePostCS.Value, PFPSearchBox.Text) ||
            StartsWith(Direct_Indirect.Value, PFPSearchBox.Text) ||
            StartsWith(Phase.Value, PFPSearchBox.Text) ||
            StartsWith(Project_Type.Value, PFPSearchBox.Text) ||
            StartsWith(Model_Flag_CMC.Value, PFPSearchBox.Text) ||
            StartsWith(Model_Flag_DEV.Value, PFPSearchBox.Text) ||
            StartsWith(Model_Flag_CMC.Value, PFPSearchBox.Text) ||
            StartsWith(Model_Flag_DEV.Value, PFPSearchBox.Text) ||
            StartsWith(Model_Flag_PRD.Value, PFPSearchBox.Text)
        )
    ),
    "Alias", If(SortDescending1, Descending, Ascending)
)

 

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

HI Randy -

 

Thank you so much, that worked!  Is the purpose of the first "With" filter to get the results based on that condition (since rdActivity has so many records) AND THEN use filter the other fields as Step 2?  I'm assuming I should update all my gallery with the With logic for this situation?  

 

Thanks again!

RandyHayes
Super User
Super User

@simms7400 

Yes, you mentioned that the result of your "pre-filter" was less than 100 records.  So the With function will create a "with scoped" variable (in this case _items) that contains those records.  Then the formula in the With scope will perform the filter against those records.

Incidentally, you can do (what would be normally non-delegable) functions in the with formula.  So, if you really need to search the column, you can use the Search function or the in operator (which are all normally not supported for delegation).  But, since you have already gathered the records in the pre-filter, there is no further delegation needed and you can do what you want.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
simms7400
Post Prodigy
Post Prodigy

Thank you, Randy, that makes sense!  

 

So in this case, it says my "Not" is not delagable. What would be a way I could get around it? Do I need to store the "Terminated" in a Context variable and then use it?

ClearCollect(
		PPSList,
			ShowColumns(
				Filter(rdPPSTarget,	Not(Status.Value="Terminated")),
				"Alias","Name"
				)
	)
RandyHayes
Super User
Super User

@simms7400 

You can't really get around that one easily as Not is not delegable.

 

If you have an ability to pre-filter the rdPPSTarget data with something that IS delegable, then you can utilize the same method from the earlier post response.  

i.e

ClearCollect(
    PPSList,
    ShowColumns(
         With({_preFilter: Filter(rdPPSTarget, <delegableCriteria>)},
             Filter(rdPPSTarget, Not(Status.Value="Terminated"))
         ),
         "Alias","Name"
    )
)

If there is no pre-filter criteria, then an alternative means of doing this is needed.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
simms7400
Post Prodigy
Post Prodigy

Randy -

 

Thank you so much as usual. This has been a huge help and learning experience for me.

 

Have a great night!

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors
Top Kudoed Authors
Users online (1,146)