I want to use the a group aggregate result as inpu...

Alina_Negulescu · 03-13-2021 08:14 AM

Hi,

I find AppSheet amazing but I think I deep dived into it before I started to understand it well enough. Now I need some help, and I’d appreciate yours - so thank you in advance.

My app is a containing 3 forms and then should use the input to perform calculations.
Namely, I have a customer table, a role table (connected to a customer), a process table (connected to customer) and step table (connected to the process).

I want the user to add a new customer, add the process for that customer, and the steps for the process.
The calculations I’m interested in are the following:

cost per step (which is why the role table is important because it has an assigned wage per role), and then the cost per process which is the sum of the steps’ cost.
cost per process (which is the sum of all the added steps that belong to a certain process, that belongs to a certain customer)
monthly process cost - which should take the cost per process and multiply it with a variable.

I managed to calculate:

cost per step - that was fairly easy
cost per process - but only from the aggregated view by grouping all steps to a process (I’d love to actually calculate it inside of a column and not in the view)

What I’m struggling with and please assist:

monthly process cost.
One idea I have is to access the cost per process from the aggregated view calculation and use it in a column where I would multiply it with a user-given variable.
I feel that I should probably use an extract, look, if…some more complex expressions, and I don’t know where to start with.

If you can help me figure a simple way for the entire app, would be wonderful - but I can live also with a simple fix on how to get that aggregated calculation from the view imported into a column in order to be used as an input for further calculation.

Thank you and I’m grateful!
Alina

Marc_Dillon

Assuming the Step table has a Ref type column pointing to the Process table, then it is as simple as:
SUM( [Related Steps][Cost per step] )
in a Virtual Column in the Process table.

Alina_Negulescu

Thanks, Mark! I did learn something, however, the calculation I make is in a new table. Maybe it was worth mentioning.
So, I have a “Calculate table” where the fields from the form are the following:

process (select a process) → which by selecting it, should act like a category for steps
auto-fill the cost per process (by making a sum of all the steps related to it)
monthly iterations - a variable add by the user
auto-fill monthly proces cost - by multiplying cost per process with monthly iterations.

Thanks again,
Alina

Marc_Dillon

I want to use the a group aggregate result as input in a column for further calculations