Hi,
I use Microsoft Dynamics 2016 on-premise and I want to create a report for our sales which shows the most recent completed activity per open opportunity. I think that the relationship between opportunities and activities is quite complicated. There are two alternatives: One is a direct relationship between opp and activity. The other is that the opp is related to an account and the account has n contacts and those contacts may also have activities.
I managed to create a ReportServer report which accomplishes this but I have another solution in mind:
First I would add a custom field lastestCompletedActivity to an opportunity that stores a pointer to an activity.
Then I would want to create a process.
This process should get triggered every time when a new activity is completed. Then the process should find an optionally available opportunity to this activity (either by regarding to-relationship or by going from the contact to the account to the opportunity). Then it should check the latestCompletedActivity's actualend date and compare it to the current activity's actualend. In case the newly completed activity is "newer" then the one stored in the custom field then the lastestCompletedActivity field should store the pointer the newly completed activity
Is that possible with processes? If yes, how can I accomplish that?
In case this is not possible using "Processes" I would like to reuse my ReportServer query to update a custom field in the MSCRM database. I know that updating fields directly via DB queries is not allowed. My problem is how could I automate the process of updating a custom field with a rather complex SQL query in any other compliant way.
Finally, I wonder how other sales teams accomplish this task:
I think checking the most recent completed activity regarding opportunities is a quite common task but there seems not standard report available in MSCRM, is there?
If you know any other Dynamics CRM forums please let me know.
