Answered

Dataverse table with Calculated column using a related table field

(0) Share

Report

Posted on by MATMASS

Hi,

I'm a newbie on all this. Apologies in advance if this is really a simple question. I have been looking through the documentation and trying with Copilot for hours without any luck.

I have two tables "Clients" and "Proposals" as follow:

tblClients

Name	Code
Company A	101
Company B	253
Company C	305

tblProposals

Title	Client (Look up)	Request date	Proposal Code
Proposal A	Company A	02/10/24	101-241002
Proposal B	Company C	15/11/25	305-251115
Proposal C	Company C	01/03/26	305-260311

I'm struggling with the creation of the "Proposal Code" calculated column based on the selection on the Lookup column (tblProposals.Client) and the Request date (format YYMMDD).

I also want "Proposal Code" column to be the main column to be the reference for Proposals lookup in other tables.

Thanks in advance.

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (3)

Answers (2)

Verified answer

Sunil Kumar Pashikanti 1,353 Moderator on at

Like (0)

Report
Copy link

Link copied!
Hi @MATMASS,

Use a Dataverse Formula (fx) column (Power Fx) on tblProposals, it can reference fields from the related lookup record and do string math. Avoid Text(Date) (not supported in all formula-column contexts); build the date parts with Year/Month/Day and format each to two digits.

Formula column (Text) on tblProposals

Concatenate( 'Client'.'Code', "-", Right(Text(Year('Request date'), "####"), 2), // YY Text(Month('Request date'), "00"), // MM Text(Day('Request date'), "00") // DD )

'Client' is the lookup column name on Proposals; accessing .'Code' pulls the client’s code.
This avoids the known limitation where Text(DateValue) isn’t allowed in formula columns.
Formula columns are the current, recommended way to compute values in Dataverse.

Dataverse lookups show the table’s Primary Name column by default. You can’t set a formula column as Primary Name; Primary Name must be Text or Autonumber.

Two practical options:

1) Copy Proposal Code into Primary Name (Text) using a tiny Power Automate flow (on create/update of Client or Request date). Then lookups will show the code.
2) Keep your Primary Name as an Autonumber and add “Proposal Code” to the Lookup View so users see it alongside the name (they’ll still click the primary).

REferences:

https://community.powerplatform.com/forums/thread/details/?threadid=574a8266-d68a-4fae-8c9c-13c8a2feda87

https://learn.microsoft.com/en-us/power-apps/maker/data-platform/formula-columns

https://community.powerplatform.com/forums/thread/details/?threadid=7a64fced-165d-499b-8186-2bd3122c283c

https://www.spguides.com/dataverse-primary-name-column-autonumber/

https://community.dynamics.com/blogs/post/?postid=3569f9b9-7f1b-f011-9989-6045bdd98975

✅ If this answer helped resolve your issue, please mark it as Accepted so it can help others with the same problem.
👍 Feel free to Like the post if you found it useful.

Was this reply helpful? Yes No
MATMASS 6 on at

Like (0)

Report
Copy link

Link copied!

Sunil,

Thanks for your answer.

However, I cannot get the code to work. The first two arguments in the Concatenate function work fine ("Client.Code" and the "-").
The second part for extracting the date in YYMMDD format does not work., I get an error as shown below:

Was this reply helpful? Yes No
Verified answer

scalca 252 Moderator on at

Like (0)

Report
Copy link

Link copied!

check this blog post

PowerFx: Overcome time zone conversion error in Dataverse Formula column - Burgers & Bytes

you have to use Time zone adjustment: Time zone independent
obviously it works also if you have date only field as well

Was this reply helpful? Yes No