Lookup a Dataverse Table Column to another Column in different table?

[DuperGoon]

Hey,

I have multiple tables, some that have some information needed for a different table. I was thinking of using, lets say column A  & B in Table One .I want to do a lookup to link Table One: Column A with Table 2 (different table): Column B. 

So the idea is that Table 2: Column B has the master list, while our TABLE ONE is tied to that specific column. Right now I can only tie to a table and use the primary column of data. Any suggestions? 

[dpoggemann · Accepted solution]

Hi @DuperGoon ,

Hopefully I understand what you are saying here...  Please review and let me know.

1. You can have a column on Table A that is a Lookup to the Primary Name column on Table B.  This is the only way in Dataverse to link the tables is through the Lookup field which will relate to the Primary Name field on that table (behind the scenes it is linked by the GUID to that table of course)...
2. You can add additional columns to table A that can be calculated columns or formula columns that can reference additional fields from table B that you are connected to.  

Option that doesn't copy data to Table A from Table B

1. You can setup your views for Table A to show columns from Table B (under Related when adding fields to table A views
2. You can setup a Quick View form on Table A that will show fields from Table B on Table A's form (https://learn.microsoft.com/en-us/power-apps/maker/model-driven-apps/create-edit-quick-view-forms)

[MD7]

Hi Drew,

I've added a lookup column to Table A which looks up Table B. Now when i add a new column I'm unable to choose any columns in Table B for a formula or calculated column. I only see the table, not the columns within the table as choices. Any ideas? Thanks.

[dpoggemann]

Hi @MD7 ,

Here is an example of how to do this... The calculated field does not support referencing through on all types but this is an example where I am showing connecting to a text field on the account.

Does this help?

[MD7]

Thanks for the reply, Drew.

It looks like the issue i have is that i need the lookup to look up multiple lines of text. The column that I'm trying to look at is paragraphs of text so it looks like this way won't work. Is there another way of doing it? I need the column from table a to have the data from table b in so when i pull out reports, it populates table b's data. 

If i use views and forms i don't think it will?

Thanks.

[dpoggemann]

Hi @MD7 ,

You might want to check out the formula field and apply as a Text Area...  The formula fields are still in preview but my quick test worked as expected (although I did not have a lot of rows of text.  It did respect the layout including CR/LF etc.

[MD7]

Unfortunately no luck.

Nothing appears in the box. Could it be to do with that there will be more than one entry for related record for table b? Ideally i'd like it to show the newest entry added to table b for the related record.

[dpoggemann]

Hi @MD7 ,

You might be thinking of this differently than I am as far as the relationship...

1. You need to be doing this with a relationship where the child record (which is the many side) has a lookup to the parent record.   In my situation I have the Account as a Lookup on my Test1 table. 
2. In the Formula column it will be utilizing the Lookup for the Account and referencing through this single value to pull the field from the Account that is setup as a multiline text field.  
3. You can not do this if you are expecting to pull from a "N" level relationship, must be a single value that is set as a related Lookup value on your current record.