Saturday, May 25, 2019

Create Saved Search Formula (Numeric) Field to Show Zero Instead of Negative Values in Results

A user would like to show 0 (zero) instead of negative value in Saved Search results. This formula could be used for Transaction Saved Search regarding sales or for inventory Saved Search.

Follow the steps below to create a Formula (Numeric) for Saved Search results:

1. Navigate to Lists > Search > Saved Searches > New > select the type of Saved Search.
2. On the Results tab > Columns subtab, Add:
    • Field = Formula (Numeric)
    • Formula = CASE WHEN {field for comparsion} < 0 THEN 0 ELSE {field for comparsion} END

Example: When a user does not want to display negative Transaction Amounts in Saved Search results, the Formula will be:
    • CASE WHEN {amount} < 0 THEN 0 ELSE {amount} END
3. Click Save or Save & Run.

1 comment:

  1. If one wanted to use a formula for your field for comparison how would that work?

    loosely - this doesn't work - but I am sure it is just a syntax thing -

    CASE WHEN Sum({durationdecimal}) - (40.00) <0 THEN 0 ElSE Sum({durationdecimal}) - (40.00)