Hi,
I have a question about GI result sorting.
There is a system default GI AR-CustomerSummary, Screen ID AR0008DB, attached screen shot for the detailed GI setting. Currently the AR is grouped by customer account, and i would like the result to sort by “Balance” descending.
The result seems to not showing the sorting as i expected, please take a look and let me know which part is doing wrongly.
Thank you.
Best answer by RoyceLithgo
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.
+4Hi
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
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?
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.
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.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
