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

How to check if a record from the past week exists via a Salesforce Formula?

Hi guys,

I have a Custom Object called Weekly Plan which has a Date field called "Start of Week", a User Field (lookup) as well as other fields. The Date Field "Start of Week" always starts with Sunday. Example: 5th Feb 2023, 12th Feb 2023, and so on.

On the Contact Page, I have a checkbox called "Transfer Ownership". There is an associated Work Flow Rule that will transfer ownership of the Contact Record to someone else ONLY if there is an entry under Weekly Plan under this user for the previous Sunday. For example. if a user wishes to own a record today (say 07 Feb 2023), then the workflow rule should ONLY transfer ownership if a record under this user exists for a Weekly Planner for 29th Jan 2023 (previous week's Sunday).

Any idea how to accomplish this? Basically, I need to check the existence of a record under a Custom Object for the previous week's Sunday. If it exists, it can go ahead. I am not sure how to get to the "previous week Sunday" automatically or to check the existence of a record under a Custom Object in a Workflow Rule.

Thank you!
Sai PraveenSai Praveen (Salesforce Developers) 

In the above scenerio I dont think you can achive this using workflow rule. As you need to stop user from changing the owner if that data does not exist which means you need to validate this and also based on some other object. 

So you may need trigger logic for this .

Let me know if you face any issues.

If this solution helps, Please mark it as best answer.

Shri RajShri Raj

You can use a Formula Field on the Contact object to check if a Weekly Plan record exists for the previous week's Sunday.
Start_of_Week__c >= LAST_WEEK(TODAY())

In this formula, you can see that the conditions are checked to make sure that the "User__c" and "Start_of_Week__c" fields are not blank or null, and the "Start_of_Week__c" field must be greater than or equal to the last week's Sunday (LAST_WEEK(TODAY())). If all conditions are met, then the formula returns "1". Otherwise, it returns "0".