Solved

Formula help in a GI

  • 25 September 2023
  • 11 replies
  • 130 views

Userlevel 2

I am getting a syntax error with this formula and can’t quite figure it out.

=IIF ((DateDiff(interval, [ARInvoice.DueDate], Today()) >=30 and (DateDiff(interval, [ARInvoice.DueDate], Today())<60, [ARInvoice.CuryDocBal],0)

I am trying to create columns of data depending on the “aging” of the invoice balance in a GI so that we can export to Excel for submission to our Auditors.  The value should either be zero or the invoice balance.

I have this similar formula that works fine for current invoices:  

=IIF ([ARInvoice.DueDate] >= Today(),[ARInvoice.CuryDocBal],0)

 

Thanks!

icon

Best answer by palbores 25 September 2023, 18:51

View original

11 replies

Userlevel 3
Badge

Hi @swittrock, Try this formula below.

=IIF ((DateDiff('d', [ARInvoice.DueDate], Today()) >=30) And (DateDiff('d', [ARInvoice.DueDate], Today())<60), [ARInvoice.CuryDocBal],0)
  • interval = d - for day
  • I added a close parenthesis on your formula which is missing
Userlevel 2

Thanks @palbores!  That works for what I was trying to do.

Badge +11

Small note that DateDiff will compare dates in either direction (X days greater OR less). A more exact approach would be to use DateAdd(Today(),’d’,-30) (30 days less than today) and compare [ARInvoice.DueDate] to it:

=IIF(([ARInvoice.DueDate] < DateAdd(Today(),'d',-30)) And ([ARInvoice.DueDate] >= DateAdd(Today(),'d',-60)), [ARInvoice.CuryDocBal], 0)

(the Invoice has been due for 31-60 days, which is what I am assuming you’re looking for)

Badge +11

Small note that DateDiff will compare dates in either direction (X days greater OR less)

I fact-checked myself and it turns out that the result of DateDiff is actually signed (+/-), which means the order that you enter the dates will determine if the result is positive or negative. The result could be confusing, but regardless, it would be clear that 30 days in the future is not the same as 30 days in the past.

Userlevel 2

Any idea how to get a GI to Sum a credit memo properly along with Invoices?  Currently what I have going adds the credit memo to the total vs what you would expect as lowering the total due.

Badge +11

If the adjustments have been applied (ie: credit memos or payments), the remaining balance should be reflected in ARInvoice.CuryDocBal. You shouldn't need to manually calculate it.

Userlevel 2

The credit memo hasn’t been applied to anything.  The credit memo may also be the only thing on the customer's account.

Badge +11

You could do an IIf condition to determine if it ARInvoice.DocType is a Credit Memo and if so, multiply the total by -1.

Userlevel 2

@darylbowman I must be missing something simple.  Here is my current formula: 

 

=IIF(DateDiff('d', [ARInvoice.DueDate], Now()) < 30 OR [ARInvoice.DueDate] IS NULL, IIF([ARInvoice.DocType] = 'CRM', [ARInvoice.CuryDocBal] * -1, [ARInvoice.CuryDocBal]), NULL)

 

I get a message: “The method or operation is not implemented.”

 

Any ideas?

Badge +11

'IS NULL' is not a valid statement.

What about this:

=IIF(DateDiff('d', [ARInvoice.DueDate], Now()) < 30 OR [ARInvoice.DueDate]=Null, IIF([ARInvoice.DocType] = 'CRM', [ARInvoice.CuryDocBal] * -1, [ARInvoice.CuryDocBal]), NULL)

Userlevel 2

Thanks @darylbowman!  I appreciate the help.

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