web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics CRM (Archived) / Need to create a field...
Microsoft Dynamics CRM (Archived)

Need to create a field with Invoice total for last 12 months

(0) ShareShare
ReportReport
Posted on by Helweh

Hello everyone,

I need to create a field that has the total invoices related to accounts for the last 12 months is this possible?

The reason I want to create this field is so I can rate the accounts based off of total revenue for that year (bronze, silver, gold). 

I have been trying to come up with a way to do this using calculated fields. Any help would be greatly appreciated.

Thanks in advance! :)

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    Aiden Kaskela Profile Picture
    Aiden Kaskela 19,696 on at

    Hi!

    There are two different issues to solve here - rolling up invoices and setting revenue.

    For rolling up invoice amounts, you should be able to use a rollup field and add a filter to limit it to either the last 12 months or define a date range. Check out this TechNet article:

    technet.microsoft.com/.../dn832162.aspx

    Once you have the invoice amounts rolled up to the invoice, you'll need to set the medal level for the account. You can't trigger a workflow on the rollup field changing, but if it's a one-time update (or once a year), there's a pretty easy process. Add a new field to the account, "2015 Spending Level" or something. Write a simple workflow that looks at the calculated amount and sets the spending level to Gold, Silver, or Bronze. Then do an advanced find for all invoices that don't have a value there, but they have an invoice from 2015 and run the workflow on that list of recurds. It'll be a little tedious but you'll get the results you're looking for.

    Hope this helps! I'd appreciate if you'd mark this as a Verified answer.

    Thanks,

     Aiden

  • Helweh Profile Picture
    Helweh on at

    The filter has to be set to a specific date in time. It is not like advanced find that you can tell it to pull all invoices in the last x amount of months. It requires you to enter a specific day or reference another field date and time.

    Running it once a year kind of defeats the purpose, I need to keep the information of what revenue category that account falls into as up to date as possible. I have seen it done in the past. I believe it was done with JavaScript

  • Verified answer
    Aiden Kaskela Profile Picture
    Aiden Kaskela 19,696 on at

    There are more than a few ways to implement this but it depends on your needs.

    If the field needs to be recalculated when somebody opens a form, you can query for the invoices via JavaScript to get the total amount, and set that and the Medal on the account form (query details: msdn.microsoft.com/.../gg985387.aspx). You could also use FetchXml to get back the invoice totals, aggregating the total to get the single value. Here are details to execute FetchXml in JavaScript: arunpotti.wordpress.com/.../retrieve-records-using-fetch-xml-java-script-in-crm-201113

    Since that solution is in JavaScript, it'll only recalculate when you open an account's form. If you need to do an advanced find or other reporting on that field, you'll need to keep that field up to date using server-side plugins. You could write a workflow that runs on the account record every time the account is updated or an invoice is updated, and a custom step in the workflow would be to recalculate the Medal level. This wouldn't always be exactly correct though because an invoice could go outside that 1 year range, and the amount wouldn't have a trigger to recalculate.

    If you need the medal level to always be up to date, you can do a combination of the above steps but you also need a scheduled process to run and check that natural expiration condition. The most efficient way would be to look for accounts that have an invoice that's {1 year + 1 day} old, but the account hasn't been updated in the last day. You should only get back the accounts that truly need to be recalculated.

    Hope this helps! Let me know if I misunderstood your scenario or I need to clarify anything. I'd appreciate if you would mark this as a Verified answer.

    Thanks,

     Aiden

  • Helweh Profile Picture
    Helweh on at

    Thank you for your help! Really appreciate it.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics CRM (Archived)

#1
SA-08121319-0 Profile Picture

SA-08121319-0 4

#1
Calum MacFarlane Profile Picture

Calum MacFarlane 4

#3
Alex Fun Wei Jie Profile Picture

Alex Fun Wei Jie 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans