Solved

use of IIF statement in Generic Inquiry with Today()

  • 12 October 2021
  • 4 replies
  • 892 views
B
Rank
Userlevel 1

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.

icon

Best answer by bob39 12 October 2021, 20:51

View original

4 replies

A
Rank

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

G
Rank
Userlevel 3
Badge

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 Attachment
Naveen Boga
Rank
Userlevel 7
Badge +17

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

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

 

 

Naveen B
B
Rank
Userlevel 1

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.

 

 

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