0

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.

4
  • One thing I am noticing here, you don't seem to have a value in your formula to denote whether the order is an "Open Sales Order" or a "Closed Sales Order". Is there something I am missing? – Bryan Anderson Nov 19 '19 at 16:49
  • @BryanAnderson it's the Line_Status__c value no? – Fadiddy Nov 19 '19 at 17:25
  • Sorry you're right, when I Ctrl+F "open" I didn't find anything. – Bryan Anderson Nov 19 '19 at 17:32
  • @BryanAnderson sorry I meant the not equal to 'CLOSED' being anything other value including OPEN. – Fadiddy Nov 19 '19 at 17:35
0

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)
4
  • Getting a Error: Syntax error. Missing ')'. Your syntax looks correct, and don't see why the error though. – Fadiddy Nov 19 '19 at 18:17
  • @Fadiddy I think I had an extra comma, I just updated my minified answer – Bryan Anderson Nov 19 '19 at 18:28
  • 1
    Thanks Bryan! That worked! Much appreciated! – Fadiddy Nov 19 '19 at 18:40
  • Glad I could help @Fadiddy – Bryan Anderson Nov 19 '19 at 18:41
1

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
)
4
  • Nice work @Derek F – Bryan Anderson Nov 19 '19 at 17:47
  • @Fadiddy Then you probably have some additional requirements that mean you'll need to modify the formula. My main goal was to help give you the tools to work through issues with formula syntax, and my secondary goal was to help you to think about how to compose a formula (copy/paste solutions aren't nearly as good a teacher as moderate struggle and eventually overcoming the problem on your own is). – Derek F Nov 19 '19 at 18:29
  • Derek, your code validated without any errors, however, when I try and test the scenarios above...it doesn't seem to be triggering the 'Past Due' checkbox correctly. Test cases 1: ========== For Line Status field set to 'CLOSED', and (Lessor of Actual Ship Date (8/8/19) or Fulfillment Date (11/15/19) > Scheduled Ship Date (11/21/19), the Past Due checkbox is still being checked in this scenario when it should be unchecked because neither the Actual Ship Date or Fulfillment Date is greater than the Scheduled Ship Date. – Fadiddy Nov 19 '19 at 18:34
  • @Fadiddy On closer inspection, it appears that I did make an incorrect assumption. I still think it'll be better for you to try to work through my mistake on your own though (a change to 2 tokens should fix it). – Derek F Nov 19 '19 at 18:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.