Patch only month and year in a form

[Anonymous]

Hi,
I have a form for user to submit and when using date picker it will require user to select the day too.  
But, is it possible to only patch month and year for date only column in dataverse? 
I am planning to use a dropdown with months and input field for year.

Would appreciate the help for this thank you!

[WannaBePro · Accepted solution]

Hi @Anonymous ,

All dates are 'interpreted' as a full day, month, year, hours, minutes, seconds by most datasources as a standard - they might just display different parts of it based on how the column is configured, but a date type by definition usually incorporates all those elements.

Having a date type ensures that Date-specific functions can work as expected with dates - like adding or subtracting two dates, or adding days to a date, or getting the month, or day of the week of a certain date - so it's still a good idea to keep the date type.

So a date only column in dataverse still stores the date in the full date format, it just sets anything for time you don't specify as "00" - so if you save a date "21/03/2022" and omit hours, minutes, seconds - it will just save it as "21/03/2022 00:00:00".  You cannot however leave out days, because it's expecting them. 

In your case you can apply the same logic to the days while saving the month and years - provided you pick a default day to be saved (which, considering months have different lengths, is probably easiest to save the 01st as your default day).  You can ignore days and time in your app and hide it from users, but ultimately in the backend you will still be saving an entire date.  You could just save numbers or strings, but stick with dates - trust me it will save you a lot of headaches later.

To test, assuming you have a form connected to dataverse with a date only column in it;

Unlock your date column card on your form and insert two dropdowns like you planned inside the card, one for Months and another for Years.  You can populate the Months dropdown by putting this formula in the Items: property;

AddColumns(Sequence(12), "MonthName", Text(Date(1900, Value, 1), "mmmm"))

Just give your control a name, (like MonthDropDown) so you can reference it easier later.  We ultimately ignore the year and day values here, so 1900 is just a random year and the 1st is a safe day to use as every month has a 01.  This formula allows us to extract the regional month values from the Today() function in a table format we can use - much easier than manually typing them out! 😁

The table created basically looks like this, and we can use it to both display friendly names to the user, and save month values back into our date;

Value	MonthName
1	January
2	February
3	March

For the Years dropdown, you can decide how you want it depending on your needs, but here's a quick and easy way to start our dropdown list 100 years before today and end 100 years after today - put this into the Items: property of the Years dropdown;

Sequence(200, Year(Today())-100) 

Give your Years dropdown a name as well (like YearsDropDown).

You can keep the card's default datepicker control if you like, it doesn't really matter as you can just hide it along with the time dropdowns if they're there - you can also delete everything and change the cards Update: property - but all that is already set up for us, so easier to just keep it and change one or two things.

Remembering that our form will have two modes (Edit and New) and we want to display a default Month/Year if it's New and the existing saved Month/Year value if it's Edit - we therefore want our two new dropdowns to behave like our datepicker initially did.  To do this, we edit their Default: values as follows;

MonthDropDown Default:

Text(Date(1900,Month(If(IsBlank(Parent.Default), Today(), Parent.Default)),1),"mmmm")

This is where storing dates as dates is really helpful, as we can use things like Today() or saved dates to extract days, months or years.  Again, for the month dropdown default on a new form, we don't care about years or days, but we have to put something in there to use the function so 1900 and 1 are good enough, otherwise we use the date that was saved before if it's in Edit mode.

YearDropDown Default:

Year(If(IsBlank(Parent.Default), Today(), Parent.Default))

Now that the new dropdowns are behaving like they should, we should also adjust our datepicker - this way we don't have to mess with the card's Update: property.  

Set the Datepicker's DefaultDate: property to;

Date(YearDropDown.Selected.Value, MonthDropDown.Selected.Value, 1)

So - we have two new dropdowns, defaulting to "today's month and year" when there is no saved value, or showing the saved month and year if there is an existing saved value.  If we save a new or edit an existing, we're only showing months and years, but we're still keeping our date integrity neat on the backend because our existing datepicker is defaulting the day to 1 for any date saved.

Now you can hide the existing datepicker and the other time dropdowns to make space for our new dropdowns.

If you don't like the idea of hidden controls in your card (some people get itchy feet about this, personally I prefer this approach as it's simpler) - you can delete the datepicker and time controls, but then you'll need to tidy up any layout errors which reference those controls and change the Card Update: property to something like this;

Date(YearDropDown.Selected.Value, MonthDropDown.Selected.Value, 1)

I hope this helps you 😁

---

PS: I do this for fun, so I don't really care whether you give it a thumbs up or mark it as a solution or not - just let me know how it goes 👍

[Anonymous]

Hi @WannaBePro ,

This works perfectly! and thank you for the explanation as well, helped me understand the logic better. :)))
Only difference is I used DefaultSelectedItems property for storing the value like so:-

{Value:Text(Date(1900,Month(If(IsBlank(Parent.Default), Today(), Parent.Default)),1),"mmmm")}