Salesforce Developer Community

Chatter

Feed
0

Best Answers
0

Likes Received
0

Likes Given
12

Questions
12

Replies

Leap Year considered in Formula

We have a "Formula (Text)" field that shows a date that is 7 years from yesterday, see below.

TEXT(DAY(TODAY()-1)) & "/" & TEXT(MONTH(TODAY()-1)) & "/" & TEXT(YEAR(TODAY()-1)+7)

However this does not consider leap years, and produces a date like 29/02/2019, even though it's not a leap year that year. Please advise how the formula can be changed to consider leap years.

PProkes
April 10, 2012
Like
0

1 replies

Editing Trigger

I need to deactivate the part of the Trigger below that creates an error message when the Opportunity Created Date is greater than the Credit Expiry Date. The other parts of the Trigger can remain. Please help me, as when I've deleted some of the Trigger it didn't calculate the Line of Credit Used etc anymore.

trigger oppMasterLeaseAgreementUpdate on Opportunity (after insert, after update, after delete, after undelete) {
    Set<String> RECORDTYPE_NAMES = new Set<String>{'AUS : CFS : Opportunity Record Type', 'AUS : DFS : Opportunity Record Type'};

    OppUtilities.isOppTrigger = true;

    Opportunity[] records;

    //Get the records
    if (Trigger.isInsert || Trigger.isUndelete || Trigger.isUpdate)
        records = Trigger.new;
    else if (Trigger.isDelete)
        records = Trigger.old;


    Map<String, Id> recordTypeMap = oppUtilities.getOppRecordTypeMap();

    Set<Id> recordTypeIds = new Set<Id>();

    for (String recType : RECORDTYPE_NAMES) {
        System.debug(recType + '::' + recordTypeMap.get(recType));
        recordTypeIds.add(recordTypeMap.get(recType));
    }

    List<Id> mlaIds = new List<Id>();

    Map<Id, Map<Id, Datetime>> mapValidateDate = new Map<Id, Map<Id, Datetime>>();

    //Loop through the opportunity records
    for (Opportunity record : records) {
        if (recordTypeIds.contains(record.RecordTypeId)) {
            if (!(Trigger.isUpdate && Trigger.oldMap.get(record.Id).Amount == record.Amount
                    && Trigger.oldMap.get(record.Id).Master_Lease_Agreement__c == record.Master_Lease_Agreement__c
                    && Trigger.oldMap.get(record.Id).ForecastCategory == record.ForecastCategory)) {

                if (record.Master_Lease_Agreement__c != null) {
                    System.debug('debug1:' + record.Master_Lease_Agreement__c);
                    mlaIds.add(record.Master_Lease_Agreement__c);

                    //we need to store the created date because we are validating whether the it is greater than the MLA Credit_Expiry_Date__c
                    mapValidateDate.put(record.Id, new Map<Id, Datetime>{record.Master_Lease_Agreement__c => record.CreatedDate});
                }

                //In case the user changes the Master_Lease_Agreement__c field from one Master_Lease_Agreement__c Id to another, update the old one too
                if (Trigger.isUpdate && Trigger.oldMap.get(record.Id).Master_Lease_Agreement__c != record.Master_Lease_Agreement__c
                        && Trigger.oldMap.get(record.Id).Master_Lease_Agreement__c != null) {
                    System.debug('debug2:' + Trigger.oldMap.get(record.Id).Master_Lease_Agreement__c);
                    mlaIds.add(Trigger.oldMap.get(record.Id).Master_Lease_Agreement__c);
                }
            }
        }
    }

    if (mlaIds.size() > 0) {
        OppUtilities oppUtil = new OppUtilities();

        Map<Id, Decimal> totalWonAmountMap = new Map<Id, Decimal>();
        Map<Id, Decimal> totalOpenAmountMap = new Map<Id, Decimal>();

        totalWonAmountMap = oppUtil.getTotalOpportunityAmountsByMLAIds(mlaIds, OppUtilities.ForecastType.WON);
        totalOpenAmountMap = oppUtil.getTotalOpportunityAmountsByMLAIds(mlaIds, OppUtilities.ForecastType.OPEN);

        Map<Id, Master_Lease_Agreement__c> mapMLAs = new Map<Id, Master_Lease_Agreement__c>([SELECT Id, Name, Credit_Expiry_Date__c FROM Master_Lease_Agreement__c WHERE Id IN :mlaIds]);

        //loop through opps and throw add error to records that are linked to Master_Lease_Agreement__c that has Credit_Expiry_Date__c in the past
        for (Id oppId : mapValidateDate.keySet()) {
            Map<Id, Datetime> mapValidateDateInner = mapValidateDate.get(oppId);

            Id mlaId;

            //This inner map will always have 1 key-value pair in it, the mla Id related to the opp and the opp Created Date
            for (Id id : mapValidateDateInner.keySet())
                mlaId = id;

            Datetime oppCreatedDate = mapValidateDateInner.get(mlaId);

            System.debug('MLA Credit_Expiry_Date__c:' + mapMLAs.get(mlaId).Credit_Expiry_Date__c);
            System.debug('Opp Created Date:' + oppCreatedDate);
            if (Date.newInstance(oppCreatedDate.year(), oppCreatedDate.month(), oppCreatedDate.day()) > mapMLAs.get(mlaId).Credit_Expiry_Date__c)
                Trigger.newMap.get(oppId).addError('This Opportunity with Id=' + oppId + ' cannot be linked with Master Lease Agreement ' + mapMLAs.get(mlaId).Name
                                                        + ' because the Created Date of the Opportunity is past the Credit Expiry Date of the Master Lease Agreement');
        }

        //Update the Master_Lease_Agreement__c records' Line_of_Credit_Used__c and Line_of_Credit_in_Pipeline__c fields
        for (Master_Lease_Agreement__c mla : mapMLAs.values()) {
            mla.Line_of_Credit_Used__c = totalWonAmountMap.get(mla.Id);
            mla.Line_of_Credit_in_Pipeline__c = totalOpenAmountMap.get(mla.Id);
        }

        update mapMLAs.values();
    }

}

