Answered

Text Label won't reset to hidden based on Datepicker/Count Rows/Filter Formula

(1) Share

Report

Posted on by RichardRhys

Hello all, I have a situation in a Canvas App using a date picker where I want to display a message if a user chooses a date that already appears certain number of times in a SharePoint list column. This message asks the user to choose another date. I found this great formula to filter and count appearances of a selected date picker date, and it seems to work well in the Visible property:

If(CountRows(Filter('Correspondence/Mail Request Tracker',DataCardValue111.SelectedDate in Text ('ColumnTitle')))<6,false, true)

If the user chooses a date that already appears in the column 6 or more times, my label successfully displays ("This day is fully booked - please select another date"), and when the user selects a date with fewer than 6 occurrences in that column, the message becomes hidden.

Here's the problem: After submitting the form, the text label is displaying - before any date is selected in the date picker. All other similar hidden items in the app are resetting correctly (beginning as hidden), but this label doesn't want to default to hidden display when beginning the form. Could it be something to do with the Count Rows/Filter formula?

Thanks for any help!

@datepicker @countrows

All responses (9)

Answers (1)

Mitanshu 1,614 Super User 2025 Season 1 on at

Like (1)

Report
Re: Text Label won't reset to hidden based on Datepicker/Count Rows/Filter Formula

Delegation warning is a warning and not an error.

You can change settings to 2000, so that impact of delegation is not seen in your App under 2000 rows.

Also update your Visible property of label to below:

If( With( { baseTable: Filter('Correspondence/Mail Request Tracker',DataCardValue111.SelectedDate in Text ('ColumnTitle')) }, CountRows(baseTable) ) > 6 || IsBlank(DataCardValue111.SelectedDate) , false, true )
Mitanshu 1,614 Super User 2025 Season 1 on at

Like (1)

Report
Re: Text Label won't reset to hidden based on Datepicker/Count Rows/Filter Formula

For the submit button of your form, can you try to update the DisplayMode property as:

If ( IsBlank(DataCardValue111.SelectedDate) || CountRows(Filter('Correspondence/Mail Request Tracker',DataCardValue111.SelectedDate in Text ('ColumnTitle'))) > 6, DisplayMode.Disabled, DisplayMode.Edit )

This will disable the button if any of the following is true:

- No date selected by user

- Date selected is already in your SharePoint list 6 times.
RichardRhys on at

Like (1)

Report

Re: Text Label won't reset to hidden based on Datepicker/Count Rows/Filter Formula

Also, if experienced with referencing SharePoint in this way, I do get a "Delegation Warning" :
Can I ignore this if working with small data sets - maybe less t
han 500 rows?
RichardRhys on at

Like (1)

Report

Re: Text Label won't reset to hidden based on Datepicker/Count Rows/Filter Formula

Thanks @Michaelfp, looks like it the formula correction worked! I did set the default date to blank and that likely helped the formula to work as well
RichardRhys on at

Like (1)

Report

Re: Text Label won't reset to hidden based on Datepicker/Count Rows/Filter Formula

This formula totally worked for me! Thanks so much - I will study it to see if I can also block the form submission based on an "overbooked" selected date

Thanks @happyume
MichaelFP 1,831 Super User 2025 Season 1 on at

Like (1)

Report

Re: Text Label won't reset to hidden based on Datepicker/Count Rows/Filter Formula

You need to set to false again the label, instead to apply that direct on the visible property, create a global or context variable to handle data, so will be able to hide and show easily.

For example, in the onVisible of the page that load the form you set the variable with your count rows formula, and when you request to reset the form you just need to set to false. you label will work fines in that way.

If my answer helped you, please give me a thumbs up (👍). If solve your question please mark as solution ✔️. This is help the community.
RichardRhys on at

Like (1)

Report

Re: Text Label won't reset to hidden based on Datepicker/Count Rows/Filter Formula

yep, this is all in a form. That makes sense, but just not sure how to make it reset!

Is there another way to hide the label (other than using the visible property drop down - since it's already in use)?
Thank you
Verified answer

Mitanshu 1,614 Super User 2025 Season 1 on at

Like (2)

Report
Re: Text Label won't reset to hidden based on Datepicker/Count Rows/Filter Formula

Have you tried with putting DefaultDate property of the DatePicker control to blank.

Then you can check as below:

If ( IsBlank(DataCardValue111.SelectedDate) || CountRows(Filter('Correspondence/Mail Request Tracker',DataCardValue111.SelectedDate in Text ('ColumnTitle'))) < 6, false, true )
MichaelFP 1,831 Super User 2025 Season 1 on at

Like (1)

Report

Re: Text Label won't reset to hidden based on Datepicker/Count Rows/Filter Formula

So you are using a Form right? I this label is from a custom card or from a card data came with the fields selected?

If is a custom card you must control the reset, because is not part of the field and the form is not able to control that. Because i believe that you don't have a Parent.Default value that came from the collection or data source.

If my answer helped you, please give me a thumbs up (👍). If solve your question please mark as solution ✔️. This is help the community.