Filter record based on mid of the month

Question

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.

Mathieu Drouin · Accepted Answer

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
 }
)

tushark8069 · Answer

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.

Harshit Bumb (Appyzie) · Answer

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
)

Filter record based on mid of the month

Top Replies