function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Rachel LinderRachel Linder 

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 (for the month of December as an example below) to complete a "Commission Date" field.
User-added image


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 cumulaitve so we can group on the commissio 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.

Thanks.
Rachel LinderRachel Linder
I would not like to use a Relative date filter on the report. I would like to automate the calculation of a commission date field. If there is anyone who can help I would appreciate it.
Parker EdelmannParker Edelmann
All five of your options are phrased as If-Then statements followed by an or, with the exception of the last option. If the 'OR' at the end of the options translates to an 'else' in an If-then-else formula, then I believe that this formula should work to find the Commission Date:
IF(AND(OR(CONTAINS(ORD_No__c, "ORD"), CONTAINS(ORD_No__c, "NA")), !ISBLANK(Invoice_Date__c)),
   IF(Defer_Commissions__c, blank, Invoice_Date__c )
   IF(CONTAINS(ORD_No__c,"SCH"),
      IF(ISBLANK(Lease_Book_and_Bill_Date__c), blank,
         IF(!Defer_Commissions__c, Lease_Book_and_Bill_Date__c, blank))))
It's a little confusing because I combined some of the options to save space. It works like a flow chart though, with the ultimate else result being blank if it doesn't match any of the criteria. I hope this formula does what you need it to, thanks for reading!