Solved: lookup column return other than primary column.

AdnanDev · ‎10-05-2023

Hello there, I am working with dataverse tables using list rows action in which I have a lookup column for the table of "roles" in "school_user" table. I have a "roles" column in role table as choice data type like student, teacher and parent, and in school_user table has a lookup column for role selection. What I am trying to do is to make a csv file of data saved in school_user table and for which using csv table action and by using custom method I am giving header name and values but for header of role I want value to be of "roles" column like student teacher not the role id(primary column) which eventually comes as a lookup column. Can I use any reference kind of formula which will give me another column(role) other than primary column of lookup table(role id).

FLMike · ‎10-06-2023

Hi @AdnanDev

Here is an Example I built

I have 2 tables.

Table A is primary
Table B is secondary
I have a relationships 1:N from A to B.
In Table B, the relationship column is named TableA_TableB (yes I know its a dumb name but its what I called it). The name of this column is SUPER important. So you must know what your Relationship Column Name is. Its what you see in the Column when you look at the Data in Dataverse. Like mine here
Where in TableA_TableB you see My Record. That is my lookup to TableA, where i have a row called My Record. (again horrible name, but thats it)

Now in the Flow

My goal is to get the Description column data from TableA. This will demonstrate how you retrieve other columns from a LookUp Primary Table, from a flow, with a Secondary Table LookUp Column

Step 1. I created a manual Flow

Step 2. I add a List Rows (Dataverse)

-I set the Table to TableBS (this is because the Table is called TableB, but Dataverse shows it as TableBS)

-I added a filter to only pull records where the Name column eq 'Item 1 Name'

Step 3. I added an Apply to each to loop through all the records from the List Rows

-I set the Input equal to the Dynamic value Property in the PopUp window of Properties, from the Get Rows action. You can see I did rename mine from the default "Get a row by Id" name

Step 4. Add a "Get a row by id" dataverse action.

-in the Table, I added TableAS, again its called TableA but Dataverse shows TableAS

-for the Row Id

--Click inside the box

--the popup window shows up.

--find the Dynamic Properties for your List Rows (again I renamed mine).

--Under those properties, you need to find a property that looks like this

Mine looks like this because my Relationship Column in TableB is called TableA_TableB and I want the (Value) which is the GUID of the primary table

Step 5. I added a Compose action, because I want to display the Description property from TableA, that I received from my Get a row by ID (again I renamed mine).

-Click inside the Input box for the Compose

-The popup window shows for Properties, I find the Description under the Get a row by ID action in the Properties Windows and click it.

Now I run my flow and you can see it ran perfectly and I was able to read the Description Property from TableA, based on the GUID in the LookUp column in TableB that points to TableA Row.

You can read any column you want by doing what I showed here

Please take the time to Mark my answer as Resolved and maybe even also do a Kudos Thumbs up

Cheers
If you like my answer, please Mark it as Resolved, and give it a thumbs up, so it can help others
Thank You
Michael Gernaey MCT | MCSE | MCP | Self-Contractor| Ex-Microsoft
https://gernaeysoftware.com
LinkedIn: https://www.linkedin.com/in/michaelgernaey

View solution in original post

FLMike · ‎10-05-2023

Hi @AdnanDev

Since you are using Dataverse, and the lookup column has the GUID of the actual record in the other table, do a Get a row by Id where the ID is the value in the lookup column.

Then grab the data you want from that row. You can even limit the columns to just the ones you want.

Cheers
If you like my answer, please Mark it as Resolved, and give it a thumbs up, so it can help others
Thank You
Michael Gernaey MCT | MCSE | MCP | Self-Contractor| Ex-Microsoft
https://gernaeysoftware.com
LinkedIn: https://www.linkedin.com/in/michaelgernaey

AdnanDev · ‎10-06-2023

Can you please elaborate more on how I can do this by get row by id?

FLMike · ‎10-06-2023

Hi @AdnanDev

Here is an Example I built

I have 2 tables.

Table A is primary
Table B is secondary
I have a relationships 1:N from A to B.
In Table B, the relationship column is named TableA_TableB (yes I know its a dumb name but its what I called it). The name of this column is SUPER important. So you must know what your Relationship Column Name is. Its what you see in the Column when you look at the Data in Dataverse. Like mine here
Where in TableA_TableB you see My Record. That is my lookup to TableA, where i have a row called My Record. (again horrible name, but thats it)

Now in the Flow

My goal is to get the Description column data from TableA. This will demonstrate how you retrieve other columns from a LookUp Primary Table, from a flow, with a Secondary Table LookUp Column

Step 1. I created a manual Flow

Step 2. I add a List Rows (Dataverse)

-I set the Table to TableBS (this is because the Table is called TableB, but Dataverse shows it as TableBS)

-I added a filter to only pull records where the Name column eq 'Item 1 Name'

Step 3. I added an Apply to each to loop through all the records from the List Rows

-I set the Input equal to the Dynamic value Property in the PopUp window of Properties, from the Get Rows action. You can see I did rename mine from the default "Get a row by Id" name

Step 4. Add a "Get a row by id" dataverse action.

-in the Table, I added TableAS, again its called TableA but Dataverse shows TableAS

-for the Row Id

--Click inside the box

--the popup window shows up.

--find the Dynamic Properties for your List Rows (again I renamed mine).

--Under those properties, you need to find a property that looks like this

Mine looks like this because my Relationship Column in TableB is called TableA_TableB and I want the (Value) which is the GUID of the primary table

Step 5. I added a Compose action, because I want to display the Description property from TableA, that I received from my Get a row by ID (again I renamed mine).

-Click inside the Input box for the Compose

-The popup window shows for Properties, I find the Description under the Get a row by ID action in the Properties Windows and click it.

Now I run my flow and you can see it ran perfectly and I was able to read the Description Property from TableA, based on the GUID in the LookUp column in TableB that points to TableA Row.

You can read any column you want by doing what I showed here

Please take the time to Mark my answer as Resolved and maybe even also do a Kudos Thumbs up

Cheers
If you like my answer, please Mark it as Resolved, and give it a thumbs up, so it can help others
Thank You
Michael Gernaey MCT | MCSE | MCP | Self-Contractor| Ex-Microsoft
https://gernaeysoftware.com
LinkedIn: https://www.linkedin.com/in/michaelgernaey