How can a user constant (date field type) can be made dynamic

Question

Hello experts,

I have created two workflow user constant, History_Enddate and Forecast_StartDate(shown in attached screenshot).

Logic is as follows:

History_enddate : Last date of previous month (eg. if workflow is run in month of Feb, this date is 31st Jan 2021)

Forecast_StartDate : First date of current month (eg. if workflow is run in month of Feb, this date is 1st feb 2021)

These two user constant are used in the SQL editor while importing data (input tool) from the system(database).

I have currently hard coded this dates.

How best can these be made dynamic with respect to the run month.

eg. if workflow is run in month of March,

History_enddate should be last date of previous month --> 28th feb 2021 and Forecast_StartDate should be first date of current month --> 1st March 2021.

Regards, Ravi

UserConstant_Dates.png

Ravy12 · Answer

Hi @afv2688 ,

Thanks for your solution, but the expression  works in formula expression editor and not at the user workflow constant. User workflow constant created in step 1, returns Null after using it in the formula expression editor. The expression worked fine in the formula but not the user constant defined in step 1.

The goal was to make the user constant dynamic.

Please find attached workflow and steps used to create the workflow.

Regards, Ravi

datesDynamic_UserConstant.yxmd

Step1_Define_User_Constant.png

Step2_User_Constant_UsedinFormula.png

Step3_User_Constant_ReturnsNull.png

afv2688 · Answer

Hello @Ravy12 ,

To get he first date for the current month I would recommend you to use the follwing formula:

DateTimeFirstOfMonth()

For the other one:

DateTimeAdd(DateTimeFirstOfMonth(),-1,'days')

hope it helps

Regards