web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

Welcome to the Power Platform Communities
Sign Up for Ask a Community Pro Today!
Share your business problems and get Power Platform leaders' perspectives!
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Apps / Seeking HELP: To get c...
Power Apps
Answered

Seeking HELP: To get correct formula in GALLERY ITEMS

(0) ShareShare
ReportReport
Posted on by asuhaimi 85

Hello,

 

I've added new column (Year) in LISTs. I'm using Calculated column (ref Date Start) for Year. My formula was error after added dropdown Year filtering on below formula. Please help to correct and able to sort base on Year.

Appreciate your help

Best Regards

MIE

 

***********************************************

Sort(
Filter(
'Greyhound 2022 - VOICE CORE - Activity',
(StartsWith(
'WORK ITEMS',
txtPicWorkItemsVoiceCoreActivity.Text
) || StartsWith(
PIC.DisplayName,
txtPicWorkItemsVoiceCoreActivity.Text
) || StartsWith(
YEAR,
txtPicWorkItemsVoiceCoreActivity.Text
)) && (STATUS.Value = drpSTATUSVoiceCoreActivity.Selected.Value || drpSTATUSVoiceCoreActivity.Selected.Value = Blank()) && ('SUB-SECTION'.Value = drpSUBSECTIONVoiceCoreActivity.Selected.Value || drpSUBSECTIONVoiceCoreActivity.Selected.Value = Blank()) && (WEEK.Value = drpWEEKVoiceCoreActivity.Selected.Value || drpWEEKVoiceCoreActivity.Selected.Value = Blank()) && (MONTH.Value = drpMONTHVoiceCoreActivity.Selected.Value || drpMONTHVoiceCoreActivity.Selected.Value = Blank())
),
Modified,
Descending
)

******************************************************************

would like to add/join above Gallery formula with  the Input Text searching for name and week, lets say using StartWith.

Note: 

asuhaimi_0-1676365016111.png

 

 

