Skip to main content
Solved

use of IIF statement in Generic Inquiry with Today()

  • October 12, 2021
  • 4 replies
  • 1116 views
B
Freshman II
  • bob39
  • Freshman II
  • 6 replies

I have an “On Hold” AP Bills Generic inquiry to present same in a dashboard.  I tried to use the two example expressions in the Style column of a Generic Inquiry for APInvoice.Due.Date to shade invoice due dates pink (bad) when an AP Bill is past due.  However, what I’m getting is pink (bad) only when the DocDate and DueDate are equal dates or when the DueDate is blank (example: for a Debit Adj).  

 

Here are the two Style IIf statements that I tried.

 

=IIf([APInvoice.DueDate]>Today(),'good','bad')

=IIf({APInvoice.DueDate]>@Today,’good’,’bad’)

 

Looking for the correct Style expression to shade the APInvoice.DueDate pink (bad) when an AP Bill is past its due date.  Thank you.

Best answer by bob39

So, I used DateDiff (Data Field) in the GI to create a Days Overdue column:

=IIf(DateDiff('d',[APInvoice.DueDate], Today())>0,DateDiff('d',[APInvoice.DueDate], Today()),0)

And then applied the desired Style this way:

=IIf(DateDiff('d',[APInvoice.DueDate], Today())>0,'bad','good')

The above ideas were new to me and work.  What I finally noticed is that someone manually edited one due date to be roughly 100 years from now.  LOL.  I’ll fix that data edit gaff, and I think we’re good.

I appreciate all the great ideas and data concepts shared.  Thank you all.

 

 

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

4 replies

A
Freshman II
  • astrauss80
  • Freshman II
  • 6 replies
  • October 12, 2021

Perhaps you should try to use the DateDiff function, in order to determine if [DueDate] is later than today.

G
Jr Varsity III
Forum|alt.badge.img
  • gpineda64
  • Jr Varsity III
  • 22 replies
  • October 12, 2021

I wrote a similar one before. XML attached.  It’s filtered by BILL type.

The expression I used was:

=IIF ( ISNULL([APInvoice.DueDate],Today()) >= Today(), 'good','bad')

The ISNULL will work for other document types without a Due Date.

I hope this helps.

1 Attachments
Naveen Boga
Captain II
Forum|alt.badge.img+19
  • Naveen Boga
  • Captain II
  • 3417 replies
  • October 12, 2021

@bob39  Yes, it should work with this DateDiff fuction. Here is the sample example.

=  DateDiff( 'd', [ARInvoice.DocDate], Today() )

 

 

Naveen B - Acumatica MVP 🏆 | Independent Developer for Hire 🚀 ⏩ https://www.linkedin.com/in/boga-naveen-kumar-54079a66/
B
Freshman II
  • bob39
  • Author
  • Freshman II
  • 6 replies
  • Answer
  • October 12, 2021

So, I used DateDiff (Data Field) in the GI to create a Days Overdue column:

=IIf(DateDiff('d',[APInvoice.DueDate], Today())>0,DateDiff('d',[APInvoice.DueDate], Today()),0)

And then applied the desired Style this way:

=IIf(DateDiff('d',[APInvoice.DueDate], Today())>0,'bad','good')

The above ideas were new to me and work.  What I finally noticed is that someone manually edited one due date to be roughly 100 years from now.  LOL.  I’ll fix that data edit gaff, and I think we’re good.

I appreciate all the great ideas and data concepts shared.  Thank you all.

 

 

Naveen Boga
cwalton3
2 people like this
  • Naveen Boga
    Naveen Boga
  • cwalton3
    cwalton3

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