Answered

How can I use columns from a parent customer in a calculation if the relationship field is type 'customer'?

(0) Share

Report

Posted on by jorkington

I need to add a calculated column to my 'Quote' table, that will use columns from the Customer table. However, the column that links to the parent customer is set as 'Customer' data type, and the system won't let me reference it in the calculation.

A basic overview:

A Quote has the fields Customer (customer data type, references the Customer table) and Amount (currency).

A Customer has the field CustAmount (Currency).

I want to add a calculated column CustTotal to Quote, that will be calculated as: Amount + Customer.CustAmount.

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (4)

Answers (1)

cchannon 4,702 Moderator on at

Like (1)

Report

I don't think this is currently possible, but a Preview version of rollup and calculated columns with Power Fx is coming out in the next couple months. I would expect that to be able to cleanly navigate a polymorphic lookup like Customer.

Was this reply helpful? Yes No
jorkington 6 on at

Like (0)

Report

Hmm. Would it be possible to automatically set a lookup column (related to 'account' table) to the account indicated by the customer column and then use that?

Was this reply helpful? Yes No
Verified answer

cchannon 4,702 Moderator on at

Like (0)

Report

Yes, that would work, but if you're going to write a Flow to do that anyway, you might as well just skip the extra lookup and just use Flow to map your Amount + Customer.CustAmount instead of Calculated Columns.

Was this reply helpful? Yes No
EricRegnier 8,720 Most Valuable Professional on at

Like (1)

Report

Hi @jorkington, if I understand correctly, yes with Formula (Power FX) column you can access deeper than one level so you can set that. Hope this helps

Was this reply helpful? Yes No