Categories:
Building Power Apps
I have the same question (0)
  • Kosenurm Profile Picture
    Kosenurm 802 on at 
    Sort(
 Filter(
 'Greyhound 2022 - VOICE CORE - Activity',
 (StartsWith('WORK ITEMS', txtSearchBox.Text) || StartsWith(PIC.DisplayName, txtSearchBox.Text)) &&
 (YEAR.Value = txtYearFilter.Text || txtYearFilter.Text = Blank()) &&
 (STATUS.Value = drpSTATUSVoiceCoreActivity.Selected.Value || drpSTATUSVoiceCoreActivity.Selected.Value = Blank()) &&
 ('SUB-SECTION'.Value = drpSUBSECTIONVoiceCoreActivity.Selected.Value || drpSUBSECTIONVoiceCoreActivity.Selected.Value = Blank()) &&
 (WEEK.Value = drpWEEKVoiceCoreActivity.Selected.Value || drpWEEKVoiceCoreActivity.Selected.Value = Blank()) &&
 (MONTH.Value = drpMONTHVoiceCoreActivity.Selected.Value || drpMONTHVoiceCoreActivity.Selected.Value = Blank())
 ),
 Modified,
 Descending
)

     

    In this formula, the txtSearchBox control is used to search for the name, and the txtYearFilter control is used to search for the year. The StartsWith function is used to check if the name or display name starts with the search text, and the YEAR.Value column is used to check if the year matches the search text.

    Note that the YEAR column should be of the "Number" data type, not a calculated column, to enable sorting based on year.

  • asuhaimi Profile Picture
    asuhaimi 85 on at

    Hi @Kosenurm . Thanks for your fast response.. Then how I'm gonna to change the Year column format as Number data type if not using Calculated cause I'm still referring the Date Start.

     

    Best Regards

  • Verified answer
    iAm_ManCat Profile Picture
    iAm_ManCat 18,256 Most Valuable Professional on at

    Have you put your columns into a gallery to see what the result looks like?

    When I created a calculated column with my dateTime field using a Text calculated column, it presents it as the numeric representation of the date, could you confirm that the calculated column values are showing correctly in a gallery and could you post a screenshot of that if possible?

     

    iAm_ManCat_0-1676376535732.png

     

    Sort(
 Filter(
 'Greyhound 2022 - VOICE CORE - Activity',
 (
 StartsWith('WORK ITEMS', txtPicWorkItemsVoiceCoreActivity.Text) 
 || 
 StartsWith(PIC.DisplayName, txtPicWorkItemsVoiceCoreActivity.Text) 
 ||
 StartsWith(YEAR, txtPicWorkItemsVoiceCoreActivity.Text)
 //Added this so that this will only search if the length is greater than zero
 ||
 Len(txtPicWorkItemsVoiceCoreActivity.Text)=0
 )
 &&
 (
 STATUS.Value = drpSTATUSVoiceCoreActivity.Selected.Value 
 || 
 drpSTATUSVoiceCoreActivity.Selected.Value = Blank()
 )
 &&
 (
 'SUB-SECTION'.Value = drpSUBSECTIONVoiceCoreActivity.Selected.Value 
 || 
 drpSUBSECTIONVoiceCoreActivity.Selected.Value = Blank()
 ) 
 && 
 (
 WEEK.Value = drpWEEKVoiceCoreActivity.Selected.Value 
 || 
 drpWEEKVoiceCoreActivity.Selected.Value = Blank()
 ) 
 && 
 (
 MONTH.Value = drpMONTHVoiceCoreActivity.Selected.Value 
 || 
 drpMONTHVoiceCoreActivity.Selected.Value = Blank()
 )
 ),
 Modified,
 Descending
)

     

  • asuhaimi Profile Picture
    asuhaimi 85 on at

    Hi @iAm_ManCat , thanks for response.

     

    Its seem working now. I still used Calculated and used Single Line Text for YEAR column datatype. The only issue now is Delegation warning..

     

    Thanks a lot for your help.. If you any idea to avoid the delegate much appreciated.

    Best Regards.

  • iAm_ManCat Profile Picture
    iAm_ManCat 18,256 Most Valuable Professional on at

    Yes, unfortunately the delegation warning will be because you've used a calculated column - you should look at replacing this with a single line of text column that uses the same logic you did for the column inside your App, and then writes to it from within the App.

     

    You can also wrap the non-delegable filter outside another delegable filter, but this doesn't solve the issue, it just separates it from the delegable parts so that that's handled first. To solve it you will need to create a text column and fill it within your app using the same logic you do for the calculated column.

     

    Here's the outside filter, but again, this won't solve the delegation, just make it less likely to apply (assuming the inner filter is valid and has items in it that reduce the results of the inner filter to 2000 items or less)

    Sort(
 Filter(
 Filter(
 'Greyhound 2022 - VOICE CORE - Activity',
 
 (
 STATUS.Value = drpSTATUSVoiceCoreActivity.Selected.Value 
 || 
 drpSTATUSVoiceCoreActivity.Selected.Value = Blank()
 )
 &&
 (
 'SUB-SECTION'.Value = drpSUBSECTIONVoiceCoreActivity.Selected.Value 
 || 
 drpSUBSECTIONVoiceCoreActivity.Selected.Value = Blank()
 ) 
 && 
 (
 WEEK.Value = drpWEEKVoiceCoreActivity.Selected.Value 
 || 
 drpWEEKVoiceCoreActivity.Selected.Value = Blank()
 ) 
 && 
 (
 MONTH.Value = drpMONTHVoiceCoreActivity.Selected.Value 
 || 
 drpMONTHVoiceCoreActivity.Selected.Value = Blank()
 )
 ),
 (
 StartsWith('WORK ITEMS', txtPicWorkItemsVoiceCoreActivity.Text) 
 || 
 StartsWith(PIC.DisplayName, txtPicWorkItemsVoiceCoreActivity.Text) 
 ||
 StartsWith(YEAR, txtPicWorkItemsVoiceCoreActivity.Text)
 //Added this so that this will only search if the length is greater than zero
 ||
 Len(txtPicWorkItemsVoiceCoreActivity.Text)=0
 )
 ),
 Modified,
 Descending
)

     

  • asuhaimi Profile Picture
    asuhaimi 85 on at

    Hi @iAm_ManCat 

     

    For the time being i'm ok due to delegation issue.. Will test again later.. For the time being at least my colleague can use it for better filtering.. Thanks again.. 

     

    Best Regards

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Welcome to the Power Platform Communities
Sign Up for Ask a Community Pro Today!
Share your business problems and get Power Platform leaders' perspectives!

Quick Links

Forum hierarchy changes are complete!

In our never-ending quest to improve we are simplifying the forum hierarchy…

Kudos to our 2025 Community Spotlight Honorees

Congratulations to our 2025 community superstars!

Congratulations to the December Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Apps

#1
Kalathiya Profile Picture

Kalathiya 372 Super User 2026 Season 1

#2
WarrenBelz Profile Picture

WarrenBelz 303 Most Valuable Professional

#3
MS.Ragavendar Profile Picture

MS.Ragavendar 238 Super User 2026 Season 1

Last 30 days Overall leaderboard

Featured topics

Auto-Populate field Power Pages
Gallery Tabs - Show or Hide Tabs based on Field Value
How to patch multiple records at once
Enable Copilot for end users in model-driven apps
Question for everyone : How are you using Create Text GPT in AI Builder?
Community content - sample components, blogs etc. - Link to this page (http://aka.ms/PCFdemos)
Welcome to the Power Apps forum!

Product updates

Microsoft Power Platform Community release plans