I am trying to report on my Accounts that have at least one Closed Loan (Status Field = "Closed") as documented on my Custom Loan Object and also have another Loan Page that is in the Underwrting Stage aka Status="Underwriting"
So Accounts with at least one Closed Loan and at least one Loan in Underwriting. I keep getting hanged up on my Cross Filter because I cannot use OR filter logic. I may also be making this more ocmplicated that it seems, hence overlooking a simple solution.
Any thoughts would be greatly appreciated.
4 réponses
Scott, is your Loans object setup in a master-detail (https://developer.salesforce.com/forums/?id=906F00000008mhlIAA" target="_blank) relationship to Accounts? An easy way to tell is if you are required to input a value into the Account lookup field when you try to create a Loan record. If you can't create a Loan record without selecting an Account, odds are there's a master-detail relationship between them. If so, you can utilize the following solution for the report you want to create. This solution requires you to create two fields on the Account object. For each:
- Go into Setup, and type "Account" into the search field
- Under Customize > Account click on Fields
- On the fields page, click the New button
- Select Roll-Up Summary
- Name your first field something like Loans - Closed
- This is the most important step. Once you get to step 3 of the field creation process, you need to select the type of summary field you want to create as well as input some filters.
- For your use-case, you want to select summary type "Count" and set a filter of "Status equals Closed"
- See screenshot below for a rough estimation of what this will look like:
- Repeat steps 1-6 above for a second field, called Loans - In Underwriting
After you've created your 2 roll-up summary fields, you can simply create an Account report with filters that each of these fields be greater than 0.
Of course, if Loan and Account are not in a master-detail relationship a different approach will be required. Let me know how this solution works out for you!