Solved

Older than "@Today" expression for row style (DateDiff?)

  • 25 September 2023
  • 4 replies
  • 120 views

Userlevel 5
Badge

Hi all, 

I just wrote a row style --

 

=Iif ([SOOrder.CustomerRefNbr] = 'PRO FORMA' and [SOOrder.Status] = 'R','red20', 'default')

 

If it’s a PRO FORMA on CREDIT HOLD, highlight the row red. So far so good.

 

I would like to modify this so it’s only on credit hold pro formas that are greater than a week old. So I did something like this:


=Iif ([SOOrder.CustomerRefNbr] = 'PRO FORMA' and [SOOrder.Status] = 'R' and [SOOrder.OrderDate]< @Today -7,'red20', 'default')

 

That unfortunately gives an error. Is there a way to do dates in expressions? I see the DateDiff function but I have no idea how to work the syntax on that. Thanks!

icon

Best answer by darylbowman 25 September 2023, 16:39

View original

4 replies

Userlevel 4
Badge +1

Hi @swartzfeger  ,

When referencing Today in a GI field, you’ll want to try Today().  @Today gets used in filters and the like.

I think that change should work, but just in case it doesn’t, I’m guessing you’re struggling with what the “interval” means in the syntax - DateDiff(Interval, Date1, Date2) - Here you have to define what sort of Date or Time increment you are calculating the difference of.  So years, months, days, minutes, etc.  I generally calculate days, so for that interval you would want to put ‘d’ (yes, with the single quotes).

Here’s a wiki link with more info:
https://help.acumatica.com/(W(1))/Wiki/ShowWiki.aspx?pageid=3eacd492-e7bb-4bf9-888d-fa3c9155329f

 

Good luck!

 

Userlevel 5
Badge

Hi @swartzfeger  ,

When referencing Today in a GI field, you’ll want to try Today().  @Today gets used in filters and the like.

I think that change should work, but just in case it doesn’t, I’m guessing you’re struggling with what the “interval” means in the syntax - DateDiff(Interval, Date1, Date2) - Here you have to define what sort of Date or Time increment you are calculating the difference of.  So years, months, days, minutes, etc.  I generally calculate days, so for that interval you would want to put ‘d’ (yes, with the single quotes).

Here’s a wiki link with more info:
https://help.acumatica.com/(W(1))/Wiki/ShowWiki.aspx?pageid=3eacd492-e7bb-4bf9-888d-fa3c9155329f

 

Thanks Craig, the Function wiki helps. I tried making heads or tails of it and no luck. I’ll circle back to this at the end of the week when I have time.

Badge +11

Use DateAdd(date, interval, number) instead:

=IIf([SOOrder.CustomerRefNbr]='PRO FORMA' And [SOOrder.Status]='R' And [SOOrder.OrderDate] < DateAdd(Today(),'d',-7),'red20', 'default')

 

Userlevel 5
Badge

Use DateAdd(date, interval, number) instead:

=IIf([SOOrder.CustomerRefNbr]='PRO FORMA' And [SOOrder.Status]='R' And [SOOrder.OrderDate] < DateAdd(Today(),'d',-7),'red20', 'default')

 

Ahhhh, that makes more sense with the DateAdd! Yes, you take Today(), subtract -7 and then say anything less than this <format>… thank you! That makes sense!

Reply


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