Skip to main content Join us at TDX in San Francisco or on Salesforce+ on March 5-6 for the Developer Conference for the AI Agent Era. Register now.

#Formulas268 discussing

I have a formula field that outputs a a js code for my website. I am using the html values for the quotes so it doesn't mess with the formula itself which generally works fine. The formula is as follows: 

Untitled-1.png

 

The problem is that the output includes a second " at the end of the script url. If I remove the html code for the quote, it displays no quote, so its either 2 quotes or zero, neither of which works: 

 

<script src="https://www.cognitoforms.com/f/seamless.js"" data-key="mwEbRdNaJka44LUyoX8j-g" data-form="24"></script> 

 

Any idea why it's happening ?

 

#Formulas

 

@Formulas - Help, Tips and Tricks

3 answers
0/9000

I am trying to show how long ago a person started volunteering, to give coordinators a chance to celebrate milestones. I use the date field itself to group the report by Month in Year so it doesn't show the full date. So for my formula, I want it to display the original date of the date field with additional calculation showing how many years in service. 

Our date is captured in date field Contact.Start_of_volunteering__c 

I copied the formula from another report using a different date field but showing what a person's age is.  

 

When I try to validate this formula, it gives an error:  

Incorrect parameter type for function 'DAY()'. Expected Date, received Object 

 

 

"Start Date: " & 

TEXT(DAY(Contact.Start_of_volunteering__c)) & "/" & 

TEXT(MONTH(Contact.Start_of_volunteering__c)) & "/" & 

TEXT(YEAR(Contact.Start_of_volunteering__c)) & 

" Upcoming anniversary " & 

TEXT(

    YEAR(TODAY()) - YEAR(Contact.Start_of_volunteering__c) + 

    IF(

        DATE(

            YEAR(TODAY()),

            MONTH(Contact.Start_of_volunteering__c),

            DAY(Contact.Start_of_volunteering__c)

        ) < TODAY(),

        1,

        0

    )

)

2 answers
  1. Feb 24, 5:03 PM

    Try breaking your Formula up into 2 parts and test each piece separately doing a Syntax Check 

     

    Test 1

    "Start Date: " &

    TEXT(DAY(Contact.Start_of_volunteering__c)) & "/" &

    TEXT(MONTH(Contact.Start_of_volunteering__c)) & "/" &

    TEXT(YEAR(Contact.Start_of_volunteering__c)) &

    " Upcoming anniversary "

      

    Test 2

    TEXT(

    YEAR(TODAY()) - ( YEAR(Contact.Start_of_volunteering__c) +

    IF(

    DATE(

    YEAR(TODAY()),

    MONTH(Contact.Start_of_volunteering__c),

    DAY(Contact.Start_of_volunteering__c)

    ) < TODAY(),

    1 ,

    0 ))

    )

0/9000

Hi,  

 

I am trying to include the UK stamp duty calculations into SF based on a tiered structure,   I have a formula that works in excel and I have tried to mirror that in SF.  This is the formula I have in SF 

  

I

F(Our_purchase_price__c <=125000, Our_purchase_price__c*0.05,IF(Our_purchase_price__c<=250000,125000*0.05+(Our_purchase_price__c-125000)*0.07,IF(Our_purchase_price__c<=925000,125000*0.05+(250000-125000)*0.07+ Our_purchase_price__c-250000*0.1)))

  but it doesn't want to except this formula.  where am I going wrong? 

 

This formula in excel works fine but I can't seem to mirror it correctly in SF-  B3 being the "purchase price"  See below the excel formula 

 

=

IF(B3<=125000,B3*0.05,IF(B3<=250000,125000*0.05+(B3-125000)*0.07,IF(B3<=925000,125000*0.05+(250000-125000)*0.07+(B3-250000)*0.1)))

 

This is the structure

  | England - From 01/04/2025  | Stamp duty %

 |                                  125,000  | 5.0%

 |                                  250,000  | 7.0%

 |                                  925,000  | 10.0%

 |                               1,500,000  | 15.0%

 | Over £1.5m  | 17%

 

Thanks 

 

 

 

#Formulas

