Solved

FSM - Date Separation

  • 12 July 2022
  • 3 replies
  • 162 views
D
Rank
Badge +4

FSM

we are currently using FSM 6 update 15, 

is there option to divide the day, month and year from the date field and store it in the different db field?

Eg: 

user_def_dttm1 = 6/24/2022

from the above fields i need to save that in three different field as below

user_def1 = 6

user_def2 = 24

user_def3 = 2022

we cant use substring expression, because it will not work for below

10/24/2022 - because the position will keep on change

can any one suggest if there way to accomplish above requirement?

icon

Best answer by Shneor Cheshin 13 July 2022, 07:52

View original

3 replies

Saranga Amaraweera
Rank
Userlevel 7
Badge +22

Hi @Damodaran ,

I’d use a either a web client script or a sql view to perform this operation.

1)Simply you can write a sql view to get the date, month and year with functions like cast, convert, dateadd and then apply this view to FSM by making primary table key relationships. I hope you’re already aware about how to incorporate views with fsm.

2)Other way will be to split the datetime with a web client script using stringSplit function. Please check the sample script given below

var planEnd=getControlValue('task','plan_end_dttm'); //Get the control value
planEnd=stringSplit(planEnd,'T'); //split the datetime value from 'T' (this will separate the time aside)
var planEndDate=stringSplit(planEnd[0], '-'); // get the first element from the above result and split the date by '-' character

var planEndYear=planEndDate[0]; //Get year
var planEndMonth=planEndDate[1]; // Get month
var planEndDay = planEndDate[2]; // Get day

//then set the results for the userdefs mentioned above

 

SAAALK
Shneor Cheshin
Rank
Userlevel 6
Badge +26

Hey @Damodaran 

You did not provide too much details. 

But there is an option to do it with a client script.

For example:

var planStartDttm=getControlValue('task','plan_start_dttm');
planStartDttmArr=stringSplit(planStartDttm,'T');
var planStartDateArr=stringSplit(planStartDttmArr[0],'-');
var planStartDay=planStartDateArr[2];
var planStartMonth=planStartDateArr[1];
var planStartYear=planStartDateArr[0];

setControlValue('task','user_def17',planStartDay);
setControlValue('task','user_def18',planStartMonth);
setControlValue('task','user_def19',planStartYear);

Demo

If you provide more details on the business requirements, maybe we can suggest another/better solution?!

Please note, you might need to take care of timezone differences, depends on your use cases and environment settings.

 

Cheers!

D
Rank
Badge +4

@Saranga Amaraweera Thanks . i think first option will work for us

Reply


Terms & ConditionsCookie settings