cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ChannelDevMike
Resolver I
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

@ChannelDevMike 

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

 

Mira_Ghaly_0-1664400252980.png

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

View solution in original post

4 REPLIES 4
Mira_Ghaly
Dual Super User
Dual Super User

@ChannelDevMike 

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

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:

ChannelDevMike_0-1664397010816.png

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:

ChannelDevMike_1-1664397285012.png

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).


For your questions directly, 
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!

@ChannelDevMike 

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

 

Mira_Ghaly_0-1664400252980.png

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

You were so awesome! Thanks a bunch 🙂 

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

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

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

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.

Power Apps Ideas

Changes to Ideas Coming

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

Users online (1,593)