1 answer
  1. Feb 24, 3:57 PM

    Your formula is hard to read because everything is written in one long line. Try adding spaces and breaking it into multiple lines so it’s easier to follow. 

     

     Each IF condition should be on a new line, making the logic clearer. Also, in the last condition, it should be written as:

    Screenshot 2025-02-24 at 15.22.45.png

     

    (Our_purchase_price__c - 250000) * 0.1 

     

    instead of 

     

    Our_purchase_price__c-250000*0.1 

     

    If you’re still unsure where it’s going wrong, break the formula down step by step. Add conditions one at a time, check the output, and then build on it. That should help you pinpoint the issue. 

     

    Here is the formula :  

     

    IF(Our_purchase_price__c <= 125000,  

       Our_purchase_price__c * 0.05, 

     

       IF(Our_purchase_price__c <= 250000,  

          (125000 * 0.05) + (Our_purchase_price__c - 125000) * 0.07, 

     

          IF(Our_purchase_price__c <= 925000,  

             (125000 * 0.05) + (250000 - 125000) * 0.07 + (Our_purchase_price__c - 250000) * 0.10, 

     

             IF(Our_purchase_price__c <= 1500000,  

                (125000 * 0.05) + (250000 - 125000) * 0.07 + (925000 - 250000) * 0.10 + (Our_purchase_price__c - 925000) * 0.15, 

     

                (125000 * 0.05) + (250000 - 125000) * 0.07 + (925000 - 250000) * 0.10 + (1500000 - 925000) * 0.15 + (Our_purchase_price__c - 1500000) * 0.17 

             ) 

          ) 

       ) 

     

     

    Screenshot 2025-02-24 at 15.56.12.png

     

     

0/9000

I have a validation rule set up on my opportunities that requires 2 fields be completed (Proposal Doc Link, ChatGPT Feedback) before advancing passed the stage Proposal Development. 

 

The next stage is Pending Approval.  

 

When an opportunity is submitted for Approval, our approval process automatically moves it to the stage "Pending Approval" 

 

If I manually update the stage form Proposal Development to Pending Approval, it triggers the validation rule like it should. But if I hit the Submit For Approval Button, it will move the opportunity to "Pending Approval" regardless of if the validation rule is being broken. How do I prevent this?  

Screenshot 2025-02-20 at 4.33.12 PM.png

 

 

#Formulas

2 answers
  1. Feb 22, 11:10 AM

    Approval processes are workflow rules and these do not trigger validation rules, as per this doc. A workaround for this could be hiding the submit for approval button unless the current opportunity stage is "Pending Approval". You can do this by upgrading the page to use dynamic actions and then adding a filter on the "Submit for Approval" button using the opportunity stage field:download (63).png

     

     

0/9000

I want to capture the average strength of a relationship a contact has with our company. Currently the Relationship object is at user record only. So it captures each company's users strength with the contact. Is there a formula I can use in a report to capture the average or most common value per contact?  

 

Example: a contact has 4 relationships strength statuses with our users. 

Contact : Eva Fritz 

Company Employee (User): Adam - Status: Knows 

Company Employee (User): Bridget - Status: Knows 

Company Employee (User): Colm - Status: Former Colleague 

Company Employee (User): Daisy - Status: Worked with the company before 

 

Report Formula reports back, 

 

Contact: Eva Fritz Status = Knows (it took the most common value) 

 

#Salesforce Developer  #Data Management  #Sales Cloud  #Reports & Dashboards  #Formulas

1 answer
  1. Eric Praud (Activ8 Solar Energies) Forum Ambassador
    Feb 24, 12:12 PM

    Hi Catriona, 

     

    While it's possible to do this, it would need to be done with some automation (ie flows) that would update a field on the Account. But, in your example, what if you have 2 "Knows" and 2 "Former Colleagues"? Which value should roll up?

0/9000

Hi folks,    following formula populates true if the Date__c value is in the current FY (4/1 - 3/31)    AND(  MONTH(TODAY()) >= 4,  Date__c >= DATE(YEAR(TODAY()),04,01),  Date__c <= DATE(YEAR(TODAY() - 1),03,31)  ),  AND(  MONTH(TODAY()) <= 3,  Date__c >= DATE(YEAR(TODAY()) - 1 ,04,01),  Date__c <= DATE(YEAR(TODAY()),03,31)  )  )    I would like to have two additional formulas to split     "1st half of current FY"  --> (4/1 - 9/30)    and     "2nd half of current FY" --> (10/1 - 3/31)    Any ideas?    Regards,  Christian    

