First Day of the month

[NeoInJS]

Hi - how can I set the my Date field in a Submission to have a default value set to the first day of the current month?

[MayMusic]

You can change the form element of your field to be a Calculated Value on your submission page and use the formula below:

 

DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)

 

[Aether]

Hello @NeoInJS,

For you to be able to get the first day of the month, you should use "calculated value" as the form element then paste this formula:
 

CONVERT(DATETIME, (CAST(Month(SysUTCDateTime()) AS VARCHAR) + "/01/" + CAST(Year(SysUTCDateTime()) AS VARCHAR)), 101)

I hope this helps.

~WatashiwaJin~

[NeoInJS]

Thank @WatashiwaJin. However, I would still like my users to be able to change the field. I want to see that field populated with the First day of the month when the DataPage loads.

[DefinitelyNot31337]

Hi @NeoInJS,

 

Having the date field default to first day of the month, and have the field be editable at the same time is currently not possible in Submission Forms without JavaScript customization.

 

Too keep things simple in cases of "editable-default" value , I usually just setup a Virtual Field that is a placeholder of the custom user-input value, then have a Calculated Value for the actual field to be either the "user-input value", or the default value if it is blank.

 

You may do so by following these instructions:

1.) Create a Virtual Field that will hold the user-input values. (I would suggest creating a not for your users that say "Defaults to first day of the month").

2.) For the actual date/time field, you may set the Form Element to a "Calculated Value", then use the syntax below (For this example, I assumed that you use Virtual 1 as your placeholder):

CASE WHEN DATALENGTH('[@cbParamVirtual1]') > 0

THEN
TRY_CONVERT(date, '[@cbParamVirtual1]')

ELSE
DateAdd(
  day,
  1 - DatePart(day, '[@cbTimestamp*]'),
  '[@cbTimestamp*]'
)

END

 

Hope this helps.

 

-DN31337

 

[Aether]

17 hours ago, NeoInJS said:

Thank @WatashiwaJin. However, I would still like my users to be able to change the field. I want to see that field populated with the First day of the month when the DataPage loads.

Hi @NeoInJS -- if that is the case then I suggest that you follow these steps:

- Add a "Virtual field" that uses "Calculated value" as the form element and paste the formula:
 

CONVERT(DATETIME, (CAST(Month(SysUTCDateTime()) AS VARCHAR) + "/01/" + CAST(Year(SysUTCDateTime()) AS VARCHAR)), 101)

- You can also hide the Virtual field -> go to the advance option then check "Hide Field:

- Now, we are going to create a script that will get the value form the formula. For us to do that we need to add a "Header and Footer" -> then in the footer paste this code:
 

<script type="text/javascript">
document.addEventListener('DataPageReady', function (event) {
 
  var vrt = document.querySelector('[name*=cbParamVirtual1]');
  var date = document.querySelector('[name*=InsertRecordYOURDATEFIELD]');
 
  vrt.onchange = function() {
  date.value = this.value.substring(0,9);
  }
  });
</script>

- Make sure that you unchecked the "Enable HTML Editor" in the advance tab of the Footer.

- After this process, your date field will get the first day of the month and it still can be editable 

 

For more details, check these article:

https://howto.caspio.com/function-reference/

https://howto.caspio.com/datapages/ajax-loading/

I hope this helps  

~WatashiwaJin~

[GoodBoy]

Hi there! If ever you would like to also get the last day of the month, you may use this formula.

DATEADD (dd, -1, DATEADD(mm, DATEDIFF(mm, 0, SysDateTime()) + 1, 0))