Formulas | Salesforce Trailblazer Community

26 juil. 2024, 19:29

Hello,

I have a Formula field called Birthday this Year which just takes the value of the standard birthdate field and changes the year to this year:

DATE( YEAR( DATEVALUE( NOW() ) ) , MONTH( Birthdate ) , DAY( Birthdate ) )

I have a custom text field that I'm attempting to swap out the standard Birthdate field with in this formula above, but I can't get any output. I'm not sure if it's because the custom field is actually a text field, but there are properly formatted date values in that text field. Here's my formula:

DATE( YEAR( DATEVALUE( NOW() ) ) , MONTH(DATEVALUE( Account.SL_BWM_Birthdate__c )) , DAY(DATEVALUE(Account.SL_BWM_Birthdate__c )))

Any ideas how I can transform a date in a text field into a date that's able to be pulled in above?

Thanks,

Ashley

15 réponses

Manok Jeet
11 août, 11:58
The key issue is likely the text-to-date conversion and whether the stored format matches what DATEVALUE expects. I recently tried a similar formula and found that checking the exact text format first made the fix much easier; keeping date fields as true dates is usually the simplest approach.

Ajouter un commentaire

Rédiger une réponse...

0/9000

7 août, 21:22

Last month default to dashboard and month filter has to be changed automatically

Hi,

I have a Date filter, Month and year, i am showing the last month filter ex: showing the July 2026 data in Aug 2026 month , meaning previous month, if i use the relative filter as previous month in the filter, will the filter in the date(MY Month) will automatically change when month is changed ?? using this relative filter previous month automatically change the filter value in the next month, ex: Sep, it will automatically select the aug 2026 data when month starts. Anyone please clarify, Thanks

Last month default to dashboard and month filter has to be changed automatically

#Tableau Desktop & Web Authoring #Reports & Dashboards #Tableau #Formulas #Tableau Prep

6 réponses

Don Wise (Tableau Community Ambassador)
8 août, 00:32
This can be done by creating the below calculation, using LOOKUP() function:For now, both July and August are in the view just so you can see that July's data for Quantity now lives in August's 'date', as shown above. But, we don't want to have to select two month's data at a time and yet, two month's of data MUST be in the view. somehow.

So, we then change the Order Date filter that you originally had into again, a Relative Date filter, again displaying only most recent two months, as I did before:

Then, the question remains, how to show just the current month, but only with last month's data in the current month. Again, another filter. BTW, both of these are Table Calculations, so certain fields must be in the view somewhere in order for them to all function correctly and display correctly. The new calculation will now filter the above result to only the last row in the view:
It is placed onto the Filters Card and in the dialog that pops up, it is set to TRUE. So that you end up with a single row of data displaying the most current month's 'date' with the prior months 'data':Example workbook attached. Again, these are table calculations, meaning they can be challenging to work with. So, if you add additional dimensions, certain settings might need to be adjusted depending on what you end up actually doing with this method - that is accomplished by right-clicking the table calculation in the view and selecting Edit Table Calculation.

As to it being automatic and adjusting for next month, it should based on the Relative Date filter..it would automatically adjust to September and pick up August's data. And so on.

If this response answered your question, please mark it as an Accepted Answer?
This way, others searching for similar solutions will find the answer faster in the future.

Best, Don Wise (Tableau Ambassador)
ExampleForcePriorMonthIntoCurrentMonth.twbx

Ajouter un commentaire

Rédiger une réponse...

0/9000

28 juil., 14:57

Getting Incorrect Parameters Error on Formula (4 Part formula)

Any suggestions would be appreciated. Thanks.

If conditions are met then the value in the field should be 1 of the 4 options.

JMP Staff

Early Adopter

Software Admin

Premier

Here is my current formula where I'm getting the error (Error: Incorrect number of parameters for function 'IF()'. Expected 3, received 2)

