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.
Discussion posts and replies are publicly visible
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).
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.
I assumed even a!queryFilters in queryRecord() could still use the "BETWEEN" operator and it works when paired with eoMonth().. am I mistaken?
Hmm. Not sure I follow. Will prob need some time to review and understand this and how I could integrate it into the query. I will try what you and Mike suggested and trying the between operator.
This works. Will definitely need to look over and understand some of these functions like eomonth though haha. Thank you!
"eoMonth" is one that I needed a long time ago but only learned about much more recently (it may itself be newer, but i honestly don't know) - it's very handy of course since it can find month boundaries much more easily than by any traditional manual calculations (since the number of days varies per month, etc).
yeah I was hoping it wasnt going to be that tedious so that is handy for sure. Im not used to the Appian discussion board and almost everything I find is super old so I thought I would get a reply in like decades, reassuring to know people are actually on here lol
jahnoelr0001 said:Im not used to the Appian discussion board and almost everything I find is super old
search doesn't always return things in a very intuitive order. often you need to specifically set it to return newer results first, and/or try a couple different phrasings for something before you'll find what you need. and of course, barring that, it's always fine to post a new thread
Mike Schmitt said:I assumed even a!queryFilters in queryRecord() could still use the "BETWEEN" operator and it works when paired with eoMonth().. am I mistaken?
I believe you are correct there, and looks like they've confirmed. BETWEEN is much more elegant also
I would also second the sentiments on fn!eomonth(), I've probably only known about that for a year or so and it is a game changer!