0

Leave formula field empty if .....

Hello.

In our quotes, we often have to write some details within line items. But in the printed quote, it show "0.00" next to it.

Qty and PPU (Price per Unit) are number fields.

Total THB is a formula field with the code below.

Qty * PPU

What I want is that the Formula field to be empty if there is no Qty entered.
I tried code like

if Qty := true then
Qty * PPU
else
let 'Total THB' := null

But it says that this formula may not modify data.

Any idea how I can achieve this?

7 replies

null
    • Sviluppatore Ninox
    • Fabio
    • 1 yr ago
    • Reported - view

    Hello Bernd.

    You need a comparison operator instead of an assignment operator.

    Try this:

    if Qty = true then //you can also write 'if Qty then'
        Qty * PPU
    else
        null
    end

       Fabio

    • NetSol Co., Ltd.
    • Bernd_Krell
    • 1 yr ago
    • Reported - view

    I was close and still far away LOL.

    Fabio thank you so much for your great help. It's working perfect now.

    • NetSol Co., Ltd.
    • Bernd_Krell
    • 1 yr ago
    • Reported - view

    Fabio An unexpected problem came up.
    Whenever the QTY exceeds 1, the Total THB field is empty.

    See attached screenshot.

    • Sviluppatore Ninox
    • Fabio
    • 1 yr ago
    • Reported - view

    It shouldn't. What is the code inside the TOTAL THB column?

    • NetSol Co., Ltd.
    • Bernd_Krell
    • 1 yr ago
    • Reported - view

    Fabio The Total THB code is 'Total Sales Price'. Nothing else.
    I have added your code to the 'Total Sales Price' Formula field.
    Slightly changed as I gave you the wrong name initially.
    So the code in 'Total Sales Price' is
    if Qty = true then Qty * SELL else void end

    If Qty is empty, the 'Total Sales Price' field stays empty (and with it, the TOTAL field in the Current Items). That is desired behavior.

    If Qty is 1, then 'Total Sales Price' shows the correct price and with it, the TOTAL field.
    If Qty is more then one, then 'Total Sales Price' is empty and so is TOTAL field.

    I can't figure out why Qty of 2 or more is different than 1 but I assume the "Qty = true" statement is the cause. Just don't know what else to put in there.
     

    • Fred
    • 1 yr ago
    • Reported - view
    Bernd Krell said:
    If Qty is more then one, then 'Total Sales Price' is empty and so is TOTAL field.

     That is weird. I was able to duplicate it. You can try the other way Fabio recommended:

    if Qty then Qty * SELL end

    I found this works. You don’t need the else if you want it to be empty. 

    Or you can do:

    if Qty > 0 then Qty & SELL end
    • NetSol Co., Ltd.
    • Bernd_Krell
    • 1 yr ago
    • Reported - view

    Hello Fred
    Thank you so much, I tried the first one, and it worked perfectly.
    Again, thank you and Fabio for the great help. 👍

Content aside

  • Status Answered
  • 1 yr agoLast active
  • 7Replies
  • 137Views
  • 3 Following