Challenging problem with :group by/aggregate and fields derived from other fields

I’ve been designing a screen that’s going to be using a lot of group by/aggregate fields. For the first RG I’ve built, the “sum” fields were not fields as such, but the results of a calculation of two other fields:

  • quantity
  • unit price

I found I couldn’t sum on this of course, so what I did (rightly or wrongly) was add a new field called “total amount”, but then I needed this to be updated whenever “quantity” or “unit price” change - so I added this into the workflow of the sheet where the user adds/edits those two values (this is a financial model that the user builds).

So far, so good in that it all works - however, I now need to create another RG doing a similar thing but based on different fields - and the “total amount” this time could change based on the user changing any one of a number of fields, to the point that it’s going to get pretty messy…so I wondered whether there is any way that fields in the database can be defined to ALWAYS be automatically the result of other fields? Or that I can define the “total” amount as some kind of temporary field to use for the aggregate feature?

Just to add some more detail/explanation:

The “Revenue” model (the one I have done already) has 24 cells (representing 24 months)

  • if the user changes the quantity for any of those 24 months, the “total amount” for that month is calculated as (quantity * unit price) where unit price comes from the product on that RG row
  • if the user changes the unit price for the product, the “total amount” for ALL of those 24 months changes as a result

This is still quite untidy and it seems having “total amount” as a calculation rather than an actual field would be SO much easier…but then I can’t use it as an aggregate on my :group by

The next RG is calculating COGs value - and the “total amount” for a given revenue line for a given month would change if ANY of these change:

  • revenue quantity for that month
  • COGS item unit cost
  • COGS item quantity used per {number of items} of sale
  • COGS {number of items}

I’m trying to avoid spending what seems like wayyy too much time setting up all these possible workflow combinations, if there’s an easy way to just have a field be dynamic…

Thank you!

Hello @debbie

Explore database triggers :smiley:

Below … an excellent resource to learn how to approach them corectly

Thank you - I did look at that, but it seems it would take the same amount of work as setting up all the workflow features (which I have set already for my “revenue” RG and works well). What I’m really looking for is not to have to do all the definition work…

This topic was automatically closed after 70 days. New replies are no longer allowed.