Any suggestions generally on how to perform more "complex" calculations in a formula?

  • 0
  • 1
  • Question
  • Updated 4 years ago
  • Answered

Any suggestions generally on how to perform more "complex" calculations in a formula? I have a "history" field that gets updated with one of several status values (status1, status2, etc.) regularly for a given record. Status is not "linear" in the sense that the status value can change then revert back to an earlier value. We'd like to have a field, [Status1 Count], that calculates the number of times the history field recorded status as "Status1" (and so on for other status values). Here is what the history field might look like after a week or two:

[date-time1 user] status1

[date-time2 user] status2

[date-time3 user] status3

date-time4 user] status2

[date-time5 user] status3

[date-time6 user] status2

So, as of the last entry:

[Status1 Count]=1

[Status2 Count]=3

[Status3 Count]=2

I don't see a clean way to do this with formula functions.

Photo of Chris

Chris

  • 0 Points

Posted 4 years ago

  • 0
  • 1
Photo of Harrison Hersch (MCF)

Harrison Hersch (MCF), Champion

  • 40 Points
You might want to consider a more scalable approach to this as using the formula will get kind of messy over time as you are seeing. It also requires a series of extra hard-coded fields. Adding an extra level of hierarchy, called a "Status History" table would be best. You can then have functions that create status records and can derive the current status on your parent table.

That being said, we certainly do complex formulas all of the time ranging from 10 lines to 100+ lines. Certain use cases though are more flexible by addressing at the architecture level.

Feel free to reach out if you would like to discuss this further: www.mcftech.com.