Skip to main content
Solved

How to sort GI result with function formular


Forum|alt.badge.img
  • Freshman I
  • 13 replies

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.

View original
Did this topic help you find an answer to your question?

6 replies

BenjaminCrisman
Acumatica Employee
Forum|alt.badge.img+4
  • Acumatica Support Team
  • 627 replies
  • October 19, 2022

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?


Forum|alt.badge.img+3
  • Pro II
  • 76 replies
  • October 19, 2022

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.


Forum|alt.badge.img
  • Author
  • Freshman I
  • 13 replies
  • October 20, 2022
BenjaminCrisman wrote:

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?

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.

 


Forum|alt.badge.img
  • Author
  • Freshman I
  • 13 replies
  • October 20, 2022
RoyceLithgo wrote:

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.

 


Forum|alt.badge.img+3
  • Pro II
  • 76 replies
  • Answer
  • October 20, 2022

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.


Forum|alt.badge.img
  • Author
  • Freshman I
  • 13 replies
  • October 21, 2022
RoyceLithgo wrote:

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.


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings