Hi @jay13! I think for this issue the sorting is not meant to accommodate an IIF statement, at least not that I’ve ever seen.

I would recommend in the expression to multiply by -1 instead of just adding a - to the value. I’m not sure if it makes a difference, but this is the most common way I’ve seen to invert the value.

It seems like the formula is just deciding on if to make it negative or not and not necessarily related to sorting, so I think it’s best to leave the formula on the results grid and just sort descending by the DocBal field.

Have you already tried something like this?

I used this for the Sorting formula and it worked as expected.

=IIf([ARInvoice.DocType] = 'CRM', -1, 1) * [ARInvoice.DocBal]

It’s the same formula I used for Balance Signed column above.

HI Benjamin,

Thank you for your reply.

I add a “TEST” column in the resulting grid, not sure whether this is what you mean, and i made the sorting by “ARInvoice.docBal” descending, most of the results are able to sort by order, but still have some records didtnt follow order, attached some screenshots.

I used this for the Sorting formula and it worked as expected.

=IIf([ARInvoice.DocType] = 'CRM', -1, 1) * [ARInvoice.DocBal]

It’s the same formula I used for Balance Signed column above.

Hi Royce,

Thanks for your reply.

I think your case is different from mine. Your case is sorting by individual invoice balance, but i grouped the document total by customers.

Was going to ask whether you’d grouped your query.

Unfortunately with grouping it’s not possible to do what you want. You need the Sort function to be aggregated by the groups as well but you can’t use aggregate functions in Sort. 

You’d need to rewrite this as a report with Report Designer.

Hi Royce,

Alright, thank you again.