locked
Rollup using Today's Date RRS feed

  • Question

  • I've asked this question here but haven't received a response so thought I'd try here.

    I can't figure out how to get this requirement to work probably. It will probably need a mix of plugins and workflows.

    The requirement is the have a field on the account form which totals the number of overdue invoices. This can then be used in dashboards to highlight certain accounts.

    Anyway, I've tried using the new rollup field type but can't seem to use 'Todays' date. Similarly I can't seem to create a calculated field on the Invoice which automatically updates to 'Today' to allow a comparison. Finally, I can't seem to trigger a workflow on a calculated or rollup field. Can I trigger a plugin on these field types?

    The only solution I can think of is to have an 'overdue' two option field on the invoice. Then use a custom entity that has a two option field. A workflow runs every 8 hours to switch between yes and no which triggers a plugin. The plugin the checks for invoices that are open, overdue is no and the overdue date has passed. For each invoice, then set the overdue switch to yes. The rollup field on the account counts the number of open invoices where overdue equals yes. But this seems a little long winded and messy

    Any advice greatly appreciated.



    • Edited by ChrisJC119 Monday, February 2, 2015 4:30 PM more info
    Monday, February 2, 2015 3:26 PM

All replies

  • You could create a console application that looks up the account then checks all the related invoices for that account, count up the overdue invoices based on the current date and time, then update the account with that number before moving to the next account.

    Then run it as a scheduled task on the server.  Depending on the number of records, you may want to run it once at something like 2am each day when no one is on the system.  After testing, if it runs quick, you could potentially have it run before the start of business and during lunch so that the field is updated twice a day.


    Jason Peterson

    Monday, February 2, 2015 10:23 PM
  • Here's a workflow option which is based on your idea:

    1. Add an overdue field to invoices (Yes/No defaults to No)
    2. You would then create a custom workflow which is triggered by Invoice Create and waits until the invoice due date.  If the invoice is still open, set the overdue field to yes.
    3. You would also need a second workflow which sets overdue to no once the invoice is paid.
    4. You could then use your rollup field.

    A screenshot of what the timeout workflow might look like:

    

    Wednesday, February 4, 2015 6:21 PM