Filtering record field for records added in the current month

Question

Hello all. 
 I am following the Appian online course and making a side project to practice. In the query data section for querying VehiclesAddedThisMonth 
 it shows us how to query and filter a vehicle that was added this month. The problem I am having is that the field I am using is datetime and not date. 
 Error : Expression evaluation error at function a!queryRecordType: Cannot apply operator [EQUALS] to field [createdOn] when comparing to value [TypedValue[it=1,v=10]]. 
 Code: 
 a!queryRecordType(
 recordType: 'recordType!{7dc09094-e6de-418e-855a-35ab22892fc7}BB Product',
 filters: a!queryFilter(
 field: 'recordType!{7dc09094-e6de-418e-855a-35ab22892fc7}BB Product.fields.{34fab307-ea54-4ea9-a01b-40a0706f32c7}createdOn',
 operator: "=",
 value: month(now())
 ),
 pagingInfo: a!pagingInfo(
 startIndex: 1,
 batchSize: 100
 )
).data 
 Seems like the issue is that the operator cannot compare the month function to my datetime field however it is ok if the month compare to a date field. 
 Not sure what to do except change the value in comparing it too in the filter.

Mike Schmitt · Accepted Answer

month(now()) will just return an integer value. Your query filter will be expecting a date value. 
 A quick workaround which MIGHT work would be setting the operator to "between" and setting the value(s) to "eomonth(now(), -1), eomonth(now(), 0)" - which would basically be finding anything in the month-to-date (basically anything after the last date in the previous month).

jahnoelr0001 · Answer

This works. Will definitely need to look over and understand some of these functions like eomonth though haha. Thank you!

Chris · Answer

Few notes, if you need to compare for items added this month, you will need 2 query filters (if you do not have the month integer value available in the data set). Such as, greater than 12 AM at the start of this month and less than 12 AM at the start of next month. Here's an a!map() showing an example of values to use in 2 query filters within a!logicalExpression(): 
 a!localVariables(
 local!currentDay: today(),
 local!firstDayNextMonth: eomonth(local!currentDay,1),

 a!map(
 greaterThan: gmt(datetime(year(local!currentDay),month(local!currentDay),1,0,0,0,0)),
 lessThan: gmt(datetime(year(local!firstDayNextMonth),month(local!firstDayNextMonth),1,0,0,0,0))
 )
)

 
 Another way we can do this is, save the month() value along with your CDT (to which you can filter in the integer value of the month directly), or apply the calculation in a database view. Note in the latter scenario, since Appian saves all date values as UTC to the DB, you will need to perform the gmt offset calculation in the view prior to matching of you will be missing a short period of time at the start/end of each month. That really only applies when all users are in one time zone however.

Filtering record field for records added in the current month

Top Replies