Solved

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

  • 25 September 2023
  • 4 replies
  • 110 views
S
Rank
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

craig2
Rank
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!

 

S
Rank
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.

darylbowman
Rank
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')

 

Daryl ⚡Contract Acumatica Developer (MVP🏆) for hire ⏩ linkedin.com/in/darylbowman
S
Rank
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


Terms & ConditionsCookie 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