How to use if_case with expressions other than equals

How to use if_case with expressions other than equals

I'm trying to define a formula column that implements logic like this case statement would:
  1. case
  2.   when numfld1 is null then null
  3.   when numfld2 > 0 then 100*numfld2
  4.   when numfld2 < 0 then numfld2
  5.   else 0.0
  6. end
In formula columns, the docs say you need to use if_case for this sort of logic. I have no idea how to do the > 0 comparison in if_case. The dumb way if_case separates the column name from the test doesn't help. From the examples given for if_case, I would guess somehing like this:

if_case('numfld1', null, null, 'numfld2', gt(0), "numfld2"*100, 'numfld2', lt(0), "numfld2", 0.0)

That doesn't work though, because there are no formula functions like gt() and lt() as far as I can tell. Even the equals() mentioned in the documentation doesn't seem to work right.

So, what is the proper translation of this logic into an if_case formula? And where can documentation about it be found?

And, while on the topic, why does if_case not work like the if function does. Like, wouldn't if_case be better if its arguments worked the way the if() function's do? Something like:

if_case("numfld1" is null, null, "numfld2'" > 0, "numfld2"*100, "numfld2" < 0, "numfld2", 0.0)

Why doesn't if() just do that in the first place?


    • Sticky Posts

    • Geo-Powered Retail Intelligence with Zoho Analytics

      In today’s highly competitive retail landscape, data-driven decisions are no longer optional — they’re essential. While businesses collect vast volumes of data across regions, stores, and customer segments, the real value lies in how effectively this
    • Create and save user-specific filter views with Zoho Sheet

      The filters in Zoho Sheet have become even more collaboration-friendly. Previously, whenever you filtered any data, the filtered view will be updated for all the spreadsheet collaborators. This disturbed the data for others working on the same file. With this new update, you can apply data filters without altering the view for other collaborators in the file.   Collaborate with user specific filters   Let us say you are working on a sales report spreadsheet in real time with two remote colleagues. If
    • Customer Segmentation using RFM Analysis

      How well do you know your customers? Whether you operate in a B2B or B2C space, chances are that 80% of your business comes from just 20% of your customers (Pareto's Principle). According to a study by Forbes, acquiring new customers costs five times
    • Announcing Zoho Analytics 6.0 Beta!

      We are delighted to open up the next major version, Zoho Analytics 6.0 Beta! The new version comes packed with a wide range of functionalities for all persona, namely business users, data analysts, data engineers, and data scientists. Zoho Analytics team
    • What's New in Zoho Analytics - August 2024

      Hello Users! We are back with the latest updates and enhancements made to Zoho Analytics. Keep reading to learn more about them. Connect to the data hosted in the cloud without allow-listing the IP addresses Utilize Zoho Databridge to connect to the data
      • Recent Topics

      • How to validate Rich Text in Zoho Creator! Urgent!

        Hi members, Recently I just started to use Rich Text field. Now I have a requirement where I need to validate to ensure this Rich Text field must contain a value. Meaning must contain something. I use the below script if(input.Rich_Text == null) { alert
      • Allow non admins to create folders

        I want to allow users in my company to create their own reporting based upon selected Datasources, however they currently can't create folders to save their work in. This can only be done by admins, which seems excessive. Is this possible ?
      • Episode I : Exploring the World of Custom Functions in Zoho Desk

        Hello Again! Welcome to an Automation Adventure in the World of Zoho Desk Join us on a journey to explore how custom functions can enhance and extend the capabilities of Zoho Desk. Automation is everywhere. From robotic arms assembling products on factory
      • Add picklist in subform lookup

        Hello, I am trying to rewrite a script that works on a from as parent form, to the same form when is is a subform. Here is what I did in the form itself : RefCat = Offre_de_produits.distinct(Categorie_OFFRE); clear Categorie_LIGNE; for each Record in
      • Free Webinar - Overview of Zoho Sign and latest updates- May 2025

        Hello there! What's the digital alternative to endless paperwork? Zoho Sign. Designed for digital-first businesses, Zoho Sign is a complete digital signature solution with powerful features and seamless integrations that streamline your entire workflow.
      • Multiple Forecast configurations

        Hi all Is it possible to have multiple Forecast Configurations? That is, not just multiple forecasts, but rather different forecasts assigned to different configurations? The use case here is that we currently have the Forecast module configured for Revenue
      • Discussions from Ask The Experts 19: Inside Zoho Desk Spring Release 2025 : Zia in Focus

        Hello everyone, We had insightful discussions in both the sessions of Ask the Experts(ATE) 19, diving deep into engaging conversations around Zia and the spring release. Your enthusiastic participation and thought-provoking questions brought the sessions
      • Basic campaign set-up

        I have been trying hard to get Zoho CRM and Zoho Mail connected, but somewhere I seem to have wires loose in my brain. I'm not winning. Could anyone just show me (1) how to set up a scheduled email campaign (test to only six internal staff), (2) how to
      • Add template Categories Qoutes sendmale

        Is there other way to add other template categories in sendmail qoute?
      • What’s New in Zoho Inventory | April 2025

        Hello users, April has been a big month in Zoho Inventory! We’ve rolled out powerful new features to help you streamline production, optimise stock management, and tailor your workflows. While several updates bring helpful enhancements, three major additions
      • Remote Control Functionality During Screen Sharing in Zoho Cliq

        Hello Zoho Cliq Team, We would like to request the addition of remote control functionality during screen sharing sessions in Zoho Cliq. Currently, while screen sharing in Cliq is very useful, it lacks the ability for another participant to take control
      • Report categories not appearing in pie chart

        I have created a simple report to display all of the accounts in our CRM in a certain region, broken down by their 'account type' field. I have displayed this data as a donut chart Highlighted in red in the top left, you can see there are a total of 968
      • Asking for the implementation roadmap or step-by-step guide

        Hello everyone, I'm a freelancer who's been hired to implement Zoho CRM for a client, and I want to make sure I approach this correctly. Could someone kindly share a comprehensive implementation roadmap or step-by-step guide that covers all the essential
      • Recurring Events Not Appearing in "My Events" and therefore not syncing with Google Apps

        We use the Google Sync functionality for our events, and it appears to have been working fine except: I've created a set of recurring events that I noticed were missing from my Google Apps calendar. Upon further research, it appears this is occurring
      • Custom Function to Format Phone / Mobile numbers in Australian Standard format

        So I got sick of phone numbers being formatted incorrectly and Zoho not doing anything to standardise phone numbers to meet E.164 formats. So I went and coded my own function to fix this. And figured I'd share with the community This is specifically for
      • Sort By Date - Deluge

        I have the following code, which normally works to sort calls by created time. Every once in a while, it doesn't work and something sneaks through in the wrong order and I can't figure out why. calls = zoho.crm.searchRecords("Calls","(Owner:equals:" +
      • Automate Pricebook per Customer

        Example Scenario: I want to create a customer package (Silver Package, Gold Package, Platinum Package) and associated it with a Price Book that contains discounted prices for certain products. When a customer assigned to this Silver Package places an
      • Automate pricebook per customer

        Example Scenario: I want to create a customer package (Silver Package, Gold Package, Platinum Package) and associated it with a Price Book that contains discounted prices for certain products. When a customer assigned to this Silver Package places an
      • Automatic needs analysis for the Deals module

        Good day, I need to get the following functionality in the Deals module: 1. add a Analysis of Customer Needs (ACN) to each transaction 2. the ACN must have a unique number, preferably automatic 3. the ACN must link to the Deal 4. the ACN is completed
      • Color of Text Box Changes

        Sometimes I find the color of text boxes changed to a different color. This seems to happen when I reopen the same slide deck later. In the image that I am attaching, you see that the colors of the whole "virus," the "irology" part of "virology," and
      • Community Digest Noviembre 2024 - Todas las novedades en Español Zoho Community

        ¡Hola, Español Zoho Community! Wow, ya termina el año, ¡gracias a vuestra participación se nos ha pasado volando! Por eso mismo estamos preparando sorpresas para todos los que participáis en la Español Zoho Community para el próximo año, ¡estad atentos
      • Odd differential in Accounting and Physical Stock

        Hi there, Around six months ago we noticed that there was an error in one of our product's stock values. Despite seemingly having no outwards requests unfufilled for this item (I.E, no stock committed any longer at the moment), Accounting Stock is -1
      • Footer in PDF template doesn't stay at the bottom of the page

        When setting up a PDF template there is an option for a header / footer. The header stays at the top of the page however the footer does not. It appears the footer actually serves no purpose as it seems to rise up to underneath the header (see image). Is there a way to lock the footer to the bottom of the page? Otherwise what is the point of it? Image demonstrating what I mean. https://ibb.co/cJY1xZ4
      • Integración Books para cumplir la ley Crea y Crece y Ley Antifraude (VeriFactu)

        Hola: En principio, en julio de 2025, entra en vigor la ley Crea y Crece y Ley Antifraude (VeriFactu). ¿Sabéis si Zoho va a cumplir con la ley para cumplir con la facturación electrónica conectada a Hacienda? Gracias
      • 7008 Request rate limit exceeded. Please retry again later.

        Hello, I'm encountering an issue with the request limit for my function calls. The function is triggered whenever a new record is created, and in many cases, we create these records in bulk (around 50 at a time). For each record, a workflow is initiated
      • At transaction level Discounts for Zoho books UAE version

        Dear Team, Please add transaction level Discounts for Zoho books UAE version. I have been requesting Zoho team past 3 years, Transaction level Discounts is a mandatory option required for our business needs. Zoho books Indian version has the option then
      • No sync

        I have amended a note on my PC, startet syncing but on the smart phne sync starts und runs for minutes. The note amendment ist not shown, but when I go to version the new version is shown but not possibility to accepte the new version
      • Staff Tracking in

        Hi , I would like to see what activity my staff does over Zoho CRM and over Zoho Mail . I need to know which deals in CRM haven't been touched or had an activity on by a particular staff member . Basically need to establish what work.gets done by WFH
      • Input list of records in Lookup

        Salut, I have 2 scripts that input list of records in a lookup. The first on works fine, the second one doesn't and I do not know why. The only differences, is that the first one input in a lookup a list of records from an actual lookup field, and with
      • Sharing shreadsheets

        My wife and I each have zoho accounts. If She creates a spreadsheet and shares it with me using my email that is associated with my account, zoho shows a warning that I do not have a zoho account and the only way I can access this file is via the link
      • run a macro on a contact that bounced from crm email merge

        how would one run a macro on a contact that bounced from crm email merge? how would i tell zoho to run a macro that SIGNALS said bounced?
      • iOS 18 is here! Discover the enhanced Bigin app with iOS 18, iPadOS 18 and macOS Sequoia.

        Hello, everyone! We are excited to be back with new features and enhancements for the Bigin app. Let us take a look at the new iOS 18 and iPadOS 18 features. The following is the list of features covered in this update: Control widgets. New app icons.
      • Tip #34- How to Configure Proxy Settings for Unattended Access in Zoho Assist- 'Insider Insights'

        Hey Zoho Assist Community! For those using Zoho Assist's Unattended Access feature, you may come across a need to configure your device through a proxy server. This setup is particularly useful if your network requires strict proxy configurations for
      • The 3.1 biggest problems with Kiosk right now

        I can see a lot of promise in Kiosk, but it currently has limited functionality that makes it a bit of an ugly duckling. It's great at some things, but woeful at others, meaning people must rely on multiple tools within CRM for their business processes.
      • Autoresponders in Zoho CRM will be discontinued—transition to Cadences for enhanced engagement

        Zoho CRM’s Autoresponder feature will be discontinued by September 30, 2025. If you're currently using Autoresponders to automate email follow-ups, we recommend switching to Cadences, a more powerful, flexible, and multi-channel engagement tool for today's
      • GCLID arrives not in CRM with iframe integrated ZOHO Form

        Hello amazing community, I enabled Adwords integration in ZOHO CRM. I have a ZOHO Form integrated in a wordpress. I tested iframe and Javascript. I enabled the "handover" GCLID in the ZOHO Form. When I add a GLID with http://www.example.com/?gclid=TeSter-123.
      • Unified customer portal login

        As I'm a Zoho One subscriber I can provide my customers with portal access to many of the Zoho apps. However, the customer must have a separate login for each app, which may be difficult for them to manage and frustrating as all they understand is that
      • Let's Take Custom Slideshow A Step Further!

        Zoho Show comes with this useful feature called Custom Slideshow. It is, as implied by the name, a feature for slideshowing. I would argue it is a waste to use the feature just for slideshowing. For a custom slideshow, you define a subset of slides. Of
      • Simple Text Search Function

        Would it be too much to ask for a simple text search function? My slide decks are often simply collections of slides of a random over, and I often have to find the slide I need at a moment's notice. A text search function, no matter how rudimentary, would
      • Free webinar alert! Empower Customer Experience in a Changing World with Zoho Desk and Zoho Workplace

        Hello Zoho Workplace Community! We’re back with another exciting webinar—and this time, it’s all about delivering exceptional customer experiences. Join us for "Empower Customer Experience in a Changing World with Zoho Desk and Zoho Workplace," where
      • Next Page