Multiple IF Statements for Formula Checkbox

Question

I am new to Salesforce, and am still learning about the formula fields...so please bare with me.

I am looking to create a formula for a new checkbox called Past Due to perform the following functions:

1) For Open Sales Order types:

Today > Scheduled Ship Date = Past Due

OR

2) For Closed Sales Order types:

(Lessor of Actual Ship Date or Fulfillment Date) > Scheduled Ship Date = Past 
Due

Note: some order types will only have a fulfillment date, and Actual Ship Date will be blank. This happens for "non-product" order types like Service or Software.

I have the following fields to use within a Sales Order object; Scheduled Ship Date, Actual Ship Date, Fulfillment Date, Order type, and Order Status.

Here is what I have come up with so far, but I keep getting an syntax error when trying to validate it:

IF(
    OR(
        TODAY() > Scheduled_Ship_Date__c,
        Line_Type__c = 'Standard-Line US',
        Line_Status__c != 'CLOSED' = TRUE
    OR(
        (Actual_Shipment_Date__c < DATEVALUE(Fulfillment_Date__c),
        Actual_Shipment_Date__c, DATEVALUE(Fulfillment_Date__c)),
    Line_Type__c = 'Standard-Line US',
        Line_Status__c != 'CLOSED' = TRUE
    )),
    True,
    False
)

Any help would be greatly appreciated.

Thanks.

Answer 1

Here is my attempt at your requirement with both a verbose explanation and a minified version:

IF
(
    OR
    (
         //First Scenario Where Sales Type is Open Order and TODAY's date is greater than Scheduled Ship Date
        AND
        (
            TODAY() > Scheduled_Ship_Date__c,
            Line_Status__c = 'OPEN'
        ),

        //Second Scenario where Sales Type is Closed Order AND
        //Actual Ship Date is greater than Scheduled Ship Date
        //OR
        //Fulfillment Date is greater than Scheduled Ship Date
        AND
        (
            OR
            (
                Actual_Shipment_Date__c > Scheduled_Ship_Date__c,
                DATEVALUE(Fulfillment_Date__c) > Scheduled_Ship_Date__c,
            ),
            Line_Status__c = 'CLOSED'
        )
    ),
    True,
    False
)

EDIT Minified:

IF(OR(AND((TODAY() > Scheduled_Ship_Date__c),(Line_Status__c = 'OPEN')),AND(OR((Actual_Shipment_Date__c > Scheduled_Ship_Date__c),(DATEVALUE(Fulfillment_Date__c) > Scheduled_Ship_Date__c)),(Line_Status__c = 'CLOSED'))),True,False)

Answer 2

Probably the most common issue with formula syntax is mismatched parenthesis, or missing commas.

Being rigorous with the formatting (even though it won't appear formatted in Salesforce, until you go to edit the formula, that is) can really help make errors more apparent. Consistency and keeping each line fairly short/self-contained are key here. Applying that to your formula...

IF(
    OR(
        TODAY() > Scheduled_Ship_Date__c,
        Line_Type__c = 'Standard-Line US',
        Line_Status__c != 'CLOSED' = TRUE
        OR(
            (
                Actual_Shipment_Date__c < DATEVALUE(Fulfillment_Date__c),
                Actual_Shipment_Date__c, 
                DATEVALUE(Fulfillment_Date__c)
            ),
            Line_Type__c = 'Standard-Line US',
            Line_Status__c != 'CLOSED' = TRUE
        )
    ),
    True,
    False
)

There are a couple of issues here:

• Line_Status__c != 'CLOSED' = TRUE isn't correct syntax (the comparison != will return a boolean result)
• Some commas are missing (e.g. after the first instance of Line_Status__c != 'CLOSED' = TRUE)
• Actual_Shipment_Date__c is a Date/DateTime, and cannot be used alone in a boolean expression (it needs to be compared against something)

Also, something that isn't an issue per se, IF(condition, true, false) can be replaced with just condition. The condition expression evaluates to a boolean type, so wrapping everything inside of an extra IF() is not required here (since the result is being fed to a checkbox, i.e. a boolean, field).

My attempt to "fix" this formula would look like this:

IF(
    Line_Status__c != 'CLOSED',
    Scheduled_Ship_Date__c < TODAY(),
    IF(
        NOT(ISBLANK(Actual_Shipment_Date__c)),
        IF(
            Actual_Shipment_Date__c < Fulfillment_Date__c,
            Actual_Shipment_Date__c,
            Fulfillment_Date__c
        ),
        Fulfillment_Date__c
    ) < TODAY()
)

This should satisfy the requirements you set out (if not closed, check today against the scheduled ship date, else check the lesser of actual/fulfillment against today).

Some explanation as to how this works (which won't be valid syntax with my comments):

IF(                                            // Our first job is to figure out whether we're working 
                                               //   on something that is closed, or something that is open
    Line_Status__c != 'CLOSED',
    Scheduled_Ship_Date__c < TODAY(),          // The case where our record is open is just a simple comparison
    IF(                                        // The "closed" case is more interesting
        NOT(ISBLANK(Actual_Shipment_Date__c)), // If actual shipment date is not blank, we need 
                                               //   to do more work
        IF(                                    // The purpose of this IF() is to select which 
                                               //   field to use in the comparison that is coming up
            Actual_Shipment_Date__c < Fulfillment_Date__c,
            Actual_Shipment_Date__c,
            Fulfillment_Date__c
        ),
        Fulfillment_Date__c                    // If there is no actual shipment date, just 
                                               //   use fulfillment date
    ) < TODAY()                                // The result of THIS particular IF() gives us 
                                               //   the date we want to compare
                                               // Compare it against TODAY to get our target true/false value
)