1

We are trying to automate our monthly commissions report so that we are not manually having to change the dates in the report filter criteria below. I want to write a formula field to replace the following report filter to complete a "Commission Date" field.

enter image description here

This will allow us to have one report that we can group by Commission Date instead of 12 reports for each month.

Again we are looking for a report that can become cumulative so we can group on the commission date. Here are the combinations of what we are trying to do (I believe this captures all the options above):

  1. If the "ORD No" field contains "ORD" or "NA and the "Invoice Date" contains a date and the "Defer Commissions" checkbox is blank then the "Commission Date" is the sames as the "Invoice Date", OR
  2. If the "ORD No" field contains "ORD" or "NA" and the "Invoice Date" contains a date and the "Defer Commissions" checkbox is not blank then the "Commission Date" is Blank, OR
  3. If the "ORD No" field contains "SCH" and the "Lease Book and Bill Date" is blank then the "Commission Date" is Blank, OR
  4. If the "ORD No" field contains "SCH" and the "Lease Book and Bill Date" is not blank and the "Defer Commissions" checkbox is blank then the "Commission Date" is the "Lease Book and Bill Date", OR
  5. If the "ORD No" field contains "SCH" and the "Lease Book and Bill Date" is not blank and the "Defer Commissions" checkbox is not blank then the "Commissions Date" is blank.

I would not like to use a Relative Date Filter on the report. I would like to automate the commission date date field. Thank you.

  • This is a complex formula and is hard to debug/understand/maintain ....so..... create five formula fields one per 1-5 above, then one formula field that looks at the results of each to set CommissionDate__c - give that a try – cropredy Feb 23 '16 at 20:23
0

Boolean Formula Field1__c

CONTAINS('ORD:NA',OrdNo__c) && NOT(ISBLANK(invoiceDate__c)) && NOT(DeferCommissions__c)

Boolean Formula Field2__c

CONTAINS('ORD:NA',OrdNo__c) && NOT(ISBLANK(invoiceDate__c)) && DeferCommissions__c

Boolean Formula Field3__c

CONTAINS('SCH:',OrdNo__c) && ISBLANK(LeaseBookBillDate__c) 

Boolean formula Field4__c

CONTAINS('SCH:',OrdNo__c) && NOT(ISBLANK(LeaseBookBillDate__c)) && NOT(DeferCommissions__c)

Boolean formula Field5__c

CONTAINS('SCH:',OrdNo__c) && NOT(ISBLANK(LeaseBookBillDate__c)) && DeferCommissions__c

Date formula field CommissionDate__c

   IF (Field1__c,InvoiceDate__c,
   IF (Field2__c,NULL,
   IF (Field3__c,NULL,
   IF (Field4__c,LeaseBookBillDate__c,
   IF (Field5__c,NULL,NULL)
   ))))

Of course, use more meaningful names to you than Field1,2,3,4,5. Personally, I would name them something like IsXXX__c to reflect the business condition met by the underlying SObject

The advantage of this approach is

(1) you can temporarily place Field1-5 on your sandbox page and debug each condition

(2) the combining logic in Commission__c is clear and can be readily reordered in case precedence changes

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.