PProkes
July 03, 2011
Like
0

2 replies

Adding different currency amounts in Trigger

I have a trigger in my production environment which I just realized sums Opportunity Amounts irrespective of currency. For example, "AUD 150" plus "NZD 100" equals "AUD 250". I have set the NZD currency to be lower in the "Manage Currencies" section, but how can I incorporate the currency differences in the code? Below is a snapshot of my current code (that doesn't factor in the currency).

//Loop through the opportunity records
    for (Opportunity opp : opps) {
      if (opp.Amount != null) {
        Decimal totalAmount = 0.00;
        if (totalAmountMap.get(opp.Master_Lease_Agreement__c) != null)
          totalAmount += totalAmountMap.get(opp.Master_Lease_Agreement__c);

PProkes
June 06, 2011
Like
0

2 replies

Validation Rule formula has no errors, but doesn't work

The formula below has no errors, yet the only time a DFS Opportunity cannot currently be saved at "Submitted to Sales Support" is when the Soft Costs rule applies. Any advice on why the rule doesn't work for the Term and Pay Method component of the formula? When either of the 3 rules (i.e. 1 or 2 or 3) apply, then the opportunity shouldn't be able to be saved at "Submtted to Sales Support".

OR (
AND (
$RecordType.Name = "AUS : DFS : Opportunity Record Type" ,
Soft_Costs_percentage__c>0.2,
Soft_Costs_Approved__c =FALSE,
ISPICKVAL (StageName,"Submitted to Sales Support : DFS"
),
OR (
AND (
$RecordType.Name = "AUS : DFS : Opportunity Record Type", Term_Approved__c = FALSE,
IF (
Term_mos__c >
VALUE
(
Master_Lease_Agreement__r.Term_Exc_Interim_Not_To_Exceed_Mths__c
) , TRUE,
ISPICKVAL
(
StageName,"Submitted to Sales Support : DFS"
)
)
),
OR (
AND (
$RecordType.Name = "AUS : DFS : Opportunity Record Type" , Pay_Method_Approved__c = FALSE,
ISPICKVAL ( Account.Pay_Method_M__c ,"DDR"),
ISPICKVAL (Pay_Method__c ,"INV"),
ISPICKVAL (StageName,"Submitted to Sales Support : DFS"),
OR ($RecordType.Name = "AUS : DFS : Opportunity Record Type") ,
Pay_Method_Approved__c = FALSE,
ISPICKVAL ( Account.Pay_Method_M__c ,"INV"),
ISPICKVAL (Pay_Method__c ,"DDR"),
ISPICKVAL (StageName,"Submitted to Sales Support : DFS")
)
))))

PProkes
February 23, 2011
Like
0

3 replies

ISCHANGED formulas

There are 3 more formulas that I can't get right. If the Soft Costs, Term mos, or Pay Method fields get edited (see below) when the “…Approved” field for the respective field is TRUE, then the “…Approved” field should be changed to FALSE.

I've made an attempt but must be completely out.

IF ( Soft_Costs_Approved__c = TRUE ),

ISCHANGED ( Soft_Costs__c , Soft_Costs_Approved__c = FALSE )

OR ( IF ( Term_Approved__c = TRUE ),

ISCHANGED ( Term_mos__c , Term_Approved__c = FALSE )

OR ( IF ( Pay_Method_Approved__c = TRUE ),

ISCHANGED ( Pay_Method__c , Pay_Method_Approved__c = FALSE )

Ideally, I'd like for these 3 formulas to be combined into one formula.

PProkes
February 22, 2011
Like
0

1 replies

Formula - copy field value

We have a picklist field called "Pay Method" (Pay_Method2__c) on a custom object and on an Account page (Pay_Method__c). The custom object is called "Facility Limit".

What formula would ensure that the value entered in the custom object field will automatically override the value on the Account page? The thing making this tricky for me is that both are a picklist fields, and the formulas I've created have errors.

PProkes
February 21, 2011
Like
0

8 replies

Combining 3 formulas into 1

I wish for a "New Task" to be created and sent when either of the following 3 validation rules (that I want to combine into 1 rule) apply. However, when I join the 3 formulas together I get an error message saying "Error: Syntax error. Extra AND". Can someone please advise me how to join the 3 formulas below so no errors occur? I initially had them created as separate rules though I only want one Task to be created, not possibly 3 (for the one opportunity).

AND ( $RecordType.Name = "AUS : DFS : Opportunity Record Type" ,
Soft_Costs_percentage__c>0.2,
Soft_Costs_Approved__c =FALSE,
ISPICKVAL (StageName,"Submitted to Sales Support : DFS"))

AND
(
$RecordType.Name = "AUS : DFS : Opportunity Record Type", IF (
Term_mos__c >
VALUE
(
Master_Lease_Agreement__r.Term_Exc_Interim_Not_To_Exceed_Mths__c
) , TRUE,
ISPICKVAL
(
StageName,"Submitted to Sales Support : DFS"
)
)
)

AND ( $RecordType.Name = "AUS : DFS : Opportunity Record Type" ,
ISPICKVAL ( Account.Pay_Method__c ,"DDR"),
ISPICKVAL (Pay_Method__c ,"INV"),
ISPICKVAL (StageName,"Submitted to Sales Support : DFS"),
OR ($RecordType.Name = "AUS : DFS : Opportunity Record Type") ,
ISPICKVAL ( Account.Pay_Method__c ,"INV"),
ISPICKVAL (Pay_Method__c ,"DDR"),
ISPICKVAL (StageName,"Submitted to Sales Support : DFS"))

PProkes
February 20, 2011
Like
0

1 replies

IF Formula - Picklist

I'm not sure what would be wrong with my formula below... depending on what picklist is chosen, certain fields should be added or subtracted. In the example below, some real field names are replaced with Fields A & B.

IF ( ISPICKVAL ( Paid_Advance_or_Arrears__c ,"Advance",Field A + Field B ), AND (
IF ( ISPICKVAL ( Paid_Advance_or_Arrears__c ,"Arrears",Field A - Field B ))))

PProkes
February 17, 2011
Like
0

6 replies

Validation rules - matching picklists

Please advise what the correct formula would be for the following: before a user is able to save a DFS opportunity at the "Submitted to Sales Support : DFS" stage, the "Paid Advance or Arrears" fields on the Account and Opportunity page must match, if not, the opportunity cannot be saved at that stage. I've made an attempt at the formula below but the following error message appears: "Error: Field Paid_Advance_or_Arrears__c is a picklist field. Picklist fields are only supported in certain functions."

AND ( $RecordType.Name = "AUS : DFS : Opportunity Record Type" ,
ISPICKVAL (Account.Paid_Advance_or_Arrears__c = "Advance"),
ISPICKVAL (Paid_Advance_or_Arrears__c = "Arrears"),
ISPICKVAL (StageName,"Submitted to Sales Support : DFS"),
AND ( $RecordType.Name = "AUS : DFS : Opportunity Record Type" ,
ISPICKVAL (Account.Paid_Advance_or_Arrears__c = "Arrears"),
ISPICKVAL (Paid_Advance_or_Arrears__c = "Advance"),
ISPICKVAL (StageName,"Submitted to Sales Support : DFS")))

PProkes
February 15, 2011
Like
0

1 replies

Lock field if a field is populated

Our company has a Custom Object with about 20 fields on the page layout. We want to set a rule where if the "RV Approved" field is populated, all the other fields get locked (i.e. uneditable) for all users except for users in 2 profile id's listed below. I have tried the formula below (copied from our US parent SFDC environment, with the correct field & profile id's), though it seems users cannot edit any fields even when the "RV Approved" field is blank, once they've already saved a Custom Object record, and want to go back in and edit the record. Any ideas?

AND(NOT(ISNULL( PRIORVALUE(RV_Approved_Dollar__c))),
$Profile.Id <> "00e90000000Ebs1",
$Profile.Id <> "00e90000000Ebs6")

PProkes
October 19, 2010
Like
0

1 replies

Mandatory custom button based on field data

When a user enters an amount equal to or greater than $125,000 in the "Amount" field on the "Opportunity" page, how can I make a Custom Object button (currently named "New Residual Request") mandatory for that opportunity?

PProkes
October 14, 2010
Like
0

5 replies

Editing Triggers

Our company has created a "Custom Object" called "Facility Limit". It was created by our US parent in 2008. Now that our company has been bought out, I am the sole administrator in Australia and need to edit the "Facility Limit" object. I can't seem to edit the Custom Field names nor the Triggers coding, as it comes up with an error message stating that the field or trigger is linked to something else. I'm confused then how to make the necessary changes because I have to start somewhere. Any ideas?