IF(

AND(

NOT(ISBLANK(ECUSTOMS__RPS_Date__c)),

NOT(

OR(

ECUSTOMS__IM_Status__c = "-",

ECUSTOMS__IM_Status__c = "Cleared",

ECUSTOMS__IM_Status__c = "False Hit",

ECUSTOMS__IM_Status__c = "Actioned",

ECUSTOMS__IM_Status__c = "Escalated",

ECUSTOMS__IM_Status__c = "Closed RPS",

ISBLANK(ECUSTOMS__IM_Status__c)

)

SAS_JMP_Employee__c = TRUE

"01. JMP Staff",

IF(

FALSE,

"02. Early Adopters",

IF(

AND(

OR(

CONTAINS(ECUSTOMS__RPS_Status__c, "Red"),

CONTAINS(ECUSTOMS__RPS_Status__c, "Yellow")

NOT(

OR(

ECUSTOMS__IM_Status__c = "-",

ECUSTOMS__IM_Status__c = "Cleared",

ECUSTOMS__IM_Status__c = "False Hit",

ECUSTOMS__IM_Status__c = "Actioned",

ECUSTOMS__IM_Status__c = "Escalated",

ECUSTOMS__IM_Status__c = "Closed RPS",

ISBLANK(ECUSTOMS__IM_Status__c)

)

INCLUDES(External_User_Audience__c, "Software Admin"),

AccountId <> "0015b00002KdPf4AAF"

"03. Software Admin",

IF(

AND(

NOT(ISBLANK(ECUSTOMS__RPS_Date__c)),

OR(

ECUSTOMS__RPS_Status__c <> "No Matches",

ECUSTOMS__RPS_RiskCountry_Status__c = "Alert"

Sales_Region__c = "Strategic",

Assigned_Authorizations__c > 0,

NOT(

OR(

ECUSTOMS__IM_Status__c = "-",

ECUSTOMS__IM_Status__c = "Cleared",

ECUSTOMS__IM_Status__c = "False Hit",

ECUSTOMS__IM_Status__c = "Actioned",

ECUSTOMS__IM_Status__c = "Escalated",

ECUSTOMS__IM_Status__c = "Closed RPS",

ISBLANK(ECUSTOMS__IM_Status__c)

)

OR(

Account.RecordTypeName__c = "Site Account",

Account.RecordTypeName__c = "User Location",

Account.RecordTypeName__c = "Enterprise Account",

Account.RecordTypeName__c = "JMP Subscription",

Account.RecordTypeName__c = "Customer Account",

Account.RecordTypeName__c = "SAS Transitional",

Account.RecordTypeName__c = "Reseller Account",

Account.RecordTypeName__c = "Related Entity",

ISBLANK(Account.RecordTypeName__c)

OR(

ISPICKVAL(Account.Account_Status__c, "Pending Approval"),

ISPICKVAL(Account.Account_Status__c, "Descartes Review"),

ISPICKVAL(Account.Account_Status__c, "Export Compliance Review"),

ISPICKVAL(Account.Account_Status__c, "Approved"),

ISPICKVAL(Account.Account_Status__c, "Waiting on Information"),

ISPICKVAL(Account.Account_Status__c, "eCommerce Approved"),

ISPICKVAL(Account.Account_Status__c, "MEQ Approved"),

ISBLANK(TEXT(Account.Account_Status__c))

)

"04. Premier"

))))

#Formulas

10 réponses

Steve Molis (You Owe Me a Beer, Inc.)
29 juil., 20:01
I'll keep your tab open at @Who owes me a beer?!?

Cheers,

SteveMo

Ajouter un commentaire

Rédiger une réponse...

0/9000

29 mars 2022, 09: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 réponses

Forum Ambassador Eric Praud (Activ8 Solar Energies)
31 mars 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

Ajouter un commentaire

Rédiger une réponse...

0/9000

21 juil., 20:14

Formula Field Rendered as Text Omitting 0 At the Start of a Number

I have a formula field that assigns a code based on a picklist selection. This then populates a text field on a record created in a flow.

I added a new code that starts with a zero (07). What I noticed though is when it populates the text field during record creation, it leaves the zero off the code.

How can I ensure that the zero remains?

TEXT(IF(

ISPICKVAL(Type_of_Grant__c, "Rent")

ISPICKVAL(Type_of_Grant__c, "Utility")

ISPICKVAL(Type_of_Grant__c, "Pharmacy")

ISPICKVAL(Type_of_Grant__c, "Move-in")

ISPICKVAL(Type_of_Grant__c, "Aging in Place")

23, IF(ISPICKVAL(Type_of_Grant__c, "Other") && Funding_Source__c = "Marc Berman", 23,

IF(ISPICKVAL(Type_of_Grant__c, "PHP-Security Deposit (Non-Subsidized)"), 20,

IF(ISPICKVAL(Type_of_Grant__c, "PHP-Security Deposit (Subsidized Housing)"), 20,

IF(ISPICKVAL(Type_of_Grant__c, "PHP-First Month's Rent (Non-Subsidized)"), 20,

IF(ISPICKVAL(Type_of_Grant__c, "PHP-First Month's Rent (Subsidized Housing)"), 20,

IF(ISPICKVAL(Type_of_Grant__c, "PHP-Utility (Non-Subsidized)"), 20,

IF(ISPICKVAL(Type_of_Grant__c, "PHP-Utility (Subsidized Housing)"), 20,

IF(ISPICKVAL(Type_of_Grant__c, "STRMU-Rent"), 20,

IF(ISPICKVAL(Type_of_Grant__c, "STRMU-Mortgage"), 20,

IF(ISPICKVAL(Type_of_Grant__c, "STRMU-Utilities"), 20,

IF(ISPICKVAL(Type_of_Grant__c, "C19R-Rent"), 20,

IF(ISPICKVAL(Type_of_Grant__c, "C19R-Mortgage"), 20,

IF(ISPICKVAL(Type_of_Grant__c, "C19R-Utilities"), 20,

IF(ISPICKVAL(Type_of_Grant__c, "Measure A"), 07,

IF(ISPICKVAL(Type_of_Grant__c, "DHSP Emergency Financial Assistance"), 20,

NULL)))))))))))))))))

#Formulas

2 réponses

22 juil., 00:24

Two things:

1) your formula structure is quite strange. You are saying "if this condition, then return a number, but hten convert that number back to text"

If you want text, just return text immediately e.g.

IF(ISPICKVAL(Type_of_Grant__c, "Other") && Funding_Source__c = "Marc Berman", 23, 
IF(ISPICKVAL(Type_of_Grant__c, "PHP-Security Deposit (Non-Subsidized)"), 20,

can simply become:

IF(ISPICKVAL(Type_of_Grant__c, "Other") && Funding_Source__c = "Marc Berman", "23", 
IF(ISPICKVAL(Type_of_Grant__c, "PHP-Security Deposit (Non-Subsidized)"), "20",

Then you no longer need to wrap everything in a TEXT() function.

2) You should avoid multiple IF statements on picklist functions. It will massively increase your compile size. CASE() is far more efficient and easier to read.

CASE(

Type_of_Grant__c,

"Rent", "23",

"Utility", "23",

"Pharmacy", "23",

"Move-in", "23",

"Aging in Place", "23",

"PHP-Security Deposit (Non-Subsidized)", "20",

"PHP-Security Deposit (Subsidized Housing)", "20",

"PHP-First Month's Rent (Non-Subsidized)", "20",

"PHP-First Month's Rent (Subsidized Housing)", "20",

"PHP-Utility (Non-Subsidized)", "20",

"PHP-Utility (Subsidized Housing)", "20",

"STRMU-Rent", "20",

"STRMU-Mortgage", "20",

"STRMU-Utilities", "20",

"C19R-Rent", "20",

"C19R-Mortgage", "20",

"C19R-Utilities", "20",

"Measure A", "07",

"DHSP Emergency Financial Assistance", "20",

"Other",

IF(

Funding_Source__c = "Marc Berman",

"23",

NULL

)

Ajouter un commentaire

Rédiger une réponse...

0/9000

20 juil., 13:05

User Object - Last Login field

I am trying to create a formula in a report that will allow me to categorise the number of Users who have not logged

IF(

TODAY() - DATEVALUE(user.Last_Login) > 30,

"No Login >30 Days",

"Logged In ≤30 Days"

)

However, I get this error - Warning

Error when encoding row-level formula: Field user.LAST_LOGIN does not exist. Check spelling.

I don't understand as I selected it from a drop down option

User Object - Last Login field

I don't understand why I am receiving this error.

I want to create a report so that I can display in a dashboard - % users not logged into SF in the past month

#Formulas

2 réponses

Angela Mullen-Smith (ROS)
20 juil., 13:45
Hi

I am working on it right now and I've got it to work - using this reference, but thank you for taking time to review

How to Calculate the Number Days that User Logged in Using Last Login API Name - ServiceMax Knowledge

Number of Days since last login
= (NOW() - LAST_LOGIN)

Login Status = IF(
NOW() - LAST_LOGIN > 30,
"Not Logged In for more than a Month",
"Logged In - Active Users"
)

Able to show 30% of Users are not actively using SF

Ajouter un commentaire

Rédiger une réponse...

0/9000

8 juin 2017, 07:31

EDB file to PST file converter

Checkout One of the best tool for EDB to PST Converter software to successfully restore emails from offline Exchange Server Database and export all mailbox to Outlook, Live Exchange. EDB to PST Converter Software,this application permits to convert EDB File to PST, EML, MSG and HTML with original method.This software, you can efficiently and easily restore and repair damaged, inaccessible or corrupt EDB database files and transform the same into Outlook PST.

This Software Supported MS Exchange server 2016, 2013, 2010, 2003, 2007, 2003, 2000 etc.

Read more information so visit here :- http://www.filesrepairtool.com/edb-to-pst-converter.html

8 réponses

Joseph Walker
18 juil., 11:52
When all you need is to connect to the Exchange mailbox from your Outlook program, using an EDB to PST conversion tool will be the simplest solution for you. These tools work by scanning the Exchange database and exporting emails, contacts, calendars, and other attachments into the PST format. The best thing about it works when the Exchange server is not available.

While comparing different options, I found that choosing a GainTools EDB to PST conversion Tool with mailbox preview, batch export and filtering features makes the process much easier, especially for larger databases. I tested one during an Exchange migration and the preview helped confirm that folders and email properties were intact before exporting. If the software also supports Office 365 or Live Exchange export, it can save additional migration work later. Always test the free trial first to confirm it reads the EDB file correctly before purchasing.

Ajouter un commentaire

Rédiger une réponse...

0/9000

12 juil., 23:30

I am trying to update this validation rule for email or phone required, however, I added a custom permission to bypass it for certain ppl so I need to update the Validation Rule reflect the bypass. Is there someone that can tell how to change the formulas

AND(

OR(

ISNEW(),

ISCHANGED(Email),

ISCHANGED(Phone)

ISBLANK(Email),

ISBLANK(Phone)

)

3 réponses

shefali tanwar (Trail Guild)
13 juil., 07:25
AND(
NOT($Permission.Your_Custom_Permission_Name),
OR(
ISNEW(),
ISCHANGED(Email),
ISCHANGED(Phone)
), Note: Replace Your_Custom_Permission_Name with the exact API Name of your custom permission, ensuring you do not include any spaces
ISBLANK(Email),
ISBLANK(Phone)

Ajouter un commentaire

Rédiger une réponse...

0/9000

6 juil., 22:01

Halp! :) Issue with formula

The following formula is not enforcing the minimum wage rule - either Jobz__r.State_Minimum_Wage__c or 10 - the greater of the 2 is the minimum... unless CA - then minimum is $25. Formula: IF( OR( ISBLANK( Est_Rev_Value__c ), Hours_for_Formulas__c <= 0 ), 0, ( Est_Rev_Value__c * (1 - Target_Margin__c) - VMS_Fee_Wkly_Amt__c - MIN( Jobz__r.GSA_Weekly_Housing__c, MAX( 0, ( Est_Rev_Value__c * (1 - Target_Margin__c) ) - Total_Fixed_Expenses__c - ( IF( Facility_State__c = "CA", MAX(25.00, BLANKVALUE(Jobz__r.State_Minimum_Wage__c, 25.00)), BLANKVALUE(Jobz__r.State_Minimum_Wage__c, 10.00) ) * ( ( MIN(Hours_for_Formulas__c, 40) + MAX(Hours_for_Formulas__c - 40, 0) * 1.5 ) * 1.08 + ( Unbillable_Hours__c / MAX( Contract_Length__c , 1) ) ) ) ) ) - Total_Fixed_Expenses__c ) / ( ( MIN(Hours_for_Formulas__c, 40) + MAX(Hours_for_Formulas__c - 40, 0) * 1.5 ) * 1.08 + ( Unbillable_Hours__c / MAX( Contract_Length__c , 1) ) ) )

3 réponses

Steve Molis (You Owe Me a Beer, Inc.)
7 juil., 15:20
Can you create a List View or Report that displays all of the Fields that are in your Formula AND the Formula Field side-by-side showing multiple combinations of input values and results and post a screenshot?

It's a LOT easier to troubleshoot when we can see the Input Values going into the Formula and the Output/Results together.

Ajouter un commentaire

Rédiger une réponse...

0/9000

27 avr. 2023, 19:48

I need some help with a formula field where one value changes yearly based on the close date.

We receive donations of food which are valued by pound. The value per pound changes each year. I'm trying to figure out the easiest way to have a "Donated Food Value" field that calculates this for us so that I'm not trusting people to do math. In 2022 food per pound was $1.74 and in 2023 it is $1.92. I want to be able to enter the number of pounds in the "Pounds of Food Donated" field and have the "Donated Food Value" field automatically calculate the value based on the close date. I also need to be able to add a new value each year. Is this possible?

8 réponses

Steve Molis (You Owe Me a Beer, Inc.)
28 avr. 2023, 13:28
Okay, if this is just a "once a year thing" and doing an annual update is not too much of a PITA

Then using a Formula like this, and then updating it each November should work. You can even build it out by adding ""placeholders" for future years, and fill in the values later
Donation_Weight__c *
CASE( YEAR ( CloseDate ),
2020, 1.87,
2021, 1.70,
2022, 1.74,
2023, 1.92,
2024, 0,
2025, 0,
2026, 0,
2027, 0,
2028, 0,
2029, 0,
NULL )