Ideas for Acumatica

Feedback processing: We do not reply to all messages, but we do read them, analyze them, and work to improve Acumatica based on the feedback we receive. Ideas and comments may not appear immediately. Some legitimate ideas are flagged as spam and will be added when we review the spam folders.
Content: This portal is for product ideas and feedback only. If you need customer service assistance, contact your Acumatica Support Partner, submit a support case, or get assistance from community resources: LinkedIn Group or StackOverflow
No Reliance: Information is maintained on a best-efforts basis and may be changed without notice. Acumatica cannot guarantee the accuracy of the information provided or guarantee completion of features/ideas described on this portal.

Make Format formula from Report Designer also available in Generic Inquiry

The Format formula from Report Designer is really useful. It would be nice if the Format formula was also available in Generic Inquiry.

 

For example, let's say that I want an April 4th, 2018 date to be represented in a format like 2018-04. I have to write a crazy formula like this:

=CStr(Year([SOOrder.OrderDate]))+'-'+Right('0'+CStr(Month([SOOrder.OrderDate])),2)

 

But if we had the Format formula available in Generic Inquiry, we could write something much more elegant like this:

=Format('{0:yyyy-MM}',[SOOrder.OrderDate])

  • Tim Rodman
  • Aug 15 2018
  • Rejected
  • Attach files
  • Tim Rodman commented
    23 Feb, 2019 04:04am

    Since all formulas are evaluated on the SQL Side, what about getting the SQL FORMAT function available in Generic Inquiries. More info on that function:

    https://www.w3schools.com/sql/func_sqlserver_format.asp

  • Vladimir Panchenko commented
    11 Oct, 2018 09:38am

    Unfortunately, it is not possible, and we don't want to consider this option for two reasons: security and compatibility (because we support more than one DBMS).

  • Tim Rodman commented
    29 Aug, 2018 11:48pm

    Ah, interesting. Didn't realize that everything was handled by SQL. Is there a way to inject SQL code into Generic Inquiry formulas?

  • Vladimir Panchenko commented
    29 Aug, 2018 04:29pm

    Thank you for your suggestion.

    In the current implementation, all formulas in Generic Inquiry are evaluated on the SQL side for performance sake. Unfortunately, SQL formatting rules and formatting rules on the Acumatica side may differ because they use the current locale, and in Acumatica you can customize those rules for each locale / user account / etc.

    Also, we support both SQL Server and MySQL, and they format things differently.