Skip to main content
GruposFormulas - Help, Tips and Tricks
Grupo

Formulas - Help, Tips and Tricks

Ask your formula questions. Also, share and learn some great formula tips and tricks.
Formular una pregunta...
Tarah Laurent ha preguntado en #Formulas
29 jul 2024, 19:37
IF statement using OR and AND

I am working on a Row-Level report formula, and I keep getting a Missing ')' error on this formula. I've tried looking online for tips but I'm still missing something. 

 

I played around with it, so I might've made it worst. I am trying to create a validation field for various conditions.

 

Here's what I have:

 

If(Churn_Event__c.Effective_Date__c > TODAY(),

    If( OR(

            IF(AND(

                (ISPICKVAL(Churn_Event__c.Save_Successful__c, "yes")),

                Churn_Event__c.Total_Amount_of_ARR_Churned__c < Churn_Event__c.Churned_Revenue__c)

                "Yes",

            IF(AND (ISBLANK(Churn_Event__c.Save_Successful__c)),

                    Churn_Event__c.Total_Amount_of_ARR_Churned__c = Churn_Event__c.Churned_Revenue__c)

                "Yes",

            IF(AND (ISPICKVAL(Churn_Event__c.Save_Successful__c, "no")),

                Churn_Event__c.Total_Amount_of_ARR_Churned__c = Churn_Event__c.Churned_Revenue__c)

                "Yes",

                ),

          ),

    "No"), 

"No")

 

@Formulas - Help, Tips and Tricks 

1 respuesta
  1. Arul jothi (Yuga Shift Corporation)
    14 sept, 18:53

    Hi Tarah, 

    I have reviewed the Row-Level Formula and identified the issue with the formula structure. The IF() statements were being used inside the OR() condition, which was causing the “Missing )” error.

    I have restructured the formula to validate the required conditions correctly: 

     IF( Churn_Event__c.Effective_Date__c > TODAY(), IF( OR( AND( ISPICKVAL(Churn_Event__c.Save_Successful__c, "yes"), Churn_Event__c.Total_Amount_of_ARR_Churned__c < Churn_Event__c.Churned_Revenue__c ), AND( ISBLANK(Churn_Event__c.Save_Successful__c), Churn_Event__c.Total_Amount_of_ARR_Churned__c = Churn_Event__c.Churned_Revenue__c ), AND( ISPICKVAL(Churn_Event__c.Save_Successful__c, "no"), Churn_Event__c.Total_Amount_of_ARR_Churned__c = Churn_Event__c.Churned_Revenue__c ) ), "Yes", "No" ), "No" )  

     

    This formula returns

    “Yes” when the Effective Date is in the future and any one of the three defined conditions is met. Otherwise, it returns “No.”

Escribir una respuesta...
0/9000
Lauren Wike ha preguntado en #Formulas
29 mar 2022, 9:00
How to add a custom field to the multi-line layout for Quote Line Items?

Hi all,

 

I need to add a custom field (text-based formula) to the multi-line layout for Quote Line Items so that when colleagues are adding line items to quotes, they know which section of the quote to add items to.

 

However, when I click Edit Multi-Line Layout on the Page Layout, the field isn't listed as an available field.

 

Is there a way around this?

 

Appreciate any advice!

 

@Formulas - Help, Tips and Tricks 

11 respuestas
  1. Eric Praud (Activ8 Solar Energies) Forum Ambassador
    31 mar 2022, 10:08

    There's a padlock as it's a read-only field (formula).

    Now, it looks like formula fields are not available, so what you coudl do is to create a Text field that gets populated by a record-triggered flow with your formula.

    You can then show this text field in your multi-line layout

Escribir una respuesta...
0/9000
Bob Moalli (Sales Operations Technology en MEDITECH)
3 jun 2024, 17:47
Replacing a special character in a TEXT FIELD.

This approach solved my problem except I'm not having luck adding in more SUBSTITUTES.   

 

SUBSTITUTE({!$Record.FIELD__c} ,  LEFT( {!$Record.FIELD__c}  ,  FIND(".",  {!$Record.FIELD__c} )), "")

&SUBSTITUTE( {!$Record.FIELD__c}  ,  LEFT( {!$Record.FIELD__c}  ,  FIND("-",  {!$Record.FIELD__c} )), "")

&SUBSTITUTE( {!$Record.FIELD__c}  ,  LEFT( {!$Record.FIELD__c}  ,  FIND("/",  {!$Record.FIELD__c} )), "")

 

 I'm looking for a more efficient method so that I could just check to see if the field is A-Z and replace the special character.

 

@Bob Moalli

 

Any input greatly appreciated. 

5 respuestas
Escribir una respuesta...
0/9000
A. S. (Axio) ha preguntado en #Formulas
12 mar, 23:42
Approval Process and Validation Formula

