Skip to main content
Solved

Conditional formula

  • 21 December 2022
  • 7 replies
  • 660 views

Forum|alt.badge.img+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!

 

Best answer by DConcannon

@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?

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

7 replies

DConcannon
Varsity II
Forum|alt.badge.img+2
  • Varsity II
  • 119 replies
  • December 21, 2022

@paula012 Have you tried

 

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


Forum|alt.badge.img+1
  • Author
  • Semi-Pro II
  • 92 replies
  • December 21, 2022

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
Varsity II
Forum|alt.badge.img+2
  • Varsity II
  • 119 replies
  • December 21, 2022

@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?

 


Forum|alt.badge.img+1
  • Author
  • Semi-Pro II
  • 92 replies
  • December 21, 2022

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!


Forum|alt.badge.img+1
  • Author
  • Semi-Pro II
  • 92 replies
  • December 21, 2022

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
Varsity II
Forum|alt.badge.img+2
  • Varsity II
  • 119 replies
  • Answer
  • December 21, 2022

@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?


Forum|alt.badge.img+1
  • Author
  • Semi-Pro II
  • 92 replies
  • January 3, 2023
DConcannon wrote:

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


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