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
StenSten 

I need help on developing formula

I want to create a report on both the rating field in accounts and due date in Task field .
This report is based on areas I have not covered for the new months.
The areas are base on the Rating picklist fields in accounts named Rating X, Rating Y and Rating Z, status completed and due date in Task
I need to write a formula to give me these information
Rating x = 30days from the due date
Rating Y = 40 dayds from the due date
Rating Z = 50 days from the due date.
Example, if the due date was March 15 th, for Rating X, The next tiuch will be 30 days from the last due date
Then I intend using the formula field to create the report and dashboard.
I need help in creating this formula and any suggestion to a better approach will be well appreciated
Thank you
 
Best Answer chosen by Sten
Waqar Hussain SFWaqar Hussain SF
Hi,

So you need a formula field to calculate Next touch date on account, so you have rating on account and due date on account related task. 

Actually in formula field, we can not use related (task) fields, because an acocunt can have multiple tasks. If the due date field is on Account then you can create formula otherwiese you will have to develop trigger to calculate this by getting the latest task's due date from the related account.

Let me know If you have any question.

Thanks

All Answers

Waqar Hussain SFWaqar Hussain SF
Hi,

So you need a formula field to calculate Next touch date on account, so you have rating on account and due date on account related task. 

Actually in formula field, we can not use related (task) fields, because an acocunt can have multiple tasks. If the due date field is on Account then you can create formula otherwiese you will have to develop trigger to calculate this by getting the latest task's due date from the related account.

Let me know If you have any question.

Thanks
This was selected as the best answer
StenSten
Thank you Waqar for your assistance. The due date field is on account related task. So, are you saying I cannot use the various task due date field unless I create a trigger? My goal is to generate a report on this. Is there any other way of achieving this? maybe my thoughts in creating a formula field to use in the report is wrong. Please can you suggest another way of approaching this if you know. Thanks
Waqar Hussain SFWaqar Hussain SF
Basically you can not create formula either on account to get task field nor you create formula field on task to get the account info.

You cannot reference polymorphic lookup fields (WhoId & WhatId) in a formula because they could be pointing to one of many possible entities.

You will have to develop a trigger on task object to calculate the next due date on each task. You can develop trigger on before insert event of task, so whenever the task will be create system will populate next due date field using trigger based on account rating and task due date. 

 
StenSten
@Waqar, thank you for this great help. This solved the problem.