We have a Validation formula that works in concert with an Approval Process.  Below are both of them described followed by a question.

         a. Approval Process says that a user can run the Approval process when the picklist field "Stage" is blank on a Growth Opportunity.

        b.  Validation formula is below.      Goal of using a. and b. together: We do not want our users to select key picklist options from the field "Stage" on a Growth Opportunity record unless that record has gone through the Approval Process (key picklist options that aren't allowed without approval are below).  All other picklist options must be available to the users whether the record has gone through approval or not (that list is also below).  And a user should be able to run the record through the approval process if either of the "allowed" approval options below is selected in the record.     Stage options not allowed without approval: 

  • Stage 0 
  • Stage 1
  • Stage 2
  • Stage 3
  • Stage 4
  • Stage 5
  • Stage 6
  • Bid Won

 

Stage options that must be allowed without approval: 

  • Lead
  • Long Term
  • Bid Lost
  • Cancelled
  • Contract Ended
  • No Bid
  • Protest

 Validation Formula:

AND(

ISCHANGED( TM_GovSuite__Stage__c ) ,

New_Opp_AP_Check_Backend__c = FALSE,

CASE( TM_GovSuite__Stage__c ,

'Bid Lost', 1,

'Cancelled', 1,

'No Bid', 1,

'Contract Ended', 1,

'Protest', 1,

'Long-Term Positioning', 1,

'Lead',1,

0 ) = 0

)

 

Question:  does it make sense to couple the approval process and a validation formula for this?  The way I have it - the process isn't working.  How can I make it possible?    

 

@Formulas - Help, Tips and Tricks 

13 respuestas
Escribir una respuesta...
0/9000
Prasadika Fernando (Senior Salesforce Engineer en Azur Technology) ha preguntado en #Formulas
9 abr, 18:03
Creating a formula field on Activity to fetch WhatId's field value

Hi trailblazers,  I have a long text area field with a length of 10,000 on Quote and Opportunity. I might have to increase the length later. They both have the same API name; ABC_MyField__c. And, I create Task records on two occasions. In the first occasion, the "Related To" field has an Opportunity linked to it. And, in the other occasion, the "Related To" field has a Quote linked to it. I want to have the ability to reflect this field value on Task object. For this, I tried creating a formula field on Activity. But, the syntax is incorrect. Could you please help me get this reflected on Task? Thanks.   

IF(

    NOT(ISBLANK(Opportunity.Id)),

    Opportunity.ABC_MyField__c,

    IF(

        NOT(ISBLANK(Quote.Id)),

        Quote.ABC_MyField__c,

        ""

    )

)   

@Formulas - Help, Tips and Tricks

4 respuestas
  1. Steve Molis (You Owe Me a Beer, Inc.)
    11 abr, 18:34

    You would need to re-purpose an existing field, like Task: Comments. 

     

    How is the information in the Activity field going to be used?   

     

    Why can't users just access the field on the Opportunity or Quote records from the Related To field on the Activity record? 

Escribir una respuesta...
0/9000
Davuluri Harsha (Senior Associate Consultant en Infosys Limited)
26 mar, 9:28
Formulas in Reports

Formulas in Reports

I want to show the 'Activity Type' picklist values in a separate individual columns using of formulas in Salesforce reports? I tried with formulas in reports but didn't worked. How can we achieve this. I'd like to show this as below in report. Thanks in advance  

image.png

 

 

 

8 respuestas
  1. Teck Lung Ng (Kydon Group)
    29 mar, 12:45

    @Davuluri Harsha, I can understand what you want to achieve, see my mock screen below.  You want the values of Activity Type picklist as columns in your report; presented as a TRUE or FALSE.  I'm not sure this is technically possible though.  I'm very interested to know if this is technically feasible.  Hopefully someone else can advise. Cheers! , I can understand what you want to achieve, see my mock screen below. You want the values of Activity Type picklist as columns in your report; presented as a TRUE or FALSE.

     

     

Escribir una respuesta...
0/9000
Brittany Flentge (Salesforce Administrator en Annexus)
13 mar, 18:14
Deriving Formula Text Field from a Picklist Value

Hi all! 

 

I have a few picklist values and I am trying to segment them into two categories via a text formula field.  

 

An example of this would be using a picklist field of "food item", with picklist values of "banana", "apple", "celery", "carrot",  and then a text value to segment them into either "fruit" or "vegetable" for the text formula field. Obviously I can make it a dependent picklist but the goal would be to not have a field that needs manual updating or something that requires a flow.  

 

Is it possible to achieve through a formula field? 

1 respuesta
  1. Derrick Abbey (Relias)
    13 mar, 23:07

    Yes, this is a perfect example of when to use the case function in a formula field.  It would look something like this:

    CASE(TEXT(Food_Item__c),
        "banana","fruit",
        "apple","fruit",
        "celery","vegetable",
        "carrot","vegetable",
       ""
    )

     

    Here's a link to the reference documentation for the case function: 

    CASE

Escribir una respuesta...
0/9000
Scott Myers (Director of Enrollment Operations en Lebanon Valley College)
10 mar, 13:33
Is there a formula that would show me how many event registrations we had 10-days prior to the start of an event?

Here's what I have so far. The formula is valid but I still get records of registrations received the day of the event instead of just those who registered10-days prior and earlier.  

 

IF(conference360__Attendee__c.conference360__Registration_DateTime__c:UNIQUE <= (conference360__Attendee__c.conference360__Event_Start_Date__c:UNIQUE - 10), 1, 0)

4 respuestas
  1. Steve Molis (You Owe Me a Beer, Inc.)
    10 mar, 14:54

    PS.  Personally, I would use a Formula(Number) like this 

    ( conference360__Attendee__c.conference360__Event_Start_Date__c
     - 
    conference360__Attendee__c.conference360__Registration_DateTime__c )

    That way you can filter/bucket the Report over multiple Date Ranges, instead of being "locked in" to just 10+ Days

Escribir una respuesta...
0/9000
Marc Bindlechner (New Enquiry Manager en LHD Lawyers) ha preguntado en #Formulas
20 feb, 2:54
Pull field value from one object, based on criteria from another object

Hey There 

 

I’ve got objectA & objectB, linked via child>parent relationship where objectA = Parent and objectB = Child

 

Both objects contain multiple unique records within.

 

My goal is to return a value from objectB, based on criteria match between objectA & objectB

 

Formula essentially is:

If( objectA.fieldA = objectB.fieldA && TEXT(objectA.fieldB) = TEXT(objectB.fieldB), objectB.fieldC, “error”)

 

FieldA in each object is TEXT

FieldB in each object is PICKVAL

 

Values in each Field match across the objects, both by field content and case.

 

Cannot get this to work 😞

 

I suspect this might be because I’m trying to get ObjectA to search and match across multiple records within ObjectB?

 

Would love to please hear ideas on how to solve? 🙏🏻 

 

@Formulas - Help, Tips and Tricks

 

 

#Formulas

5 respuestas
  1. Dhanashri Gadekar (Salesforce)
    22 feb, 16:32

    well the formulas can only look "Up" (Child to Parent), never "Down" or "Sideways" (Parent to Child or across unrelated records) and this is one of the limitation we have in salesforce. 

    Why your current logic is failing is bcoz

    Child records have a "hardcoded" link to exactly one Parent and Parent records

    have no field pointing to the children; they only have a "Related List," which is a dynamic query. Formulas cannot run queries.. 

     

    as a best approach, Set a Record-Triggered Flow to run on 'ObjectA' when it is created or updated.

    • The Logic: Use a Get Records element to search ObjectB.
    • Filter where FieldA equals $Record.FieldA AND FieldB (converted to text) equals $Record.FieldB.
    • The Action: If a match is found, use an Update Records element to stamp the value from ObjectB.FieldC into a plain text field on ObjectA.

    This allows you to "search" the database, handle the "error" logic if no match is found, and physically store the value on 'ObjectA' so you can use it in reports or other automation. Let me know if you need any further help on this :)

