cancel
Showing results for
Did you mean:
Helper V

## 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
Multi 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
Multi 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
Helper V

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!

Multi 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
Helper V

You were so awesome! Thanks a bunch 🙂

Announcements

#### Celebrating the May Super User of the Month: Laurens Martens

@LaurensM  is an exceptional contributor to the Power Platform Community. Super Users like Laurens inspire others through their example, encouragement, and active participation. We are excited to celebrated Laurens as our Super User of the Month for May 2024.   Consistent Engagement:  He consistently engages with the community by answering forum questions, sharing insights, and providing solutions. Laurens dedication helps other users find answers and overcome challenges.   Community Expertise: As a Super User, Laurens plays a crucial role in maintaining a knowledge sharing environment. Always ensuring a positive experience for everyone.   Leadership: He shares valuable insights on community growth, engagement, and future trends. Their contributions help shape the Power Platform Community.   Congratulations, Laurens Martens, for your outstanding work! Keep inspiring others and making a difference in the community!   Keep up the fantastic work!

#### Announcing Power Apps Copilot Cookbook Gallery

We are excited to share that the all-new Copilot Cookbook Gallery for Power Apps is now available in the Power Apps Community, full of tips and tricks on how to best use Microsoft Copilot as you develop and create in Power Apps. The new Copilot Cookbook is your go-to resource when you need inspiration--or when you're stuck--and aren't sure how to best partner with Copilot while creating apps.   Whether you're looking for the best prompts or just want to know about responsible AI use, visit Copilot Cookbook for regular updates you can rely on--while also serving up some of your greatest tips and tricks for the Community. Check Out the new Copilot Cookbook for Power Apps today: Copilot Cookbook - Power Platform Community.  We can't wait to see what you "cook" up!