Hello Community,
Trying to sort a query field that is currently text but hold dates. Was wondering if in the query it might be possible to cast as type date.
Currently attempting to sort the C2 column, however due to it being text it's not correct.
query: a!query( pagingInfo: a!pagingInfo( startIndex: 1, batchSize: 1000, sort: a!sortInfo(field: "c2", ascending: true) ), logicalExpression: a!queryLogicalExpression( operator: "AND", filters: { a!queryFilter( field: "c4", operator: "includes", value: local!activityFilter, applyWhen: not(isnull(local!activityFilter)) ), a!queryFilter( field: "c1", operator: "in", value: local!statusFilter, applyWhen: a!isNotNullOrEmpty(local!statusFilter) ), }, logicalExpressions: a!queryLogicalExpression( operator: "OR", filters: { a!queryFilter( field: "c2", operator: "starts with", value: "Enrollment" ), a!queryFilter( field: "c2", operator: "starts with", value: "Screening" ) } ) )
Discussion posts and replies are publicly visible
I can't figure out what you mean by "is currently text but hold dates". Is it text, or is it a date? Your queryFilters seem to believe it will be text holding something other than "dates", since it's trying to find only entries starting with "Enrollment" or "Screening". If the field is text, it should sort as normal by lexical order. I'm not sure what you're hoping to "cast as type date" in this.
Situations like this underline the importance to use correctly-typed data points. You will not be able to cast to date for sorting within Appian in this situation. My only suggestion would be to attempt to cast on the DB side within a view, create a new CDT with a date type value for this view, then you may be able to sort and filter more accurately.
Based on the field names my guess is that this person is querying from a!queryProcessAnalytics, so it's less about typing on the DB side but maybe typing within a report(?)
sorry for the confusion. When pulling in the data it's coming in as text. I was hoping to possibly tell the query that it was a date. I'll investigate the back end source to see if I can correct the discrepancy.
I mean... what data does the column in question actually contain / return? What's its source in the report column definition? Why is it being filtered for "text starting with..." if it's really a date / date-in-text-form? Just confused. I'm familiar with some of the finnickiness of filtering on reports (presuming i'm reading correctly that you're querying from a report, based just on the column names in question), but I can't offer much concrete help without being clearer on these things.
It looks like the task list was build from an example in an Appian post: https://docs.appian.com/suite/help/22.3/fnc_system_a_queryprocessanalytics.html
Fields look the same.
I'm familiar with that function. I'm not sure what information this is supposed to be giving me about your column C2, though, and what data / type of data it's returning/expected to return.
The task list was built from an Appian post:
https://docs.appian.com/suite/help/22.3/fnc_system_a_queryprocessanalytics.html
a!queryProcessAnalytics() FunctionFunctiona!queryProcessAnalytics( report, query, contextGroups, contextProcessIds, contextProcessModels, contextUsers )
Executes process reports and returns the resulting data.
The column for DATE_TIME was moved from c2 to c3. I modified the sort to the proper field and it's sorting properly now.