Solved: Re: Lookup column is blank in many-to-on relations...

J_Taylor · ‎10-02-2022

I am trying to create a dataverse lookup column. I tried to follow Shane Young's guide, but it doesn't work for me. Also, I have tried the 'solution' from a similar post, but have it wrong or don't understand it. The documentation isn't much use, either.

I have two tables:

1. College Students

- StudentLastname (primary column) - single line of text

- StudentFirstname - single line of text

- StudentNumber - whole number [unique]

- StudentGrade - single line of text

2. Completion Year

- StudentGrade (primary column) - single line of text ... [unique]

- StudentCompletionYear - whole number

I am trying to setup a many-to-one relationship with College Students and Completion Year. The purpose of this is so that I can just include the StudentGrade (in College Students table) and also see the resultant StudentCompletionYear from table 2.

When I create this lookup column in College Students, the column stays blank. What am I doing wrong here?

Screenshot of Lookup column settings

Screenshot of many-to-one relationship details

Screenshot of blank lookup column

Screenshot of table 2 Completion Year

Screenshot of Excel editting window with lookup column selected

dpoggemann · ‎10-02-2022

Hi @J_Taylor ,

It will not, you will need to select the appropriate year from the Completion Year table and populate on the College Student table. Nothing will populate by default here... If you know the years for each of the students as the completion year you can export all the data to Excel or Excel Online, populate the Completion Year column and then re-import and it will make that relationship for you when you set that column value.

Hope this helps. Please accept if answers your question or Like if helps in any way.
Thanks,
Drew

View solution in original post

dpoggemann · ‎10-02-2022

Hi @J_Taylor ,

If you are unable to do a call...

Upload all your years to the Completion Year table
Export your College Students table to Excel, in the Completion Year field set the values as appropriate (2022, 2023, etc.)
Re-import the College Students table and it will automatically set the lookup appropriately to the Completion year table

Hope this helps. Please accept if answers your question or Like if helps in any way.
Thanks,
Drew

View solution in original post

dpoggemann · ‎10-02-2022

Hi @J_Taylor ,

I read through this a couple times. I think you are setting up your tables in a way that I would modify. Please look at the information below and let me know if this makes sense from a structure for you:

College Student Table

- StudentLastname (primary column) - single line of text

- StudentFirstname - single line of text

- StudentNumber - whole number [unique]

- StudentGrade - single line of text

- Completion Year (Lookup to Completion Year Table)

Completion Year Table

- Year - Note, I would do this as "Text" vs number so you don't see the commas in the year 😀 for the number separator...

The above structure added the lookup to the student table since I am guessing the student is graduating in a specific year and you want to be able to see all the students that graduate in that year by looking at the Completion Year table right?

Hope this helps. Please accept if answers your question or Like if helps in any way.
Thanks,
Drew

J_Taylor · ‎10-02-2022

Thanks @dpoggemann for your quick assistance.

I deleted the relationship and lookup column and tried again. However, I still cannot get it to work (same result). I'm not exactly sure what the difference was from my initial attempt, so maybe I missed the point.

Below is the new relationship info.

dpoggemann · ‎10-02-2022

Hi @J_Taylor ,

You added a lookup column to the College Student for the Completion Year right?

If you did this, what "doesn't work" from here? This will allow you to add a sub-grid (for example) to the Completion Year table for all the College Students that graduated that year...

Hope this helps. Please accept if answers your question or Like if helps in any way.
Thanks,
Drew

J_Taylor · ‎10-02-2022

Yes, I added a column in College Student with the lookup settings.

When I completed it, the column remained blank. I was hoping/assuming that the completing year (some value between 2022 and 2034) will be in that column. Is that the correct expectation?

dpoggemann · ‎10-02-2022

Hi @J_Taylor ,

It will not, you will need to select the appropriate year from the Completion Year table and populate on the College Student table. Nothing will populate by default here... If you know the years for each of the students as the completion year you can export all the data to Excel or Excel Online, populate the Completion Year column and then re-import and it will make that relationship for you when you set that column value.

Hope this helps. Please accept if answers your question or Like if helps in any way.
Thanks,
Drew

J_Taylor · ‎10-02-2022

Sorry, @dpoggemann, I don't know what you mean. Are you saying I should just import all the years and create a new column?

dpoggemann · ‎10-02-2022

Hi @J_Taylor , Can I private message you and we can do a quick teams meeting and work through? I will do, let me know your email if this works in the message and we can review...

Hope this helps. Please accept if answers your question or Like if helps in any way.
Thanks,
Drew

dpoggemann · ‎10-02-2022

Hi @J_Taylor ,

If you are unable to do a call...

Upload all your years to the Completion Year table
Export your College Students table to Excel, in the Completion Year field set the values as appropriate (2022, 2023, etc.)
Re-import the College Students table and it will automatically set the lookup appropriately to the Completion year table

Hope this helps. Please accept if answers your question or Like if helps in any way.
Thanks,
Drew

J_Taylor · ‎10-02-2022

Thanks, @dpoggemann.

It makes sense that the lookup column needs to be the grade, not the year. I thought it would display the year if had it as the lookup. However, Views are completely different and it needs to be connected through grade as the lookup.