Skip to main content
A
adarshn422531
October 11, 2023
Question

Filter record based on mid of the month

  • October 11, 2023
  • 3 replies
  • 0 views

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.

3 replies

tushark6475
tushark6475
October 11, 2023

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.

harshitb6843
harshitb6843
October 11, 2023

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
)

mathieud0001
mathieud0001
Brainy
October 11, 2023

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

Terms & ConditionsAccessibility statement