Answered

Creating a Dynamic Holiday Collection

(0) Share

Report

Posted on by pfuegy

So far every solution for Holiday's that I have found involves using hard-typed date values (not dynamic functions) when moving into PowerApps, most often using an Excel file as the record source. Formulas in Excel and PowerApps do not get along which means every year I have to manually drive into my Excel file and update the holiday schedule (not really ideal when the point is to automate Holiday dates). My goal here is to create a dynamic Holiday schedule, showing last year, this year, and next year (anyone would be able to use this by altering the dates in the Holiday schedule to match their companies schedules).

I have coded a screen that has DatePicker's with default values to display the holiday dates and I have verified the dates being calculated are correct:

If it makes a difference I could change them from DatePicker to Label? In my mind it made sense to use DatePicker when working with dates.

Currently my strategy is following the method laid out by Reza Dorrani "Add Days to Date exluding weekends & Holidays". I can remove weekends with ease, but when I try to create a collection from my Dynamic PowerApps Holidays (this is where I deviate from his example, and every other example I can find) I run into "PowerApps encountered an error...". I cannot get my collection to recognize my dates.

I have also tried:

DatePicker (just the name of the date picker directly i.e. NewYearsDay_LastYear) (this does work to display the date as a Label but does not work in a collection)
DatePicker.SelectedDate (this does not provide a red error, but does not return a date)
DateValue(Text(DatePicker.Text),"[$-en-US]mm/dd/yyyy")) and DateValue(DatePicker.Text,"[$-en-US]mm/dd/yyyy")) (both do not return a date)
I have assigned the date from the DatePicker to a Global Variable (i.e. varNewYearsDay) and tried calling it up in the collection (i.e. varNewYearsDay.Text or Text(varNewYearsDay.Value) or Value(varNewYearsDay.Text) or DateValue(varNewYearsDay), etc. (none of them returned a date in the collection)

Other things I have tried just to rule them out include trying to link to my Excel file with custom formulas (PowerApps won't talk to Excel formulas). I have tried just putting the equation to calculate the date in directly too.

I do not believe I can make the fields dynamic in SharePoint easily (or it is beyond my capabilities); if this is possible I could just link PowerApps to the dynamic list.

I would really appreciate someone pointing me in the correct direction. I simply do not comprehend or accept that a tool as powerful as PowerApps, and a program so clearly based entirely from Excel, can't be made to easily include NetWorkDays() even if I have to code it out the long and hard way because Microsoft was not diligent. And I do not accept the solutions posted which required hard-typed date values rather than dynamic functions; in a program like this there has to be a way to be dynamic.

Categories:

Building Power Apps

All responses (5)

Answers (1)

Sort by

mdevaney 29,993 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
@pfuegy
Christmas this year

Date(Year(Today(), 12, 25)

Christmas next year

Date(Year(Today()+1, 12, 25)

Christmas 2 years from now

Date(Year(Today()+2, 12, 25)

Note:
I think that trying to calculate holidays is fun as a brain-teaser but it would be far less time just to update the company holidays in a spreadsheet.

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."
pfuegy 6 on at

Like
a
(0)

Report
Copy link

Link copied!

Yes. I already can code the dates to properly calculate as I stated in the original post. What I cannot do is get these values to populate a collection ClearCollect() with the date values.

I am not debating the manual updating of a system versus an automated system.
Verified answer

v-bofeng-msft Microsoft Employee on at

Like
a
(2)

Report
Copy link

Link copied!
Hi @pfuegy :

There are many publicly related APIs on the Internet. You can use them to create a Custom Connector to get your country's holidays.I've made a test:

1\Get the API

You can use google to search for them. In my test, I used the free API provided by https://calendarific.com/

2\Create a custom connectior

\

If you are not sure how to create your own custom connector, I suggest you refer to this article:

Custom connectors for canvas apps

3\Set my canvas app's OnStart property to:

ClearCollect( Holidays, GetHoliday.GetHolidays({api_key:"MY-API-KEY",country:"US",year:"2020"}).response.holidays )

4\Run OnStart

Best Regards,

Bof
mdevaney 29,993 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

@pfuegy
Keep in mind that the solution by @v-bofeng-msft includes a premium connector so you would need to pay for the license in Flow.

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."
pfuegy 6 on at

Like
a
(0)

Report
Copy link

Link copied!

On a whim I decided to push past the "PowerApps ran into an issue..." and collection table filled with "Invalid date" to see if I could use these values even if they were presenting as problematic. The first example shown "ClearCollect(collHolidays,NewYearsDay_LastYear.SelectedDate,...)" indicates the collection has invalid dates when viewing the collection. However, if I just ignore that it has invalid dates, and tell it to evaluate my collDateRange and to remove any values which match collHolidays, sure enough it is actively removing the dates. I was just getting hung up on the fact that my collection was not displaying valid dates, and so I assumed that if it is telling me that I have an invalid date it would not be useful in further date calculations. I made an incorrect (logical?) assumption.