Hello All, i am trying to filter the record which has been created in the 1st half of that particular month. So basically, I have a record with a column "Created on" (Data type).
on this column, i have to apply the filter to get the records which have been created in the 1st half of the month. For eg. if record created on "11/10/2023, it is created in the 1st half (Before 16th October and so on.
Discussion posts and replies are publicly visible
Hi Adarsh, you can filter the record data after querying that.
a!localVariables( local!data: rule!AT_getEmployee(date: null), local!fixData: index( local!data, where( day(local!data['recordType!{da9ea760-c50b-42bd-ac44-1c1a62930161}Employee.fields.{68245e32-2244-4909-9a6c-845d3969cadb}hireDate'])<16 ) ), local!fixData['recordType!{da9ea760-c50b-42bd-ac44-1c1a62930161}Employee.fields.{68245e32-2244-4909-9a6c-845d3969cadb}hireDate'] )
Note: Here I am querying the record data in local!data, then filter the data that have "hireDate" in the first half of the respective month.
You can simply add a new custom record field with the below expression and then use it to filter the records.
if( day(rv!record['recordType!{51d93a85-f899-40bf-98c3-744d5c51b519}EAMS Employee.fields.{706e9825-5d59-440a-8eb0-94d8ae5f499a}dateOfBirth_dt'])<15, 1, 2 )
This solution takes into account the fact that months have varying amounts of days. (28, 29, 30, 31) and that the mid way point can change.
a!localVariables( /* Get current datetime */ local!now: now(), /* Get datetime for end of month */ local!endDate: eomonth(local!now, 0), /* Get the day from end of month datetime */ local!endOfMonthDay: day(local!endDate), /* Find the middle of the month */ local!midMonthDay: tointeger(round(local!endOfMonthDay / 2, 0)), /* Calculate start and end dates */ local!startDateTime: userdatetime( year(local!endDate), month(local!endDate), 1, 0, 0, 0 ), local!endDateTime: userdatetime( year(local!endDate), month(local!endDate), local!midMonthDay, 23, 59, 59 ), { startDateTime: local!startDateTime, endDateTime: local!endDateTime } )