Escribir una respuesta...
0/9000
Jake Hammerman (Global Salesforce Manager en Ingredion) ha preguntado en #Formulas
28 ene, 18:02
Summary-Level Formula Question

I've been searching for formula help on this but can't seem to quite get a solution so hoping someone can help out!    Problem:  Opportunities with Products Report with a Grouping Field of Global Region (Opportunity.Global_Region__c). Global Region is a picklist field but is related to the Global Region on the account where the opportunity is created from.  Needing to take YTD Closed Won totals (using Expected Product Amount) for separate regions and create a formula to compare the individual regions with static 2025 net sales totals for each one. Summary-Level Formula used for the global grand total which works with the static total sales value.    Example (without formulas and field names):  USCA YTD Sum of Expected Product Amount = $10M  USCA 2025 Total Sales = $50M  CY YTD vs. PY Rev = 20%    Would rather not have to create 4 separate reports to calculate the metric but also not creating new fields on the page.      @Formulas - Help, Tips and Tricks 

7 respuestas
  1. Jake Hammerman (Ingredion)
    20 feb, 5:04

    @Steve Molis, can let this one go. Had a coaching session with Salesforce and essentially, no other solution in my org other than creating a custom field with static sales numbers to use in the formula. Appreciate the help though!

Escribir una respuesta...
0/9000