Code the calendar gallery small dots (circles)

[Arime]

Hi, hope someone can help me. 

I have made a calendar gallery  based on a SharePoint list. I wanna code the small dots under the dates to show only if its meets specific criteria in a column on a SharePoint list. 

I have a list with 

Name; DateFrom; DateTo; Functions

In the functions column you must choose from a dropdown list, you can choose; Yellow, Blue and Red. 

I want that the dot under the date only to be visible if i do not have; 2 persons that have functions Yellow, 3 persons have functions Blue, and one person have the function Red. 

Can someone help?:) 

[Jacky_Shen]

Please try this:

If(And(CountRows(Filter(DataSource,And(Functions="Yellow",ThisItem.Date>DateFrom,ThisItem.Date<DateTo)))=2,CountRows(Filter(DataSource,And(Functions="Blue",ThisItem.Date>DateFrom,ThisItem.Date<DateTo)))=3,CountRows(Filter(DataSource,And(Functions="Red",ThisItem.Date>DateFrom,ThisItem.Date<DateTo)))=1),true,false)

This is for the property "Visible" of the Circle.

[Arime]

Thanks for trying! 

I got some error on ThisItem.Date when i use this i get "name isn't valid" i changes to ThisItem.Value, that remove the error, but then i got a "operation is invalid" Server response: String was not recognized as a valid DateTime.  

I also got and error on "Function=" but the error was clear when i used "Function.value="

But thanks for trying:) 

[Jacky_Shen]

Probably you need to convert the value into a data value using:

DateValue(ThisItem.Value)

Yes, the "Function.Value" is correct.

[v-xiaochen-msft · Accepted solution]

Hi @Arime ,

Could you tell me: 

1. Do you want the dot to be visible when the following conditions are all true?

• A certain day is within the date range of two yellow function records.
• A certain day is within the date range of three blue function records.
• A certain day is within the date range of one red function record.

1. What are the data types of DateForm, DateTo, and Functions? (Are their types Date and time, Date and time and choice?)
2. What data type is the date in your gallery?( Is it a date type?)
3. What does ‘Person’ mean?(Whether a record refers to a person?) 

If so, the key is to find the intersection of dates and use if() function on the visible property of the dots.

I assumed some conditions and did a test for your reference: 

I assumed: 

• Fields’ data types in list are Date and time, Date and time and choice.
• The date’s data type in my gallery is date.

1\ Show my test data source

2\ Add a calendar screen and remove redundant controls:

 // required controls

3\ Modify the visible property of ‘LblMonthSelected2’ control:

True

4\ Modify the visible property of ‘MonthDayGallery2’ control:

True

5\ Modify the visible property of ‘iconPrevMonth2’ control:

True

6\ Modify the visible property of ‘iconNextMonth2’ control:

True

7\ Modify the visible property of ‘Title6’ control:

True

8\ Modify the onselect property of ‘iconPrevMonth2’ control:

Set(_firstDayOfMonth, DateAdd(_firstDayOfMonth, -1, Months));          

Set(_firstDayInView, DateAdd(_firstDayOfMonth, -(Weekday(_firstDayOfMonth) - 2 + 1), Days));          

Set(_lastDayOfMonth, DateAdd(DateAdd(_firstDayOfMonth, 1, Months), -1, Days));                    

Set(_minDate, _firstDayInView)

9\ Modify the onselect property of ‘iconNextMonth2’ control:

Set(_firstDayOfMonth, DateAdd(_firstDayOfMonth, 1, Months));          

Set(_firstDayInView, DateAdd(_firstDayOfMonth, -(Weekday(_firstDayOfMonth) - 2 + 1), Days));                 

Set(_lastDayOfMonth, DateAdd(DateAdd(_firstDayOfMonth, 1, Months), -1, Days));              

Set(_maxDate, DateAdd(_firstDayInView, 40, Days))

10\ Set App’s onstart property to:

Set(_dateSelected, Today());        

Set(_firstDayOfMonth, DateAdd(Today(), 1 - Day(Today()), Days));        

Set(_firstDayInView, DateAdd(_firstDayOfMonth, -(Weekday(_firstDayOfMonth) - 2 + 1), Days));        

Set(_lastDayOfMonth, DateAdd(DateAdd(_firstDayOfMonth, 1, Months), -1, Days))         

11\ Modify the Visible property of ‘Circle3’ control:

If(

    CountRows(

        Filter(

            calendar,                       // my test list

            Function.Value = "Yellow",

            DateAdd(

                _firstDayInView,

                ThisItem.Value

            ) >= DateFrom,

            DateAdd(

                _firstDayInView,

                ThisItem.Value

            ) <= DateTo

        )

    ) = 2 && CountRows(

        Filter(

            calendar,

            Function.Value = "Blue",

            DateAdd(

                _firstDayInView,

                ThisItem.Value

            ) >= DateFrom,

            DateAdd(

                _firstDayInView,

                ThisItem.Value

            ) <= DateTo

        )

    ) = 3 && CountRows(

       Filter(

            calendar,

            Function.Value = "Red",

            DateAdd(

                _firstDayInView,

                ThisItem.Value

            ) >= DateFrom,

            DateAdd(

                _firstDayInView,

                ThisItem.Value

            ) <= DateTo

        )

    ) = 1,

    true,

    false

)

12\ Show my test calendar gallery:

Best Regards,

Wearsky