Lookup column is blank in many-to-on relationship in Dataverse tables

[J_Taylor]

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]

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?

[J_Taylor]

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]

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...

[J_Taylor]

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 · Accepted solution]

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.

[J_Taylor]

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]

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...

[dpoggemann · Accepted solution]

Hi @J_Taylor ,

If you are unable to do a call...

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

[J_Taylor]

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.