Answered

Calculate with value in another table

(0) Share

Report

Posted on by ChannelDevMike

255

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)

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (4)

Answers (1)

Mira Ghaly 11,415 Moderator on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
ChannelDevMike 255 on at

Like (0)

Report
Copy link

Link copied!

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

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!

Was this reply helpful? Yes No
Verified answer

Mira Ghaly 11,415 Moderator on at

Like (1)

Report
Copy link

Link copied!

@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

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

Was this reply helpful? Yes No
ChannelDevMike 255 on at

Like (0)

Report
Copy link

Link copied!

You were so awesome! Thanks a bunch 🙂

Was this reply helpful? Yes No