Group Formulas - | Discussion | Salesforce Trailblazer Community

Mar 29, 2022, 9:00 AM

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 answers

Eric Praud (Activ8 Solar Energies) Forum Ambassador
Mar 31, 2022, 10:08 AM
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

Add a comment

Write an answer...

0/9000

Jun 3, 2024, 5:47 PM

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 answers

Jose Carlos De Oliveira (Survival International)
Apr 27, 7:49 PM
You can now achieve this using an Apex Action
https://unofficialsf.com/stringnormaliser-apex-action/

:-)

Add a comment

Write an answer...

0/9000

Mar 12, 11:42 PM

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 answers

Steve Molis (You Owe Me a Beer, Inc.)
Apr 15, 3:03 AM
@A. S.

https://www.youtube.com/watch?v=f4zyjLyBp64

Add a comment

Write an answer...

0/9000

Apr 9, 6:03 PM

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 answers

Steve Molis (You Owe Me a Beer, Inc.)
Apr 11, 6:34 PM
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?

Add a comment

Write an answer...

0/9000

Mar 26, 9:28 AM

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

8 answers

Teck Lung Ng (Kydon Group)
Mar 29, 12:45 PM
@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!

Add a comment

Write an answer...

0/9000

Mar 13, 6:14 PM

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 answer

Derrick Abbey (Relias)
Mar 13, 11:07 PM
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

Add a comment

Write an answer...

0/9000

Mar 10, 1:33 PM

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 answers

Steve Molis (You Owe Me a Beer, Inc.)
Mar 10, 2:54 PM
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

Add a comment

Write an answer...

0/9000

Feb 20, 2:54 AM

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 answers

Dhanashri Gadekar (Salesforce)
Feb 22, 4:32 PM
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 :)

Add a comment

Write an answer...

0/9000

Jan 28, 6:02 PM

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 answers

Jake Hammerman (Ingredion)
Feb 20, 5:04 AM
@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!

Add a comment

Write an answer...

0/9000

Feb 5, 6:59 PM

Formula field IF function multiple outcomes based on checkbox status

Hi! I am struggling to get this formula to work. The goal is IF Requires_Custom_Rappel_Rate__C checkbox is TRUE, assign value based on the value of Rate_of_Rappel__C, then IF Requires_Custom_Rappel_Rate__C checkbox is FALSE, assign value based on the value of Rappel_Height__C This is what I currently have for the formula but it keeps coming back with an error: IF( Requires_Custom_Rappel_Rate__c = TRUE,

IF( Rate_of_Rappel__c ="8", 62,

IF( Rate_of_Rappel__c <="7", 54,

IF( Rate_of_Rappel__c <="6", 46,

IF( Rate_of_Rappel__c <="5", 38,

IF( Rate_of_Rappel__c <="4", 30,

IF( Rate_of_Rappel__c <="3", 22,

IF( Rate_of_Rappel__c <="2", 14, NULL )))))))),

IF( Requires_Custom_Rappel_Rate__c = FALSE,

IF ( Rappel_Height__c <= 200, 46,

IF( Rappel_Height__c <= 330, 38,

IF( Rappel_Height__c <= 400, 30,

IF( Rappel_Height__c <= 500, 22,

IF( Rappel_Height__c > 500, NULL , NULL, NULL )))))))

@Formulas - Help, Tips and Tricks

3 answers

Feb 6, 2:39 PM

@Sydni Jardine - You’re very close — the main issues are:

You don’t need IF(checkbox = TRUE, …) / IF(checkbox = FALSE, …) in Salesforce formulas. A checkbox field is already boolean, so use it directly.
Your last height IF has too many arguments: IF(condition, true, false) only takes 3 parameters, but you have 4: NULL, NULL, NULL. That will throw an error.
Your “rate” section mixes "8" (text) with <= comparisons (numeric-style). Use numbers if the field is numeric, or TEXT() if it’s a picklist/text.

If Rate_of_Rappel__c is a Number data type

IF(  Requires_Custom_Rappel_Rate__c,  CASE(    Rate_of_Rappel__c,    8, 62,    7, 54,    6, 46,    5, 38,    4, 30,    3, 22,    2, 14,    NULL  ),  IF(    Rappel_Height__c <= 200, 46,    IF(      Rappel_Height__c <= 330, 38,      IF(        Rappel_Height__c <= 400, 30,        IF(          Rappel_Height__c <= 500, 22,          NULL        )      )    )  ))

If Rate_of_Rappel__c is a Picklist/Text field type

IF(  Requires_Custom_Rappel_Rate__c,  CASE(    TEXT(Rate_of_Rappel__c),    "8", 62,    "7", 54,    "6", 46,    "5", 38,    "4", 30,    "3", 22,    "2", 14,    NULL  ),  IF(    Rappel_Height__c <= 200, 46,    IF(      Rappel_Height__c <= 330, 38,      IF(        Rappel_Height__c <= 400, 30,        IF(          Rappel_Height__c <= 500, 22,          NULL        )      )    )  ))

Add a comment

Write an answer...

0/9000

Formulas - Help, Tips and Tricks

Group Leaders