cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Code the calendar gallery small dots (circles)

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?:) 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft
Microsoft

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

v-xiaochen-msft_0-1605665666823.png

 

2\ Add a calendar screen and remove redundant controls:

v-xiaochen-msft_1-1605665666826.png

 

 // 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:

v-xiaochen-msft_2-1605665666828.jpeg

 

Best Regards,

Wearsky

View solution in original post

4 REPLIES 4
Resolver III
Resolver III

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.

Frequent Visitor

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:) 

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

DateValue(ThisItem.Value)

Yes, the "Function.Value" is correct.

Microsoft
Microsoft

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

v-xiaochen-msft_0-1605665666823.png

 

2\ Add a calendar screen and remove redundant controls:

v-xiaochen-msft_1-1605665666826.png

 

 // 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:

v-xiaochen-msft_2-1605665666828.jpeg

 

Best Regards,

Wearsky

View solution in original post

Helpful resources

Announcements
News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

Power Apps Community Call

Power Apps Community Call- January

Mark your calendars and join us for the next Power Apps Community Call on January 20th, 8a PST

PP Bootcamp Carousel

Global Power Platform Bootcamp

Dive into the Power Platform stack with hands-on sessions and labs, virtually delivered to you by experts and community leaders.

secondImage

Power Platform Community Conference On Demand

Watch Nick Doelman's session from the 2020 Power Platform Community Conference on demand!

Top Solution Authors
Top Kudoed Authors
Users online (4,487)