1 answer
  1. Feb 21, 12:08 PM

    @Christian Borowiak If you're creating two separate formula fields, please see below: 

     

    You can use the following formula to check 

    1st Half Of Current FY

    AND(    Date__c >= DATE(        IF(MONTH(TODAY()) >= 4, YEAR(TODAY()), YEAR(TODAY()) - 1), 4, 1    ),    Date__c <= DATE(        IF(MONTH(TODAY()) >= 4, YEAR(TODAY()), YEAR(TODAY()) - 1), 9, 30    ))

     

    And you can use the following to check 

    2nd Half of Current FY: 

     

    AND(    Date__c >= DATE(        IF(MONTH(TODAY()) >= 4, YEAR(TODAY()), YEAR(TODAY()) - 1), 10, 1    ),    Date__c <= DATE(        IF(MONTH(TODAY()) >= 4, YEAR(TODAY()) + 1, YEAR(TODAY())), 3, 31    ))

     

    I tried these in my Developer Sandbox and they seem to be working fine. Let me know if you have any questions.

0/9000

Hi all, the field name is interest posted in the loan product. I have set the lending product's "Rounding method" field to Nearest Round, and the digits after the decimal are 2.  

But when the interest posted field is updated through the backend, it shows more than 3 digits after the decimal. And if I try to update it from the UI then it is showing only up to 2 digits after decimal and that is correct. 

Please help, why it is showing 3 digits when it is calculating the field through the backend? 

 

#Salesforce Developer  #Trailhead  #Q2  #Formulas  #Integration

0/9000
Hi friends, unfortunatily my windows has been chrashed than my exchange Outlook server has been broken give my any sight to retrive. My Exchange OST file into microsoft Outlook version 2016 
16 answers
  1. Feb 24, 5:58 AM

     The EmailProHelp OST to PST Converter

    is a powerful and efficient tool designed to convert OST files into Outlook PST files with complete data integrity and accuracy. This versatile software not only converts OST to PST but also supports various other formats, including MBOX, PDF, JSON, and more. Users can easily select single or multiple OST files for conversion and export emails, contacts, journals, tasks, and notes directly to an Outlook PST file. 

     

    Key features include: 

     

    - Conversion of OST fis inleto multiple formats such as PST, MBOX, EML, EMLX, HTML, CSV, and MHTML. 

    - Capability to import OST files into over 15 web/cloud-based email clients, including Office 365, Gmail, Thunderbird, and Yahoo Mail. 

    - Compatibility with all versions of MS Outlook and Exchange Server, ensuring comprehensive support for OST files. 

    - This user-friendly software is suitable for both technical and non-technical users, making the conversion process seamless and efficient. 

0/9000

I have a very long formula, but just the first part is causing the error once I had add a fourth criterion.   

 

How can I incorporate the new fourth criterion (italicized) with a single outcome? 

Here's the example:  

IF(AND(ISPICKVAL(Account_Registration__c, "Retirement_Account"),

ISPICKVAL(Managed__c,"Managed"), 

(Employee = FALSE),

NOT(ISPICKVAL(Account_Type__c , "HSA")),

"0111-1111 and 0222-2222", 

... 

Thank you for your help!! 

4 answers
0/9000

Is there a way to create a report formula that returns a total count of how many users had more than 3 opportunities on a report. I am basically trying to see how many reps met their quota. 

 

I have grouped the report by user and then opportunity name. I'm looking for a way to return a 1 if a user has more than 3 opportunities. So if there are 10 users and 7 of them have more than 3 opportunities, the total on the summary formula would be 7. 

   Any help would be greatly appreciated!

@Formulas - Help, Tips and Tricks 

2 answers
  1. Feb 21, 5:31 PM
    That’s not gonna be possible using report filters, you would need to store the opportunity count in a custom field on the User object and update it with Flow or Code
0/9000