Hello,
is it possible to have a conditional sort on multiple columns in a query rule? I need to sort the data by modified date and created date columns. Modified date might be null for some rows. Below order by expression gives just what i want when i use it with the SQL query. Is there a way of creating same logic in a query rule?
order by case when MODIFIED_DATE is null then CREATED_DATE else MODIFIED_DATE end DESC
I tried below code but it didn't help.
pagingInfo: a!pagingInfo( startIndex: 1, batchSize: -1, sort: {a!sortInfo(field: "createdDate", ascending: false), a!sortInfo(field: "modifiedDate", ascending: false)} )
Discussion posts and replies are publicly visible
You cannot express this with an Appian query, but you could just create a view in DB which implements this logic.
Or you could just make sure to set the modified date to the create date when created.
emineo0001 said:order by case when MODIFIED_DATE is null then CREATED_DATE else MODIFIED_DATE end DESC
If you claim this works in SQL then I'll take your word for it, but inherently it really doesn't make much sense. ORDER BY is meant to apply across all rows of a search result set - it logically breaks if you try to pick and choose which results within that set it applies to.
I second Stefan's suggestion to write a View where you establish a bespoke "sort date" column, which can (on a row-by-row basis) establish the value for that new column based on whether the "created date" actually exists, and if not, fill it with the "modified date" instead. From Appian's end it'll then be fairly straightforward to sort a query result by the value of that column.
If you're using a synced record type, this is also a great scenario for using a custom record field - just add a field that uses the a!defaultValue() function and returns the CREATED_DATE if MODIFIED_DATE is null. Then you can sort directly on that custom record field to achieve the result you're looking for.