Skip to main content

Notifications

Power Apps - Building Power Apps
Answered

Seeking HELP: To get correct formula in GALLERY ITEMS

(0) ShareShare
ReportReport
Posted on by 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

 

 

  • asuhaimi Profile Picture
    asuhaimi 85 on at
    Re: Seeking HELP: To get correct formula in GALLERY ITEMS

    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

  • iAm_ManCat Profile Picture
    iAm_ManCat 18,129 on at
    Re: Seeking HELP: To get correct formula in GALLERY ITEMS

    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
    Re: Seeking HELP: To get correct formula in GALLERY ITEMS

    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.

  • Verified answer
    iAm_ManCat Profile Picture
    iAm_ManCat 18,129 on at
    Re: Seeking HELP: To get correct formula in GALLERY ITEMS

    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
    Re: Seeking HELP: To get correct formula in GALLERY ITEMS

    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

  • Kosenurm Profile Picture
    Kosenurm 802 on at
    Re: Seeking HELP: To get correct formula in GALLERY ITEMS
    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.

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

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,475

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,767

Leaderboard