Solved

Conditional formula

  • 21 December 2022
  • 7 replies
  • 436 views
P
Rank
Userlevel 3
Badge +1

Hello, can someone please kindly help me with this?

I want to display the value of ‘not null’ VAT Sales (or) ‘not null’ VAT Exempt (or) ‘not null’ ZERO Rated in “Total Sales”. I need a formula, I’ve been trying to use ISNULL and IFF but I’m wrong. Can anyone please give one. Thanks so much!

 

icon

Best answer by DConcannon 21 December 2022, 20:49

View original

7 replies

DConcannon
Rank
Userlevel 6
Badge +2

@paula012 Have you tried

 

=IFF([ARInvoice.CuryVatTaxableTotal] = 0.00, [ARInvoice.CuryVatExemptTotal],[ARInvoice.CuryVatTaxableTotal])

P
Rank
Userlevel 3
Badge +1

Oh yes @DConcannon , this exactly is what I have now. It works, but I want to add another condition here for the next field which is if Vat Exempt  = 0, then the Total Sales would give me the value of Vat Sales which is if also 0, then I’ll get another value which is the total sales itself. Am I making sense to you? Thanks so much again!

DConcannon
Rank
Userlevel 6
Badge +2

@paula012 I am not sure I completely understand, but if you are wanting to display total sales before  taxes and document discounts, can you just use [ARInvoice.CuryLineTotal] and not use IFFs?

 

P
Rank
Userlevel 3
Badge +1

Thanks. Ok let me try again, sorry if I can’t explicitly word my intentions here. I can display the Detail Total ONLY IF all of them are 0.00. That’s what I’m missing in the condition because what you first gave already works, which is if VAT Sales is 0.00, then VAT Exempt value displays, vice versa.

 

Or could I say I’m needing a “nested” IIF statement? Please help me w/ this. Thank you!

P
Rank
Userlevel 3
Badge +1

Hi @DConcannon, this worked! Can you see through here what I wanted as an output? If VatTaxable is 0, I want the VatExempt. But if VatExempt is also 0, then I want the CuryLineTotal.

And since, VatTax has value, I got its value. But I don’t have any sample that I could test for the condition that if VatExempt has value, it would get the CuryLineTotal. Would it? Thank you!

DConcannon
Rank
Userlevel 6
Badge +2

@paula012  IIF stops processing once it finds a True condition, so if ...TaxableTotal is 0.00 you will always get ...ExemptTotal even if it is 0.00.  Try the formula below, it is very similar, the nested IIF just starts sooner.

 

=IIF([ARInvoice.CuryVatTaxableTotal] = 0.00, (IIF([ARInvoice.CuryVatExemptTotal] = 0.00,[ARInvoice.CuryLineTotal] , [ARInvoice.CuryVatExemptTotal])),[ARInvoice.CuryVatTaxableTotal])

 

The image below images contain a single test I did with the New formula and your Existing formula.

 

Invoice Used for Print

 

Bottom of Invoice Print

Do you have a test tenant or a local installation where you can build the all your Invoice cases to verify you are publishing a good report?

P
Rank
Userlevel 3
Badge +1

Do you have a test tenant or a local installation where you can build the all your Invoice cases to verify you are publishing a good report?

Sorry for the late update. This works, yes. But I couldn’t make another sample for the other two cases yet. Thank you very much!

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