cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Axeum
Helper I
Helper I

Formula Help (Starts With & Filters)

Good Morning, 

I'm struggling with a formula for my Gallery and I was hoping somebody here could assist me. Formula is as follows;

 

SortByColumns(
StartsWith("Medical",'Appointment Type',
Filter('CarePlan Appointment Log',
('Date of Appointment' >= 'Start Date_1'.SelectedDate) && ('Date of Appointment' <= 'End Date_1'.SelectedDate),
CustomID = VarEmpID)),
"Created", Ascending)

The SharePoint List will easily have a few thousand entries so I know it needs to begin with 'StartsWith' to avoid delegation issues, but from the matching rows it brings into the PowerApp, I then need it to filter based on a CustomID column and to 2 Date Selectors as criteria. 

I'm not too sure where I'm going wrong with this formula. 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @Axeum ,

I just noticed your StartsWith() is the wrong way around - should be

StartsWith(
   'Appointment Type',
   "Medical"
) 

This is fully Delegable (as is the rest of your statement although you had a comma in the wrong place).

SortByColumns(
   Filter(
      'CarePlan Appointment Log',
      StartsWith(
         'Appointment Type',
         "Medical"
      ) &&
      'Date of Appointment' >= 'Start Date_1'.SelectedDate &&
      'Date of Appointment' <= 'End Date_1'.SelectedDate &&
      CustomID = VarEmpID
   ),
   "Created"
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

 

View solution in original post

9 REPLIES 9
WarrenBelz
Super User III
Super User III

Hi @Axeum ,

Something like this should do it

 

SortByColumns(
   Filter(
      'CarePlan Appointment Log',
      StartsWith(
         'Appointment Type',
         "Medical"
      ) &&  
      'Date of Appointment' >= 'Start Date_1'.SelectedDate &&
      'Date of Appointment' <= 'End Date_1'.SelectedDate &&
      CustomID = VarEmpID
   ),
   "Created"
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

ganeshsanap
Super User
Super User

@Axeum  Try using this formula: 

 

 

SortByColumns(
	Filter(
		'CarePlan Appointment Log',
		StartsWith('Appointment Type', "Medical") && 'Date of Appointment' >= 'Start Date_1'.SelectedDate && 'Date of Appointment' <= 'End Date_1'.SelectedDate && CustomID = VarEmpID
	),
	"Created", Ascending
)

 

 

Considering column name is Appointment Type.


Please click Accept as solution & ‌‌👍 if my answer helped you to solve your issue. This will help others to find the correct solution easily. If the answer was useful in other ways, please consider giving it ‌‌👍

 

Best Regards,

Ganesh Sanap

Blog site 

Thank You!

The formula works but with 'StartWith' taking place after the 'Filter' will I not be hit with the delegation issues?

From memory, Filters pulls in the first 2000 rows from a source and filters that, whilst 'StartWith' searches the source and only pulls in the first 2000 relevant rows. 

Hi @Axeum ,

StartsWith is fully Delegable, so there are no issues there. Dates are now also Delegable (or so it seems), so your formula should work without any issues.

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Axeum
Helper I
Helper I

Hello all, 

Perhaps I need to start my problem with more clarity. 

I have a SharePoint List that I know will contain a minimum of 10,000 rows. Thus, I need a formula that avoids all delegation problems. 

I know that using 'Filter(Source, Logical Test)' pulls in as much of the source as it can (Rows 1-2000) and then applies the logical test on those rows but 10,000 rows this isn't useful, especially since the newer (More important) data will be at the bottom entries. 

The only other option I know of is using 'StartsWith(Text, Start)' but this has proven problematic since it has no Source in its requirements. 

I've been using 'SortByColumns(Source,Column)' as I have done in all my other Galleries and hoped that using this Source, I could then go into a 'StartWith' but I've not been able to make that work either. 

My current formula (thanks to earlier help) is;

SortByColumns(
Filter('CarePlan Appointment Log',
StartsWith( "Medical",'Appointment Type') &&
'Date of Appointment' >= 'Start Date_1'.SelectedDate &&
'Date of Appointment' <= 'End Date_1'.SelectedDate &&
CustomID = VarEmpID
),"Created")

Ideally... I need a formula that goes. 

SortByColumns(Source
StartWith(Appointment Type = Medical & CustomID = VarEMPID),
//Of this selection
Filter(Between Start&End Date),
Descending Date Order)


Any help in resolving this would be great

Sorry Warren, 

With the filter aspect going first, it's coming up with the delegation problem on the StartsWith.


Axeum_0-1624270524122.png

 

Hi @Axeum ,

I just noticed your StartsWith() is the wrong way around - should be

StartsWith(
   'Appointment Type',
   "Medical"
) 

This is fully Delegable (as is the rest of your statement although you had a comma in the wrong place).

SortByColumns(
   Filter(
      'CarePlan Appointment Log',
      StartsWith(
         'Appointment Type',
         "Medical"
      ) &&
      'Date of Appointment' >= 'Start Date_1'.SelectedDate &&
      'Date of Appointment' <= 'End Date_1'.SelectedDate &&
      CustomID = VarEmpID
   ),
   "Created"
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

 

View solution in original post

@WarrenBelz Exactly, syntax of StartsWith is: 

 

StartsWith( Text, StartText )

 

This is what I have written in my answer above.

 

But I think @Axeum didn't try my answer/suggestion yet 😄

 

Microsoft documentationEndsWith and StartsWith functions in Power Apps 


Please click Accept as solution & ‌‌👍 if my answer helped you to solve your issue. This will help others to find the correct solution easily. If the answer was useful in other ways, please consider giving it ‌‌👍

 

Best Regards,

Ganesh Sanap

Blog site 

Thank you @WarrenBelz You're a life saver

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Users online (1,082)