Skip to main content
Solved

Conditional formula

  • 21 December 2022
  • 7 replies
  • 554 views

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!

 

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