Solved

Formula help in a GI

  • 25 September 2023
  • 11 replies
  • 139 views
swittrock
Rank
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

palbores
Rank
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
~ Phil
swittrock
Rank
Userlevel 2

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

darylbowman
Rank
Badge +12

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)

Daryl ⚡Contract Acumatica Developer (MVP🏆) for hire ⏩ linkedin.com/in/darylbowman
darylbowman
Rank
Badge +12

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.

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

darylbowman
Rank
Badge +12

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.

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

darylbowman
Rank
Badge +12

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

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

darylbowman
Rank
Badge +12

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

Daryl ⚡Contract Acumatica Developer (MVP🏆) for hire ⏩ linkedin.com/in/darylbowman
swittrock
Rank
Userlevel 2

Thanks @darylbowman!  I appreciate the help.

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