Discussions

 View Only
Expand all | Collapse all

Have a calculated field roll up under a different month than original record

  • 1.  Have a calculated field roll up under a different month than original record

    Posted 10-05-2017 23:30
    Does anyone have a good suggestion how you can break out the output from a calculated field to either post to a new record or somehow have it roll up under a different month?

    Use Case:
    Budget Owners have to input [Estimated Amount] and [Expense Month], let's say it is $100 in Month 1.

    Once the final invoice arrives they'll have to input that amount in the same record but in the fields [Final Amount] and [Invoice Month], let's say that is $110 in Month 2.

    Since the books for Month 1 have already closed we'll have to post an adjustment of the difference in Month 2.
    There are Formula fields which will populate an [Adjustment] = $10,  [Adjustment Month] = 2

    From a roll-up point of view, how do I get this record to add the different amounts to two different Months?
    I figured one way would be to automatically create a child record of all adjustments and have that summary field to be added.
    All reporting will look at the total cost by [Expense Month], but I'm not sure how to get the [Adjustments] by [Adjustment Month] to get added to that.

    Any clever suggestions are very welcome!

    Thanks,
    Freddy


  • 2.  RE: Have a calculated field roll up under a different month than original record

    Posted 10-13-2017 16:33
    Figured I would share the solution in case someone else have a similar problem.

    The solution was to use Quick Base Action. I set it up so that a new record is created whenever a current record is modified and the calculated Adjustment is not equal to blank (plus a few other conditions that were specific to our situation).
    By using Quick Base Action I could have another record created and populate the fields that we use to summarize by in the Summary Reports.

    Quick Base Action offers a ton of great functionality so if you haven't checked it out yet I strongly suggest you do!

    Best,
    Freddy