# Need a formula field to determine commission date

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.

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 Feb 23, 2016 at 20:23

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