Skip to main content
Solved

Formula for field to define [date] age

  • October 24, 2024
  • 6 replies
  • 123 views
A
Freshman I
Forum|alt.badge.img

I know there is already a request for a report of inventory aging here: 

https://community.acumatica.com/ideas/stock-aging-analysis-reports-and-stock-audit-as-the-standard-report-in-acumatica-2571?tid=2571&postid=106161#post106161

 

However all we really need is one field “inventory age” with the formula “X DAYS SINCE RECEIVED” to inform us how old the inventory is in general. We are fine with it always using the most recent receiving date for each item, without needing a value for every single receiving to have occurred against it. 

So the field would need to show the count of days since the last time the stock item was received.

Days since receiving date.

 

Using the date received field we just need to count days “since”. So received date being yesterday would have the value of “1” in that field for output, for example.

 

What would this formula look like in the results grid builder for GI customization? 

 

Best answer by Laura02

Hello Amber,

Did you try subtracting the dates with DateDiff function, like this?

 

=DateDiff('d', [INRegister.TranDate], Today())

 

To find the latest receipt for each inventory item, Group InTran Receipt records by Item, and use Max aggregate on the Date field of the Results tab.

HTH

Laura

View original
Dmitrii Naumov
Evan Templin
etrowbridge
3 people like this
  • Dmitrii Naumov
    Dmitrii Naumov
  • Evan Templin
    Evan Templin
  • etrowbridge
    etrowbridge
Did this topic help you find an answer to your question?

6 replies

Laura02
Captain II
Forum|alt.badge.img+19
  • Laura02
  • Captain II
  • 3132 replies
  • Answer
  • October 25, 2024

Hello Amber,

Did you try subtracting the dates with DateDiff function, like this?

 

=DateDiff('d', [INRegister.TranDate], Today())

 

To find the latest receipt for each inventory item, Group InTran Receipt records by Item, and use Max aggregate on the Date field of the Results tab.

HTH

Laura

Laura ~ Klear Systems, Inc. Irvine, CA. ~ Click my logo to Follow me ~
craig2
miguel80
Chris Hackett
3 people like this
  • craig2
    craig2
  • miguel80
    miguel80
  • Chris Hackett
    Chris Hackett
A
Freshman I
Forum|alt.badge.img
  • AHanke
  • Author
  • Freshman I
  • 11 replies
  • January 21, 2025

​@Laura02 
When I use =DateDiff('d', [INRegister.TranDate], Today()) it gives me the most recent date in the field instead of the oldest. How would I rewrite this equation to show max instead of min? I don’t understand the formula enough to know which part of it needs to be reversed. 

L
Acumatica Employee
  • LauraBarber24
  • Acumatica Employee
  • 6 replies
  • January 21, 2025

hello,

If you are looking for the oldest date, use Min aggregate on the Date field of the Results tab in the GI, instead of Max aggregate as recommended 2 months ago.

 

Best regards,

 

Laura

Chris Hackett
1 person likes this
  • Chris Hackett
    Chris Hackett
A
Freshman I
Forum|alt.badge.img
  • AHanke
  • Author
  • Freshman I
  • 11 replies
  • January 22, 2025

​@LauraBarber24 
Is this correct?
=DateDiff(Min( [INCostStatus.ReceiptDate], Today()))
=DateDiff(Min( [POLine.OrderDate], Today()))
=DateDiff(Min( [inItemLot.CreatedDateTime], Today()))
=DateDiff(Min( [POReceiptLine.ReceiptDate], Today()))

When I try this using various table’s date fields as above, I get the same errors “Unable to cast object of type 'PX.Data.SQLTree.SQLExpression' to type 'PX.Data.SQLTree.SQLConst'.”

StevenRatner
Varsity I
Forum|alt.badge.img
  • StevenRatner
  • Varsity I
  • 75 replies
  • January 22, 2025

​@AHanke - Try this…

 

 

Steve - Klear Systems
Chris Hackett
1 person likes this
  • Chris Hackett
    Chris Hackett
A
Freshman I
Forum|alt.badge.img
  • AHanke
  • Author
  • Freshman I
  • 11 replies
  • January 23, 2025

​@StevenRatner This gives the same error “Unable to cast object of type 'PX.Data.SQLTree.SQLSwitch' to type 'PX.Data.SQLTree.SQLConst'.”

Reply


Most helpful members this week

Terms & ConditionsCookie settings

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
About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved