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.
Best Answer
Here is my attempt at your requirement with both a verbose explanation and a minified version:
EDIT Minified: