cancel
Showing results for
Did you mean:
Resolver I

## Calculate with value in another table

How do you specifically take a column from one table to use as a calculation in another table? For instance, I have a product table. A customer buys many products. I need to take the list price of product and times it by the quantity a customer is buying. How can I make column with this calculation? (and have it inside the customer table.. to keep track of all purchases)

1 ACCEPTED SOLUTION

Accepted Solutions
Dual Super User

As per our teams conversation please see the below steps that helped fixing the problem.

1. On the Custom Quote Product Add a Total Amount Hidden

2. Set the Total Amount Hidden to be the Rollup Aggregate on the Custom Quote

3. Set the Calculated Field as below

3. Add the Product Total and Total Amount hidden to the form of custom Quote Product.

4. Hide Total Amount Hidden Column

5. Business Rule that sets the Total Amount Hidden to be equal to Product Total

If this post helps you with your problem, please mark your as Accepted solution.If you like my response, please give it a Thumbs Up.

Blog: here
4 REPLIES 4
Dual Super User

1. Where are you storing product price?

2. Can you share how your data is structured?

But what you are talking about can be a mixture of calculated field on the purchase table where you multiply product price by quantity

On the customer table you will need a roll-up field that sums up the calculated column on the purchase table.

If this post helps you with your problem, please mark your as Accepted solution.If you like my response, please give it a Thumbs Up.

Blog: here
Resolver I

I was trying to simplify my question because my other questions weren't getting answered. But real data is this..

A customer is getting a quote. The quote looks like this:

Relationship data = One customer to many Quotes

Then in the subgrid, I have quote product. And Relationship data = One quote to many quote products.

Quote Products looks like this:

Relationship data = One Product to many quote products.

I was trying to make a calculated field "Product total" That would take "list price" (from product table) and multiply it by quantity in quote products.

Then, once that is done. To use a rollup in Quote that will sum all the "Product totals". (Which I believe I can get that part working).

1. Product price (real data = list price) is in the product table.
2. Did this explain how its structured or is there another way that suits your preferences? Sorry, still learning how to explain problems!

Dual Super User

As per our teams conversation please see the below steps that helped fixing the problem.

1. On the Custom Quote Product Add a Total Amount Hidden

2. Set the Total Amount Hidden to be the Rollup Aggregate on the Custom Quote

3. Set the Calculated Field as below

3. Add the Product Total and Total Amount hidden to the form of custom Quote Product.

4. Hide Total Amount Hidden Column

5. Business Rule that sets the Total Amount Hidden to be equal to Product Total

If this post helps you with your problem, please mark your as Accepted solution.If you like my response, please give it a Thumbs Up.

Blog: here
Resolver I

You were so awesome! Thanks a bunch 🙂

Announcements

#### Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

#### Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

#### European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

#### Changes to Ideas Coming

We are excited to announce a new way to share your ideas for Power Apps!

Top Solution Authors
Top Kudoed Authors
Users online (1,593)